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(3)演化baseline

相關文章