直方圖與ACS

一般情況下ACS必須結合直方圖一起使用才能發揮作用，我們看看列上的資料有傾斜，但是卻不收集直方圖情況下，ACS的表現會怎麼樣，緊接著還會舉出一個特例。以下的程式碼刪除了列status上的直方圖。

SQL>begin

2 dbms_stats.delete_column_stats(ownname => 'test',

3 tabname => 'test',

4 colname => 'status',

5 col_stat_type => 'HISTOGRAM');

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>alter system flush shared_pool;

System altered.

刪除直方圖是11G提供的功能，如果你的版本小於11G，可以重新收集表的統計資訊不收集直方圖。

SQL>exec :a :='Active'

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

49900

SQL>exec :a:='Inactive'

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

100

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

1709288874 a9cf9a1ky3bda 0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

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

PLAN_TABLE_OUTPUT

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

SQL_ID a9cf9a1ky3bda, child number 0

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

select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

我們看到v$sql_cs_histogram裡的這個cursor的3個桶裡已經有2個桶的count非0，說明最佳化器已經認識到第二次執行返回的記錄數跟第一次大大不同了。按照我們之前所做的測試，如果列上有直方圖，再次執行這個SQL，應該就會新產生一個遊標了。我們來看看缺少直方圖會怎麼樣：

SQL> select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

100

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

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

0 2 463 Y N

1 1 210 Y Y

SQL>

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

1709288874 a9cf9a1ky3bda 0 1 1

1709288874 a9cf9a1ky3bda 0 0 1

1709288874 a9cf9a1ky3bda 0 2 0

1709288874 a9cf9a1ky3bda 1 1 0

1709288874 a9cf9a1ky3bda 1 0 1

1709288874 a9cf9a1ky3bda 1 2 0

6 rows selected.

SQL>

SQL>-- 統計資訊

SQL>SELECT hash_value, sql_id, child_number, executions,

2 rows_processed

3 FROM v$sql_cs_statistics

4 WHERE sql_id='a9cf9a1ky3bda'

5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

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

1709288874 a9cf9a1ky3bda 0 1 101

1709288874 a9cf9a1ky3bda 1 1 49901

SQL>

SQL>-- 選擇率

SQL>SELECT hash_value, sql_id, child_number, predicate, range_id, low, high

2 FROM v$sql_cs_selectivity

3 WHERE sql_id='a9cf9a1ky3bda'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

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

1709288874 a9cf9a1ky3bda 1 =A 0 0.450000 0.550000

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

PLAN_TABLE_OUTPUT

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

SQL_ID a9cf9a1ky3bda, child number 0

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

select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

SQL_ID a9cf9a1ky3bda, child number 1

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

select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

我們看到雖然新生成了一個子cursor，而且這個cursor的bind aware為Y，但是檢視執行計劃，發現新生成的child_number為1的執行計劃也為全表掃描，而非索引掃描。其實最佳化器在發現這個cursor處理的行數發生鉅變後，下次再次執行的話，就會窺探變數值，然後根據窺探到的值進行硬解析，但是由於不存在直方圖，最佳化器認為索引掃描的代價太高，因此硬解析後依然還是生成了全表掃描的執行計劃。我們看看索引掃描的COST值是多少：

SQL>select /*+ index(test) */ count(name) from test where status=:a;

COUNT(NAME)

-----------

100

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

PLAN_TABLE_OUTPUT

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

SQL_ID 88jwg2t11b237, child number 0

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

select /*+ index(test) */ count(name) from test where status=:a

Plan hash value: 2948918962

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

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

| 0 | SELECT STATEMENT | | | | 218 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 25000 | 610K| 218 (1)| 00:00:03 |

|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 25000 | | 63 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("STATUS"=:A)

索引掃描的cost 為218已經超過了全表掃描的cost 51，因此由於缺少直方圖即使重新硬解析也只能產生全表掃描的執行計劃。最佳化器在嘗試糾正錯誤，但是無耐給的資訊不夠，錯誤不能得到有效的糾正。

但是有特例，如果列上做的是非等值查詢，即使沒有直方圖，依然可能會使用到ACS，我們看一個案例：

l 建立一張表，500萬的記錄數，id欄位根據rownum生成

l 在id欄位上建立索引

l 收集統計資訊，不收集直方圖

l 清空shared_pool

SQL>create table t as select rownum id,a.* from dba_objects a ,dba_objects b where rownum<5000001;

Table created.

SQL>create index i on t(id);

Index created.

SQL>begin

2 dbms_stats.gather_table_stats(ownname => 'test',

3 tabname => 't',

4 no_invalidate => FALSE,

5 estimate_percent => 100,

6 force => true,

7 degree => 5,

8 method_opt => 'for all columns size 1',

9 cascade => true);

10 end;

11 /

PL/SQL procedure successfully completed.

SQL>alter system flush shared_pool;

System altered.

SQL>var a number;

SQL>exec :a :=4999999;

PL/SQL procedure successfully completed.

SQL>select count(object_id) from t where id > :a;

COUNT(OBJECT_ID)

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

1

SQL>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

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

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

| 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)

先查詢了id大於4999999的，由於只返回一條記錄，記錄集非常小，ORACLE選擇了索引掃描。我們看看ACS相關檢視的表現：

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='1vmttxn3jrww3';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

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

0 1 48 Y N

SQL>

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='1vmttxn3jrww3'

4 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的情況下，這個時候要幾乎返回整個表的資料：

SQL>exec :a :=1;

select count(object_id) from t where id > :a;

PL/SQL procedure successfully completed.

SQL>

COUNT(OBJECT_ID)

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

4999999

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='1vmttxn3jrww3';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

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

0 2 76425 Y N

SQL>

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='1vmttxn3jrww3'

4 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的桶上。再次執行：

SQL>select count(object_id) from t where id > :a;

COUNT(OBJECT_ID)

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

4999999

SQL>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

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

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

| 0 | SELECT STATEMENT | | | | 14373 (100)| |

| 1 | SORT AGGREGATE | | 1 | 10 | | |

|* 2 | TABLE ACCESS FULL| T | 4999K| 47M| 14373 (2)| 00:02:53 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID">:A)

再次執行後，已經產生出了全表掃描的執行計劃了，因為再次執行，最佳化器會去窺探繫結變數的值做硬解析，最佳化器重新評估索引掃描和全表掃描的cost後選擇了全表掃描，下面的程式碼給出了ACS相關檢視的變化和索引掃描的cost。

SQL> -- 檢查ACS狀態

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='1vmttxn3jrww3';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

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

0 2 76425 Y N

1 1 64685 Y Y

SQL>-- 直方圖

SQL>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='1vmttxn3jrww3'

4 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

SQL>select /*+ index(t) */count(object_id) from t where id > :a;

select

COUNT(OBJECT_ID)

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

4999999

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

PLAN_TABLE_OUTPUT

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

SQL_ID 51qy01unwm5r0, child number 0

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

select /*+ index(t) */count(object_id) from t where id > :a

Plan hash value: 3694077449

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

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

| 0 | SELECT STATEMENT | | | | 76652 (100)| |

| 1 | SORT AGGREGATE | | 1 | 10 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| T | 4999K| 47M| 76652 (1)| 00:15:20 |

|* 3 | INDEX RANGE SCAN | I | 4999K| | 11792 (1)| 00:02:22 |

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

Predicate Information (identified by operation id):

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

3 - access("ID">:A)

v$sql中也已經出現了child_number為1的子游標。 is_bind_sensitive和is_bind_aware都為Y。v$sql_cs_histogram中也產生出了新的3行記錄。說明ACS已經發揮作用產生了新的遊標，而且執行計劃也非常優秀。上面沒有直方圖的第一個做等值查詢的例子，雖然ACS也發揮了作用，但是由於缺少直方圖，並沒有產生出優秀的執行計劃。

從上面的兩個例子可以看出，所謂ACS發揮作用，只不過是給最佳化器一個機會，讓其根據具體的繫結變數的值重新硬解析，但是至於硬解析出來的執行計劃優不優秀，要看統計資訊的完整度、準確度以及你查詢的謂詞是做的何種查詢。

Adaptive Cursor Sharing（第三篇）

直方圖與ACS

相關文章