SQL文の実行計画を見る

「実行したSQL文が遅いんだけど」何とかならないかな？といったときにOracleがどんな風に考えているかを確認することができます。このOracleが考えている部分を実行計画といいます。

Oracleでは、実行計画を確認することにより表の結合方法やインデックスの使用状況などを確認することができ、データベースをチューニングするために役立てます。

確認環境：Oracle9i(9.2.0.1.0)

環境設定

実行計画の手順は、PLAN_TABLEというテーブルに保存されるので、まずPLAN_TABLEを作成します。

Oracleをインストールした際、PLAN_TABLEの構造が定義された utlxplan.sql スクリプトが用意されているので、SYSユーザーで実行します。($ORACLE_HOME/rdbms/admin/utlxplan.sql)

SYSユーザーでSQL*Plusを起動します。

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

全てのユーザーが使用できるように、PUBLICシノニムを作成し権限を付与します。

SQL> CREATE OR REPLACE PUBLIC SYNONYM PLAN_TABLE  FOR SYS.PLAN_TABLE;
SQL> GRANT ALL ON PLAN_TABLE TO PUBLIC;

以上で実行計画を見る準備ができました。

実行計画を見る

実行計画を確認するには、EXPLAIN PLAN 文を使用します。確認したいSQL文(SELECT、INSERT、UPDATE、DELETE、CREATE TABLE、CREATE INDEX、ALTER INDEX ・・・ REBUILD)をFOR句に指定して実行します。

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM MST_USER WHERE USER_ID=12345;

解析されました。

PLAN_TABLEに格納された実行計画を確認するには、Oracleから提供されているutlxpls.sqlを使用します。 ($ORACLE_HOME/rdbms/admin/utlxpls.sql)

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id  | Operation                   |  Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| MST_USER     |       |       |       |
|*  2 |   INDEX UNIQUE SCAN         | PK_MST_USER  |       |       |       |
----------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

   2 - access("MST_USER"."USER_ID"='12345')

utlxpls.sqlの中では、次のようなSELECT文が実行されておりIDの指定やフォーマットの指定ができます。

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

指定できるフォーマットは、BASIC、TYPICAL、ALL、SERIALの4種類となっているようです。