The Curious Case of Bind Peeking

In my previous post I shared cursor’s role in SQL execution, and how cursors sharing is useful in case of bind variables. My this can lead to bind peeking also, what is bind peeking? is it good or bad? Will blog all in this post.

As per oracle “with bind variables, you can write a SQL statement that accepts inputs or parameters at run time, a bind variable is a simple placeholder. It’s the equivalent of insert your literal. Whenever you change a literal, it does not matter: the statement is effectively the same”.

As discussed in earlier post, how bind variables helps in avoiding hard parse but comes with both advantage and disadvantage as per performance point of view. let go through both,

Advantage:

It helps improving performance by sharing parent cursors in Library Cache i.e. avoid hard parsing.

SQL> variable n NUMBER
SQL> variable v VARCHAR2(32)
SQL> execute :n :=1; :v := 'Delhi';
SQL> INSERT INTO JITEN (n, v) VALUES (:n, :v);
SQL> execute :n :=2; :v := 'Goa';
SQL> INSERT INTO JITEN (n, v) VALUES (:n, :v);
SQL> execute :n :=3; :v := 'Pune';
SQL> INSERT INTO JITEN (n, v) VALUES (:n, :v);

Check associated child cursor.

SQL> SELECT sql_id, child_number, executions FROM v$sql WHERE sql_text = 'INSERT INTO JITEN (n, v) VALUES (:n, :v)';

SQL_ID     	CHILD_NUMBER    EXECUTIONS
-------------	--------------	-----------
9pr6521w64tfr              0		3

As we can see for 3 different values of Insert statement only 1 PARENT cursor is created, means it avoids hard parsing. Now single parent cursor may have many child cursor as shown in below example.

SQL> variable v VARCHAR2(40)
SQL> execute :n :=4; :v := 'Mumbai';
SQL> INSERT INTO JITEN (n, v) VALUES (:n, :v);
SQL> 
SQL> variable v VARCHAR2(60)
SQL> execute :n :=4; :v := 'Mumbai';
SQL> INSERT INTO JITEN (n, v) VALUES (:n, :v);



SQL> SELECT sql_id, child_number, executions FROM v$sql WHERE sql_text = 'INSERT INTO JITEN (n, v) VALUES (:n, :v)';

SQL_ID     	CHILD_NUMBER    EXECUTIONS
-------------	--------------	-----------
9pr6521w64tfr          0		3
9pr6521w64tfr	       1		2

Child cursor contains execution plan and execution environment. Here new child cursor 1 is created because the environment between first 3 execution is same but for 4th has been changed. Mismatch between BIND LENGTH. You can query V$SQL_SHARED_CURSOR to find to out reason.

Disadvantage:

Without bind variables different execution plans are used if the value used in the WHERE clause change. this is because the query optimizer
recognize the different selectivity of the two predicates. so optimizer is able to estimate better plan as literals gives clear picture about values on basis of which it creates plan.

Example:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> CREATE TABLE t AS SELECT rownum AS id, rpad('*',100,'*') AS pad FROM dual CONNECT BY level <= 1000;
SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
SQL> BEGIN
	2	dbms_stats.gather_table_stats(
	3	owname	=>SYS,
	4	tabname	=>'t',
	5	estimate_percent	=>100,
	6 	method_opt =>'for all column size 1'
	7	);
	8	END;
	9 /
SQL>
SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) from t;

COUNT(ID)	COUNT(DISTINCT ID)	MIN(ID)		MAX(ID)
----------	-----------------	--------	--------
	1000		1000		1		1000

Executing using literal values,

SQL> SELECT count(pad) from t where id < 990;

COUNT(PAD)
----------
       989

Above table having 1000 distinct value and we selected 989 rows which caused optimizer knows that 99% of table is selected so chooses Full table Access as better Plan.

Lets select values <10. As optimizer knows that only 1% of table is selected so using Index sql will perform better so used Index.

SQL> SELECT count(pad) from t where id < 10;

COUNT(PAD)
----------
	 9

On thing to note here is HARD PARSING happening with Literals which also a Resource Consuming Activity.

So we can conclude that Problem with Bind Variables when used in WHERE Clause is that crucial Information is hidden(actual Values) from Query Optimizer. With Literals Query Optimizer always able to make better estimate as we seen above example. To overcome this issue Bind Peeking Introduced in Oracle 9i.

Before generating execution plan, optimizer peeks at values of bind variables and uses them as literals. Problem with this approach is, generated execution plan depends on value provided by first execution. Better understand with this example.

Execution 1:

SQL> EXECUTE :id :=990;
SQL> 
SQL> SELECT count(pad) from t WHERE id < :id;

COUNT(PAD)
----------
      989

Execution 2:

SQL> EXECUTE :id :=10;
SQL> SELECT count(pad) from t WHERE id < :id;

COUNT(PAD)
----------
	9

Now test one more time, this time we will execute SQL in lower case on purpose to generate new SQL ID.

Execution 3:

SQL> EXECUTE :id :=10;
SQL> select count(pad) from t where id < :id;

COUNT(PAD)
----------
	9

Execution 4:

SQL> EXECUTE :id :=990;
SQL> select count(pad) from t where id < :id;

COUNT(PAD)
----------
	989

Result:  Execution 1 and 2 we have seen first execution done by value 990 and optimizer finds full table Scan as better plan and used that. For execution 2 also Optimizer used same Plan due to BIND PEEKING. Same for execution 3 and 4.

Things to point out here is as long as cursor remains in library cache and can be shared ,it will be reused. This occurs regardless of the efficiency of Execution Plan related to cursor. Thats why many times in production environment sql performance reported improved after flushing share pool or flushing problematic sql from memory as that helps optimizer to recreate new plan.

Now we got the problem and understand curious case of BIND PEEKING, but what is the solution for that?

Oracle 11.1 introduced a feature called ‘Adaptive Cursor Sharing’ to overcome this issue, will share a post on that real soon.

Thanks for reading…

subscribe to get notified for latest post.