SQLチューニングの必須知識を総ざらい（後編）：Oracle SQLチューニング講座（3）（2/3 ページ）

[倉田寛正，株式会社アゲハ]

統計情報の取得

　前編で説明したように、Oracle9iまでは初期化パラメータ「OPTIMIZER_MODE」がデフォルトの「CHOOSE」の場合、統計情報が取得されているかによって、オプティマイザが使用するアプローチ方法（コストベース、あるいはルールベース）が異なります。

　ルールベース・アプローチであった場合、あらかじめOracle内で決められたアクセス順序に従い実行計画を生成しますが、コストベース・アプローチでは、統計情報を基にコストを計算し、実行計画を生成しています。そのため、SQLチューニングを行うには、どちらのアプローチ方法が使用されているのか、またコストベース・アプローチであった場合には、統計情報がいつ取得されたものかを把握しておく必要があります。

取得される主な統計情報

　統計情報とは、表、索引などのレコード件数や、使用している領域、カーディナリティ注2、データ分布注3 などのデータ特性を表す情報であり、表、索引、もしくは列単位で取得できます。具体的には、表1のような情報のことを指しています。

注2：カーディナリティ

レコード中の一意な値の個数を指します。例えば、性別データ（男、女）のように値の種類が少ない場合には、カーディナリティが低いといいます。逆に社員番号のようにほとんどの値が一意である場合は、カーディナリティが高いといいます。

注3：データ分布

列単位で取得した場合のみ収集されます。コストベース・アプローチは、値が平均的に分散していることを前提として動作するため、「特定の種類のデータが極端に多い」など、データの偏りが大きい場合には適切でない実行計画を選択してしまう場合があります。このような場合、列単位で、より詳細な統計情報（ヒストグラム）を取得することで、オプティマイザにデータの分布も考慮させることができます。

	項目	ディクショナリの列名
表の統計情報	行数	NUM_ROWS
	使用ブロック数	BLOCKS
	未使用ブロック数	EMPTY_BLOCKS
	空き領域の平均サイズ（bytes）	AVG_SPACE
	行連鎖、行移行の行数	CHAIN_CNT
	行の平均長（bytes）	AVG_ROW_LEN
索引の統計情報	行数	NUM_ROWS
	リーフ・ブロックまでのB*Treeの深さ	BLEVEL
	リーフ・ブロック数	LEAF_BLOCKS
	重複していない行数	DISTINCT_KEY
	索引の値ごとの平均リーフ・ブロック数	AVG_LEAF_BLOCKS_PER_KEY
	索引の値ごとの平均データ・ブロック数	AVG_DATA_BLOCK_PER_KEY
	クラスタ係数	CLUSTER_FACTOR
列の統計情報	列内で重複しない値の数	NUM_DISTINCT
	列内の下限値	LOW_VALUE
	列内の上限値	HIGH_VALUE
	列内のNULLの数	NUM_NULLS
	列の密度	DENSITY
	列のヒストグラム内のバケット数	NUM_BUCKETS
表1　収集される統計情報一覧

　取得した統計情報は、SYSユーザーが所有するデータ・ディクショナリに格納され、下記のようにディクショナリ・ビュー（ALL_または、DBA_、USER_で始まるビュー、以下ではUSER_で始まるビュー一覧）で統計情報を確認することができます。

• USER_TABLES
• USER_INDEXES
• USER_TAB_COLUMNS
• USER_TAB_PARTITIONS
• USER_IND_PARTITIONSなど

　以下の例で、統計情報が取得されていない場合は、TABLE_NAME以外は表示されません。

SQL> select table_name, num_rows, blocks, avg_row_len, sample_size, last_analyzed   2  from dba_tables where table_name = 'EMPLOYEE'; TABLE_NAME      NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED --------------- -------- -------- ----------- ----------- ------------------- EMPLOYEE              32        1          42          32 2004-08-06 20:15:32

リスト1　統計情報の取得（クリックすると別ウィンドウで表示します）

統計情報の取得方法

　統計情報の取得は、DBMS_STATSパッケージ、あるいはANALYZEコマンドによって取得できます。DBMS_STATSパッケージにはパフォーマンス改善の機能が追加されています。例えば、「統計情報の取得時間短縮のためのパラレル処理による実行」「1％以下のランダムなデータサンプリングによる取得」「統計情報が古いオブジェクトのみ再取得」などです。

　なおOracle9iからは、DBMS_STATSパッケージにてシステムの統計情報（システムのI/OおよびCPU性能など）を取得することも可能となっており、より最適な実行計画を選択できるようになっています。

情報取得に対する注意点

　オプティマイザは、事前に取得された統計情報を使用して実行計画を決定します。そのため、次のような点に注意する必要があります。

• 統計情報取得後に、大幅にデータ件数が増減した
• 複数の表の結合処理を実行するような場合、一部の表のみ統計情報が古かった
• 複数の表の結合処理を実行するような場合、一部の表のみ統計情報が取得されていなかった注4

注4：一部の表のみ統計情報が取得されていなかった

コストベース・アプローチで統計情報が取得されていないオブジェクトにアクセスした場合、Oracleはハードコーディングされているデフォルト値を使用してコスト計算を行います。Oracle9iR2からは、初期化パラメータ「optimizer_dynamic_sampling」を「1」（デフォルト）以上の値に設定することで、SQL実行時に統計情報を動的に収集することも可能となっていますが、SQL実行時の余分なオーバーヘッドとなるため、可能な限り事前に取得しておくようにすべきです。

　上記のような場合には、最適な実行計画を生成することができずに、パフォーマンスに影響を及ぼす可能性があります。そのため、頻繁に更新される表や、大幅にデータ件数が増減するような表に関しては、定期的に関連するオブジェクトの統計情報を取得し直すことが重要です。