連載
» 2004年08月25日 00時00分 公開

Oracle SQLチューニング講座(3):SQLチューニングの必須知識を総ざらい(後編) (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実行時の余分なオーバーヘッドとなるため、可能な限り事前に取得しておくようにすべきです。


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

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

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

メールマガジン登録

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