※本記事は、Nigel Bayliss による”SQL Plan Management 2 of 4 – SPM-Aware Optimizer”(2024/12/8) を翻訳したものです。

Part 1 では、SQL plan baseline を作成する方法を見ました。文に対して SQL plan baseline を作成すると、その後のその文の実行では SQL plan baseline が使用されます。optimizer は、SQL plan baseline に含まれる複数の plan の中から、現在の環境（bind 値、最新の統計情報、パラメータなど）において最もコストの低いものを選択します。同時に、SQL plan baseline が存在しなかった場合に本来選択していたはずの最小コスト plan も生成します。

ただし、この最小コスト plan はその場では使用されず、後で検証するためにその文の plan history に追加されます。つまり、optimizer は新しく未知の plan ではなく、SQL plan baseline にある既知の plan を使用します。これにより、パフォーマンス劣化が起きないことが保証されます。

では、この plan 選択プロセスが実際にどう動くのか見てみましょう。まず、自動 plan capture を有効にし、クエリを 2 回実行して SQL plan baseline を作成します。

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 plan baseline が作成されたことが分かります。では、この文が再び hard parse されると仮定しましょう（ここでは shared pool を flush することで行います）。次に、SQL plan management を無効にし、別の bind 値でクエリを実行します。

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 |
------------------------------------------

新しい bind 値によって述語の選択性が低くなったため、optimizer が別の plan を選択したことが分かります。では SQL plan management を再び有効にし、同じ bind 値でクエリを再実行してみます。

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 から、optimizer が SQL plan baseline を使用していることが分かります。つまり、optimizer は新しい plan ではなく、SQL plan baseline 内の accepted plan を使用したということです。実際、optimizer が新しい plan をその文の 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

accepted 列の値が NO であることは、新しい plan が plan history には入っているものの、良い plan であることが検証されるまでは使用できないことを意味します。optimizer は、新しい plan が検証されて SQL plan baseline に追加されるまで、accepted plan を使い続けます。SQL plan baseline に複数の plan が存在する場合、optimizer はその時点の条件（統計情報、bind 値、パラメータ設定など）の下で最もコストの低いものを使用します。

このように、SQL 文に対して SQL plan baseline を作成すると、SPM aware optimizer は SQL plan baseline に含まれるもの以外の新しい plan を使用しないことを保証します。これにより、ときにパフォーマンス劣化につながる予期しない plan 変更を防ぐことができます。

新しい plan を使わせないこと自体は有効ですが、ではその新しい plan の方が実際にはより良い場合はどうなるのでしょうか。Part 3 では、新しく改善された plan がどのように SQL plan baseline に追加されるのかを説明します。

参考情報: