Bind variable peeking. Part 2.

As Jonathan Lewis wrote: "bind variable peeking is always done, even if histograms are not generated", so be prepared, your plan can be changed without any warning.

Let me show the following example:

create table tab1 as select * from all_objects;
create unique index tab1_obj_id on tab1(object_id);
create index tab1_date_obj_id on tab1(DATA_OBJECT_ID,object_name,object_id);
exec dbms_stats.gather_table_stats(user, 'TAB1', cascade=> true, method_opt => 'FOR ALL COLUMNS SIZE 1');

It is obvious that if a query has filter object_id=:bind, the first index tab1_obj_id has to be chosen by CBO.

explain plan for
select count(1) from tab1 where object_id=:a1 and DATA_OBJECT_ID=:a2;

Plan hash value: 17355806
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1        |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | TAB1_OBJ_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATA_OBJECT_ID"=TO_NUMBER(:A2))
   3 - access("OBJECT_ID"=TO_NUMBER(:A1))

But bind peeking can lead to some unexpected result. An unlucky bind value, which is NULL creates lots of confusion and also sometimes very inefficient plan.

variable a1 number

variable a2 number

begin

  :a1:=29;

  :a2:=null;

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and DATA_OBJECT_ID=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  25bxjz9jgv53y, child number 0

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and

DATA_OBJECT_ID=:a2

Plan hash value: 683818241

--------------------------------------------------------------------------------------

| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01 |

|   1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01 |

|*  2 |   INDEX RANGE SCAN| TAB1_DATA_OBJ_ID |      1 |      1 |      0 |00:00:00.01 |

--------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (NUMBER): (null)

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DATA_OBJECT_ID"=:A2 AND "OBJECT_ID"=:A1)

       filter("OBJECT_ID"=:A1)

One more execution with all non-null binds just reuses already parsed plan. With real-world data, it might get stuck forever.

begin

  :a1:=29;

  :a2:=29;

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and DATA_OBJECT_ID=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  25bxjz9jgv53y, child number 0

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and

DATA_OBJECT_ID=:a2

Plan hash value: 683818241

----------------------------------------------------------------------------------------------

| Id | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01|       3 |

|  1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01|       3 |

|* 2 |   INDEX RANGE SCAN| TAB1_DATA_OBJ_ID |      1 |      1 |      1 |00:00:00.01|       3 |

----------------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (NUMBER): (null)

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DATA_OBJECT_ID"=:A2 AND "OBJECT_ID"=:A1)

       filter("OBJECT_ID"=:A1)

A little variation of this case, OBJECT_TYPE VARCHAR2 is used instead of DATA_OBJECT_ID NUMBER:

create table tab1 as select * from all_objects;

create unique index tab1_obj_id on tab1(object_id);

create index tab1_type_obj_id on tab1(object_type,object_name,object_id);

exec dbms_stats.gather_table_stats(user, 'TAB1', cascade=> true, method_opt => 'FOR ALL COLUMNS SIZE 1'

Unlucky bind value:

begin

  :a1:=29;

  :a2:=null;

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  6buxk2515azrt, child number 0

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2

Plan hash value: 3906737377

--------------------------------------------------------------------------------------

| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01 |

|   1 |  SORT AGGREGATE   |                  |      1 |      1 |      1 |00:00:00.01 |

|*  2 |   INDEX RANGE SCAN| TAB1_TYPE_OBJ_ID |      1 |      1 |      0 |00:00:00.01 |

--------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (VARCHAR2(30), CSID=873): (null)

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_TYPE"=:A2 AND "OBJECT_ID"=:A1)

       filter("OBJECT_ID"=:A1)

But when it is time to lucky one, the picture is much better:

begin

  :a1:=29;

  :a2:='CLUSTER';

end;

/

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2;

select * from table(dbms_xplan.display_cursor(null, null, 'LAST ALLSTATS +peeked_binds'));

SQL_ID  6buxk2515azrt, child number 1

-------------------------------------

select /*2*/ count(1) from tab1 where object_id=:a1 and object_type=:a2

Plan hash value: 17355806

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |      1 |       3 |
|   1 |  SORT AGGREGATE              |             |      1 |      1 |      1 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1        |      1 |      1 |      1 |       3 |
|*  3 |    INDEX UNIQUE SCAN         | TAB1_OBJ_ID |      1 |      1 |      1 |       2 |
-----------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

   1 - (NUMBER): 29

   2 - (VARCHAR2(30), CSID=873): 'CLUSTER'

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OBJECT_TYPE"=:A2)

   3 - access("OBJECT_ID"=:A1)

Please notice, the second execution resulted in creating new child cursor number 1.

Look at this comment, which is about the following claim "part of the identifying characteristic for a child cursor is the memory allocation for the input bind variables and, at some layer in the library code, character columns are allowed to have 4 different allocation sizes, namely 32, 128, 2000, or 4000 bytes". So, zero-size bind seems adds one more allocation size and identifies the first child cursor number 0 as that not suitable for execution with non-zero length bind.

Upd.: one more piece of information is here. 

SQLPATH environment variable length limit

I heavily use lots of sql scripts spread across several folders. Recently I started some reorganization of that directories to meet new needs of SQLcl (I'm testing it now in order to put it in my tool set). However some my scripts did not work properly with SQLcl, so I had to return to SQL*Plus for a while. But when I started SQL*Plus, I found that it could find neither login.sql nor any other scripts from my big library anymore.

It appeared almost accidentally that SQL*Plus can not handle SQLPATH variable which is longer than 260 characters. In contrast, SQLcl did not show any problem with the even longer value of SQLPATH.