為了減少帶繫結變數的sql的解析時間，oracle 9i引入的繫結變數窺測的功能。也就是在同一個SQL的變數被賦於不同值時採用同一個遊標，這樣雖然節省了sql的解析時間，但有時所採用的執行計劃並不是最優的。這個功能可以透過一個隱含引數"_optim_peek_user_binds"開啟或關閉。

繫結變數窺測存在的問題

下面實驗一下，先準備一個測試表
SQL> create table t as select 1 n, 'aaa' c from dual CONNECT BY level <= 1000;

Table created.

SQL> insert into t select 2,'bbb' from dual CONNECT BY level <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from t where n=1;

COUNT(1)
----------
      1000

SQL> select count(1) from t where n=2;

COUNT(1)
----------
        10

SQL> create index in_t on t (n);

Index created.
第一個欄位為1有1000條記錄，第2個欄位為2有10條記錄，在上面建立一個索引
SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='T';

COLUMN_NAME                       DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
C                                      .5           1 NONE
N                               .00049505           2 FREQUENCY

SQL> vari n number;
SQL> exec :n :=1;

PL/SQL procedure successfully completed.

SQL> select count(c) from t where n=:n;

COUNT(C)
----------
      1000

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 | SORT AGGREGATE    |      |     1 |     7 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 |   TABLE ACCESS FULL| T    | 1000 | 7000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - filter("N"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("C")[22]
   2 - "C"[CHARACTER,3]

31 rows selected.
從上面看到當變數n為1時走的是全表掃描，這樣當然是正確的,因為要讀取99％表中的記錄；如果變數為2，只讀取1％的記錄應該走索引吧，測試一下：
SQL> exec :n :=2;

PL/SQL procedure successfully completed.

SQL> select count(c) from t where n=:n;

COUNT(C)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 | SORT AGGREGATE    |      |     1 |     7 |            |          |
|* 2 |   TABLE ACCESS FULL| T    | 1000 | 7000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      DB_VERSION('11.1.0.6')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

   1 - :N (NUMBER): 1

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

   2 - filter("N"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("C")[22]
   2 - "C"[CHARACTER,3]

50 rows selected.
結果還是全表掃描，而且注意執行計劃裡有“Peeked Binds (identified by position): ”一項n的值為1,但n明明是2
SQL> print :n

         N
----------
         2
這就是繫結變數窺測的的功能是起作用，我們可以看到n=2時用的sql_id和child number和前面執行的n=1時的一樣，oracle的最佳化器自動取了前面一次的執行計劃。為了解決這個問題，我們可以flush shared_pool，把上次生成的解析後的sql去掉，但這樣做會造成整個shared pool中的sql全部失效，在生產的高峰做這樣的事情可能會引起災難性的後果，在oracle 10.2.0.4後的版本提供了一個過程dbms_shared_pool.purge可以只讓一條指定的sql失效（但在10.2.0.4的版本上並不生效，參考 metalink Doc ID: 751876.1 10.2.0.4預設不開啟,要靠event 5614566或者補丁5614566來啟用），我們可以在11G上試驗一下：

SQL> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select count(c) from t where n=:n';

ADDRESS HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
000000009F89FAC8 1223360943 2 2

SQL> exec dbms_shared_pool.purge('000000009F89FAC8,1223360943','a');

PL/SQL procedure successfully completed.

SQL> select count(*) from v$sql where sql_TEXT like 'select count(c) from t where n=:n';

COUNT(*)
----------
0

我們也可以透過修改隱含引數_optim_peek_user_binds來遮蔽繫結變數窺測這項功能：
SQL> Alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> select count(c) from t where n=:n;

COUNT(C)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 1
-------------------------------------
select count(c) from t where n=:n

Plan hash value: 2140154646

-------------------------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|   1 | SORT AGGREGATE              |      |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   505 | 3535 |     3   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | IN_T |   505 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$1 / T@SEL$1

Outline Data
-------------

/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      DB_VERSION('11.1.0.6')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."N"))
      END_OUTLINE_DATA
*/

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

   3 - access("N"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("C")[22]
   2 - "C"[CHARACTER,3]
   3 - "T".ROWID[ROWID,10]

49 rows selected.
我們看到這次走了索引，sql_id還是一樣，但child number從0變成了1,生成了一個新的執行計劃。

我們也可以用outline來固定這條sql的執行計劃，如可以規定所有這樣的sql都必需走全表掃描。可以採用下面兩種方法生成這樣的outline。

可以用現在走索引的執行計劃生成一個outline，然後直接修改OUTLN的相關表將執行計劃改成走全表掃描。這就要求對outline的相關語句非常熟悉才能做到，或者可以參考前面dbms_xplan.display_cursor中的Outline Data部分來修改相關的表，注意format要設定成advanced才會有outline部分的生成；
也可以用當前sql生成一個走索引的執行計劃，再加hint生成另一個全表掃描的執行，然後將這兩個執行計劃換一下即可。

第二種方法比較好操作，我們就用第二種方法。

SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.

SQL> create outline line_t on select count(c) from t where n=:n;

Outline created.

SQL> create outline full_t on select /*+ full(t) */ count(c) from t where n=:n;

Outline created.

SQL> select ol_name,hint_text from OUTLN.OL$HINTS;

OL_NAME     HINT_TEXT
----------- ------------------------------
LINE_T      INDEX_RS_ASC(@"SEL$1" "T"@"SEL
            $1" ("T"."N"))

LINE_T      OUTLINE_LEAF(@"SEL$1")
LINE_T      ALL_ROWS
LINE_T      DB_VERSION('11.1.0.6')
LINE_T      OPTIMIZER_FEATURES_ENABLE('11.
            1.0.6')

LINE_T      IGNORE_OPTIM_EMBEDDED_HINTS
FULL_T      FULL(@"SEL$1" "T"@"SEL$1")
FULL_T      OUTLINE_LEAF(@"SEL$1")
FULL_T      ALL_ROWS
FULL_T      DB_VERSION('11.1.0.6')
FULL_T      OPTIMIZER_FEATURES_ENABLE('11.
            1.0.6')

FULL_T      IGNORE_OPTIM_EMBEDDED_HINTS

12 rows selected.

看看生成的兩個outline已經達到了我們的要求，現在把它們換個個：

SQL> UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'FULL_T','LINE_T','LINE_T','FULL_T') WHERE OL_NAME IN ('LINE_T','FULL_T');

12 rows updated.

SQL> commit;

Commit complete.

SQL> select ol_name,hint_text from OUTLN.OL$HINTS;

OL_NAME     HINT_TEXT
----------- ------------------------------
FULL_T      INDEX_RS_ASC(@"SEL$1" "T"@"SEL
            $1" ("T"."N"))

FULL_T      OUTLINE_LEAF(@"SEL$1")
FULL_T      ALL_ROWS
FULL_T      DB_VERSION('11.1.0.6')
FULL_T      OPTIMIZER_FEATURES_ENABLE('11.
            1.0.6')

FULL_T      IGNORE_OPTIM_EMBEDDED_HINTS
LINE_T      FULL(@"SEL$1" "T"@"SEL$1")
LINE_T      OUTLINE_LEAF(@"SEL$1")
LINE_T      ALL_ROWS
LINE_T      DB_VERSION('11.1.0.6')
LINE_T      OPTIMIZER_FEATURES_ENABLE('11.
            1.0.6')

LINE_T      IGNORE_OPTIM_EMBEDDED_HINTS

12 rows selected.
換好了，
SQL> ALTER SESSION SET USE_STORED_OUTLINES = TRUE;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(c) from t where n=:n;

COUNT(C)
----------
        10                                      SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2f973m14fq0dg, child number 0
-------------------------------------
select count(c) from t where n=:n

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 | SORT AGGREGATE    |      |     1 |     7 |            |          |
|* 2 |   TABLE ACCESS FULL| T    | 1000 | 7000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("N"=:N)

Note
-----
- outline "LINE_T" used for this statement

23 rows selected.

現在我們可以看到這個sql走了LINE_T的outline，是全表掃描。

11G的適應性遊標共享

為了解決這個問題，Oracle 11g中引入了一個“適應性遊標共享（adaptive cursor sharing）”的新功能，它可以在重用遊標時識別是否有更好的執行計劃，如果有，會再生成一個新子游標。
我們在將n分別等於1,和2後，執行幾次這個sql，查詢一下游標的情況：
SQL> SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
2 FROM v$sql
3 WHERE sql_text = 'select count(c) from t where n=:n'
4 ORDER BY child_number;

SQL_ID        CHILD_NUMBER I I I
------------- ------------ - - -
2f973m14fq0dg            0 Y N N
2f973m14fq0dg            1 Y Y Y
2f973m14fq0dg            2 Y Y Y
可以看出這個sql對應了3個子遊標，其中0號子游標因為沒有使用繫結變數可知（is_bind_aware是N），而被設定成不再共享（is_shareable為N）。

SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',1,'basic'));

PLAN_TABLE_OUTPUT
---------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c) from t where n=:n

Plan hash value: 2966233522

-----------------------------------
| Id | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 | SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

14 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('2f973m14fq0dg',2,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c) from t where n=:n

Plan hash value: 2140154646

---------------------------------------------
| Id | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 | SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | IN_T |
---------------------------------------------

15 rows selected.

從上面的執行計劃可以看出，子游標1,2分別對應的是全表掃描和索引飯碗掃描，從下面的檢視也可以看出1號遊標要處理1001行，2號遊標處理21行，基本和實際的資料相符。

SQL> SELECT child_number, peeked, executions, rows_processed, buffer_gets FROM v$sql_cs_statistics WHERE sql_id = '2f973m14fq0dg'
2 ORDER BY child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
           0 Y          1             21          11
           1 Y          1           1001          15
           2 Y          1             21          11

這和下面直方圖的統計也基本一致：
SQL> select endpoint_value,endpoint_number from user_tab_histograms where column_name='N';

ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
1 1000
2 1010

這個檢視顯示了子游標的執行次數和直方圖中桶的對應關係
SQL> SELECT child_number, bucket_id, count FROM v$sql_cs_histogram WHERE sql_id = '2f973m14fq0dg' ORDER BY child_number;

CHILD_NUMBER BUCKET_ID      COUNT
------------ ---------- ----------
           0          1          1
           0          0          1
           0          2          0
           1          0          0
           1          1          4
           1          2          0
           2          1          0
           2          0          4
           2          2          0

從上面可以看出oracle選擇哪個子游標的依據是統計資訊中的的直方圖的分佈，因此DBA在平時就要關注這些資訊，一旦出現低效的sql執行計劃時才能及時進行處理。
雖然我們不能完全依賴11G的這個新功能，但我們可以發現oracle是越來越聰明瞭。

繫結變數窺測的演變

相關文章