津島博士のパフォーマンス講座 
第63回 Oracle Database 12cR2のオプティマイザについて

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年は残暑もなく過ごしやすい秋になると思っていたら、急に夏のような暑い日が続いたり寒くなったりと不安定な天候が多いですね。体調を崩さないように注意してください。
今回は、Oracle Database 12cR2(Oracle12cR2)でオプティマイザもいくつか変更されていますので、その中から第57回でも触れた適応問合せ最適化について説明します。後半に、インクリメンタル統計とSQL計画管理についても説明していますので、参考にしてください。

1. 適応問合せ最適化の変更
Oracle12cから提供された適応問合せ最適化(Adaptive Query Optimization)が、Oracle12cR2からパフォーマンス問題を改善するためにいくつか変更になっているので、その変更内容について説明します。
このようなオプティマイザ統計を補正する機能は、非常に便利な機能に思えますが、その分オーバーヘッドも発生します。そのため、そのオーバーヘッドを許容できないときには使用しないようにする必要があります。また、デフォルトで有効になっているので、知らずに使用して問題になる場合も多いことから、以下のようにデフォルト値や動作が変更になりました。

  • 適応統計のデフォルト値
  • 列グループ統計の自動作成
  • 小さい問合せでの自動再最適化
  • 動的統計の共有方法

(1)適応統計のデフォルト値
まずは、適応統計のデフォルト値が変更されたことから説明します。
適応問合せ最適化は、第57回で説明したようにデフォルトがTRUEのため、知らずに使用してパフォーマンス問題になる場合もあることから、デフォルトで動作しないようになりました。ただし、適応計画は、オーバーヘッドも少なく有効な場合も多いので、Oracle12cR2からは初期化パラメータOPTIMIZER_ADAPTIVE_FEATURESを廃止して、以下の二つのパラメータに分割されています(そして、適応統計だけのデフォルトをFALSEにしました)。

  • 適応計画(OPTIMIZER_ADAPTIVE_PLANS)
  • 適応統計(OPTIMIZER_ADAPTIVE_STATISTICS)

このように適応統計をFALSEにすることで、自動再最適化、動的統計、SQL計画ディレクティブは動作しなくなりますが、Oracle Database 11gR2(Oracle11gR2)と同様のカーディナリティ・フィードバックと動的サンプリングは動作するので、間違わないでください(単一表カーディナリティの補正レベルだけは動作します)。

(2)列グループ統計の自動作成
次に、列グループ統計の自動作成の変更について説明します。
SQL計画ディレクティブは、他のSQLでも実行時にオプティマイザ統計を補正する以外に、オプティマイザ統計収集時に拡張統計の列グループ統計を自動的に作成することができます。ただし、SQL計画ディレクティブが作成されると、列グループ統計を必要としないシステムでも自動的に作成されてしまうのが問題でした(そのようなシステムでは、自動作成されることを嫌うため、作成されて欲しくありませんでした)。
そのため、列グループ統計の自動作成は、Oracle12cR2からSQL計画ディレクティブが存在しても作成するかどうか指定できるように、別のパラメータで制御するようになっています。それがプリファレンス・パラメータAUTO_STAT_EXTENSIONSになります(デフォルトはOFFです)。このパラメータをONにするには、以下のようにプリファレンス・パラメータ設定プロシージャで行います。

exec DBMS_STATS.SET_TABLE_PREFS(NULL,'<表名>','AUTO_STAT_EXTENSIONS','ON');

SQL計画ディレクティブは、OPTIMIZER_ADAPTIVE_STATISTICSがFALSEでも作成されるので(これも単一表カーディナリティの補正レベルだけです)、このプリファレンス・パラメータだけで自動的に作成するか制御することが可能です。つまり、これにより逆に適応統計を使用したくないときでも、自動的に列グループ統計を作成できるようになりました。

(3)小さい問合せでの自動再最適化
次に、小さい問合せでの自動再最適化の変更について説明します。
自動再最適化は、実行計画の変化で処理時間が大きく変わるようなSQLに有効な機能です。そのため、処理時間にあまり影響しないような小さい問合せでは動作する必要がありません(逆に動作しない方が好ましいです)。この機能で問題になるのは、このような処理時間が短いSQLで動作して、ハード解析や動的統計のオーバーヘッドにより、処理時間が長くなってしまうことです。そのため、Oracle12cR2から小さい問合せに対して、自動再最適化が回避されるようになりました(これはBug#23596611の修正になります)。この小さい問合せとは、アクセスするブロック数が少ないSQLのことを言います(正確には、BUFFER_GETSの数が100未満のSQLです)。このようなSQLは、以下のように見積り行数(オプティマイザ統計)が正しくないときでも、あまり処理時間に影響しません。

  • 行数が少ない表に対する問合せ
  • 最適でない索引でもアクセスするデータが少ない問合せ
  • ヒントで索引アクセスに固定してもアクセスするデータが少ない問合せ

つまり、アクセス数が少ない問合せに対して、統計フィードバックやSQL計画ディレクティブの作成が行われなくなります。分かりやすいように、同じSQLを索引スキャンと全表スキャンを行って、アクセスするデータ数を変えてみました。以下のように同じ見積り行数でも、アクセスするデータ数が多い全表スキャンのときだけIS_REOPTIMIZABLEがY(次回実行時に再最適化を行う)になります。これはOPTIMIZER_ADAPTIVE_STATISTICSがFALSEのときでも、Oracle11gR2までのカーディナリティ・フィードバックが動作しなくなります。

SQL> select /*+gather_plan_statistics*/ * from test1 where c1=1 and c2=1;
---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |        |     77 |      80 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST1    |      1 |      5 |     77 |      80 |
|*  2 |   INDEX RANGE SCAN                  | IX_TEST1 |      1 |      5 |     77 |       3 |
---------------------------------------------------------------------------------------------

SQL> select /*+gather_plan_statistics full(test1)*/ * from test1 where c1=1 and c2=1;
------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |     77 |    1438 |
|*  1 |  TABLE ACCESS FULL| TEST1 |      1 |      5 |     77 |    1438 |
------------------------------------------------------------------------

SQL> SELECT SQL_ID,SQL_TEXT,IS_REOPTIMIZABLE FROM V$SQL WHERE SQL_TEXT LIKE 'select /*+gather_plan_statistics%';

SQL_ID        BUFFER_GETS SQL_TEXT                                                                         I
------------- ----------- -------------------------------------------------------------------------------- -
7d3a6w4aunthk        1440 select /*+gather_plan_statistics full(test1)*/ * from test1 where c1=1 and c2=1  Y
0mwmarunh7jfy          80 select /*+gather_plan_statistics*/ * from test1 where c1=1 and c2=1              N

これによりOLTPシステムなどのショート・トランザクションでは動作しなくなるので、適応統計を使用しても影響するSQLは少なくなります。また、ヒントで固定してオプティマイザ統計の収集頻度が少ないシステムなどでも影響しなくなります(これで少しは使いやすくなると思います)。

(4)動的統計の共有方法
最後に、動的統計の共有方法の変更について説明します。
動的統計は、オプティマイザ統計を補正するのは嬉しいのですが、オーバーヘッドがあるためあまり多く動作して欲しくないことから、Orcale12cからの適応動的統計は、他のSQLでも共有できるようになっています。ただし、このときの動的サンプリング問合せ結果は、結果キャッシュ(Result Cache)に格納するので、結果キャッシュ関連の待機が発生する場合が多くありました。また、結果キャッシュ・サイズや再利用できる期間(3600秒)などで、あまり再利用できることも多くありませんでした。そのため、Oracle12cR2からは、格納先が結果キャッシュからSQL計画ディレクティブ・レポジトリに変更されています。SQL計画ディレクティブには、以下のようなDYNAMIC_SAMPLING_RESULTタイプが作成されるようになります。つまり、ディスクにも格納されるので、SQL計画ディレクティブが作成されていると、再起動されても再利用できるようになります。

SQL> EXEC dbms_spd.flush_sql_plan_directive;
SQL> SELECT TO_CHAR(directive_id) dir_id,object_name obj_name,subobject_name col_name,object_type type,
2         state||'('||EXTRACTVALUE(d.notes,'/spd_note/internal_state')||')' state,reason
3    FROM dba_sql_plan_directives d INNER JOIN dba_sql_plan_dir_objects USING(directive_id)
4   WHERE object_name IN ('TEST1') ORDER BY 1,2,3,4;

DIR_ID               OBJ_NAME COL_NAME OBJECT TYPE                    STATE         REASON
-------------------- -------- -------- ------ ----------------------- ------------- ------------------------------------
1252556337120300147  TEST1             TABLE  DYNAMIC_SAMPLING_RESULT USABLE(NEW)   VERIFY CARDINALITY ESTIMATE
4058482662417233619  TEST1    C1       COLUMN DYNAMIC_SAMPLING        USABLE(NEW)   SINGLE TABLE CARDINALITY MISESTIMATE
4058482662417233619  TEST1    C2       COLUMN DYNAMIC_SAMPLING        USABLE(NEW)   SINGLE TABLE CARDINALITY MISESTIMATE
4058482662417233619  TEST1             TABLE  DYNAMIC_SAMPLING        USABLE(NEW)   SINGLE TABLE CARDINALITY MISESTIMATE

 

適応統計は、このようにデフォルトがFALSEになりましたが、いろいろ改善されて効果があるようなシステムも多くなっているので、Ad-Hoc問合せが多いシステムでは使用を検討してみてください。また、第57回でも説明しましたが、複数表のカーディナリティ(結合カーディナリティなど)は、オプティマイザ統計だけでは正確に見積れない場合があります。そのため、適応統計が有効な場合も多いことは忘れないでください。

2. インクリメンタル統計とSQL計画管理の拡張
インクリメンタル統計(増分統計)とSQL計画管理(SPM)もOracle12cR2で拡張されているので、その拡張された以下の機能についても紹介しておきます。

  • 新しい収集アルゴリズム(増分統計)
  • 自動取得する実行計画のフィルター指定(SPM)
  • AWRからの実行計画の取得(SPM)

(1)新しい収集アルゴリズム(増分統計)
増分統計は、第33回で説明したように、パーティション表に対するオプティマイザ統計収集に最適な方法ですが、パーティションの数や個別値が多いと、SYSAUX表領域に作成されるシノプシスのサイズが大きくなってしまうという問題があります。これを改善するために、Oracle12cR2から新しい収集アルゴリズムが追加されています。これはOracle12cから追加されたAPPROX_COUNT_DISTINCT関数と同じアルゴリズム(HyperLogLogアルゴリズム)を使用して、重複していない値の近似カウントを集計するように行います(重複していない値が多数含まれるデータの場合に、差異が無視できる程度で使用することができます)。これによりシノプシスを収集する処理の高速化と格納サイズの削減ができるようになります。
この収集アルゴリズムを指定するには、以下のようにプリファレンス・パラメータAPPROXIMATE_NDV_ALGORITHMで行います。

exec DBMS_STATS.SET_TABLE_PREFS(NULL,'<表名>','APPROXIMATE_NDV_ALGORITHM','HYPERLOGLOG');

このパラメータには、以下の三つの値を設定することができます。

  • 'REPEAT OR HYPERLOGLOG'(デフォルト)
    古いフォーマットのシノプシスが存在する場合、古いアルゴリズムを使い続けます。シノプシスが存在しないまたは新しいフォーマットが存在する場合、新しいアルゴリズムを使用します。
  • 'ADAPTIVE SAMPLING'
    強制的に古いアルゴリズムを使用します。
  • 'HYPERLOGLOG'
    強制的に新しいアルゴリズムを使用します。

(2)自動取得する実行計画のフィルター指定(SPM)
SPMの自動計画取得は、第35回で説明したように、Oracle12cからSPM展開アドバイザ・タスクが導入されて、自動取得した実行計画を自動承認することができるようになりましたが、2回以上実行されたSQLのすべてが対象になってしまいます。そのため、SYSAUX表領域に不要なSQL計画ベースラインが多く作成されてしまう可能性があり、あまり効果的に使用することができませんでした(そのため、デフォルトは有効になっていません)。Oracle12cR2からは、以下のようにフィルター条件を指定して、取得する実行計画を絞り込むこと(重要でないSQLを除外するなど)が可能になります。

DBMS_SPM.CONFIGURE(<フィルター・タイプ>,<検索基準値>,<enable>)

以下が指定できるフィルター・タイプとそれに指定する検索基準値になります。

  • AUTO_CAPTURE_SQL_TEXT(発行したSQL文字列)
  • AUTO_CAPTURE_PARSING_SCHEMA_NAME(SQLが解析されたスキーマ名)
  • AUTO_CAPTURE_MODULE(SQLを発行したプログラムのMODULE名)
  • AUTO_CAPTURE_ACTION(SQLを発行したプログラムのACTION名)

フィルター・タイプに対して、以下のようにenableと検索基準値でフィルター条件を指定します。検索基準値がNULLのときは、フィルター・タイプに対する既存のフィルター条件をすべて削除します。

フィルター・タイプ フィルター条件
enable=true
enable=false
AUTO_CAPTURE_SQL_TEXT
LIKE <検索基準値>
NOT LIKE <検索基準値>
AUTO_CAPTURE_SQL_TEXT以外
= <検索基準値>
<> <検索基準値>

例えば、以下はテキストTEST_ONLYを含むSQL文を除外します。

exec DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%TEST_ONLY%',false);

(3)AWRからの実行計画の取得(SPM)
これまでは、AWRから実行計画をロードするには、まず以下のように、AWRスナップショットに保存されている実行計画をSTS(SQLチューニング・セット)にロードしてから、SQL計画ベースラインにロードする必要がありました(以下の例は、スナップショット212と213のAWRの中から、'select /* test1 */'から始まるSQLだけをロードします)。

exec DBMS_SQLTUNE.CREATE_SQLSET('T1_STS', 'TEST1 STS');
declare
cur sys_refcursor;
begin
 open cur for select value(p) from table(
   DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap=>212,end_snap=>213,
                                           basic_filter=>'sql_text like ''select /* test1 */%''')) p;
 DBMS_SQLTUNE.LOAD_SQLSET('T1_STS', cur);
 close cur;
end;
/
declare
 my_plans PLS_INTEGER;
begin
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'T1_STS');
end;
/

Oracle12cR2からは、以下のDBMS_SPM.LOAD_PLANS_FROM_AWRファンクションを使用して、直接ロードすることができるようになりました。これによりSQLの過去の実行計画を、AWRから簡単にロードすることができるようになります。

SQL> VARIABLE cnt NUMBER
SQL> exec :cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap=>212, end_snap=>213, 
  2                                            basic_filter=>'sql_text like ''select /* test1 */%''');

3. おわりに
今回はOracle Database 12cR2のオプティマイザについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。