控制執行計劃之-SPM BASELINE（四）
Baseline Internal
我們接著上面一節繼續來思考，一個SQL在dba_sql_plan_baselines中存在2個accepted為YES狀態的baseline。最佳化器如何決定使用哪一個？看到網上有些文章提到是依據dba_sql_plan_baselines. OPTIMIZER_COST來決定，哪個baseline對應的執行計劃的cost小，就使用哪個。但是很容易驗證說法是錯誤的。我們可以修改dba_sql_plan_baselines檢視的基表sqlobj$auxdata中的OPTIMIZER_COST欄位，來調整2個baseline的OPTIMIZER_COST的大小，但是實驗結果最終證明是無效的，並不是透過dba_sql_plan_baselines. OPTIMIZER_COST的大小來決定使用哪個baseline。實驗結果這裡不再貼出。那到底是如何決定呢？這個要根據系統的配置來定，我們接著上面的例子，但是先關閉ACS，但是繫結變數窺探的功能保持開啟。
|
test@DLSP>show parameter _optimizer_extended_cursor_sharing_rel
NAME TYPE VALUE -------------------------------------- ---------------------- -------- _optimizer_extended_cursor_sharing_rel string NONE
test@DLSP>show parameter bind
NAME TYPE VALUE ------------------------------------ ---------------------- ----------- _optim_peek_user_binds boolean TRUE
test@DLSP>alter system flush shared_pool;
System altered. |
提示：針對這個SQL當前在dba_sql_plan_baselines檢視中存在2個baseline。
|
test@DLSP>ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
test@DLSP>var a varchar2(100) test@DLSP>exec :a :='Inactive'
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 100
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID aa8mzbnrzu42f, child number 0 ------------------------------------- select count(name) from test where status= :a
Plan hash value: 4130896540
-------------------------------------------------------------------------------------- | 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 | 100 | 2500 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
Note ----- - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement
|
當對status的繫結值傳入Inactive時，選用了我們進化後的baseline。由於列上存在直方圖資訊，我們看到了執行計劃的輸出基數部分非常的準確。這也要歸功於繫結變數窺探的作用。上面讀者可以再繼續試試多次執行這個SQL，執行計劃都會一直使用索引掃描。我們繼續試驗，看看重新重新整理共享池，對status的繫結之傳入Active會怎麼樣：
|
test@DLSP>alter system flush shared_pool;
System altered.
test@DLSP>exec :a :='Active';
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 49900
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID aa8mzbnrzu42f, child number 0 ------------------------------------- select 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 | 49900 | 1218K| 51 (2)| 00:00:01 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("STATUS"=:A)
Note ----- - SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement |
已經選用了我們最初建立的baseline，執行計劃已經走了全表掃描了。雖然baseline的作用是為了穩固執行計劃，但是就像我們看到的，由於同一個SQL存在2個可接受的baseline，因此執行計劃發生了不穩固的現象，繫結變數窺探的害處在這裡又重新體現出來了。其實繫結變數和ACS都會在baseline起作用前就會發生，但是繫結變數窺探和ACS發揮作用後產生的執行計劃必須從baseline中選用。因此就會發生前面產生的場景，繫結變數窺探導致已經使用baseline的SQL的執行計劃不穩定。這種情況可以透過ACS來解決：
|
--------開啟ACS test@DLSP>alter system set "_optimizer_extended_cursor_sharing_rel"=simple ;
System altered.
test@DLSP>alter system flush shared_pool;
System altered.
test@DLSP>exec :a :='Active';
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 49900
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID aa8mzbnrzu42f, child number 0 ------------------------------------- select 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 | 49900 | 1218K| 51 (2)| 00:00:01 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("STATUS"=:A)
Note ----- - SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement
23 rows selected.
test@DLSP> -- 檢查ACS狀態 test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='aa8mzbnrzu42f';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- -- 0 1 452 Y Y N
-- 直方圖 SELECT hash_value, sql_id, child_number, bucket_id, COUNT test@DLSP>test@DLSP> 2 FROM v$sql_cs_histogram 3 WHERE sql_id='aa8mzbnrzu42f' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ---------- -------------------------- ------------ ---------- ---------- 805113934 aa8mzbnrzu42f 0 0 0 805113934 aa8mzbnrzu42f 0 2 0 805113934 aa8mzbnrzu42f 0 1 1
test@DLSP>exec :a :='Inactive';
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 100
test@DLSP> -- 檢查ACS狀態 test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='aa8mzbnrzu42f';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- -- 0 2 662 Y Y N
test@DLSP>-- 直方圖 test@DLSP>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='aa8mzbnrzu42f' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ---------- -------------------------- ------------ ---------- ---------- 805113934 aa8mzbnrzu42f 0 0 1 805113934 aa8mzbnrzu42f 0 2 0 805113934 aa8mzbnrzu42f 0 1 1
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 100
test@DLSP> -- 檢查ACS狀態 test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='aa8mzbnrzu42f';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- -- 0 2 662 Y N N 1 1 102 Y Y Y
test@DLSP>-- 直方圖 test@DLSP>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='aa8mzbnrzu42f' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ---------- -------------------------- ------------ ---------- ---------- 805113934 aa8mzbnrzu42f 0 1 1 805113934 aa8mzbnrzu42f 0 0 1 805113934 aa8mzbnrzu42f 0 2 0 805113934 aa8mzbnrzu42f 1 1 0 805113934 aa8mzbnrzu42f 1 0 1 805113934 aa8mzbnrzu42f 1 2 0
6 rows selected.
--------檢視child_number為1的執行計劃 test@DLSP>select * from table(dbms_xplan.display_cursor('aa8mzbnrzu42f',1));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID aa8mzbnrzu42f, child number 1 ------------------------------------- select count(name) from test where status= :a
Plan hash value: 4130896540
-------------------------------------------------------------------------------------- | 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 | 100 | 2500 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
Note ----- - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement
test@DLSP>exec :a :='Active';
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 49900
test@DLSP> -- 檢查ACS狀態 test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE, 2 is_bind_aware 3 FROM v$sql 4 WHERE sql_id='aa8mzbnrzu42f';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- -- 0 2 662 Y N N 1 1 102 Y Y Y 2 1 210 Y Y Y
test@DLSP>-- 直方圖 test@DLSP>SELECT hash_value, sql_id, child_number, bucket_id, COUNT 2 FROM v$sql_cs_histogram 3 WHERE sql_id='aa8mzbnrzu42f' 4 ORDER BY sql_id, child_number;
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ---------- -------------------------- ------------ ---------- ---------- 805113934 aa8mzbnrzu42f 0 1 1 805113934 aa8mzbnrzu42f 0 0 1 805113934 aa8mzbnrzu42f 0 2 0 805113934 aa8mzbnrzu42f 1 0 1 805113934 aa8mzbnrzu42f 1 1 0 805113934 aa8mzbnrzu42f 1 2 0 805113934 aa8mzbnrzu42f 2 1 1 805113934 aa8mzbnrzu42f 2 0 0 805113934 aa8mzbnrzu42f 2 2 0
9 rows selected.
test@DLSP>select * from table(dbms_xplan.display_cursor('aa8mzbnrzu42f',2));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID aa8mzbnrzu42f, child number 2 ------------------------------------- select 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 | 49900 | 1218K| 51 (2)| 00:00:01 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("STATUS"=:A)
Note ----- - SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1241481/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）
- 控制執行計劃之-SPM BASELINE（三）
- 控制執行計劃之-SPM BASELINE（二）
- 控制執行計劃之-SPM BASELINE（一）
- 透過SPM手動新增執行計劃到baseLine
- 資料庫遷移，spm baseline 保持執行計劃的穩定性資料庫
- 【SPM】Oracle如何固定執行計劃Oracle
- baseline固定SQL執行計劃SQL
- 使用SPM和STA進行固定執行計劃
- SQL BASELINE修改固定執行計劃SQL
- 控制執行計劃之-SQL Profile（一）SQL
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- MySQL執行計劃解析（四）MySql
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle檢視執行計劃（四）Oracle
- 使用spm繫結執行計劃來線上優化資料庫優化資料庫
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 【最佳化】SPM(上）自動捕獲sql執行計劃並演進SQL
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- zt_oracle11g sql baseline與sql execution plan執行計劃OracleSQL
- Oracle訪問索引的執行計劃（四）Oracle索引
- 獲取執行計劃之Autotrace
- 十六、Mysql之Explain執行計劃MySqlAI
- 執行計劃-1：獲取執行計劃
- 【SPM】Oracle計劃管理器SPM介紹及用例Oracle
- mysql調優之——執行計劃explainMySqlAI
- MongoDb學習之Explain執行計劃MongoDBAI
- 執行計劃
- 通過內部的hint來控制執行計劃
- 【最佳化】SPM(下）手工捕獲sql執行計劃並檢視演進情況SQL
- Sqlserver執行計劃中表的四種連線方式SQLServer
- Oracle調優之看懂Oracle執行計劃Oracle
- mysql之 explain、optimizer_trace 執行計劃MySqlAI
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle