oracle 11g sql plan baseline(3)演化baseline
baseline的演化
當sql語句執行時候根據sql_text算出簽名去log裡找sql plan baselines，如果存在sql plan baselines，就需要用當前統計資訊產生的執行計劃和sql plan baselines中的plan比較，如果
不一致，就將新的plan存入資料字典，成為新的sql plan baselines為noaccept的，此時還是使用已經存在且狀態為accept的sql plan baseline提供的執行計劃，即便新產生的sql plan
baseline 效率更好，但因為為noaccept還是不會使用,此時就需要演化evolution(evo),演化時候進行比較看看哪個執行計劃更好,好的話 讓其變為accept,這樣query optimizer就可以用了
測試,建立實驗表,及其sql plan baseline
SQL> drop table t1;
Table dropped.
SQL> create table t1 (a int);
Table created.
SQL> declare
2 begin
3 for i in 1..3000 loop
4 insert into t1 values(i);
5 end loop;
6 commit;
7 end ;
8 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('SYS','T1');
PL/SQL procedure successfully completed.
SQL> select * from t1 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fmq5wnan0m99r, child number 0
-------------------------------------
select * from t1 where a=2
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
18 rows selected.
現在的執行計劃是走fts
這次用自動捕捉建議一個sql plan baselines,原理看(1)
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
Session altered.
SQL> select * from t1 where a=2;
A
----------
2
SQL> select * from t1 where a=2;
A
----------
2
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = false;
Session altered.
SQL> SELECT sql_handle, plan_name
2 FROM dba_sql_plan_baselines
3 WHERE creator = user
4 AND created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4dbd90e8e ~~~~~~~~~~~走fts 的sql plan baselines
SQL> SELECT sql_handle, plan_name,sql_text
2 FROM dba_sql_plan_baselines
3 WHERE creator = user
4 AND created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4dbd90e8e
select * from t1 where a=2
建立一個index
SQL> create index ind_t1 on t1(a);
Index created.
SQL> select * from t1 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fmq5wnan0m99r, child number 1
-------------------------------------
select * from t1 where a=2
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=2)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_55b659b4dbd90e8e used for this statement
22 rows selected.
使用了baseline 還是走了fts
SQL> select * from t1 where a=2;
A
----------
2
SQL> SELECT sql_handle, plan_name,sql_text
2 ,accepted FROM dba_sql_plan_baselines
3 WHERE creator = user
4 AND created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ACC
---
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4dbd90e8e
select * from t1 where a=2
YES
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4f9529f4b ~~~~~~~~~~~~~~~~一個新的baseline走index但還未accept
select * from t1 where a=2
NO
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ACC
---
SQL> col sql_text for a20
SQL> SELECT sql_handle, plan_name,sql_text
2 ,accepted FROM dba_sql_plan_baselines
3 WHERE creator = user
4 AND created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ACC
-------------------- ---
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4dbd90e8e
select * from t1 whe YES
re a=2
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4f9529f4b
select * from t1 whe NO
re a=2
開始evo
time_limit:演化時間,單位分鐘,或dbms_spm.auto_limit(no_limit)
verify:yes(default),預設將實際執行sql驗證基於此sql plan baseline的 plan 效能,no的話不執行,直接接受baselines
commit:yes(defautt)預設資料字典按演化結果修改(dba_sql_plan_baselines,accepted改為yes),no的話不修改.
SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_0a48a24255b659b4',plan_name=>'SYS_SQL_PLAN_55b659b4f9529f4b',time_limit=>10,verify=>'yes',commit=>'yes') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_0A48A24255B659B4',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
當時忘記設定set long了 所以輸出看不到
SQL> set long 99999
SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_0a48a24255b659b4',plan_name=>'SYS_SQL_PLAN_55b659b4f9529f4b',time_limit=>10,verify=>'yes',commit=>'yes') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_0A48A24255B659B4',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_0a48a24255b659b4
PLAN_NAME = SYS_SQL_PLAN_55b659b4f9529f4b
TIME_LIMIT = 10
VERIFY = yes
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_0A48A24255B659B4',PLAN_NA
--------------------------------------------------------------------------------
COMMIT = yes
Plan: SYS_SQL_PLAN_55b659b4f9529f4b
-----------------------------------
It is already an accepted plan.
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
There were no SQL plan baselines that required processing.
再次執行報錯誤,說明已經是accepted的了,不可重複演化
SQL> SELECT sql_handle, plan_name,sql_text
,accepted FROM dba_sql_plan_baselines
WHERE creator = user
2 3 4 AND created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT ACC
-------------------- ---
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4dbd90e8e
select * from t1 whe YES
re a=2
SYS_SQL_0a48a24255b659b4 SYS_SQL_PLAN_55b659b4f9529f4b
select * from t1 whe YES
re a=2
SQL> select * from t1 where a=2;
A
----------
2
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fmq5wnan0m99r, child number 0
-------------------------------------
select * from t1 where a=2
Plan hash value: 1882569892
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IND_T1 | 1 | 3 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=2)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_55b659b4f9529f4b used for this statement
22 rows selected.
經過演化使用了走index的 sql plan baseline,多個sqlplan baselines時候 都為accept,比較cost 誰小用誰,所以用了走index的
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-628960/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 11g sql plan baseline(4)使用baseline覆蓋hintOracleSQL
- oracle 11g sql plan baseline(1)基本使用OracleSQL
- Oracle SQL Plan Baseline 學習OracleSQL
- oracle 11g sql plan baseline(5)baseline的問題和補充OracleSQL
- sql_plan_baselineSQL
- sql plan baseline（二）SQL
- sql plan baseline使用心得SQL
- oracle 11g sql plan baseline(2)調整baselines屬性OracleSQL
- Oracle SQL baselineOracleSQL
- Oracle OCP 1Z0-053 Q252(SQL Plan Baseline)OracleSQL
- zt_oracle11g sql baseline與sql execution plan執行計劃OracleSQL
- SQL Plan Baseline與Shared Cursor機制研究（一）SQL
- SQL Plan Baseline與Shared Cursor機制研究（二）SQL
- SQL Plan Baseline與Shared Cursor機制研究（三）SQL
- sql pan baselineSQL
- oracle baseline基線_awrOracle
- Oracle OCP 1Z0 053 Q201(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE)OracleSQL
- baseline固定SQL執行計劃SQL
- baseline依賴SQL文字還是SQL ID?SQL
- 11203測試sql baselineSQL
- SQL BASELINE修改固定執行計劃SQL
- 關於Oracle baseline的幾點Oracle
- sql profile和baseline的協作關係SQL
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- 關於Oracle Baseline和DBMS_SQLTUNE工具OracleSQL
- Oracle 11g 新特性 -- SQL Plan Management 示例OracleSQL
- Oracle 11g SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- align-items:baseline 作用
- 深度剖析Baseline設計原理
- benchmark和baseline的區別
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- 【DBA】Oracle 11g 針對SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- 布匹缺陷檢測baseline提升過程
- 理解awr中的基準線(baseline)
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）
- 控制執行計劃之-SPM BASELINE（四）
- 控制執行計劃之-SPM BASELINE（三）