My Oracle Support Banner

Bind Peeking By Example (Doc ID 430208.1)

Last updated on MARCH 01, 2024

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
 








Goal

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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

To view full details, sign in with your My Oracle Support account.

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

In this Document
Goal
Solution
 Setup
 Example
 Conclusion
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.