Database Support Blog

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

  • Oracle Database
2018.09.11

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

【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 Database
2018.08.08

【Oracle Database】18cで追加された手軽に使える便利な新機能3つ

Oracle Database 18c で追加された手軽に使える便利な新機能を3つご紹介します。

  • Oracle Database
2018.07.31

【Oracle Database】18c新機能:統合監査のData Pump EXPORT/IMPORT

Oracle Database 18cでは統合監査の情報をData Pumpを使ってEXPORT/IMPORTできるようになっています。注意事項と併せてご紹介します。

  • Oracle Database
2018.07.12

【Oracle Database Cloud】10gR2以下のJDBC接続でORA-12650が発生する原因と対処

オンプレミス環境からDBCSへの移行後、JDBCによるOracle Databaseへの接続でORA-12650エラーが発生する原因と対処方法を紹介します。

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る