【Oracle Database】実行計画の固定化方法まとめ

Oracle Databaseではバージョンを追うごとにコストベース・オプティマイザ（CBO）の動作が改善／改良されており、最新の統計情報を維持していれば適切な実行計画が選択されやすくなっています。

しかしCBOは統計情報などから最適な実行計画を予測で生成していますので、適切ではない実行計画が選択される可能性も0ではありません。そのため、毎晩実行されるバッチなどの一部の処理においては意図せぬ実行計画変更によるパフォーマンスダウンを防ぐために実行計画を固定化したいという要望をいただくことがあります。

実行計画を固定化する方法は幾つかありますが、Oracle Database 18cからStandard Editionでも実行計画固定のためにSQL Plan Management（SPM）の一部機能が利用できるようになりました。そこで今回はサポートへの質問も多い「実行計画を固定する方法」を4つご紹介します。

Case 1：オプティマイザ統計情報のロック

CBOは主にオプティマイザ統計情報を基にして実行計画を生成しますので、統計情報をロックすることで現在選択されている実行計画で固定できます。統計情報のロックはDBMS_STATS.LOCK_TABLE_STATSプロシージャを使用します。

　
　--統計情報のロック（ユーザ名とテーブル名を指定）
　SQL> EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('TESTUSER','TESTTAB');
　
　--ロックされたことを確認（STATTYPE_LOCKED=ALL）
　SQL> SELECT owner,table_name,partition_name,stattype_locked
　  2  FROM dba_tab_statistics
　  3  WHERE owner = 'TESTUSER'
　  4  AND  table_name='TESTTAB';
　
　OWNER    TABLE_NAME              PARTITION_NAME  STATT
　-------- ----------------------- --------------- -----
　TESTUSER TESTTAB                                   ALL
　
　--統計情報を取得しようとしてもエラーになる
　SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TESTUSER',tabname => 'TESTTAB');
　BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TESTUSER',tabname => 'TESTTAB'); END;
　
　*
　行1でエラーが発生しました。:
　ORA-20005: object statistics are locked (stattype = ALL)
　ORA-06512: "SYS.DBMS_STATS", 行36873
　ORA-06512: "SYS.DBMS_STATS", 行36507
　ORA-06512: "SYS.DBMS_STATS", 行8582
　ORA-06512: "SYS.DBMS_STATS", 行9461
　ORA-06512: "SYS.DBMS_STATS", 行35836
　ORA-06512: "SYS.DBMS_STATS", 行36716
　ORA-06512: 行1
　
　--補足：ロック解除はDBMS_STATS.UNLOCK_TABLE_STATS
　SQL> EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('TESTUSER','TESTTAB');

コマンド1つで実行できるため今回ご紹介する4つの方法の中で最も簡単と言えますが、該当のオブジェクトを参照するすべての処理が影響を受けるため、可能であれば影響範囲が処理単位となる「Case 2」以降をご検討ください。

Case 2：オプティマイザ・ヒント（ヒント句）

SQLにヒント句を入れることで結合順序や索引の使用可否を制限できるため意図した実行計画が選択されるようにチューニングを行うことが可能です。一般的によく利用されるヒント句には次のようなものがあります。※テーブル名でエイリアスを利用している場合は、例のようにエイリアスで指定する必要があります

INDEXヒント（指定した表の索引を使う）

　
　SQL> SELECT /*+ INDEX(E) */ e.empno,d.dname FROM dept d,emp e WHERE d.deptno = e.deptno;
　
　-----------------------------------------------------------------------------------------------
　| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
　-----------------------------------------------------------------------------------------------
　|   0 | SELECT STATEMENT                     |        |    14 |   672 |    45(0)   | 00:00:01 |
　|*  1 |  HASH JOIN                           |        |    14 |   672 |    45(0)   | 00:00:01 |
　|   2 |   TABLE ACCESS FULL                  | DEPT   |     4 |    88 |     3(0)   | 00:00:01 |
　|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |    14 |   364 |    42(0)   | 00:00:01 |
　|   4 |    INDEX FULL SCAN                   | PK_EMP |    14 |       |     2(0)   | 00:00:01 |
　-----------------------------------------------------------------------------------------------

LEADINGヒント（指定した順番で結合）

　
　SQL> SELECT /*+ LEADING(D E) */ e.empno,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno;
　
　---------------------------------------------------------------------------
　| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
　---------------------------------------------------------------------------
　|   0 | SELECT STATEMENT   |      |    14 |   672 |     6   (0)| 00:00:01 |
　|*  1 |  HASH JOIN         |      |    14 |   672 |     6   (0)| 00:00:01 |
　|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
　|   3 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
　---------------------------------------------------------------------------

ORDEREDヒント（FROM句に現れる順序で表を結合）

　
　SQL> SELECT /*+ ORDERED */ e.empno,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno;
　
　---------------------------------------------------------------------------
　| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
　---------------------------------------------------------------------------
　|   0 | SELECT STATEMENT   |      |    14 |   672 |     6   (0)| 00:00:01 |
　|*  1 |  HASH JOIN         |      |    14 |   672 |     6   (0)| 00:00:01 |
　|   2 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
　|   3 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
　---------------------------------------------------------------------------
　
　SQL> SELECT /*+ ORDERED */ e.empno,d.dname FROM dept d,emp e WHERE d.deptno = e.deptno;
　
　---------------------------------------------------------------------------
　| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
　---------------------------------------------------------------------------
　|   0 | SELECT STATEMENT   |      |    14 |   672 |     6   (0)| 00:00:01 |
　|*  1 |  HASH JOIN         |      |    14 |   672 |     6   (0)| 00:00:01 |
　|   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
　|   3 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
　---------------------------------------------------------------------------

他にも多くのヒント句があり、たとえば結合の方法なども指定することが可能です。SQL言語リファレンスマニュアルに記載されておりますので利用の際は参考にしてください。
　

Oracle® Database SQL言語リファレンス 18cバージョン 18.1

ヒント

Case 3：プラン・スタビリティ

プラン・スタビリティはSQL文に対して実行計画を再現させるためのストアドアウトライン（オプティマイザ・ヒントのセット）を作成します。ストアドアウトラインが作成されたSQLを実行するとヒントが内部的に考慮されるため、実行計画が固定（再現）されます。
　
後述のSQL Plan Management（SPM）はプラン・スタビリティを置き換える機能として11gR1から追加されましたが、18cまではStandard Editionでは利用できません。12cR2以下のStandard Edition環境ではこちらの方法で実行計画を固定化します。

　
　--アウトラインの作成を宣言
　SQL> alter session set create_stored_outlines = outline_test;
　
　セッションが変更されました。
　
　--SQLの実行（アウトラインが作成され実行計画が保存される）
　SQL> select e.deptno,dname,sum(sal) sum_sal
　  2  from emp e,dept d
　  3  where e.deptno = d.deptno
　  4  group by e.deptno,dname order by e.deptno;
　
　    DEPTNO DNAME                           SUM_SAL
　---------- ---------------------------- ----------
　        10 ACCOUNTING                         8750
　        20 RESEARCH                           6775
　        30 SALES                              9400
　
　--アウトラインの作成終了を宣言
　SQL> alter session set create_stored_outlines = false;
　
　セッションが変更されました。
　
　--作成されたアウトラインの確認
　SQL> SELECT category,name,used,enabled,sql_text FROM user_outlines;
　
　CATEGORY      NAME                          USED         ENABLED          SQL_TEXT
　------------- ----------------------------- ------------ ---------------- --------------------------------------
　OUTLINE_TEST  SYS_OUTLINE_18083111150748509 UNUSED       ENABLED          select e.deptno,dname,sum(sal) sum_sal
　                                                                          from emp e,dept d
　                                                                          where e.deptno = d.dept
　
　--作成したアウトラインの使用を宣言
　SQL> ALTER SESSION SET use_stored_outlines = outline_test;
　
　--SQLの実行（アウトラインが使用されていることを確認）
　SQL> set autot on
　SQL> SELECT e.deptno,dname,sum(sal) sum_sal
　  2  FROM emp e,dept d
　  3  WHERE e.deptno = d.deptno
　  4  GROUP BY e.deptno,dname ORDER BY e.deptno;
　
　    DEPTNO DNAME                           SUM_SAL
　---------- ---------------------------- ----------
　        10 ACCOUNTING                         8750
　        20 RESEARCH                           6775
　        30 SALES                              9400
　
　
　実行計画
　----------------------------------------------------------
　Plan hash value: 3258911389
　
　------------------------------------------------------------------------------------------
　| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)|Time      |
　------------------------------------------------------------------------------------------
　|   0 | SELECT STATEMENT              |          |     3 |    87 |     8  (38)|00:00:01  |
　|   1 |  SORT GROUP BY                |          |     3 |    87 |     8  (38)|00:00:01  |
　|   2 |   MERGE JOIN                  |          |     3 |    87 |     7  (29)|00:00:01  |
　|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     4 |    52 |     2   (0)|00:00:01  |
　|   4 |     INDEX FULL SCAN           | PK_DEPT  |     4 |       |     1   (0)|00:00:01  |
　|*  5 |    SORT JOIN                  |          |     3 |    48 |     5  (40)|00:00:01  |
　|   6 |     VIEW                      | VW_GBC_5 |     3 |    48 |     4  (25)|00:00:01  |
　|   7 |      HASH GROUP BY            |          |     3 |    21 |     4  (25)|00:00:01  |
　|   8 |       TABLE ACCESS FULL       | EMP      |    12 |    84 |     3   (0)|00:00:01  |
　------------------------------------------------------------------------------------------
　
　
　Predicate Information (identified by operation id):
　---------------------------------------------------
　
　   5 - access("ITEM_1"="D"."DEPTNO")
　       filter("ITEM_1"="D"."DEPTNO")
　
　Note
　-----
　   - outline "SYS_OUTLINE_18083111150748509" used for this statement
　   
　   
　--補足：アウトラインの削除
　SQL> exec DBMS_OUTLN.DROP_BY_CAT ('OUTLINE_TEST');
　
　PL/SQLプロシージャが正常に完了しました。
　
　SQL> SELECT category,name,used,enabled,sql_text FROM user_outlines;
　
　レコードが選択されませんでした。

プラン・スタビリティについては次の資料にも情報がありますので、利用の際には併せてご確認ください。

Oracle® Databaseパフォーマンス・チューニング・ガイド 11gリリース2 (11.2)

20 プラン・スタビリティの使用方法

My Oracle Support
ヒントを使わずに実行計画を固定する方法(プラン・スタビリティ)(KROWN:23739) (ドキュメントID 1707310.1)

Case 4：SQL Plan Management（SQL計画管理／SPM）

SPMはプラン・スタビリティと同じく内部的なオプティマイザ・ヒントを適用することで実行計画を固定化します。固定するための内部的な情報をベースラインと呼びます。SPMは繰り返し実行されるSQLの安定稼動を目的とし、許可された1つ以上のベースラインの中から実行コストの最も低い実行計画を選択する機能です。

Oracle Database 18cからは、Standard Editionでも1つのSQLにつき1つのベースラインを作成することができるようになり、SPMで実行計画を固定できます。※同一SQLに対して複数のベースラインを作成するにはEnterprise Editionのライセンスが必要

　
　SQL> SELECT banner_full FROM v$version;
　
　BANNER_FULL
　----------------------------------------------------------------------
　Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
　Version 18.3.0.0.0
　
　
　--SPMの作成を宣言
　SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
　
　セッションが変更されました。
　
　--SQLの実行（1回目）
　SQL> SELECT count(*) FROM emp WHERE empno > 5000;
　
　  COUNT(*)
　----------
　        12
　        
　        
　--ベースラインは作成されない
　SQL> SELECT sql_handle, plan_name,accepted,sql_text FROM dba_sql_plan_baselines;
　
　レコードが選択されませんでした。
　
　
　--SQLの実行（2回目）
　SQL> SELECT count(*) FROM emp WHERE empno > 5000;
　
　  COUNT(*)
　----------
　        12
　
　
　--SPMの作成を終了
　SQL> ALTER  SESSION SET optimizer_capture_sql_plan_baselines = false;
　
　セッションが変更されました。
　
　
　--ベースラインが作成される
　SQL> SELECT sql_handle, plan_name,accepted,sql_text FROM dba_sql_plan_baselines;
　
　SQL_HANDLE           PLAN_NAME                      ACCEPT  SQL_TEXT                                     
　-------------------- ------------------------------ ------- ------------------------------------------- 
　SQL_03882407244d6925 SQL_PLAN_072140wk4uu95a4f9f478 YES     select count(*) from emp where empno > 5000  
　
　
　--ベースラインの使用を宣言
　SQL> ALTER SESSION SET optimizer_use_sql_plan_baselines = TRUE;
　
　セッションが変更されました。
　
　
　--SQLの実行（ベースラインが使用されていることを確認）
　SQL> set autot on
　SQL> SELECT count(*) FROM emp WHERE empno > 5000;
　
　  COUNT(*)
　----------
　        12
　
　
　実行計画
　----------------------------------------------------------
　Plan hash value: 109489892
　
　----------------------------------------------------------------------------
　| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
　----------------------------------------------------------------------------
　|   0 | SELECT STATEMENT  |        |     1 |     4 |     1   (0)| 00:00:01 |
　|   1 |  SORT AGGREGATE   |        |     1 |     4 |            |          |
　|*  2 |   INDEX RANGE SCAN| PK_EMP |    12 |    48 |     1   (0)| 00:00:01 |
　----------------------------------------------------------------------------
　
　Predicate Information (identified by operation id):
　---------------------------------------------------
　
　   2 - access("EMPNO">5000)
　
　Note
　-----
　   - SQL plan baseline "SQL_PLAN_072140wk4uu95a4f9f478" used for this statement
　
　
　--補足：ベースラインの削除
　SQL> var xx number
　SQL> exec :xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_03882407244d6925',plan_name=>'SQL_PLAN_072140wk4uu95a4f9f478');
　
　PL/SQLプロシージャが正常に完了しました。
　
　SQL> SELECT sql_handle, plan_name,accepted,sql_text FROM dba_sql_plan_baselines;
　
　レコードが選択されませんでした。

ベースラインの登録には同じSQLを2回以上実行する必要があります。SPMについては次の資料にも情報がありますので、利用の際には併せてご確認ください。

Oracle® Database SQLチューニング・ガイド 18c

29.2 SQL計画管理の構成

My Oracle Support
[11g新機能]SQL計画の管理(SPM: SQL Plan Management)(KROWN:127655) (ドキュメントID 1741949.1)

まとめ

実行計画の固定化で運用中の意図しない実行計画の変化によるパフォーマンスダウンを防ぐことができますが、データの種類や量の変化に対応できなくなります。特にヒント句の利用はメンテナンスが難しくなることも考えられますので、基本はCBOに任せ、DBAによる実行計画の固定化は例外的な対応とすることをお勧めします。

筆者情報

大野高志

サービス事業部付加価値創造部カスタマーエンゲージメント・プランナー

2007年アシスト入社。Oracle Databaseのサポート業務を経て、サポートセンターに蓄積されたナレッジを使用したサービスの立ち上げに従事。現在は「アシストの超サポ」を広め、カスタマーエンゲージメントの構築を実現するための活動を行っている。

■本記事の内容について
　本記事に記載されている製品およびサービス、定義及び条件は、特段の記載のない限り本記事執筆時点のものであり、予告なく変更になる可能性があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java及びMySQLは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。

【Oracle AI Database 26ai】同時リフレッシュでON COMMITマテリアライズド・ビューのenqueue待ちにサヨナラ！OLTP／DWHスループットを実測検証

本記事では、従来のON COMMIT MViewが抱えてきたこのようなスループット低下や待機イベントのボトルネックを振り返りつつ、Oracle AI Database 26aiが提供する「同時リフレッシュ（Concurrent Refresh）」によって、OLTP／DWHそれぞれのユースケースでどのような改善が見込めるのかを検証していきます。