【Oracle】過去の実行計画を確認する方法

Oracle
2023.05.17

はじめに

OracleではSQL*PlusのAUTOTRACEを使えば、SQLを流すと同時に実行計画が取得できますが、過去の実行計画はAUTOTRACEでは取得できません。

Oracleで過去の実行計画を確認する方法を記載します。

過去の実行計画を取得するSQL

sysユーザーにて以下SQLを実行します。

以下SQLの★部分へSQL_IDを設定します(IN句にしているのでカンマ区切りにして複数設定可能)。

SET LINESIZE 250
COLUMN ID FORMAT 999
COLUMN OPERATION FORMAT A55
COLUMN OBJECT FORMAT A30
COLUMN BYTES FORMAT A6
COLUMN COST FORMAT A6
COLUMN PREDICATES FORMAT  A100

SELECT 
    ID,
    '|', --項目仕切り
    DECODE( OPTIMIZER,
    'ANALYZED', LPAD( ' ', DEPTH * 1, ' ' ) || OPERATION || ' ' || NVL2( OPTIONS, OPTIONS, null ),
    NULL, LPAD( ' ', DEPTH * 1, ' ' ) || OPERATION || ' ' || OPTIONS,
    LPAD( ' ', DEPTH * 1, ' ' ) || OPERATION
  ) OPERATION,
    '|', --項目仕切り
    DECODE( OPTIMIZER,
    'ANALYZED', OBJECT_NAME,
    NULL, OBJECT_NAME,
    'Optimizer = ' || OPTIMIZER
  ) OBJECT,
    '|', --項目仕切り
    CASE WHEN BYTES > 1073741824+1073741824 THEN TO_CHAR(ROUND(BYTES/1073741824)) ||'G' WHEN BYTES > 1048576+1048576 THEN TO_CHAR(ROUND(BYTES/1048576)) ||'M' WHEN BYTES > 1024+1024 THEN TO_CHAR(ROUND(BYTES/1024)) ||'K' ELSE TO_CHAR(BYTES) END BYTES,
    '|', --項目仕切り
    CASE WHEN COST  > 1073741824+1073741824 THEN TO_CHAR(ROUND( COST/1073741824)) ||'G' WHEN COST  > 1048576+1048576 THEN TO_CHAR(ROUND( COST/1048576)) ||'M' WHEN COST  > 1024+1024 THEN TO_CHAR(ROUND( COST/1024)) ||'K' ELSE TO_CHAR( COST) END COST,
    '|', --項目仕切り
    DECODE(FILTER_PREDICATES,NULL,'','FIL■'||FILTER_PREDICATES)|| DECODE(ACCESS_PREDICATES,NULL,'','ACC□'||ACCESS_PREDICATES) PREDICATES --PROJECTION(行生成前フィルタ処理)
  FROM V$SQL_PLAN
  WHERE (hash_value,address) in (select hash_value,address 
                                   from V$SQL 
                                  where SQL_ID in ('xxxxxx')  --★ここへSQL_IDを設定する
                                 )
    AND CHILD_NUMBER = 0
  ORDER BY ADDRESS, HASH_VALUE, CHILD_NUMBER, ID;
--  ID | OPERATION                                               | OBJECT                         | BYTES  | COST   | PREDICATES(FIL:FILTER ACC:ACCESS)
-----|---------------------------------------------------------|--------------------------------|--------|--------|-----------------------------------------------------------------------------------------------------

「SET LINESIZE。。。」の部分は、画面いっぱいに表示したTeraTermで実行して丁度良いサイズにしています(お好みでカスタマイズしてください)。

インデントが見やすく、BYTEとCOSTも単位を付けるようにしていて見やすいのでいつもこのSQLを使用して実行計画を取得しています。

SQLの最後の2行「– ID | OPERATION。。。」(コメントアウトしている部分)も一緒に流すことで取得した実行計画のヘッダーを付けています。

実行計画の取得結果イメージ

私が個人で運営している販売管理DBの実行計画取得結果(一番下の行はモザイク無しにしていますのでご参考までにご参照ください)

取得項目

取得項目の簡単な説明です(取得内容はこちらのOracleのサイトを見た方が良いですね)。

  • OPERATION：このステップで実行された内部操作の名前
  • OBJECT：このステップで使われたINDEXの名前(INDEXを使用していない場合はテーブル名)
  • BYTE：見積バイト数
  • COST：見積累計コスト
  • PREDICATES：FIL■＝フィルター情報(フィルタにかけた後で行を生成する場合に使用する述語)、ACC□＝アクセス情報(アクセス構造内の行の検索に使用する述語)

SQL_ID取得方法はこちらで備忘logしています。

まとめ

データベースの実行計画を調査することにはいくつかの重要なメリットがあります。
実行計画は、データベースクエリの効率性とパフォーマンスに関連する情報を提供します。
以下は、実行計画を調査するメリットのいくつかです。

  1. パフォーマンスの最適化: 実行計画を調査することにより、クエリがどのように実行されるかを理解し、不要な遅延やリソースの浪費を軽減できます。効率的な実行計画を選択することで、クエリの実行時間を短縮し、データベースのパフォーマンスを向上させることができます。
  2. インデックスの最適化: 実行計画を分析すると、クエリがどのテーブルや列をスキャンしているかを理解できます。この情報に基づいて、適切なインデックスを設計および調整できます。適切なインデックスを使用することで、データの取得が高速化されます。
  3. クエリのチューニング: 実行計画の調査により、クエリのボトルネックや瓶頸の特定が容易になります。これにより、クエリを最適化し、データベースのパフォーマンスを向上させることができます。クエリのチューニングは、データベースアプリケーションの全体的な効率性に影響を与えます。
  4. リソースの最適な利用: 実行計画の分析により、クエリがどれだけのメモリ、CPU、ディスクI/Oなどのリソースを必要とするかを把握できます。これにより、リソースの過度な使用を抑え、複数のクエリが共存する状況での安定したパフォーマンスを確保できます。
  5. トラブルシューティング: パフォーマンスの問題が発生した場合、実行計画の調査はトラブルシューティングの手がかりを提供します。不正確な実行計画が生成されている可能性があり、それが問題の原因である場合、修正が必要です。
  6. セキュリティ: 実行計画を調査することで、クエリ内のセキュリティ上の脆弱性や悪意のある操作を検出しやすくなります。セキュリティポリシーに違反するクエリを特定し、適切なセキュリティ対策を講じることができます。

データベースの実行計画を定期的に調査し、最適化することは、データベースアプリケーションのパフォーマンスとセキュリティの向上に重要な要素です。

コメント

タイトルとURLをコピーしました