ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle 11g sql plan baseline(3)演化baseline

oracle 11g sql plan baseline(3)演化baseline

原创 Linux操作系统 作者：fufuh2o 时间：2010-03-09 13:17:20 0 删除 编辑

 

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博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-628960/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： oracle 11g sql plan baseline(2)调整baselines属性
下一篇： oracle 11g sql plan baseline(4)使用baseline覆盖hint
请登录后发表评论 登录
全部评论
fufuh2o

注册时间：2009-06-26

  • 博文量
    182
  • 访问量
    471639

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593