SPM BASELINE怎么用
这篇文章主要介绍"SPM BASELINE怎么用"，在日常操作中，相信很多人在SPM BASELINE怎么用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"SPM BASELINE怎么用"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
基线的进化
如果针对已经创建过baseline的sql，优化器意识到可能有更好的执行计划，那么优化器会自动生成一个baseline，这个baselne在视图中dba_sql_plan_baselines的accepted状态为NO。DBA可以通过两种方式来对baseline进行进化：dbms_spm.evolve_sql_plan_baseline函数和 SQL Tuning Advisor。
test@DLSP>create index t_ind on test(status); Index created. test@DLSP>select count(name) from test where status= :a; COUNT(NAME) ----------- 100 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 |
我们在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：只有性能确实有提升的情况下才会进行进化。
test@DLSP>SELECT dbms_spm.evolve_sql_plan_baseline( 2 sql_handle => 'SQL_619bd8394153fd05', 3 plan_name => 'SQL_PLAN_636ys750p7z8519ccc485', 4 time_limit => 10, 5 verify => 'yes', 6 commit => 'yes' 7 ) 8 FROM dual; DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_619BD8394153FD05',PLAN_NAME= ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SQL_619bd8394153fd05 PLAN_NAME = SQL_PLAN_636ys750p7z8519ccc485 TIME_LIMIT = 10 VERIFY = yes COMMIT = yes Plan: SQL_PLAN_636ys750p7z8519ccc485 ------------------------------------ Plan was verified: Time used .05 seconds. Plan passed performance criterion: 2.06 times better than baseline plan. Plan was changed to an accepted plan. Baseline Plan Test Plan Stats Ratio ------------- --------- ----------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 2.167 .253 8.57 CPU Time(ms): 2.221 .222 10 Buffer Gets: 210 102 2.06 Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of plans verified: 1 Number of plans accepted: 1 |
函数dbms_spm.evolve_sql_plan_baseline执行后，会产生出一个report，详细的对比了2个baseline对应的执行计划的消耗资源的差异。由于待进化的baseline经过验证后，性能确实有提高，因此优化器接收了这个baseline。如下代码也显示了，视图dba_sql_plan_baselines中的accepted字段也已经从NO变为了YES。重新执行查询，也已经使用到了我们新创建的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 YES NO SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO test@DLSP>select count(name) from test where status= :a; COUNT(NAME) ----------- 100 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 plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement |
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函数来创建一个调优任务。
test@DLSP>var c varchar2(100) test@DLSP>exec :c := dbms_sqltune.CREATE_TUNING_TASK(SQL_ID=>'aa8mzbnrzu42f') PL/SQL procedure successfully completed. test@DLSP>exec dbms_sqltune.execute_tuning_task(task_name => :c) PL/SQL procedure successfully completed. test@DLSP>select dbms_sqltune.report_tuning_task(:c) from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK(:C) -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_980 Tuning Task Owner : TEST Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 07/29/2014 15:48:50 Completed at : 07/29/2014 15:48:51 ------------------------------------------------------------------------------- Schema Name: TEST SQL ID : aa8mzbnrzu42f SQL Text : select count(name) from test where status= :a Bind Variables : 1 - (VARCHAR2(2000)):Inactive ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 51.46%) ------------------------------------------ - Consider accepting the recommended SQL profile. The SQL plan baseline corresponding to the plan with the SQL profile will also be updated to an accepted plan. execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_980', task_owner => 'TEST', replace => TRUE); Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time. Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .002302 .000358 84.44 % CPU Time (s): .002199 .0003 86.35 % User I/O Time (s): 0 0 Buffer Gets: 210 102 51.42 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions. |
调优任务执行结束后会生成一个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 |
到此，关于"SPM BASELINE怎么用"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！
