BIND VARIABLE PEEKING : fonctionnement

Très bon article sur le peeking :

To peek or not to peek

 
Un autre excellent lien sur le bind variable peeking :

Flushing a cursor out of the Library Cache


Extrait de la doc Oracle 10gR2 :

From Chapter 13 of the Performance tuning Guide:
 
“13.4.1.2 Peeking of User-Defined Bind Variables
 
The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement. Bind peeking works for a specific set of clients, not all clients.”

Oracle 11g :
 

Solution définitive aux problèmes de bind varable peeking en 11g avec "Adaptive Cursor Sharing"

Adaptive Cursor Sharing : explication