基線的進化

如果針對已經建立過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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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（三）

基線的進化

相關文章