您好,登录后才能下订单哦!
这篇文章主要介绍“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怎么用”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。