WR blog

about Enterprise IT, Oracle Database, Jazz/Fusion Music, etc…

WR blog RSS Feed
 
 
 
 

SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わないこのエントリをはてなブックマークに登録

Oracle DatabaseのSQLパフォーマンス問題が発生したときの、実行計画の取得についてまとめます。

SQLパフォーマンス問題が発生したときに、まず確認すべき情報はSQLの実行計画ですが、 Oracle Databaseには、実行計画を確認する方法が、大きく分けて3つあります。

  • EXPLAN PLAN : (一般にSQL*Plusから)EXPLAN PLAN FOR <実行したいSQL>を実行して、実行計画を確認します。
  • SQLトレース : SQLの実行前にalter session sql_trace=true;を実行して、トレースファイルに出力された情報から実行計画を確認します。
  • DBMS_XPLAN.DISPLAY_CURSORプロシージャ : 共有プールに保管された共有カーソルの情報から、実行計画を確認します。共有カーソルが共有プールからage-outされた場合、実行計画は確認できません。

EXPLAIN PLANとSQLトレースを使わない理由

従来から存在しており、かつ、広く知られている実行計画の取得方法は、 EXPLAIN PLANとSQLトレースの2つです。しかし、私がOracle DatabaseのSQLパフォーマンス問題 に対応する場合、これらの2つの方法は原則的に使いません。

これら2つの方法には、いくつか使いにくい点があるからです。 まず、EXPLAIN PLANで実行計画を確認する方法では、得られた実行計画が、パフォーマンス 問題が発生したときと同じであるという保障がありません。 EXPLAIN PLANを実行した環境(一般にSQL*Plusを実行する環境)と、 問題となっているSQLを実行したアプリケーションの環境で、初期化パラメータ値が 同じであるという保障はありませんし、バインド変数などの、 プログラミングインタフェースに依存する部分が、実行計画の作成に影響する可能性もあります。

次に、SQLトレースですが、SQLを実行する前にあらかじめトレース設定をしておく 必要があります。たいていのアプリケーション実行環境では、あらかじめSQLトレース 設定をしていませんから、当然ながらパフォーマンス問題が発生したときの実行計画を SQLトレースから確認することはできません。 また、SQLトレースから確認できる実行計画は、非常にシンプルな形式であるため、 Predicate Infomation(フィルタ条件やアクセスパスに関する情報) などの実行計画の詳細情報が確認できません。

SQL ID: 6377g9s3af8u6
Plan Hash: 3732797803
select  ch, cname, pa.pa, pname
from
 ch, pa where ch.pa = pa.pa and pa.pa = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0        112          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          0        112          0         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  NESTED LOOPS  (cr=112 pr=0 pw=0 time=0 us cost=2 size=1113 card=1)
      1   TABLE ACCESS BY INDEX ROWID PA (cr=3 pr=0 pw=0 time=0 us cost=2 size=85 card=1)
      1    INDEX UNIQUE SCAN IDX_PA (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74595)
    100   TABLE ACCESS BY INDEX ROWID CH (cr=109 pr=0 pw=0 time=0 us cost=0 size=1028 card=1)
    100    INDEX RANGE SCAN IDX_CHPA (cr=9 pr=0 pw=0 time=1386 us cost=0 size=0 card=1)(object id 74598)

DBMS_XPLAN.DISPLAY_CURSORプロシージャのすすめ

このような理由により、私は、SQLパフォーマンス問題が発生したときには、 10.1から導入された(※3) DBMS_XPLAN.DISPLAY_CURSORプロシージャ(※1) を使います。 このプロシージャはV$SQL_PLAN(共有プール内の実行計画情報)から実行計画を取得する ため、ほぼ確実に(※2)、SQL実行時に使用されていた実行計画を取得できますし、 Predicate Infomationなどの実行計画の詳細情報を確認できます。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6377g9s3af8u6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  6377g9s3af8u6, child number 0
-------------------------------------
select  ch, cname, pa.pa, pname from ch, pa where ch.pa = pa.pa and
pa.pa = 1

Plan hash value: 3732797803

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |          |     1 |  1113 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PA       |     1 |    85 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | IDX_PA   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| CH       |     1 |  1028 |     0   (0)|          |
|*  5 |    INDEX RANGE SCAN          | IDX_CHPA |     1 |       |     0   (0)|          |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PA"."PA"=1)
   5 - access("CH"."PA"=1)


24行が選択されました。
  • (※1) またはその仲間のDBMS_XPLAN.DISPLAY_AWRなど
  • (※2) 共有カーソルが共有プールからpurgeされた場合は確認できません。また、例外的に複数の子カーソルが存在する場合は、 子カーソルを特定するために、ASH(Active Session History)と付き合わせる 必要がある場合があります。
  • (※3) 9.x では、直接V$SQL_PLANを問い合わせるカスタムSQLを使用します。

EXPLAIN PLANとSQLトレースは、 Oracle 8iでも使用できるOracle Databaseに昔から存在している方法です。 したがって、昔からOracle Databaseを使用しているエンジニアの中には、これ以外の実行計画の取得方法をしらずに、 EXPLAIN PLANとSQLトレースを使用している場合もあるでしょう。 しかし、上記の理由から、Oracle Database 10g以降(*3)では、DBMS_XPLAN.DISPLAY_CURSORプロシージャ を使用することを、強くお勧めします。

そのほかの実行計画確認方法

なお、上記の3つ以外にも、実行計画を確認する方法があります。 しかし、これらは、上記の3つのバリエーションと考えることができます。(※4)

  • SQL*Plusのautotrace

→ 内部的にEXPLAIN PLANを実行しています。

  • StatspackのSQLレポート
  • (10.2-) AWRのSQLレポート (要 Enterprise Edition + Diagnostics Pack)

→ これらの機能では、V$SQL_PLANの情報をスナップショットとしてコピーした情報を 元に実行計画を取得しているため、 DBMS_XPLAN.DISPLAY_CURSORプロシージャ(V$SQL_PLAN) と同等であると分類できます。 SQLの共有カーソルが、共有プールからage-outされた場合、V$SQL_PLAN から実行計画を確認すること ができませんが、これらの方法では、共有プールからage-outされた過去のSQL実行についても 実行計画を確認できる場合があります。 (スナップショット収集時に、共有カーソルが共有プールに存在しているかどうかに依存します)

  • (※4) 11.1から導入されたリアルタイムSQL監視の分類は難しいですが、別の方法と分類するか、 V$SQL_PLANを強化したものと位置づけるのが妥当でしょうね。
このエントリをはてなブックマークに登録

2 Responses to “SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わない”

  1. 1
    WR blog » DBMS_XPLAN.DISPLAY_CURSORの使い方とちょっとした落とし穴:

    [...]         « SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わな… [...]

  2. 2
    WR blog » 9/30(金) Oracle LOVERS2 でお話させていただきました。:

    [...] SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わな… [...]

Leave a Reply

Profile

渡部亮太 / Watabe Ryota
代官山在住のOracle Database Engineer。 株式会社コーソル所属。講演/講師業もぼちぼち。書籍「プロとしてのOracle運用管理入門」「プロとしてのOracleアーキテクチャ入門」買ってくれるとうれしいなっと。 twitter:wrcsus4

Book



Other Works

Certifications

  • Oracle Master 10g Platinum
  • Oracle Master 11g Gold
  • Oracle Master Silver Oracle PL/SQL Developer
  • Oracle Master Expert 10g RAC
  • Oracle Master Expert Oracle on Linux
  • LPIC level2
  • CCNA
  • 日商簿記3級

Contact

wrcsus4 _at_ gmail _dot_ com

Archives

Recent Posts

Recent Comments

Categories

Tags

Meta