Bind Peeking

Bind peeking - it means when the query is first Hard parsed, the optimizer will peek at the binds in order to determine how to optimize the query. Its not at every parse, only at Hard parsing.

create table t
as
select 1 as id,a.*
from all_objects a;

update t set id = 99 where rownum = 1;
create index t_ind on t(id);

exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254',cascade=>true);
/* massively skewed data, index on t(id) should be used if  ID is not 1..So we run these queries against this data */

variable x number;
exec :x := 1;

select * from t x_was_1 where id = :x;

exec :x := 99;
select * from t x_was_99 where id = :x;

/* Now we execute the same queries (soft parse) --but with the inputs flip flopped */
exec :x := 1;
select * from t x_was_99 where id = :x;

exec :x := 99;
select * from t x_was_1 where id = :x;

********************************************************************************
BEGIN :x := 1; END;

select *
from
 t x_was_1 where id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      344      0.04       0.06          0       1073          0       51437
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      346      0.04       0.06          0       1073          0       51437

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
  51437  TABLE ACCESS FULL T (cr=1073 pr=0 pw=0 time=34 us)

/* This is what expected, when the bind was set to 1 during Hard parse. A Full Table scan */
********************************************************************************
BEGIN :x := 99; END;

select *
from
 t x_was_99 where id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=30 us)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=21 us)(object id 65996)

/*  Expected plan with Bind is 99 - Index range scan */

********************************************************************************
BEGIN :x := 1; END;

select *
from
 t x_was_99 where id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      344      0.07       0.09          0       1512          0       51437
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      346      0.07       0.09          0       1512          0       51437

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
  51437  TABLE ACCESS BY INDEX ROWID T (cr=1512 pr=0 pw=0 time=102909 us)
  51437   INDEX RANGE SCAN T_IND (cr=445 pr=0 pw=0 time=24 us)(object id 65996)

/*  Here the bind variable is in fact 1, if you remember from the test case. The plan was fixed during the Hard parse and same got reused. */
********************************************************************************
BEGIN :x := 99; END;

select *
from
 t x_was_1 where id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        735          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        735          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T (cr=735 pr=0 pw=0 time=50 us)

/* Same here now. Full table scan is not appropriate */
********************************************************************************

Binds - Are Mandatory on systems that parses many queries / second.
Binds - Not mandatory on systems that have many seconds between parses ( typically data warehouses, and queries that run for minutes or hours )