溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“SPM BASELINE怎么用”，在日常操作中，相信很多人在SPM BASELINE怎么用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對(duì)大家解答”SPM BASELINE怎么用”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！
如果針對(duì)已經(jīng)創(chuàng)建過baseline的sql，優(yōu)化器意識(shí)到可能有更好的執(zhí)行計(jì)劃，那么優(yōu)化器會(huì)自動(dòng)生成一個(gè)baseline，這個(gè)baselne在視圖中dba_sql_plan_baselines的accepted狀態(tài)為NO。DBA可以通過兩種方式來對(duì)baseline進(jìn)行進(jìn)化：dbms_spm.evolve_sql_plan_baseline函數(shù)和 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 |
我們?cè)趖est的status字段上建立索引后，再次執(zhí)行查詢，發(fā)生在dba_sql_plan_baselines中又產(chǎn)生了一個(gè)新的baseline，這個(gè)baseline的產(chǎn)生方式是：AUTO-CAPTURE，accepted為NO。接下來我們分別通過函數(shù)dbms_spm.evolve_sql_plan_baseline和 SQL Tuning Advisor兩種方式來進(jìn)行進(jìn)化baseline。
1) dbms_spm包的方式
下面的代碼我們通過dbms_spm 包的evolve_sql_plan_baseline函數(shù)來進(jìn)化baseline，verify參數(shù)設(shè)置為了YES：只有性能確實(shí)有提升的情況下才會(huì)進(jìn)行進(jìn)化。
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 |
函數(shù)dbms_spm.evolve_sql_plan_baseline執(zhí)行后，會(huì)產(chǎn)生出一個(gè)report，詳細(xì)的對(duì)比了2個(gè)baseline對(duì)應(yīng)的執(zhí)行計(jì)劃的消耗資源的差異。由于待進(jìn)化的baseline經(jīng)過驗(yàn)證后，性能確實(shí)有提高，因此優(yōu)化器接收了這個(gè)baseline。如下代碼也顯示了，視圖dba_sql_plan_baselines中的accepted字段也已經(jīng)從NO變?yōu)榱薡ES。重新執(zhí)行查詢，也已經(jīng)使用到了我們新創(chuàng)建的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還沒進(jìn)化的時(shí)候。
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函數(shù)來創(chuàng)建一個(gè)調(diào)優(yōu)任務(wù)。
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.
|
調(diào)優(yōu)任務(wù)執(zhí)行結(jié)束后會(huì)生成一個(gè)report，report里給出了建議，讓我們接受一個(gè)sql profile來完成優(yōu)化任務(wù)。
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后，之前為不可接受狀態(tài)的baseline也已經(jīng)變?yōu)榭山邮軤顟B(tài)。執(zhí)行這個(gè)SQL后查看執(zhí)行計(jì)劃，已經(jīng)走了索引掃描，而且執(zhí)行計(jì)劃的Note部分顯示，這個(gè)SQL同時(shí)使用到了SQL profile和baseline。這里我們可以簡單的證明一下，一個(gè)SQL語句可以同時(shí)使用到SQL profile和baseline，并且兩個(gè)都會(huì)發(fā)揮作用。我們上面例子里，雖然通過Note部分看到SQL profile和baseline都使用了，但是由于2個(gè)所實(shí)現(xiàn)的功能都是一樣的，都是讓執(zhí)行計(jì)劃走索引掃描，因此不能確認(rèn)兩個(gè)都發(fā)揮了作用或者說不能確認(rèn)是哪個(gè)發(fā)揮了作用。我們可以構(gòu)造一個(gè)例子：
1）讓SQL profile做一件事，這個(gè)事baseline沒有做
2）讓baseline做一件事，這個(gè)事SQL profile沒有做
3）上面所做的兩個(gè)事保證不能沖突（比如一個(gè)全表掃描，一個(gè)索引掃描會(huì)沖突）
我們可以讓baseline不動(dòng)，然后刪除生成的SQL profile，然后重新為這個(gè)SQL產(chǎn)生一個(gè)SQL profile，增加gather_plan_statistics這個(gè)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 |
到此，關(guān)于“SPM BASELINE怎么用”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
