※本記事は、Nigel Bayliss による”SQL Plan Management 3 of 4 – Evolving SQL Plan Baselines” (2024/12/8)を翻訳したものです。
Part 2 の例では、optimizer が新しい plan ではなく、accepted plan を使用したことを確認しました。この statement の plan history には 2 つの plan がありますが、accepted なのは 1 つだけで、そのため SQL plan baseline に含まれているのもその 1 つだけです。
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 < :pidnon-accepted plan は、evolve_sql_plan_baseline function を実行して検証できます。この function は non-accepted plan を実行し、その性能を最良の accepted plan と比較します。実行には、non-accepted plan が plan history に追加された時点の条件（bind value、parameter など）が使われます。non-accepted plan の性能がより良ければ、その plan は accepted となり、SQL plan baseline に追加されます。
この 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.plan verification report から、新しい plan のほうが高性能だったため accepted となり、SQL plan baseline の一部になったことが分かります。これは 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これで SQL plan baseline には accepted plan が 2 つになりました。SYS_SQL_PLAN_fcc170b08cbcb825 も accepted になっています。
evolve_sql_plan_baseline() function は手動で実行することも、maintenance window で自動実行するようスケジュールすることもできます。
SQL plan baseline を進化させるもう 1 つの方法は、SQL Tuning Advisor を使うことです。evolve_sql_plan_baseline を実行する代わりに、accepted plan が 1 つ、non-accepted plan が 1 つある元の状態から始めるとします。
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
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 Tuning Advisor が tuned plan を見つけたということです。しかもそれは偶然にも、plan history に含まれている non-accepted plan でした。推奨された SQL profile を受け入れると、SQL Tuning Advisor は SQL profile を作成すると同時に、non-accepted plan の status も accepted に変更します。これにより、SQL plan baseline は 2 つの plan を持つ形に進化します。
なお、SQL Tuning Advisor は、plan history に存在しないまったく新しい tuned plan を見つける場合もあります。その場合に推奨された SQL profile を受け入れると、SQL Tuning Advisor は SQL profile を作成し、その tuned plan も SQL plan baseline に追加します。
このように、SQL plan baseline は evolve_sql_plan_baseline function を実行する方法でも、SQL Tuning Advisor を使う方法でも進化させることができます。新しく、かつ実証可能により優れた plan は、どちらの方法でも SQL plan baseline に追加されます。