bind peeking и гистограммы

Oracle обещает

There are two cases where the optimizer would peek at the actual bindings of a bind variable and where the actual bindings therefore could make a difference for what plan would get generated.
Range predicates. Example:
sales_date between :1 and :2 and price > :3.
Equality predicates when the column has histograms. Example:
order_status = :4
assuming that order_status has histograms.

Проверка показывает:
CREATE TABLE a(a NUMBER, b CHAR);

CREATE INDEX ix_a ON a(a);

INSERT INTO a SELECT 2, 'x' FROM all_objects WHERE ROWNUM <= 1;

INSERT INTO a SELECT 1, 'x' FROM all_objects WHERE ROWNUM <= 1000;

BEGIN
dbms_stats.gather_table_stats(
'sps',
'a',
CASCADE => TRUE,
method_opt => 'for all indexed columns size 50');
END;
/

COMMIT;

var a NUMBER

EXEC :a := 1;

ALTER SESSION SET tracefile_identifier = 'hist_test';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT * FROM a WHERE a = :a;

EXEC :a := 2;

SELECT * FROM a WHERE a = :a;

DISCONNECT;


По трейсу 10053 имеем только одно вхождение строки
*******************************************
Peeked values of the binds in SQL statement
*******************************************

и имеем следующие планы выполнения:
SELECT *
FROM
a WHERE a = :a


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.34 0.38 0 0 0 0
Fetch 11 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.34 0.38 0 16 0 1000

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

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS FULL A (cr=16 pr=0 pw=0 time=1054 us)


SELECT *
FROM
a WHERE a = :a

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 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1

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

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL A (cr=7 pr=0 pw=0 time=128 us)

Результаты получаемы через explain plan:

SQL> SELECT * FROM a WHERE a = 2;

Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 5 | 2 (0)|
| 2 | INDEX RANGE SCAN | IX_A | 1 | | 1 (0)|
-------------------------------------------------------------------------

Это показывает, что для значения переменной :a=2 никакого дополнительного считывания переменной не было и в помине.

Bind Peeking бывает только при hard parse, судя по всему не зависимо от наличия гистограмм

Для отключения bind peeking можно воспользоваться скрытым параметром

ALTER SESSION SET "_optim_peek_user_binds"=FALSE;