Bind Peeking By Example (Doc ID 430208.1)

Last updated on NOVEMBER 10, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
Checked for relevance on 26-mar-2013


Goal

The purpose of this note is to demonstrate the concept of bind peeking. For more details about bind peeking, refer to:

<Document 387394.1> Query using Bind Variables is suddenly slow.

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-07
Chapter 11 The Query Optimizer
Section 11.1.3 Bind Variable Peeking
http://docs.oracle.com/cd/E36909_01/server.1111/e16638/optimops.htm#i79423

Bind peeking was introduced in Oracle 9i and 10g.The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature allows the optimizer to determine the selectivity of any WHERE clause condition.

Up to 10g, no further peeking takes place on subsequent invocations of the cursor and the cursor is shared based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

From 11g, the adaptive cursor sharing feature enables a single statement that contains bind variables to use different execution plans for different binds.
For more information on Adaptive Cursor Sharing See

<Document 740052.1> Adaptive Cursor Sharing: Overview
<Document 1518681.1> FAQ: Adaptive Cursor Sharing (ACS) Frequently Asked Questions


Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-07
Chapter 11 The Query Optimizer
11.1.3.1 Adaptive Cursor Sharing
http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF95174

 

This document demonstrates Bind Peeking as it exists prior to 11g without the option of Adaptive Cursor Sharing.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms