作者：Maria Colgan
Master Product Manager
在本系列博客的第二篇（點(diǎn)此鏈接）的示例中，我們看到優(yōu)化器使用已接受的執(zhí)行計(jì)劃，而不是全新的執(zhí)行計(jì)劃。在語句的執(zhí)行計(jì)劃歷史記錄中有兩個(gè)執(zhí)行計(jì)劃，但在SQL plan baseline中只有一個(gè)已接受的執(zhí)行計(jì)劃：
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
未被接受的執(zhí)行計(jì)劃可以通過執(zhí)行evolve_sql_plan_baseline函數(shù)來驗(yàn)證。該函數(shù)會(huì)執(zhí)行未接受的執(zhí)行計(jì)劃，并和已接受的最佳執(zhí)行計(jì)劃進(jìn)行性能比較。執(zhí)行時(shí)使用影響到該執(zhí)行計(jì)劃被添加到執(zhí)行計(jì)劃歷史時(shí)的條件（比如綁定變量，參數(shù)等）。如果未接受的執(zhí)行計(jì)劃的性能更好，該函數(shù)會(huì)設(shè)置其為已接受，從而將其添加到SQL plan baseline中。讓我們看一看執(zhí)行該函數(shù)時(shí)會(huì)發(fā)生什么：
SQL> var report clob;
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();
PL/SQL procedure successfully completed.
SQL> print :report
REPORT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_fcc170b08cbcb825
-----------------------------------
Plan was verified: Time used .1 seconds.
Passed performance criterion: Compound improvement ratio >= 10.13
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 960 960
Elapsed Time(ms): 19 15 1.27
CPU Time(ms): 18 15 1.2
Buffer Gets: 1188 116 10.24
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
執(zhí)行計(jì)劃驗(yàn)證報(bào)告顯示新的執(zhí)行計(jì)劃的性能更好，所以，它被置為已接受，并成為SQL執(zhí)行計(jì)劃基線的一部分。我們可以通過查詢dba_sql_plan_baselines視圖來確認(rèn)：
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
SQL執(zhí)行計(jì)劃基線現(xiàn)在有了兩個(gè)已接受的執(zhí)行計(jì)劃：SYS_SQL_plan_fcc170b08cb825現(xiàn)在被接受。你可以手動(dòng)執(zhí)行evolve_sql_plan_baseline（）函數(shù)，也可以安排它在維護(hù)窗口中自動(dòng)運(yùn)行。
演進(jìn)SQL執(zhí)行計(jì)劃基線的另一種方法是使用SQL優(yōu)化顧問。來替代執(zhí)行evolve_sql_plan_baseline，假設(shè)我們從原始狀態(tài)開始，其中有一個(gè)已接受的計(jì)劃和一個(gè)未接受的計(jì)劃：
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
你可以對緩存中的游標(biāo)執(zhí)行SQL優(yōu)化顧問:
SQL> var tname varchar2(30);
SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname);
PL/SQL procedure successfully completed.
SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_505
Tuning Task Owner : SH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/11/2008 16:43:12
Completed at : 11/11/2008 16:43:13
-------------------------------------------------------------------------------
Schema Name: SH
SQL ID : bfbr3zrg9d5cc
SQL Text : select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- A potentially better execution plan was found for this statement.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 5 | INDEX RANGE SCAN | PRODUCTS_PK |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 10 | INDEX UNIQUE SCAN | TIME_PK |
| 11 | TABLE ACCESS BY INDEX ROWID | TIMES |
----------------------------------------------------------------
2- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 5 | INDEX RANGE SCAN | PRODUCTS_PK |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 10 | INDEX UNIQUE SCAN | TIME_PK |
| 11 | TABLE ACCESS BY INDEX ROWID | TIMES |
----------------------------------------------------------------
3- Using SQL Profile
--------------------
Plan hash value: 2361178149
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | PARTITION RANGE ALL| |
| 4 | TABLE ACCESS FULL | SALES |
| 5 | TABLE ACCESS FULL | TIMES |
| 6 | TABLE ACCESS FULL | PRODUCTS |
------------------------------------------
-------------------------------------------------------------------------------
SQL> exec dbms_sqltune.accept_sql_profile(task_name => :tname);
PL/SQL procedure successfully completed.
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
SQL> select sql_text, type, status from dba_sql_profiles;
SQL_TEXT TYPE STATUS
---------------------------------------- ------- --------
select p.prod_name, s.amount_sold, t.cal MANUAL ENABLED
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
我們在這里看到的是，sql優(yōu)化顧問發(fā)現(xiàn)了一個(gè)經(jīng)過調(diào)優(yōu)的計(jì)劃（碰巧在我們的計(jì)劃歷史中，這個(gè)計(jì)劃是不可接受的）。當(dāng)我們接受推薦的SQL profile時(shí)，SQL調(diào)優(yōu)顧問創(chuàng)建了一個(gè)SQL profile文件，并將未接受的計(jì)劃更改為accepted狀態(tài)，從而將SQL計(jì)劃基線演進(jìn)為兩個(gè)計(jì)劃。
請注意，SQL優(yōu)化顧問還可能會(huì)找到一個(gè)全新的優(yōu)化計(jì)劃，該計(jì)劃不在計(jì)劃歷史記錄中。如果您隨后接受建議的SQL profile文件，那么SQL調(diào)優(yōu)顧問將創(chuàng)建一個(gè)SQL profile文件，并將優(yōu)化后的計(jì)劃添加到SQL計(jì)劃基線中。
因此，您可以通過執(zhí)行evolve_SQL_plan_baseline函數(shù)或使用SQL調(diào)優(yōu)顧問來演進(jìn)SQL計(jì)劃基線。新的、可證明更好的計(jì)劃將通過上述方法之一，添加到SQL執(zhí)行計(jì)劃基線中。
在接下來的第4篇中，我們將展示一些用戶界面，描述SPM與其他特性的交互，并回答您的一些問題。
原文鏈接：https://blogs.oracle.com/optimizer/sql-plan-management-part-3-of-4:-evolving-sql-plan-baselines
原文內(nèi)容：
SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines
Maria Colgan
Master Product Manager
In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline:
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using the conditions (e.g., bind values, parameters, etc.) in effect at the time the non-accepted plan was added to the plan history. If the non-accepted plan’s performance is better, the function will make it accepted, thus adding it to the SQL plan baseline. Let’s see what happens when we execute this function:
SQL> var report clob;
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();
PL/SQL procedure successfully completed.
SQL> print :report
REPORT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_fcc170b08cbcb825
-----------------------------------
Plan was verified: Time used .1 seconds.
Passed performance criterion: Compound improvement ratio >= 10.13
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 960 960
Elapsed Time(ms): 19 15 1.27
CPU Time(ms): 18 15 1.2
Buffer Gets: 1188 116 10.24
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
The plan verification report shows that the new plan’s performance was better and so it was made accepted and became part of the SQL plan baseline. We can confirm it by looking in the dba_sql_plan_baselines view:
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
The SQL plan baseline now has two accepted plans: SYS_SQL_PLAN_fcc170b08cbcb825 is now accepted.
You can either execute the evolve_sql_plan_baseline() function manually or schedule it to run automatically in a maintenance window.
Another way of evolving a SQL plan baseline is to use the SQL Tuning Advisor. Instead of executing evolve_sql_plan_baseline, suppose we start from the original state where we have one accepted and one non-accepted plan:
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
You can execute the SQL Tuning Advisor on the cursor in the cursor cache:
SQL> var tname varchar2(30);
SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname);
PL/SQL procedure successfully completed.
SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_505
Tuning Task Owner : SH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/11/2008 16:43:12
Completed at : 11/11/2008 16:43:13
-------------------------------------------------------------------------------
Schema Name: SH
SQL ID : bfbr3zrg9d5cc
SQL Text : select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- A potentially better execution plan was found for this statement.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 5 | INDEX RANGE SCAN | PRODUCTS_PK |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 10 | INDEX UNIQUE SCAN | TIME_PK |
| 11 | TABLE ACCESS BY INDEX ROWID | TIMES |
----------------------------------------------------------------
2- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 5 | INDEX RANGE SCAN | PRODUCTS_PK |
| 6 | PARTITION RANGE ALL | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 | BITMAP CONVERSION TO ROWIDS | |
| 9 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 10 | INDEX UNIQUE SCAN | TIME_PK |
| 11 | TABLE ACCESS BY INDEX ROWID | TIMES |
----------------------------------------------------------------
3- Using SQL Profile
--------------------
Plan hash value: 2361178149
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | PARTITION RANGE ALL| |
| 4 | TABLE ACCESS FULL | SALES |
| 5 | TABLE ACCESS FULL | TIMES |
| 6 | TABLE ACCESS FULL | PRODUCTS |
------------------------------------------
-------------------------------------------------------------------------------
SQL> exec dbms_sqltune.accept_sql_profile(task_name => :tname);
PL/SQL procedure successfully completed.
SQL> select sql_text, plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
SQL> select sql_text, type, status from dba_sql_profiles;
SQL_TEXT TYPE STATUS
---------------------------------------- ------- --------
select p.prod_name, s.amount_sold, t.cal MANUAL ENABLED
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
What we see here is that SQL Tuning Advisor found a tuned plan (that coincidentally happened to be the non-accepted plan in our plan history). When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans.
Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.
Thus, you can evolve a SQL plan baseline either by executing the evolve_sql_plan_baseline function or by using the SQL Tuning Advisor. New and provably better plans will be added by either of these methods to the SQL plan baseline.
