Oracle® 非公式 SQL, PL/SQL & DBA's ガイドサイト

テーブル・カラム・インデックス統計情報の収集

テーブル・カラム・インデックス統計情報とは以下の情報をもつ。

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

ここでは、テーブル統計、カラム統計、インデックス統計の関係を理解する。

統計情報の収集 (DBMS_STATS) に戻る。

統計情報取得用のスキーマ定義

テスト用の スキーマオブジェクト を以下の定義とする。(RIVUSスキーマ上に作成)

CREATE TABLE RIVUS.STAT_SAMPLE
(
   ID_10                          NUMBER(1,0),
   ID_100                         NUMBER(2,0),
   NAME                           VARCHAR2(100),
   CREATED_ON                     DATE DEFAULT SYSDATE
);
--
CREATE INDEX RIVUS.STAT_SAMPLE_IX10
ON RIVUS.STAT_SAMPLE (ID_10);
--
CREATE INDEX RIVUS.STAT_SAMPLE_IX100
ON RIVUS.STAT_SAMPLE (ID_100);

統計情報の削除

統計情報の削除には、DELETE_〜_STATS シリーズを使用する。

テーブル統計情報の削除

テーブル統計に関するパッケージは、DBMS_STATS.DELETE_TABLE_STATS を利用する。

BEGIN
	DBMS_STATS.DELETE_TABLE_STATS('RIVUS','STAT_SAMPLE');
END;
/

統計情報の作成

統計情報の作成は DBMS_STATS.GATHER_〜_STATS シリーズを利用する。
ANALYZE には フリーリストブロック、VALIDATE、行移行、行連鎖 に関する CBO に関係のない情報の取得の機能だけが利用を許されている。（Oracle 10g 時点）

テーブル統計の収集

表単位の統計の収集を行う。
確認用のため、収集範囲の制限を行う。デフォルト設定を初期値から変更していなければ 自動で取得される設定になる。

• 関連する索引統計を収集しない(CASCADE=>FALSE パラメータ)。
• カラム統計は、インデックス項目だけを収集する（METHOD_OPT=>'FOR ALL INDEXED' パラメータ)。

DBMS_STATS.GATHER_TABLE_STATS の例

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(
		OWNNAME       => 'RIVUS'
		,TABNAME      => 'STAT_SAMPLE'
		,METHOD_OPT   => 'FOR ALL INDEXED'
		,CASCADE      => FALSE
	);
END;
/

統計収集の確認(LAST_ANALYZED 日付が格納されていることを確認)。

SELECT TABLE_NAME NAME, LAST_ANALYZED
  FROM USER_TABLES
 WHERE TABLE_NAME = 'STAT_SAMPLE'
UNION
SELECT INDEX_NAME NAME, LAST_ANALYZED
  FROM USER_INDEXES
 WHERE INDEX_NAME IN ('STAT_SAMPLE_IX10','STAT_SAMPLE_IX100');

インデックス統計の収集

テーブル統計の取得 にて、CASCADE => TRUEでテーブル統計を収集していれば不要のはずである内容

BEGIN
	-- INDEX IX10
	DBMS_STATS.GATHER_INDEX_STATS(
		OWNNAME   => 'RIVUS',
		INDNAME   => 'STAT_SAMPLE_IX10'
	);
	-- INDEX IX100
	DBMS_STATS.GATHER_INDEX_STATS(
		OWNNAME   => 'RIVUS',
		INDNAME   => 'STAT_SAMPLE_IX100'
	);
END;
/

インデックス統計収集の確認(LAST_ANALYZED 日付が格納されていることを確認)。

SELECT TABLE_NAME NAME, LAST_ANALYZED
  FROM USER_TABLES
 WHERE TABLE_NAME = 'STAT_SAMPLE'
UNION
SELECT INDEX_NAME NAME, LAST_ANALYZED
  FROM USER_INDEXES
 WHERE INDEX_NAME IN ('STAT_SAMPLE_IX10','STAT_SAMPLE_IX100');

カラム統計の収集

テーブル統計の取得 にて、METHOD_OPT => 'FOR ALL COLUMNS' で統計収集していれば不要のはずである内容
カラム統計情報の収集がインデックス項目だけであることを確認。

SELECT COLUMN_NAME, LAST_ANALYZED
  FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = 'STAT_SAMPLE';

カラム単位に統計情報を取得するには 'DBMS_STATS.GATHER_TABLE_STATS' の 'METHOD_OPT'を使用する。
NAME, CREATED_ON カラムのカラム統計収集を行う場合。

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(
		OWNNAME       => 'RIVUS'
		,TABNAME      => 'STAT_SAMPLE'
		,METHOD_OPT   => 'FOR COLUMNS NAME, CREATED_ON'
		,CASCADE      => FALSE
	);
END;
/

すべての項目が統計情報を取得していることを確認

SELECT COLUMN_NAME, LAST_ANALYZED
　FROM USER_TAB_COLUMNS
 WHERE TABLE_NAME = 'STAT_SAMPLE' ;

カラム統計の収集 (ヒストグラムを収集)

通常、データアドミニストレータ（≠データベースアドミニストレータ）であれば、カラムのヒストグラムを把握しているのでヒアリングしてみる。
例えば、この実験用テーブルの 'ID_10' は、 0〜9(10) までで構成されており、ID_100は同様に 0〜99 が値域と仮定、 各カラムの値の構成比率は、いびつな構造をしており、値 90が 全体の 25 %を超えている。値が 0〜 50 のレコードは、ほとんど存在しない、などである。
ヒストグラムは索引の使用の有無、結合順序などに影響する。

データの偏りがある場合、ヒストグラムの生成は、Oracle にヒントを与えていることになる(特に出現頻度が異常に低いデータがある場合はサンプリング時に発見されないこともあるので、統計の結果を見て estimate_percent 句の使用を検討する)。

注意 ヒストグラムの生成と SQLのハード解析時 のヒストグラムの分析と利用は高いコストを伴うため、高速化の特効薬というものではない。(逆に足かせになりかねない)。

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(
		OWNNAME       => 'RIVUS'
		,TABNAME      => 'STAT_SAMPLE'
		,METHOD_OPT   => 'FOR COLUMNS ID_10 SIZE  AUTO,
		                      COLUMNS ID_100 SIZE SKEWONLY'
		,CASCADE      => FALSE
	);
END;
/

DBMS_STATS まとめ（テーブル、カラム、インデックス）

DBMS_STATS パッケージの基本的なパラメータ群

DBMS_STATS.GATHER_TABLE_STATS(
	OWNNAME           => 'スキーマ'
	,TABNAME          => 'テーブル'
	,PARTNAME         => 'パーティション'
	,ESTIMATE_PERCENT => 'サンプルレート'
	,BLOCK_SAMPLE     => 'サンプルレート(block)'
	,METHOD_OPT       => 'method_opt'
	,DEGREE           => '並列度'
	,GRANULARITY      => 'パーティション表オプション'
	,CASCADE          => 'インデックス統計 有無'
	--
	,以下 省略
);

並列度を２以上にする場合にはエンタープライズ・エディションが必要

• Oracle 10g Technical Data 主要機能一覧 ／ Oracle 9i Technical Data 主要機能一覧 (oracle.co.jp)
  

テーブル統計の収集例

表単位の統計の収集を行う。

DBMS_STATS.GATHER_TABLE_STATS の例

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(
		OWNNAME       => 'RIVUS'
		,TABNAME      => 'STAT_SAMPLE'
	);
END;
/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(
		OWNNAME       => 'RIVUS'
		,TABNAME      => 'STAT_SAMPLE'
		,METHOD_OPT   => 'FOR ALL COLUMNS SIZE AUTO'
		,CASCADE      => TRUE
	);
END;
/

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(
		OWNNAME           => 'RIVUS'
		,TABNAME          => 'STAT_SAMPLE'
		,ESTIMATE_PERCENT => 10
		,METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO'
		,CASCADE          => TRUE
	);
END;
/

関連事項

• スキーマ統計情報
• データベース統計情報
• システム統計情報