SQLチューニングの基盤となる統計情報：Oracle SQLチューニング講座（5）（4/4 ページ）

» 2004年10月21日 00時00分公開

[亀田明裕，株式会社アゲハ]

SQL*PlusのAUTOTRACE機能

　AUTOTRACEはSQL*Plusの機能で、SQLの実行計画、および実行時に必要としたシステムリソース（これを実行統計と呼びます）などを簡単に確認することができます。また、SQLトレースには含まれないメモリソート、ディスクソートの発生回数なども確認できます。

AUTOTRACE機能の設定方法

　AUTOTRACE機能を使用するためには、初回のみ事前準備作業が必要になります。ここでは、例としてSCOTTユーザーに対して設定を行ってみます。

1. SYSユーザーでplustrce.sqlを実行し、PLUSTRACEロールを作成する

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

　このスクリプトは、データベースに対して1度だけ実行します。

2. AUTOTRACE機能を使用するユーザーにPLUSTRACEロールを付与する

SQL> GRANT plustrace TO scott;

3. 実行計画の情報を格納するためのPLAN_TABLE表を作成する

$ sqlplus scott/パスワード

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

　AUTOTRACE機能を実行するユーザーでutlxplan.sqlを実行し、実行計画を格納するためのPLAN_TABLE表を作成します。上記では、SCOTTユーザーで実行します。

　これで、SCOTTユーザーがAUTOTRACE機能を使用する準備は完了です。

AUTOTRACE機能の使用方法

　それでは、実際にAUTOTRACE機能を利用してみます。AUTOTRACE機能には、表6にあるオプションがあります。ここでは、TRACEONLYオプションを指定し、SQLの実行結果を出力せずに、実行計画、実行統計のみを出力します。


オプション	説明
SET AUTOTRACE ON	実行計画と実行統計をレポート出力する
SET AUTOTRACE OFF	レポート出力をしない
SET AUTOTRACE ON EXPLAIN	実行計画のみレポート出力する
SET AUTOTRACE ON STATISTICS	実行統計のみレポート出力する
SET AUTOTRACE TRACEONLY	データをフェッチするが、結果を出力せずに実行計画、実行統計をレポート出力する。後ろに、EXPLAIN、STATISTICSオプションを付けることも可能
表6　AUTOTRACEの主なオプション

図2　AUTOTRACEの出力結果例


（1）	実行計画	SQLの実行計画を表示
（2）	recursive calls	SQL実行時に内部で発行されたリカーシブコール数
（3）	db block gets	DMLやSELECT FOR UPDATEを発行したときなどに発生するカレントモードで読み込まれたブロック数
（4）	consistent gets	SELECTを発行したときなどに発生する読み取り一貫性モードで読み込まれたブロック数
（5）	physical reads	ディスクアクセスによって読み込まれたブロック数
（6）	redo size	REDOログに書き込まれたサイズ（byte）
（7）	bytes sent via SQL*Net to client	クライアントへ送られた合計byte数
（8）	bytes received via SQL*Net from client	クライアントから受信した合計byte数
（9）	SQL*Net roundtrips to/from client	クライアントに送受信されたNetメッセージの合計数
（10）	sorts (memory)	メモリソート回数
（11）	sorts (disk)	ディスクソート回数
（12）	rows processed	SQLが処理した件数
表7　AUTOTRACEの主な出力項目

V$SQL_PLANでの実行計画確認

　Oracle9iからは、実行計画が共有プール内にキャッシュされているため、キャッシュに保持されている間は、過去に実行されたSQLの実行計画を確認することができるようになりました。ここでは実際の確認方法を説明します。

V$SQL_PLANからの実行計画確認方法

　SQLの実行計画を調べるためには、SQLが特定されることと、ADDRESS値、HAHS_VALUE値が必要になります（確認方法は、第4回「チューニングが必要なSQLを洗い出す」を参照してください）。

DEFINE W_ADDRESS=54B4DF20 -- ここに対象SQLの　　　　　　　　　　　　　　　 -- ADDRESS値を指定 DEFINE W_HASH_VALUE=1273901568 -- ここに対象SQLの　　　　　　　　　　　　　　　 -- HASH_VALUE値を指定 SELECT id, lpad (' ', depth) || operation operation, options, object_name, optimizer, cost FROM v$sql_plan WHERE hash_value=&W_HASH_VALUE AND address='&W_ADDRESS' START WITH id = 0 CONNECT BY (PRIOR id=parent_id AND PRIOR hash_value=hash_value AND PRIOR child_number=child_number) ORDER SIBLINGS BY id, position; ID OPERATION OPTIONS OBJECT_NAME ⇒ OPTIMIZE COST ---- -------------------- --------------- ----------------- ⇒------------- -------- ---------- 0 SELECT STATEMENT ⇒ CHOOSE 1 SORT GROUP BY 2 TABLE ACCESS FULL LINEITEM

リスト2　V$SQL_PLANから出力した実行計画例（クリックすると別ウィンドウで表示します）
（注：⇒記号は表示の都合で折り返していることを表します）

　最後に、各取得方法のメリット、デメリットを表8にまとめます。


	メリット	デメリット
SQLトレース＋TKPROFユーティリティ	・詳細な情報が取得可能・時間統計の取得が可能・アプリケーションで実行されるすべてのSQLを取得可能	・トレースファイルを格納するためのディスク領域が必要・取得時に多少の負荷が発生
*SQLPlusのAUTOTRACE機能**	・簡単に実行計画を確認することが可能	・環境設定が必要
動的パフォーマンスビューの利用	・過去に実行されたSQLの実行計画を確認することが可能	・共有SQL領域が大きい環境やシステム自体の負荷が非常に高い環境では、オーバーヘッドとなる場合がある
表8　各方法のメリット／デメリット