優化器革命之- adaptive cursor sharing （二）

請先閱讀優化器革命之- adaptive cursor sharing （一）
http://blog.itpub.net/22034023/viewspace-1218980/

上文提到了對於沒有直方圖有索引的列，即使存在資料傾斜也不會使用到ACS，但是有一個特殊情況，如果不做等值查詢，也有可能在沒有直方圖的列上使用到ACS。
我們來看下一個實驗：
1.建立一張表，500萬的記錄數，id欄位根據rownum生成
2.在id欄位上建立索引
3.收集統計資訊，不收集直方圖
4.清空shared_pool

create table t as select rownum id,a.* from dba_objects a ,dba_objects b where rownum<5000001;
create index i on t(id);
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 't',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for all columns size 1',
cascade => true);
end;
/

alter system flush shared_pool;

上面的程式碼完成後，我們先來看下查詢id>4999999的情況：
var a number;
exec :a :=4999999;
select count(object_id) from t where id > :a;
COUNT(OBJECT_ID)
----------------
1
select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1vmttxn3jrww3, child number 0
-------------------------------------
select count(object_id) from t where id > :a

Plan hash value: 3694077449

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

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

3 - access("ID">:A)

由於只返回一條記錄，記錄集非常小，ORACLE選擇了索引掃描。

SELECT child_number, executions, buffer_gets, is_bind_sensitive,
is_bind_aware
FROM v$sql
WHERE sql_id='1vmttxn3jrww3';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 1 224 Y N

這個SQL也已經被標記為繫結敏感 is_bind_sensitive='Y'
SELECT hash_value, sql_id, child_number, bucket_id, COUNT
FROM v$sql_cs_histogram
WHERE sql_id='1vmttxn3jrww3'
ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- -------------------------- ------------ ---------- ----------
119272323 1vmttxn3jrww3 0 0 1
119272323 1vmttxn3jrww3 0 2 0
119272323 1vmttxn3jrww3 0 1 0

由於處理的結果集較小，執行的統計被列入到了bucket 0。
我們繼續看看查詢id>1的情況下，這個時候要幾乎返回整個表的資料：

exec :a :=1;
select count(object_id) from t where id > :a;
COUNT(OBJECT_ID)
----------------
4999999

SELECT child_number, executions, buffer_gets, is_bind_sensitive,
is_bind_aware
FROM v$sql
WHERE sql_id='1vmttxn3jrww3';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 76540 Y N

沒有生成新的執行計劃，這個SQL還是隻存在一個遊標。
SELECT hash_value, sql_id, child_number, bucket_id, COUNT
FROM v$sql_cs_histogram
WHERE sql_id='1vmttxn3jrww3'
ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- -------------------------- ------------ ---------- ----------
119272323 1vmttxn3jrww3 0 0 1
119272323 1vmttxn3jrww3 0 2 1
119272323 1vmttxn3jrww3 0 1 0

但是 v$sql_cs_histogram已經捕獲到本次執行的SQL處理的結果集已經跟第一次執行大大不同，執行的統計已經被列入到了bucket_id為2的桶上。
再次執行：
select count(object_id) from t where id > :a;
COUNT(OBJECT_ID)
----------------
4999999
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1vmttxn3jrww3, child number 1
-------------------------------------
select count(object_id) from t where id > :a

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14359 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| T | 4999K| 47M| 14359 (2)| 00:02:53 |
---------------------------------------------------------------------------

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

2 - filter("ID">:A)

我們看到索引掃描也就是child_number為0的執行計劃已經失效了，已經產生了child_number為1的全表掃描的執行計劃。

SELECT child_number, executions, buffer_gets, is_bind_sensitive,
is_bind_aware
FROM v$sql
WHERE sql_id='1vmttxn3jrww3';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 76540 Y N
1 1 64624 Y Y

v$sql中也已經出現了child_number為1的子游標。 is_bind_sensitive和is_bind_aware都為Y。

SELECT hash_value, sql_id, child_number, bucket_id, COUNT
FROM v$sql_cs_histogram
WHERE sql_id='1vmttxn3jrww3'
ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- -------------------------- ------------ ---------- ----------
119272323 1vmttxn3jrww3 0 1 0
119272323 1vmttxn3jrww3 0 0 1
119272323 1vmttxn3jrww3 0 2 1
119272323 1vmttxn3jrww3 1 1 0
119272323 1vmttxn3jrww3 1 0 0
119272323 1vmttxn3jrww3 1 2 1

v$sql_cs_histogram也已經產生了新增的3個記錄。
至此我們證明了，在做不等值查詢的時候，即使列上沒有直方圖資訊，也可以使用到ACS，根據繫結值，智慧的生成執行計劃。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1222110/，如需轉載，請註明出處，否則將追究法律責任。

優化器革命之- adaptive cursor sharing （二）

相關文章