Maria Colgan
Master Product Manager
在本系列博客的第一篇（點此鏈接）,我們了解了如何創建SQL計劃基線（sql plan baseline)。為語句創建SQL計劃基線后，該語句的后續執行將使用SQL計劃基線。優化器將從SQL計劃基線中的所有計劃中,選擇當前環境中（包括綁定值、當前統計信息、參數等）成本最佳的計劃。優化器還將生成在沒有SQL計劃基線的情況下，所使用的最佳成本計劃。但是，并不會使用該最佳成本計劃，而是將其添加到執行計劃歷史記錄中，以供以后驗證。換句話說，優化器將使用來自SQL計劃基線的已知計劃，而不是一個新的、迄今為止未知的計劃。這就保證了性能不會下降。
讓我們看看這個執行計劃選擇過程的實際操作。首先，我們通過啟用自動計劃捕獲并執行兩次查詢來創建SQL計劃基線：
SQL> alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure successfully completed.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
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
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 |
----------------------------------------------------------------
25 rows selected.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.
SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
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_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執行計劃基線。假設語句再次被硬解析（我們在這里通過刷新共享池來實現）。讓我們關閉SQL執行計劃管理并使用不同的綁定值來執行查詢：
SQL> exec :pid := 100000;
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
960 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
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
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執行計劃管理并用同樣的綁定變量值再次執行：
SQL> alter session set optimizer_use_sql_plan_baselines = true;
Session altered.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
960 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
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
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 |
----------------------------------------------------------------
Note
-----
- SQL plan baseline SYS_SQL_PLAN_fcc170b0a62d0f4d used for this statement
底部的NOTE部分告訴我們優化器使用了SQL plan baseline。換句話說， 我們可以看到優化器使用了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
“ACCEPTED”列的“NO”值表示新執行計劃在執行計劃歷史記錄中，但在驗證它是一個好計劃之前不能使用。優化器將繼續使用已接受的計劃，直到新的計劃被驗證并添加到SQL計劃基線中。如果SQL計劃基線中有多個計劃，優化器將使用在當前條件下（統計信息、綁定值、參數設置等）成本最佳的計劃。
當為SQL語句創建SQL plan baseline時，SPM感知優化器因此而保證，不會使用SQL plan baseline中執行計劃之外的，任何新的執行計劃。這可以防止不期望的計劃變化，這些變化有時會導致性能下降。
阻止新執行計劃被使用是可以的，但是如果新計劃實際上更好呢？在本系列博客中的第3篇中，我們將描述如何將新的和改進的執行計劃添加到SQL執行計劃基線中。
原文鏈接：https://blogs.oracle.com/optimizer/sql-plan-management-part-2-of-4-spm-aware-optimizer
原文內容：
SQL Plan Management (Part 2 of 4) SPM Aware Optimizer
Maria Colgan
Master Product Manager
In Part 1, we saw how you can create SQL plan baselines. After you create a SQL plan baseline for a statement, subsequent executions of that statement will use the SQL plan baseline. From all the plans in the SQL plan baseline, the optimizer will select the one with the best cost in the current environment (including bind values, current statistics, parameters, etc.). The optimizer will also generate the best-cost plan that it would otherwise have used without a SQL plan baseline. However, this best-cost plan will not be used but instead added to the statement’s plan history for later verification. In other words, the optimizer will use a known plan from the SQL plan baseline instead of a new and hitherto unknown plan. This guarantees no performance regression.
Let’s see this plan selection process in action. First, we create a SQL plan baseline by enabling automatic plan capture and executing the query twice:
SQL> alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure successfully completed.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
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
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 |
----------------------------------------------------------------
25 rows selected.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.
SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
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_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
We can see that a SQL plan baseline was created for the statement. Suppose the statement is hard parsed again (we do it here by flushing the shared pool). Let’s turn off SQL plan management and execute the query with a different bind value:
SQL> exec :pid := 100000;
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
960 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
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
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 |
------------------------------------------
We can see that the optimizer selected a different plan because the new bind value makes the predicate less selective. Let’s turn SQL plan management back on and re-execute the query with the same bind value:
SQL> alter session set optimizer_use_sql_plan_baselines = true;
Session altered.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
960 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
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
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 |
----------------------------------------------------------------
Note
-----
- SQL plan baseline SYS_SQL_PLAN_fcc170b0a62d0f4d used for this statement
The note at the bottom tells you that the optimizer is using the SQL plan baseline. In other words, we can see that the optimizer used an accepted plan in the SQL plan baseline in favor of a new plan. In fact, we can also check that the optimizer inserted the new plan into the statement’s plan history:
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
The ‘NO’ value for the accepted column implies that the new plan is in the plan history but is not available for use until it is verified to be a good plan. The optimizer will continue to use an accepted plan until new plans are verified and added to the SQL plan baseline. If there is more than one plan in the SQL plan baseline, the optimizer will use the one with the best cost under the then-current conditions (statistics, bind values, parameter settings and so on).
When you create a SQL plan baseline for a SQL statement, the SPM aware optimizer thus guarantees that no new plans will be used other than the ones in the SQL plan baseline. This prevents unexpected plan changes that sometimes lead to performance regressions.
Preventing new plans from being used is fine, but what if the new plans are in fact better? In Part 3, we will describe how new and improved plans are added to a SQL plan baseline.
