當oracle開啟繫結變數窺視功能，會在sql硬解析時窺探變數的實際值以求生成更為精確的執行計劃，但是若該列資料分佈極度不均，則可能適得其反，11g引入了adaptive cursor sharing.

查詢最佳化器會不斷做出調整以便不同的繫結變數各自獲取最優執行計劃，該功能系統預設開啟，無法手工禁止。

經此特性處理的sql需經歷三個階段： bind-sensitive -> bind-aware -> cursor merging

以下是11R2的原文解釋，對於原文的理解可能有偏差，還是直接看英文文件比較好；

Bind-sensitive cursor

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior. of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:

· The optimizer has peeked at the bind values to generate selectivity estimates.

· A histogram exists on the column containing the bind value

Bind-aware cursor

When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:

· Generates a new plan based on the new bind value.

· Marks the original cursor generated for the statement

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate

Cursor merging

If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform. cursor merging. In this case, the database merges cursors to save space in the shared SQL area

做一組試驗測試一下該功能

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

SQL> create table emp(id number(10), name varchar(500)) pctfree 40;

Table created.

SQL> begin

2 for i in 1..1000 loop

3 insert into emp values(1,lpad('x',300,'x'));

insert into emp values(i,lpad('y',300,'x'));

end loop;

commit;

end; 4 5 6 7

8 /

PL/SQL procedure successfully completed

SQL> create index t_ind on emp(id);

Index created.

案例1

開啟變數窺測，收集直方圖

SQL> exec dbms_stats.gather_table_stats('SYS','EMP',estimate_percent => 100, cascade => true, method_opt =>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select column_name,NUM_BUCKETS,DENSITY,NUM_NULLS from dba_tab_columns where TABLE_NAME='EMP';

COLUMN_NAME NUM_BUCKETS DENSITY NUM_NULLS

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

NAME 1 .00025 0

ID 254 .0005 0

SQL> variable id number;

SQL> exec :id :=6;

PL/SQL procedure successfully completed.

SQL> select /*test*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 7jrqjukk5g62z, child number 0

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

select /*test*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 304 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

--檢視檢視，bind_sensitive=Y, bind_aware=N

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 1 25 Y N Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

0 1021760792 Y 1 3 25 0

--更改繫結變數值

SQL> exec :id := 1;

PL/SQL procedure successfully completed.

SQL> select /*test*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 7jrqjukk5g62z, child number 0

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

select /*test*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 304 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

--第一次並不會更改執行計劃，但是v$sql.buffer_gets卻發生了改變，由25增加到162

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 2 162 Y N Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

0 1021760792 Y 1 3 25 0

--第二次執行

SQL> select /*test*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 7jrqjukk5g62z, child number 1—生成了一個新的子游標

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

select /*test*/ count(name) from emp where id = : id

Plan hash value: 1849991560

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

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

| 0 | SELECT STATEMENT | | | | 39 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

|* 2 | TABLE ACCESS FULL| EMP | 984 | 292K| 39 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID"=:ID)

19 rows selected.

--新生成的子游標選擇全表掃描，且bind_aware=Y

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 2 162 Y N Y

1 1 140 Y Y Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

1 2342552567 Y 1 1002 140 0

0 1021760792 Y 1 3 25 0

--此時將id改為一個可以使用索引掃描的值

SQL> exec :id :=100;

PL/SQL procedure successfully completed.

SQL> select /*test*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 7jrqjukk5g62z, child number 2

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

select /*test*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 304 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

--生成一個新的子游標，且第一個子游標被設定為不可共享

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 2 162 Y N N

1 1 140 Y Y Y

2 1 3 Y Y Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

2 336594526 Y 1 3 3 0

1 2342552567 Y 1 1002 140 0

0 1021760792 Y 1 3 25 0

--第一個子游標已經被標識為不可共享，但若將id值重新改為6，還是可以重用

SQL> exec :id := 6;

PL/SQL procedure successfully completed.

SQL> select /*test*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 9jxcwk6y1fzpt, child number 0

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

select /*test*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 304 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

但是過了一會就無法再用了，有點奇怪

SQL> exec :id := 6;

PL/SQL procedure successfully completed.

SQL> select /*test*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 7jrqjukk5g62z, child number 2

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

select /*test*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 304 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='7jrqjukk5g62z';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 2 162 Y N N

1 1 140 Y Y Y

2 4 12 Y Y Y

--檢視子游標無法共享的原因

SQL> select CHILD_NUMBER,BIND_EQUIV_FAILURE,LOAD_OPTIMIZER_STATS from v$sql_shared_cursor where sql_id='7jrqjukk5g62z';

CHILD_NUMBER B L

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

0 N Y

1 Y N

2 Y N

-- BIND_EQUIV_FAILURE: The bind value's selectivity does not match that used to optimize the existing child cursor

-- LOAD_OPTIMIZER_STATS : A hard parse is forced in order to initialize extended cursor sharing

小結：當系統開啟繫結變數窺測且收集了直方圖時，oracle會對不同的繫結變數生成不同的子游標(但該變數必須執行至少兩次)，並依據selectivity生成新的執行計劃

案例2

刪除直方圖

SQL> exec dbms_stats.delete_column_stats('SYS','EMP','ID',col_stat_type =>'HISTOGRAM');

PL/SQL procedure successfully completed.

SQL> select column_name,NUM_BUCKETS,DENSITY,NUM_NULLS from dba_tab_columns where TABLE_NAME='EMP';

COLUMN_NAME NUM_BUCKETS DENSITY NUM_NULLS

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

NAME 1 .00025 0

ID 1 .001 0

SQL> exec :id := 600;

PL/SQL procedure successfully completed.

SQL> select /*justin*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 6d84s04yqn3cp, child number 0

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

select /*justin*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='6d84s04yqn3cp';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 1 22 Y N Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='6d84s04yqn3cp';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

0 3287955162 Y 1 3 22 0

將id改為1

SQL> exec :id :=1;

PL/SQL procedure successfully completed.

SQL> select /*justin*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 6d84s04yqn3cp, child number 0

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

select /*justin*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> select /*justin*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 6d84s04yqn3cp, child number 1

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

select /*justin*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

--執行兩次後生成一個新的子游標，但是仍舊選擇索引掃描

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='6d84s04yqn3cp';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 2 159 Y N N

1 1 137 Y Y Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='6d84s04yqn3cp';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

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

1 2342552567 Y 1 2003 137 0

0 3287955162 Y 1 3 22 0

小結：刪除直方圖，遊標依舊為bind_sensitive，但是即便生成了不同的子游標，由於缺乏足夠的統計資訊，當id=1時oracle依舊選擇了索引掃描

案例3

沒有直方圖，也不開啟繫結變數窺視

SQL> alter system set "_optim_peek_user_binds"=false;

System altered.

SQL> select /*thirid*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 01k335m3nbvty, child number 0

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

select /*thirid*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> exec :id :=2;

PL/SQL procedure successfully completed.

SQL> select /*thirid*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 68jwfamnx5gty, child number 0

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

select /*thirid*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

--可以看到只生成一個子遊標，且bind_sensitive=N

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='01k335m3nbvty';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

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

0 1 137 N N Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='01k335m3nbvty';

no rows selected

小結：沒有繫結變數窺測時，oracle不會生成不同的子游標

案例4

只有直方圖，沒有窺測

SQL> alter system set "_optim_peek_user_binds"=false;

System altered.

SQL> select /*fourth*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID ax6hs790n3ru2, child number 0

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

select /*fourth*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> exec :id :=1;

PL/SQL procedure successfully completed.

SQL> select /*fourth*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 5ru3vdjyva40h, child number 0

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

select /*fourth*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> select /*fourth*/ count(name) from emp where id = : id;

COUNT(NAME)

-----------

1001

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 5ru3vdjyva40h, child number 0

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

select /*fourth*/ count(name) from emp where id = : id

Plan hash value: 1800857609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 304 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 608 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_IND | 2 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"=:ID)

20 rows selected.

SQL> SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQL where sql_id ='5ru3vdjyva40h';

CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B

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

0 2 274 N N Y

SQL> select CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS,CPU_TIME from v$sql_cs_statistics where sql_id ='5ru3vdjyva40h';

no rows selected

小結：沒有繫結變數窺測時，oracle不會生成不同的子游標

總結：

11G若要正確的使用adaptive cursor sharing功能，必須開啟繫結變數窺視，且對應列需要收集直方圖資訊，兩者缺其一都會誤導oracle做出錯誤的選擇；

若只開啟peeking，則即便使用了adaptive cursor sharing生成不同的子游標，由於缺乏正確的統計資訊也可能導致沿用錯誤的執行計劃，若不開啟peeking則adaptive cursor sharing根本無從談起。

驗證11g的adaptive cursor sharing功能

相關文章