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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-628960/