溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“直方圖與ACS實例分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“直方圖與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' ----------- 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 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
------------------------------------------------------------------------------------- | 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)
|
先查詢了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>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
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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>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>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
------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 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發揮作用，只不過是給優化器一個機會，讓其根據具體的綁定變量的值重新硬解析，但是至于硬解析出來的執行計劃優不優秀，要看統計信息的完整度、準確度以及你查詢的謂詞是做的何種查詢。
到此，相信大家對“直方圖與ACS實例分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
