[Oracle] 実行したSQLの実行計画を見る(V$SQL_PLAN)

実行したSQLの実行計画はV$SQL_PLANで見ることができます。

例えば、

SELECT DEPT.DNAME, EMP.ENAME
  FROM EMP
  INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
  WHERE EMP.SAL > 2500
  ORDER BY DEPT.DEPTNO, EMP.EMPNO;

というSQLの実行計画を見る場合、まず、V$SQLから該当SQLのADDRESS、HASH_VALUE、CHILD_NUMBERを探しだし、

SET LINESIZE 100
COLUMN SQL_TEXT FORMAT A50 WORD_WRAP
COLUMN ADDRESS FORMAT A10
COLUMN HASH_VALUE FORMAT 9999999999
COLUMN CHILD_NUMBER FORMAT 99

SELECT SQL_TEXT, ADDRESS, HASH_VALUE, CHILD_NUMBER
  FROM V$SQL
  WHERE SQL_TEXT LIKE 'SELECT DEPT.DNAME, EMP.ENAME%';

SQL_TEXT                                           ADDRESS     HASH_VALUE CHILD_NUMBER
-------------------------------------------------- ---------- ----------- ------------
SELECT DEPT.DNAME, EMP.ENAME   FROM EMP   INNER    27FEE294    2845027971            0
JOIN DEPT     ON EMP.DEPTNO = DEPT.DEPTNO   WHERE
EMP.SAL > 2500   ORDER BY DEPT.DEPTNO, EMP.EMPNO

そのADDRESS、HASH_VALUE、CHILD_NUMBERをキーにV$SQL_PLANを検索します。

この時、V$SQL_PLANを単純に検索するだけでは見にくいので、インデントをかますなど検索結果を少し加工してやれば見やすくなります。

Object Browser風に加工してV$SQL_PLANから実行計画を取り出してみると以下のようになります。

SET LINESIZE 100
COLUMN ID FORMAT 999
COLUMN OPERATION FORMAT A50
COLUMN OBJECT FORMAT A20
COLUMN COST FORMAT 9999
COLUMN BYTES FORMAT 99999

SELECT
  ID,
  DECODE( OPTIMIZER,
    'ANALYZED', LPAD( ' ', DEPTH * 3, '　' ) || OPERATION || ' ' || NVL2( OPTIONS, OPTIONS, null ),
    NULL, LPAD( ' ', DEPTH * 3, '　' ) || OPERATION || ' ' || OPTIONS,
    LPAD( ' ', DEPTH * 3, '　' ) || OPERATION
  ) OPERATION,
  DECODE( OPTIMIZER,
    'ANALYZED', OBJECT_NAME,
    NULL, OBJECT_NAME,
    'Optimizer = ' || OPTIMIZER
  ) OBJECT,
  COST,
  BYTES
  FROM V$SQL_PLAN
  WHERE ADDRESS = '27FEE294' AND HASH_VALUE = 2845027971 AND CHILD_NUMBER = 0
  ORDER BY ADDRESS, HASH_VALUE, CHILD_NUMBER, ID;

  ID OPERATION                                          OBJECT                COST  BYTES
---- -------------------------------------------------- -------------------- ----- ------
   0 SELECT STATEMENT                                   Optimizer = CHOOSE      13
   1 　 SORT ORDER BY                                                           13    168
   2  　　 MERGE JOIN                                                           12    168
   3 　　　　 TABLE ACCESS BY INDEX ROWID               DEPT                     3     55
   4  　　　　　 INDEX FULL SCAN                        PK_DEPT                  1
   5 　　　　 SORT JOIN                                                          9     91
   6  　　　　　 TABLE ACCESS FULL                      EMP                      7     91

きれいに階層化されてなかなか見やすくなります。

ただ、毎回このような複雑なSQLを叩くのは大変なので、頻繁に使うのであればこのSQL（WHERE句を除く）をビューとして定義しておけば、結構便利に使えるでしょう。