Oracle SQLチューニング講座（5）

SQLチューニングの基盤となる統計情報　Page 4

株式会社アゲハ
亀田 明裕
2004/10/21

■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を取得可能		 ・トレースファイルを格納するためのディスク領域が必要
・取得時に多少の負荷が発生
SQL*PlusのAUTOTRACE機能 ・簡単に実行計画を確認することが可能 ・環境設定が必要
動的パフォーマンス
ビューの利用		 ・過去に実行されたSQLの実行計画を確認することが可能 ・共有SQL領域が大きい環境やシステム自体の負荷が非常に高い環境では、オーバーヘッドとなる場合がある
表8　各方法のメリット／デメリット 

◇

　以上でSQLの情報収集に関する説明は終了です。今回説明したような方法を使用して、実行計画など、より詳細な情報を収集し、チューニングを進めていきます。次回以降は、実際のチューニング方法や、テクニックについて説明します。（次回に続く）

4/4  

　Index
連載 Oracle SQLチューニング講座（5）
SQLチューニングの基盤となる統計情報
  Page 1
・SQL詳細情報の取得
・SQLトレース、TKPROFユーティリティの使用方法
　−SQLトレース、TKPROFユーティリティの実行手順
  Page 2
　−EVENT 10046でのSQLトレースの取得
　−別セッションのSQLトレース、EVENT 10046のSQLトレース取得手順
  Page 3
・トレースファイルの注目ポイント
　−SQL実行時の実行計画の見方
　−TKPROF実行時の実行計画の見方
　−SQLの分析
Page 4
・SQL*PlusのAUTOTRACE機能
　−AUTOTRACE機能の設定方法
　−AUTOTRACE機能の使用方法
・V$SQL_PLANでの実行計画確認
　−V$SQL_PLANからの実行計画確認方法


連載　Oracle SQLチューニング講座

TechTargetジャパン

Database Expert フォーラム 新着記事

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH