SQLチューニングの基盤となる統計情報：Oracle SQLチューニング講座（5）（4/4 ページ）
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）
SQL*PlusのAUTOTRACE機能
AUTOTRACEはSQL*Plusの機能で、SQLの実行計画、および実行時に必要としたシステムリソース（これを実行統計と呼びます）などを簡単に確認することができます。また、SQLトレースには含まれないメモリソート、ディスクソートの発生回数なども確認できます。
AUTOTRACE機能の設定方法
AUTOTRACE機能を使用するためには、初回のみ事前準備作業が必要になります。ここでは、例としてSCOTTユーザーに対して設定を行ってみます。
1. SYSユーザーでplustrce.sqlを実行し、PLUSTRACEロールを作成する
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
このスクリプトは、データベースに対して1度だけ実行します。
2. AUTOTRACE機能を使用するユーザーにPLUSTRACEロールを付与する
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
3. 実行計画の情報を格納するためのPLAN_TABLE表を作成する
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
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の主なオプション | |
| （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を洗い出す」を参照してください）。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
最後に、各取得方法のメリット、デメリットを表8にまとめます。
| メリット | デメリット | |
|---|---|---|
| SQLトレース＋TKPROFユーティリティ | ・詳細な情報が取得可能 ・時間統計の取得が可能 ・アプリケーションで実行されるすべてのSQLを取得可能 |
・トレースファイルを格納するためのディスク領域が必要 ・取得時に多少の負荷が発生 |
| SQL*PlusのAUTOTRACE機能 | ・簡単に実行計画を確認することが可能 | ・環境設定が必要 |
| 動的パフォーマンス ビューの利用 |
・過去に実行されたSQLの実行計画を確認することが可能 | ・共有SQL領域が大きい環境やシステム自体の負荷が非常に高い環境では、オーバーヘッドとなる場合がある |
| 表8 各方法のメリット／デメリット | ||
以上でSQLの情報収集に関する説明は終了です。今回説明したような方法を使用して、実行計画など、より詳細な情報を収集し、チューニングを進めていきます。次回以降は、実際のチューニング方法や、テクニックについて説明します。
Copyright © ITmedia, Inc. All Rights Reserved.