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

Oracle SQLチューニング講座(5):SQLチューニングの基盤となる統計情報 (4/4)

[亀田明裕,株式会社アゲハ]
前のページへ 1|2|3|4       

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の出力結果例 図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の情報収集に関する説明は終了です。今回説明したような方法を使用して、実行計画など、より詳細な情報を収集し、チューニングを進めていきます。次回以降は、実際のチューニング方法や、テクニックについて説明します。


前のページへ 1|2|3|4       

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。