溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“SQL ACS知識點有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
ACS默認是啟用的，當然你可以通過一些隱含參數來打開或關閉ACS,ACS的出現也引入了一些新視圖和新列。
l 兩個新列在V$SQL視圖里：IS_BIND_SENSITIVE 和 IS_BIND_AWARE， IS_BIND_SENSITIVE的值可以為Y和N，為Y的cursor，代表這個cursor被ACS監控了，為N的cursor代表沒有被監控或者沒有啟用ACS功能。而IS_BIND_AWARE為Y的，代表這個cursor每次解析時都要窺探綁定變量的值，計算謂詞的選擇率然后根據選擇率查看是否當前共享池中是否有滿足要求的執行計劃，如果有則重用，如果沒有，就要硬解析重新生成一個。
l V$SQL_CS_HISTOGRAM視圖，此視圖是ACS的關鍵視圖，主要記錄SQL處理的行數的直方圖，處理的行數驅動著ACS發揮作用。每個子cursor在這個視圖里有3個bucket可用，編號從0-2，字段bucket_id表示桶號，bucket_id 從0-2在每次SQL執行結束后，根據SQL處理行的數量，V$SQL_CS_HISTOGRAM中對應記錄的count會發生變化，count代表執行的次數，每一個bucket代表著cursor操作的數據量范圍，在11GR2版本，當返回的行數在0-1000時候，SQL執行完成后會更新在bucket_id 為0的桶的count字段，每執行一次， count字段的值會加1，當返回的行數在1000-1000000，SQL執行完成后會更新在bucket_id為1的桶的count字段，返回的行數超過1000000，更新在bucket_id為2的桶的count字段。請讀者不要死記數字，以后的版本可能會不同。如果SQL處理的行數發生巨變，也就是說處理的行數散落在了至少2個桶內，下一次解析時，就要窺探綁定變量的值，重新硬解析生成執行計劃。
l V$SQL_CS_SELECTIVITY，記錄游標謂詞的選擇率范圍，只有被標注為bind aware的cursor才會在此視圖中記錄。一旦一個cursor被標注為bind aware，每次解析時都要窺探綁定變量值，計算謂詞選擇率，然后根據計算的結果與此視圖中的相關記錄做比對，如果計算的選擇率落在了此視圖中對應游標的選擇率范圍內，則軟解析，復用此游標，如果不在，則硬解析，重新生成一個子游標。文章有一節會專門針對此問題進行探討。
l V$SQL_CS_STATISTICS 記錄游標處理的行數、buffer gets等信息，但是此視圖具有一定的誤導性，視圖里的數據只有在硬解析有新游標產生的時候才會發生變化，軟解析的時候，這些指標值不會發生變化。此視圖一旦有新的行產生代表著SQL又重新產生了一個新的cursor，新產生的cursor是ACS起作用的結果。
理論的東西太枯燥，我們先來看一個測試示例。再回頭來看這些理論就會很容易理解。
test@DLSP>CREATE TABLE test 2 AS 3 SELECT ROWNUM id, 4 DBMS_RANDOM.STRING('A', 12) name, 5 DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status 6 FROM all_objects a,dba_objects b 7 WHERE ROWNUM <= 50000;
Table created. SQL>CREATE INDEX test_id_ind ON test(status);
Index created.
SQL>begin 2 dbms_stats.gather_table_stats(user, 3 'test', 4 method_opt => 'for columns status size 2', 5 cascade => true); 6 end; 7 /
PL/SQL procedure successfully completed.
SQL>SELECT COUNT (*) cnt, status 2 FROM test 3 GROUP BY status 4 / ---------- ---------------- 49900 Active 100 Inactive |
上面的代碼創建了一張測試表test，表上有一列status數據有傾斜，此列上創建了索引，收集表的統計信息，并收集列status的直方圖。表中大部分的數據status為Active，極少的數據status為Inactive。我們可以開始我們的測試了，通過測試在針對列status不同值做查詢的情況下，幾個相關視圖中值的變化。
----------- 100
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID a9cf9a1ky3bda, child number 0 ------------------------------------- select /*+ find_me */ count(name) from test where status=:a
Plan hash value: 2948918962
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT| | | | 2 (100)| | | 1 | SORT AGGREGATE| | 1 | 25 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 87| 2175 | 2 (0)| 00:00:01 | |* 3 |INDEX RANGE SCAN |TEST_ID_IND| 87| | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
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 1 148 Y N
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 0 1 1709288874 a9cf9a1ky3bda 0 2 0 1709288874 a9cf9a1ky3bda 0 1 0
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 201
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;
no rows selected |
從上面的輸出我們看到了，v$SQL的IS_BIND_SENSITIVE ='Y'，優化器已經標注此SQL為bind sensitive，優化器參考直方圖等統計信息后，使用了索引掃描INDEX RANGE SCAN，由于Inactive值非常少，因此這是一個正確的決定，注意v$sql的輸出和其他V$視圖的輸出，由于此SQL處理的行數是100，因此視圖v$sql_cs_histogram中桶號bucket_id為0的行的count字段發生了變化，值從0變為了1，代表執行了1次。這里需要注意，優化器對綁定敏感的SQL需要在每次SQL執行結束后，更新v$sql_cs_histogram視圖中對應桶的count字段。
SQL>exec :a :='Active' ----------- 49900
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID a9cf9a1ky3bda, child number 0 ------------------------------------- select /*+ find_me */ count(name) from test where status=:a
Plan hash value: 2948918962
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows| Bytes| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 (100) | | | 1 | SORT AGGREGATE | |1 | 25 | | | | 2 |TABLE ACCESS BY INDEX ROWID| TEST |87 | 2175 | 2 (0)| 00:00:01| |* 3 |INDEX RANGE SCAN | TEST_ID_IND |87 | | 1 (0)| 00:00:01| --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
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 482 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='a9cf9a1ky3bda' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ---------- --------------- ------------ ---------- ---------- 1709288874 a9cf9a1ky3bda 0 0 1 1709288874 a9cf9a1ky3bda 0 2 0 1709288874 a9cf9a1ky3bda 0 1 1
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 201
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;
no rows selected
|
我們看到我們查詢status為Active時，v$sql中并沒有新的游標產生，優化器復用了跟status為Inactive一樣的執行計劃，這個執行計劃是很糟糕的，因為status為Active的有49900個，占了表里的大部分數據，走全表掃描更好。仔細觀察輸出，我們注意到前后兩次v$sql_cs_histogram的輸出差異。第一次輸出的值，只有bucket_id為0記錄的count為1，第二次輸出的值，bucket_id為0的記錄值保持不變，bucket_id為1的的count已經從0變為了1，說明優化器已經意識到了這個SQL的處理的行數跟第一次已經大大的不同了。就像本章一開頭說到的，本次處理的行數已經超過了10000，所以SQL執行結束后更新了bucket_id為1的記錄里的count的值。
我們繼續拿Active作為查詢值看看會出現什么結果：
SQL>exec :a :='Active' ----------- 49900
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- 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 | 49909 | 1218K| 51 (2) | 00:00:01 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("STATUS"=:A) 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 482 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 1 1709288874 a9cf9a1ky3bda 1 0 0 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 201 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.898361 1.097996 |
優化器終于意識到自己犯了錯誤，重新硬解析產生了新的執行計劃，采用了full table scan。V$SQL已經產生了新游標，IS_BIND_AWARE的值也已經變成了Y，視圖v$SQL_CS_HISTOGRAM額外的多了3行，為了記錄新游標（child_numer為1）的處理行數直方圖信息。由于有了硬解析，視圖V$SQL_CS_STATISTICS也新增了一行記錄新游標的行處理信息和buffer gets信息。$SQL_CS_SELECTIVITY也有了記錄，記錄了新游標謂詞的選擇率范圍。截止到目前，這個SQL已經是bind aware的了。bind aware意味著以后每次解析此SQL都要去窺探這個SQL的綁定變量值計算選擇率，然后根據計算的結果與v$sql_cs_selectivity視圖中的相關記錄做比對，如果計算的選擇率落在了此視圖中對應游標的選擇率范圍內，則軟解析，復用此游標，如果不在，則硬解析，重新生成一個子游標。
SQL>var a varchar2(100) ----------- 100 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 482 Y N 1 1 210 Y Y 2 1 102 Y Y
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 0 0 1709288874 a9cf9a1ky3bda 1 1 1 1709288874 a9cf9a1ky3bda 1 2 0 1709288874 a9cf9a1ky3bda 2 1 0 1709288874 a9cf9a1ky3bda 2 0 1 1709288874 a9cf9a1ky3bda 2 2 0
9 rows selected.
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 201 1709288874 a9cf9a1ky3bda 1 1 49901 1709288874 a9cf9a1ky3bda 2 1 201
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.898361 1.097996 1709288874 a9cf9a1ky3bda 2 =A 0 0.001557 0.001903
|
由于這個SQL已經是bind aware的了，當再次執行status='Inactive'時，優化器窺探到傳入的值并參考直方圖信息等統計信息，計算選擇率與現有的游標的選擇率不符，因此重新硬解析產生了一個新的游標，這個游標的is_bind_aware字段為'Y'，產生了index range scan的執行計劃，非常棒！視圖v$sql_cs_histogram，v$sql_cs_statistics，v$sql_cs_selectivity也都有相應的變化。
從上面的實驗中，我們可以知道，ACS存在著不穩定期，必須在一個游標的性能變糟后，優化器才能意識到犯了錯誤，再下次執行時嘗試糾正錯誤。觸發這一行為是依靠在v$sql_cs_histogram視圖中，此游標的3個桶中出現了兩個桶中的count都有非0值，一旦被觸發，在每次解析階段，都要去窺探綁定變量的值計算選擇率，如果計算選擇率與現有的游標的選擇率不符，就會基于窺探到的綁定變量的值硬解析重新產生了一個新的游標，并且此SQL會被標注為bind aware。
“SQL ACS知識點有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
