tech_memo / ORACLE / SQL_PLAN

tech_memo/ORACLE

実行計画の各ステップでかかった時間 †

• 参考
  • http://www.oracle.com/technetwork/jp/ondemand/db-basic/d-16-ssqltuning-1448439-ja.pdf
• dbms_sqltune.report_sql_monitorプロシージャを利用すると、EMのSQLリアルタイム監視と同等の情報が得られる

  set trimspool on
  set trim on
  set pages 0
  set linesize 1000
  set long 1000000
  set longchunksize 1000000
  
  spool sqlmon_active.html
  select dbms_sqltune.report_sql_monitor(sql_id=>'SQL_ID', type=>'active') from dual; -- ★typeはACTIVE(HTML) or TEXTを指定
  spool off

• 上記プロシージャが情報をとってくる動的パフォーマンス・ビュー
  • V$SQL_MONITOR
  • V$SQL_PLAN_MONITOR
• 単一の実行で最低5秒以上のCPU時間またはI/O時間を消費すると、監視対象としてエントリされ、情報を取得できるようになる。
  • 強制的に監視対象にするには、SQLにMONITORヒントを入れる

    select /*+ MONITOR */ COL1 from TBL1

  • 逆に強制的に監視対象外にするには、SQLにNO_MONITORヒントを入れる

    select /*+ NO_MONITOR */ COL1 from TBL1

過去のSQL実行時の実行計画の各ステップ所要時間を取得したい場合 †

• 直近ではなく、過去のSQL実行時の監視情報を取得したい場合は、その時のsql_exec_idを特定して、repot_sql_monitorプロシージャの引数に指定すればよい。

1. sql_exec_idの特定

   select dbms_sqltune.report_sql_monitor_list(sql_id=>'SQL_ID', type=>'TEXT') from dual; 

   • SQL実行日時と所要時間、sql_exec_id等が取得できる
2. 上記で取得したsql_exec_idを指定して、SQLリアルタイム監視情報を取得

   select dbms_sqltune.report_sql_monitor(sql_id=>'SQL_ID', sql_exec_id=>'上記で取得したID', type=>'active') from dual; 

プラン・スタビリティ †

• 実行計画を実現するためのアウトライン「後で埋め込むためのヒント文の集まり」
• SQLの実行計画のヒントセットを保存しておき、それを利用することにより、実行計画の固定化を促進できる機能。
• 現在、非推奨（？）。SPMを利用することを薦められている
• 参考
  • https://docs.oracle.com/cd/E16338_01/server.112/b56312/outlines.htm

SPM (SQL Plan Management) †

• tech_memo/ORACLE/SPM

ヒント句 †

• Hashジョインの小規模表・大規模表の指定
  • 参考 : http://www.doppo1.net/oracle/tuning/hash-join-hint.html
  • 小規模表指定

    SWAP_JOIN_INPUTS(表別名)

  • 大規模表指定

    NO_SWAP_JOIN_INPUTS(表別名)

読み方 †

レコード単位か、テーブル単位か？ †

• 以下のようなシンプルなテーブルが２つあり、

  create table TEST_1(ID NUMBER, STATUS CHAR(1));
  create table TEST_2(ID NUMBER);

• HASH結合とフィルタを行ったとき、

  select /*+ use_hash(t1, t2) swap_join_inputs(t2) */
    t1.id
  from
    TEST_1 t1
  left join
    TEST_2 t2
  on
    t1.ID = t2.ID
  where
    t1.STATUS = '0' and t2.ID is not null or
    t1.STATUS = '1' and t2.ID is null
  ;

• 以下のような実行計画が得られるが、

  Plan hash value: 1517313122
   
  ---------------------------------------------------------------------------------
  | Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT       |        |    82 |  2378 |     4   (0)| 00:00:01 |
  |*  1 |  FILTER                |        |       |       |            |          |
  |*  2 |   HASH JOIN RIGHT OUTER|        |    82 |  2378 |     4   (0)| 00:00:01 |
  |   3 |    TABLE ACCESS FULL   | TEST_2 |    82 |  1066 |     2   (0)| 00:00:01 |
  |   4 |    TABLE ACCESS FULL   | TEST_1 |    82 |  1312 |     2   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------
   
  Predicate Information (identified by operation id):
  ---------------------------------------------------
   
     1 - filter("T1"."STATUS"='0' AND "T2"."ID" IS NOT NULL OR 
                "T1"."STATUS"='1' AND "T2"."ID" IS NULL)
     2 - access("T1"."ID"="T2"."ID"(+))

• Q. 上記のID=1のフィルタ処理は、ID=2の結合処理がすべて終わってから実行されるのか？それとも１レコードずつID=2 ⇒ ID=1の処理が行われるのか？
  • A. 上記SQLであれば、１レコードずつ、結合、フィルタが行われる。全レコードの平均より大きいなどの比較条件がある場合、全レコードの結合を待つ必要があるので、全レコード結合後、フィルタ処理が行われることになる。
    • どちらのケースが利用されるか、基本的には実行計画が作成された段階でどちらのケースを利用するかが決定される。

Predicate Information (フィルタ述語とアクセス述語) †

• 参考
  • http://www.shift-the-oracle.com/sqlplus/tutorial/autotrace.html
  • http://www.csus4.net/d/2011/06/30/predicate/
• フィルタ述語とアクセス述語
  • フィルタ述語
    • Predicate Informationではfilter(…)と記載され、行頭の番号に対応するオペレーションの実行時に、取得したデータから対象データを抜粋（フィルタ）する処理が実行されたことを示す。
  • アクセス述語
    • Predicate Informationではaccess(…)と記載され、行頭の番号に対応するオペレーションの実行時に、表示された述語を使用してデータにアクセスしたことを示す。一般に索引スキャン実行時に指定される。
  • 下記は、TEST2_IX1インデックスを利用して、NUM=1の行を取得している（アクセス述語が指定されている）

    ------------------------------------------------------------------------------
    | Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |           |     1 |     4 |     1   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| TEST2_IX1 |     1 |     4 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - access("NUM"=1) # ★アクセス述語

実行計画 項目 †

INDEX †

• INDEXスキャンの実行計画操作の種類

  操作	オプション	操作の内容	マニュアルに記載されたバージョン
  INDEX	UNIQUE SCAN	B-Tree 索引 からユニークキーを使用して１つの ROWID を取得する	8.0 以前
  	RANGE SCAN	索引から範囲（スタート・キー、ストップ・キー）をキーにして１または複数 ROWID の取得
  	RANGE SCAN DESCENDING	INDEX RANGE SCAN を索引値の降順にスキャン
  	FULL SCAN	索引からのすべての ROWID の取得。遅い(テーブルフルスキャンよりマシ)。利点は、INDEXの順にアクセスするので、ソート済みの状態でデータが取得できる	Oracle 9i Release2
  	FULL SCAN DESCENDING	INDEX FULL SCAN を索引の降順で行なう
  	FAST FULL SCAN	INDEX FULL SCAN の高速版：マルチブロック読み込みを使用するため取得順番は不定。インデックスに全カラムが含まれている場合 TABLE FULL SCAN と同じ意味になる（※ つまり ROWID だけでなくカラム値も取得する）	Oracle 9i Release2 CBO のみ
  	SKIP SCAN	INDEX SKIP SCAN を使用した ROWID の取得

  • 参照 : http://www.shift-the-oracle.com/performance-tuning/explain-plan-operation.html
  • Indexの実行計画ごとのアクセス方式はここがわかりやすい
    • http://d.hatena.ne.jp/sh2/20111217

テーブル結合方法 †

• http://www.atmarkit.co.jp/ait/articles/0408/25/news101.html

HASH結合メモ †

• 小規模表(A)と大規模表(B)のIndexスキャンについて
  • 下記のように、各表に閉じたIndexスキャンは可能

    WHERE (A).ID = 1
         AND  (B).ID = 100

  • 下記のような結合条件によるIndexスキャンは不可

    WHERE (A).ID = (B).ID

  • 参考 : http://www.doppo1.net/oracle/tuning/hash-join.html

AWRレポートのSQL_IDを使って実行計画を取得 †

• AWRレポートのSQL Idを利用して、以下のSQLで実行計画が取得できる

  select * from table(DBMS_XPLAN.DISPLAY_AWR('<SQL_ID>',null,null,'ALL'));

• AWRレポート抜粋

          Elapsed                  Elapsed Time
          Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
  ---------------- -------------- ------------- ------ ------ ------ -------------
          43,399.6         18,049          2.40   19.4     .9     .0 6qb74qdmz6jxw
  Module: JDBC Thin Client
  SELECT MY_ID FROM MY_TABLE WHERE MY_ID IN ( 以下略

V$SQL_PLANから実行計画を取得 †

• http://ameblo.jp/archive-redo-blog/entry-10036103530.html

実行計画の取得 †

EXPLAIN PLAN †

• 参考 : http://www.shift-the-oracle.com/sql/explain-plan.html
• EXPLAIN PLANは実際にSQLは実行されないので、商用環境などで有効。解析と表示の2手順を踏む。

1. 実行計画を解析

   EXPLAIN PLAN FOR <実行計画を取得したSQLテキスト>;

2. DBMS_XPLAN.DISPLAY()から実行計画を取得

   SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())

set autotrace †

• 内部的にはEXPLAIN PLAN FORを呼び出している
• set autotrace on explain
  • 実行結果と実行計画を表示する。
• set autotrace on statistics
  • 実行結果と実行時統計情報を表示する。
• set autotrace on
  • 実行結果と実行計画、統計情報を表示する。
• set autotrace traceonly
  • 実行計画と統計を表示する。実行結果は表示しない。
• set autotrace off
  • autotrace をオフにする。

バインド変数を含んだSQLの実行計画の取得 †

• 参考 : http://d.hatena.ne.jp/gonsuke777/20150723/1437662167
• PL/SQLではなく、SQLとしてバインド変数を含めて実行計画を取りたい場合

  VAR B1 NUMBER;
  EXEC :B1 := 10000;
  SET AUTOTRACE TRACEONLY;
  SELECT * FROM TBL_A WHERE C1 <= :B1;

EXPLAIN PLAN(set autotrace)で取得できる実行計画は正確ではない!? †

• 参考
  • http://www.cosol.jp/tech/detail/d3_explain_plan_problem.shtml
  • http://www.oracle.com/technetwork/jp/articles/chapter4-2-100886-ja.html#p01d
• 実際の実行計画と、EXPLAIN PLANで取得する実行計画には差分が生じる場合がある
  • クライアント環境の差異
    • 実SQLはJavaから実行されるが、EXPLAIN PLANはSqlplusから実行している
  • バインド変数のバインドピークON/OFFの差異
    • バインドピークとは？ ⇒ バインド変数の中身を展開してから実行計画を作成する。ただし、共有プールにキャッシュされている場合は、これは行われない。
    • ちなみに、バインドピークOFFの場合は、Oracleデフォルトのヒット率が利用される（範囲検索の場合は5%ヒット）
    • バインド変数 ON ⇒ 共有プールから実行計画が削除された後、再度同じSQL（バインド変数の中身は違う）を発行した場合、実行計画が変わる可能性がある
    • バインド変数 OFF ⇒ 実行計画は変わりにくいが、バインド変数値がOracle判断になるので、最適でない実行計画になる可能性がある
    • EXPLAIN PLANではバインドピークは実行されない
    • 実際に実行したときのバインドピークの実行有無は、隠しパラメータ「_optim_peek_user_binds」で設定(TRUEであれば実行)
• 正確な実行計画を取得するには、DBMS_XPLAN.DISPLAY_CURSOR()を利用して、Oracleの共有プールの実行計画を見る

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALL'));

INDEXスキャンしやすくする †

• optimizer_index_cachingとoptimizer_index_cost_adjパラメータを調整する。
  • http://ameblo.jp/archive-redo-blog/entry-10048143385.html

    -- RACの場合はsid='*'で両ノード一度に変更可能
    alter system set optimizer_index_caching=90  scope=both sid='*';
    alter system set optimizer_index_cost_adj=10  scope=both sid='*';