基线的进化
如果针对已经创建过baseline的sql,优化器意识到可能有更好的执行计划,那么优化器会自动生成一个baseline,这个baselne在视图中dba_sql_plan_baselines的accepted状态为NO。DBA可以通过两种方式来对baseline进行进化:dbms_spm.evolve_sql_plan_baseline函数和 SQL Tuning Advisor。
我们在test的status字段上建立索引后,再次执行查询,发生在dba_sql_plan_baselines中又产生了一个新的baseline,这个baseline的产生方式是:AUTO-CAPTURE,accepted为NO。接下来我们分别通过函数dbms_spm.evolve_sql_plan_baseline和 SQL Tuning Advisor两种方式来进行进化baseline。
1) dbms_spm包的方式
下面的代码我们通过dbms_spm 包的evolve_sql_plan_baseline函数来进化baseline,verify参数设置为了YES:只有性能确实有提升的情况下才会进行进化。
函数dbms_spm.evolve_sql_plan_baseline执行后,会产生出一个report,详细的对比了2个baseline对应的执行计划的消耗资源的差异。由于待进化的baseline经过验证后,性能确实有提高,因此优化器接收了这个baseline。如下代码也显示了,视图dba_sql_plan_baselines中的accepted字段也已经从NO变为了YES。重新执行查询,也已经使用到了我们新创建的baseline。
2) SQL Tuning Advisor方式
我们重新倒回到baseline还没进化的时候。
test@DLSP>select sql_handle, plan_name, origin, accepted,fixed 2 from dba_sql_plan_baselines 3 where sql_text like '%count(name)%';
SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED -------------------- -------------------------------- --------------- ------ ------ SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE NO NO SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO |
我们通过dbms_sqltune包的CREATE_TUNING_TASK函数来创建一个调优任务。
调优任务执行结束后会生成一个report,report里给出了建议,让我们接受一个sql profile来完成优化任务。
test@DLSP>execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_980',task_owner => 'TEST', replace => TRUE);
PL/SQL procedure successfully completed.
test@DLSP>select sql_handle, plan_name, origin, accepted,fixed 2 from dba_sql_plan_baselines 3 where sql_text like '%count%';
SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED -------------------- ------------------------------ ---------------- ------ ------ SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE YES NO
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID aa8mzbnrzu42f, child number 0 ------------------------------------- select count(name) from test where status= :a
Plan hash value: 4130896540
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 25 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2500 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
Note ----- - SQL profile SYS_SQLPROF_0147811f30c60000 used for this statement - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement |
接受SQL PROFILE后,之前为不可接受状态的baseline也已经变为可接受状态。执行这个SQL后查看执行计划,已经走了索引扫描,而且执行计划的Note部分显示,这个SQL同时使用到了SQL profile和baseline。这里我们可以简单的证明一下,一个SQL语句可以同时使用到SQL profile和baseline,并且两个都会发挥作用。我们上面例子里,虽然通过Note部分看到SQL profile和baseline都使用了,但是由于2个所实现的功能都是一样的,都是让执行计划走索引扫描,因此不能确认两个都发挥了作用或者说不能确认是哪个发挥了作用。我们可以构造一个例子:
1)让SQL profile做一件事,这个事baseline没有做
2)让baseline做一件事,这个事SQL profile没有做
3)上面所做的两个事保证不能冲突(比如一个全表扫描,一个索引扫描会冲突)
我们可以让baseline不动,然后删除生成的SQL profile,然后重新为这个SQL产生一个SQL profile,增加gather_plan_statistics这个hint到SQL里。
test@DLSP>exec dbms_sqltune.drop_sql_profile('profile_aa8mzbnrzu42f_dwrose');
PL/SQL procedure successfully completed.
test@DLSP>var a varchar2(100) test@DLSP>exec :a :='Inactive';
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 100 test@DLSP>@profile Enter value for sql_id: aa8mzbnrzu42f
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID aa8mzbnrzu42f, child number 0 ------------------------------------- select count(name) from test where status= :a
Plan hash value: 4130896540
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 25 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2500 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Outline Data -------------
/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 10) OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."STATUS")) END_OUTLINE_DATA */
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
Note ----- - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement
40 rows selected.
Enter value for hint_text: gather_plan_statistics
Profile profile_aa8mzbnrzu42f_dwrose created.
test@DLSP>select count(name) from test where status= :a;
COUNT(NAME) ----------- 100
test@DLSP>select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID aa8mzbnrzu42f, child number 0 ------------------------------------- select count(name) from test where status= :a
Plan hash value: 4130896540
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 102 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 102 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 100 | 100 |00:00:00.01 | 102 | |* 3 | INDEX RANGE SCAN | T_IND | 1 | 100 | 100 |00:00:00.01 | 2 | ------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("STATUS"=:A)
Note ----- - SQL profile profile_aa8mzbnrzu42f_dwrose used for this statement - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-1241479/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-1241479/