オプティマイザの判断ミスを疑ってみよう：Oracleパフォーマンス障害の克服（4）（1/3 ページ）

Oracleデータベースの運用管理者は、突発的に直面するパフォーマンス障害にどうやって対処したらよいか。本連載は、非常に複雑なOracleのアーキテクチャに頭を悩ます管理者に向け、短時間で問題を切り分け、対処法を見つけるノウハウを紹介する。対象とするバージョンはOracle8から9iまでを基本とし、10gの情報は随時加えていく。（編集局）

[高橋潤，＠IT]

　前回「Bツリーインデックスに最高のパフォーマンスを」では、インデックスの状況を確認するためにANALYZEコマンドを使用しました。本稿では、ANALYZEコマンドで取得した統計情報から、オプティマイザが正しい実行計画を立てているかをチェックする方法を解説します。

オプティマイザの2つのモード

　オプティマイザとは、Oracleがデータに対して最も効率的な実行計画を決定する機能です。オプティマイザの2つのモードを正しく理解しましょう。

• ルールベース
  データの状態にかかわらず、規定されたルールに基づいて、実行計画を決定します（Oracle 10g以降、この機能は廃止されました）。　
• コストベース
  ANALYZEコマンドによる統計情報を基にして、実行されるSQL文の処理にかかるコストを判断し、コストが最小となる実行計画を採用します。

　オプティマイザのモードは、初期化パラメータの「OPTIMIZER_MODE」で指定します。

OPTIMIZER_MODEの設定値	説明
RULE	オプティマイザはルールベースを採用する
CHOOSE （デフォルト値）	統計情報が使用可能か（ANALYZEが実行されているか）どうかを判断し、統計情報が使用できればコストベース、そうでない場合はルールベースを採用する
ALL_ROWS	サーバ内リソースの最小使用とスループットの最小化を行う
FIRST_ROWS	応答時間が最短となるよう検索を行う。検索結果の1行目に対する応答時間を最適化する
表1　初期化パラメータOPTIMIZER_MODEの設定値

　ルールベースでは、索引が存在する場合は必ずインデックス・スキャンが採用されます。これに対しコストベースでは、索引が存在していても、必ずしもインデックス・スキャンを採用するとは限らず、フル・スキャンしてしまうことや、誤ったインデックス・スキャンが行われることがあります。例えば、索引ブロックを検索するコストよりも、表データ全体を検索する方がコストが少ないと判断された場合です。コストベースでは統計情報を基にアクセスパスを作成するので、ユーザーが希望する検索方法を採用するとは限りません。

　インデックスを作成していてもフル・スキャンするという現象については、本稿の後半で詳しく見ていきます。その前段階として、まずはコストベースの基となる統計情報の取得に必要なANALYZEコマンドの解説を行います。

ANALYZEコマンドによる統計情報の取得

　ANALYZEコマンドで指定可能なオブジェクトは表と索引です。以下の注意点を参考にして、それぞれのケースで使用しやすい方法を採用してください。

• サンプルサイズが小さ過ぎると、正確な情報を取得することができません。データ構造を考慮に入れた経験的な最適サンプルサイズを模索する必要がありますが、表では25％程度、索引は100％が必要と考えられます。
• ANALYZEコマンドでサンプリングを50％以上に指定すると、COMPUTEオプションを使った操作と同じ100％のサンプリングによる統計となります。
• 索引が作成されている表に対してANALYZEを行うと、索引に対してもANALYZEされます。このとき表に対してESTIMATEを指定してANALYZEしても、索引に対してはCOMPUTEでANALYZEされます。

使用方法

SQL> ANALYZE オブジェクト名 COMPUTE STATISTICS;

リスト1　オブジェクトを走査し、正確な統計値（サンプリング＝100％）を取得する

SQL> ANALYZE オブジェクト名 ESTIMATE STATISTICS SAMPLE ##%;

リスト2　「##」で指定したパーセンテージのサンプリングを行い、統計値を推測する

SQL> ANALYZE オブジェクト名 DELETE STATISTICS;

リスト3　統計値を削除する

　DBMS_UTILITYパッケージによるスキーマ全体のANALYZEも可能です。特別にオブジェクトを指定せずスキーマ全体のANALYZEを行いたい場合や、バッチ作業時の作業の省力化に有効です。またOracle8i以降で追加されたDBMS_STATSパッケージでも同様の作業が可能ですが、ANALYZEコマンドほどの詳細な情報（行連鎖の数、未使用ブロックなど）は取得できません。

SQL> EXECUTE DBMS_UTILITY. ANALYZE_SCHEMA('スキーマ名', ⇒'{COMPUTE | ESTIMATE} STATISTICS'); SQL> EXECUTE DBMS_STATS. GATHER_SCHEMA_STATS ('スキーマ名', ⇒サンプリングパーセンテージ);

リスト4　DBMS_UTILITY／DBMS_STATSパッケージの利用法 （注：⇒記号は表示の都合で折り返していることを表します）

　前回で使用した「VALIDATESTRUCTURE」オプションを使用したコマンドの実行中は、統計情報を作成しているオブジェクトに対して排他ロックがかかるため、アプリケーションが稼働していない時間帯に使用してください。また、このオプションを使用して作成した統計情報はオプティマイザの判断に使用されません。つまり、実行環境でコストベースのオプティマイザを採用している環境でも、実行計画に影響を与えず統計情報を取得できるということです。（次ページへ続く）