Oracle 11g 执行计划管理2
1.创建测试数据
SQL> conn NC50/NC50 Connected. SQL> create table tab1(id number,object_name varchar2(100)); SQL> insert into tab1 select rownum,object_name from dba_objects; SQL> commit; SQL> set line 180 SQL> select * from tab1 where id=200; SQL> select * from tab1 where id=200;
尽管执行两次，但是这时去查询dba_sql_plan_baselines，试图找到SQL文本为select * from tab1 where id=200;的记录时，会发现没有记录，因为optimizer_capture_sql_plan_baselines缺省为false。我们将该参数设置为true以后继续测试
SQL> show parameter optimizer_capture_sql_plan_baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE SQL> select * from dba_sql_plan_baselines where SQL_TEXT like '%tab1%'; no rows selected SQL>
2.开启自动捕获
SQL> alter session set optimizer_capture_sql_plan_baselines=true; SQL> set line 180 SQL> set autotrace off SQL> select * from tab1 where id=200; ID OBJECT_NAME ---------- ---------------------------------------------------------------------------------------------------- 200 SYS_LOB0000000207C00007$$ --查看对应的执行计划 SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge 2 from dba_sql_plan_baselines 3 where sql_text like 'select * from tab1 where id=200'; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT ---------- ------------------------------ ------------------------------ -------------- --- --- --- --- 8.3850E+18 SYS_SQL_745da4c2e5a4b97a SQL_PLAN_78rd4sbku9fbuec7b7588 AUTO-CAPTURE YES YES NO YES SQL>
可以看到，SQL语句在plan history里产生了一个执行计划。其中: sql_handle表示SQL语句的句柄； plan_name则表示该SQL的执行计划的名字； origin表示该执行计划是如何进入plan history的，该列值为AUTO-CAPTURE则说明是由优化器自动加入的，如果为MANUAL则说明是由DBA手工加入的； Enabled (控制活动)： + YES (活动的，但不一定会被使用) + NO (可以理解为被标记删除) Accepted(控制使用)： + YES (只有 “Enabled” 并且“Accepted” 的计划才会被选择使用) + NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执行) Fixed(控制优先级)： + YES (如果是“Enabled”并且“Accepted”，会优先选择这个计划，这个计划会被视为不需要改变的) + NO (普通的计划，无需优先) autopurge表示该执行计划是否为定期自动删除，YES表示是，NO表示否。
继续测试，在id上添加一个索引，从而让原来的SQL不走全表扫描，而改走索引扫描
SQL> create index idx_tab1 on tab1(id); SQL> exec dbms_stats.gather_table_stats('NC50','tab1',cascade=>true); SQL> select * from tab1 where id=200; ID OBJECT_NAME ---------- ---------------------------------------------------------------------------------------------------- 200 SYS_LOB0000000207C00007$$ SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge 2 from dba_sql_plan_baselines 3 where sql_text like 'select * from tab1 where id=200'; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT ---------- ------------------------------ ------------------------------ -------------- --- --- --- --- 8.3850E+18 SYS_SQL_745da4c2e5a4b97a SQL_PLAN_78rd4sbku9fbu61724234 AUTO-CAPTURE YES NO NO YES 8.3850E+18 SYS_SQL_745da4c2e5a4b97a SQL_PLAN_78rd4sbku9fbuec7b7588 AUTO-CAPTURE YES YES NO YES
这时我们可以看到，dba_sql_plan_baselines视图里多了一个执行计划，也就是我们后面那个使用了索引扫描的执行计划。而该执行计划的accepted为NO，说明该计划并没有进入plan baseline里，但是进入了plan history里。因为新生成的为不是ACCEPTED，所以不被启用。验证如下：
SQL> set autotrace traceonly explain SQL> select * from tab1 where id=200; Execution Plan ---------------------------------------------------------- Plan hash value: 2211052296 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 112 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TAB1 | 1 | 28 | 112 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=200) Note ----- - SQL plan baseline "SQL_PLAN_78rd4sbku9fbuec7b7588" used for this statement SQL>
启用的执行计划不是高效的（SQL_PLAN_78rd4sbku9fbu61724234），而是全表扫描的（SQL_PLAN_78rd4sbku9fbuec7b7588）。启用高效的执行计划之前，需要
手动将执行计划演化成ACCEPTED
3.现将新生成的执行计划演化成ACCEPTED
SQL> set autotrace off SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_745da4c2e5a4b97a') FROM dual; SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge 2 from dba_sql_plan_baselines 3 where sql_text like 'select * from tab1 where id=200'; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT ---------- ------------------------------ ------------------------------ -------------- --- --- --- --- 8.3850E+18 SYS_SQL_745da4c2e5a4b97a SQL_PLAN_78rd4sbku9fbu61724234 AUTO-CAPTURE YES YES NO YES 8.3850E+18 SYS_SQL_745da4c2e5a4b97a SQL_PLAN_78rd4sbku9fbuec7b7588 AUTO-CAPTURE YES YES NO YES SQL> --查看 当前所走的执行计划 SQL> set autotrace traceonly explain SQL> select * from tab1 where id=200; Execution Plan ---------------------------------------------------------- Plan hash value: 2722636538 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 28 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TAB1 | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=200) Note ----- - SQL plan baseline "SQL_PLAN_78rd4sbku9fbu61724234" used for this statement SQL>
结论：存在多个（ “Enabled”“Accepted”）的计划时，选择cost最小的
4.删除Plans 和 Baselines
DECLARE l_plans_dropped PLS_INTEGER; BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( sql_handle => 'SYS_SQL_745da4c2e5a4b97a', plan_name => 'SQL_PLAN_78rd4sbku9fbu61724234'); DBMS_OUTPUT.put_line(l_plans_dropped); END; SQL> set autotrace off SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge 2 from dba_sql_plan_baselines 3 where sql_text like 'select * from tab1 where id=200'; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT ---------- ------------------------------ ------------------------------ -------------- --- --- --- --- 8.3850E+18 SYS_SQL_745da4c2e5a4b97a SQL_PLAN_78rd4sbku9fbuec7b7588 AUTO-CAPTURE YES YES NO YES SQL>
