2004年09月28日(火)

[Oracle] SQLの実行計画を取得する方法

テーマ:DB-Oracle
SQLのパフォーマンス調査をする際、そのSQLがどのように実行されているかを知るにはSQLの実行計画を取得する必要があります。
1.事前準備
SQLの実行計画を取得するには、事前にPLAN_TABLEを作成し、必要な権限を与えてやらなければなりません。

PLAN_TABLEの作成は以下の手順で行います。

1) SQL*PLUS を SYS ユーザーで起動します。

2) PLAN_TABLE を作成するスクリプトを実行します。
SQL> @[ORACLE_HOME]\rdbms\admin\utlxplan.sql
3) PLAN_TABLE がすべてのユーザーで参照可能となるように権限を付与します。
SQL> grant all on plan_table to public;
4) PLUSTRACEロールを作成するスクリプトを実行します。
SQL> @[ORACLE_HOME]\sqlplus\admin\plustrce.sql
5) PLUSTRACEロールをすべてのユーザーに付与します。
grant plustrace to public;
6) PUBLIC シノニムを作成します。
SQL> create public synonym plan_table for sys.plan_table;

2.SQLの実行計画を取得する方法(SQL*PlusのAUTOTRACE)
SQL*Plusで以下のコマンドを実行すると、それ以降にSQLを実行した際に、実行結果に続いて実行計画、実行統計が表示されます。

SQL> set auto trace on

なお、AUTOTRACEは以下のようにオプションをつけることにより、表示する内容を選択することができます。

実行結果 実行計画 実行統計
set autotrace on
set autotrace traceonly ×
set autotrace on explain ×
set autotrace on statistics ×

3.SQLの実行計画を取得する方法(EXPLAIN PLAN)
以下のようにEXPLAIN PLANコマンドを実行するとPLAN_TABLEに実行計画が格納されます。

explain plan for SQL文;


ただし、格納された実行計画を見るにはわざわざ複雑なSQLを実行しなければならないので少々使いづらいです。

4.SQLの実行計画を取得する方法(V$SQL_PLAN)

Oracle9iでは、ディクショナリV$SQL_PLANに実行計画が保存されるようになりました。


該当するSQLが共有SQL領域にキャッシュされている限りは、これを検索すれば実行計画を見ることができます。


ただし、これもわざわざ複雑なSQLを実行しなければならないので少々使いづらいです。

5.SQLの実行計画を取得する方法(OBJECT BROWSER)

OBJECT BROWSERを使えば、SQL実行の画面でグラフィカルに実行計画を見ることができます。


簡単で見やすいので、OBJECT BROWSERが使えるならばこの方法で実行計画を見るのが一番いいでしょう。

6.SQLの実行計画を取得する方法(SQLトレース)
SQLトレースは、実行計画を取得したいSQL文が特に決まっているわけではなく、セッションまたはシステム全体でパフォーマンスの悪いSQLを見つけたいときなどに便利です。

SQLトレースをセッションレベルで取りたい場合は以下のように設定します。

SQL> alter session set timed_statistics=true;
SQL> alter session set sql_trace=true;


インスタンスレベルで取りたい場合には初期化パラメータファイルに設定してインスタンスを再起動します。

実行計画は初期化パラメータUSER_DUMP_DESTで指定されたディレクトリにトレースファイル(.trc)として出力されます。


ただし、これをそのまま見てもわかりづらいので、TKPROFコマンドを使って整形してから見たほうがいいでしょう。

C:\> tkprof トレースファイル名 出力ファイル名 オプション

  ※オプションについてはマニュアルなどを参照のこと

SQLトレースの実行中はOracleのパフォーマンスが低下する上に、トレースファイルもどんどん大きくなるので、パフォーマンス調査時などに限って利用したほうがいいでしょう。

コメントComments

[コメントをする]

コメント投稿

一緒にプレゼントも贈ろう!

Amebaおすすめキーワード