統計情報の収集による実行計画のチューニング ( DBMS_STATS パッケージ )

DBMS_STATS による 統計情報の管理には、並列実行機能、バージョンの概念があり時間によるリストアが可能になり、 ディクショナリに保存する以外にユーザースキーマに保存*1することでバックアップと移行が可能になった。
また、表の新規作成、表定義の変更やTRUNCATE TABLEによる表の切り捨てなどによって統計情報が失効された場合には、 初回の SQL 実行時に(とても小さな)仮の統計情報が収集されるようになっている。

統計情報の収集方法の種類を収集するパッケージプログラム

統計情報の収集するパッケージ DBMS_STATSには、以下の統計収集サブプログラムがある。

統計収集の対象対応する DBMS_STATS サブプログラム
テーブルGATHER_TABLE_STATS の使用例
GATHER_INDEX_STATS の使用例
カラム
インデックス
スキーマGATHER_SCHEMA_STATS の使用例
データベースGATHER_DATABASE_STATS の使用例
システムGATHER_SYSTEM_STATS の使用例
ディクショナリGATHER_DICTIONARY_STATS

統計情報の種類

統計情報としては、以下のカテゴリの統計内容を収集して管理している。

システム統計
CPU、I/O 情報
  • cpuspeednw
  • ioseektim
  • iotfrspeed
テーブル統計
レコード数、データブロック数、レコードの平均長
カラム統計
個別数(DISTINCT カラム名)、NULLの数、ヒストグラム
インデックス統計
リーフブロック数、レベル(階層)、クラスタ化係数

推奨されない統計情報の取得方法

ANALYZE ( 下位互換用コマンド 8i 以下 )

CBO 向けには DBMS_STAT を使用する

  • テーブルのレコード数
  • テーブルの増加率
  • 各テーブルの更新率
  • ワークテーブルなどのように、レコード数が極端に増加/減少するテーブル
  • 検索に使用する列で値の偏りがあるテーブルと列

以上の指標の元に、ANALYZE のプランを立てる

  • 実施する周期のグループを分け(月、週、etc)
  • COMPUTE/ ESTIMATE (サンプリング数の決定)
  • 統計情報の取得
    ANALYZE TABLE tablename COMPUTE STATISTICS
    ANALYZE TABLE tablename ESTIMATE STATISTICS
  • 統計情報の削除
    ANALYZE TABLE tablename DELETE STATISTICS

10gのマニュアルでは、CBO用に ANALYZE は使用しないで、という表現があったので要注意(9iは猶予期間がどうかわかりません)

関連事項 (統計情報の手動収集と廃止コマンド)

  • 統計情報の手動収集について

自動更新が行われる間に大量のトランザクションが発生している場合やチューニング時には、統計情報の手動作成を行う。
自動更新はジョブスケジューラに 'GATHER_STATS_JOB' として登録されている。
(DBA_TAB_MODIFICATIONS の情報も同時に更新されていると思われる)

-- 自動更新が行われているスケジュールの確認
SELECT *
  FROM DBA_SCHEDULER_JOBS
 WHERE JOB_NAME = 'GATHER_STATS_JOB';
-- 自動更新 プログラムの手動実行
BEGIN
	EXECUTE DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
END;
/

Oracle 10g で廃止されたコマンド

ALTER_DATABASE_TAB_MONITORING ;
ALTER_SCHEMA_TAB_MONITORING ;


日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)

*1 オプティマイザはデータディクショナリ表の統計情報しか使用しない。あくまでバックアップ・リストア用