Database OracleDB インフラ

OracleDBの統計情報の取得・更新方法「DBMS_STATS.GATHER_XXXX_STATS」を紹介

2018年8月8日

こんにちは!シミダイ(@shimidai2100)です。

OracleDatabaseの「統計情報」の手動取得方法を紹介します。

OracleDatabaseを運用して上で最も重要な要素の1つです。

この記事でOracleDatabase初心者でも分かりやすく統計情報の取得方法を紹介していますので、「統計情報」をしっかり取得しましょう。

統計情報となにか?については以下のページでまとめてますので、まずは下の参照ください。

OracleDBの統計情報とは?なぜ取得?取得しないことの影響は?

こんにちは!シミダイ(@shimidai2100)です。 OracleDatabaseの「統計情報」とは何か?なぜ取得す ...

続きを見る


表統計の取得:DBMS_STATS.GATHER_TABLE_STATS

表統計の取得は「DBMS_STATS.GATHER_TABLE_STATS」で行えます。

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS (
	 	 OWNNAME          => '<スキーマ名>'
		,TABNAME          => '<表名>'
		,PARTNAME         => '<パーティション名>'
		,ESTIMATE_PERCENT => <サンプルレートを指定>
		,METHOD_OPT       => '<オプション>'
		,DEGREE           => <並列度>
		,GRANULARITY      => 'パーティション表のオプション'
		,CASCADE          => <索引統計取得有無>
	);
END;
/

7行目の「METHOD_OPT」は、列統計を取得するか設定します。

基本はデフォルト値の「FOR ALL COLUMNS SIZE AUTO」を使用すれば問題ないです。

索引が張られている列に対してのみ列統計を取得する場合は「FOR ALL INDEXED」を設定します。

ちなみに「METHOD_OPT」のデフォルト値を変更する場合、

  • SET_DATABASE_PREFSプロシージャ
  • SET_GLOBAL_PREFSプロシージャ
  • SET_SCHEMA_PREFSプロシージャ
  • SET_TABLE_PREFSプロシージャ

を使用します。

10行目の「CASCADE」は表統計の取得時に合わせて、索引統計も取得するか「True/False」制御します。

以下サンプルになります。

   > BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS (
  3             OWNNAME          => 'TEST001'
  4            ,TABNAME          => 'TAB001'
  5            ,METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO'
  6            ,DEGREE           => 2
  7            ,CASCADE          => TRUE
  8    );
  9  END;
 10  /

PL/SQLプロシージャが正常に完了しました。

  > col OWNER for a10
  > col TABLE_NAME for a20
  > col LAST_ANALYZED for a30
  > select OWNER
  2        ,TABLE_NAME
  3        ,LAST_ANALYZED
  4    from DBA_TABLES
  5   where OWNER = 'TEST001';

OWNER      TABLE_NAME           LAST_ANALYZED
---------- -------------------- ------------------------------
TEST001    TAB001               2018-07-30 12:21:21

列統計の取得:DBMS_STATS.GATHER_TABLE_STATS

列統計の取得は「DBMS_STATS.GATHER_TABLE_STATS」で行います。

表統計取得のプロシージャと同一なので注意してください。

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS (
	 	 OWNNAME          => '<スキーマ名>'
		,TABNAME          => '<表名>'
		,PARTNAME         => '<パーティション名>'
		,ESTIMATE_PERCENT => <サンプルレートを指定>
		,METHOD_OPT       => 'FOR COLUMNS <列名> SIZE {integer | REPEAT | AUTO | SKEWONLY}'
		,DEGREE           => <並列度>
		,GRANULARITY      => 'パーティション表のオプション'
		,CASCADE          => <索引統計取得有無>
	);
END;
/

7行目の「METHOD_OPT」で、「FOR COLUMNS」を指定して列統計を取得します。

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_TABLE_STATS (
 3             OWNNAME          => 'TEST001'
 4            ,TABNAME          => 'TAB001'
 5            ,METHOD_OPT       => 'FOR COLUMNS COL01 SIZE AUTO'
 6    );
 7  END;
 8  /

PL/SQLプロシージャが正常に完了しました。
  
   > col TABLE_NAME for a20
   > col COLUMN_NAME for a20
   > col LAST_ANALYZED for a20
   > select TABLE_NAME
  2        ,COLUMN_NAME
  3        ,LAST_ANALYZED
  4   from DBA_TAB_COLUMNS
  5  where TABLE_NAME = 'TAB001';

TABLE_NAME           COLUMN_NAME          LAST_ANALYZED
-------------------- -------------------- --------------------
TAB001               COL01                2018-07-30 13:00:29

索引統計の取得:DBMS_STATS.GATHER_INDEX_STATS

索引統計の取得は「DBMS_STATS.GATHER_TABLE_STATS」で行います。

BEGIN
	DBMS_STATS.GATHER_INDEX_STATS (
	 	 OWNNAME          => '<スキーマ名>'
		,INDNAME          => '<索引名>'
		,PARTNAME         => '<パーティション名>'
		,ESTIMATE_PERCENT => <サンプルレートを指定>
		,DEGREE           => <並列度>
		,GRANULARITY      => 'パーティション表のオプション'
	);
END;
/

8行目の「GRANULARITY」でパーティションの統計をどのように取得するか制御します。

[table id=62 column_widths="30%|70%"/]

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_INDEX_STATS (
 3             OWNNAME          => 'TEST001'
 4            ,INDNAME          => 'TAB001_IDX'
 5            ,DEGREE           => 2
 6            ,GRANULARITY      => 'AUTO'
 7    );
 8  END;
 9  /

  > col TABLE_NAME for a20
  > col INDEX_NAME for a20
  > col LAST_ANALYZED for a20
  > select TABLE_NAME
 2        ,INDEX_NAME
 3      ,LAST_ANALYZED
 4  from DBA_INDEXES
 5  where INDEX_NAME = 'TAB001_IDX';

TABLE_NAME           INDEX_NAME           LAST_ANALYZED
-------------------- -------------------- --------------------
TAB001               TAB001_IDX           2018-07-30 13:17:53

スキーマ全体の統計情報取得:DBMS_STATS.GATHER_SCHEMA_STATS

スキーマ全体の統計情報取得は「DBMS_STATS.GATHER_SCHEMA_STATS 」で行えます。

BEGIN
	DBMS_STATS.GATHER_SCHEMA_STATS (
	 	 OWNNAME          => '<スキーマ名>'
		,OPTIONS          => '<オプション>'
		,ESTIMATE_PERCENT => <サンプルレートを指定>
		,DEGREE           => <並列度>
		,GRANULARITY      => 'パーティション表のオプション'
		,CASCADE          => <索引統計取得有無>
	);
END;
/

1行目の「OPTIONS」で統計情報取得時のオプションを指定します。

[table id=63 column_widths="30%|70%"/]

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_SCHEMA_STATS (
 3             OWNNAME          => 'TEST001'
 4            ,OPTIONS          => 'GATHER'
 5            ,DEGREE           => 2
 6    );
 7  END;
 8  /

PL/SQLプロシージャが正常に完了しました。

データベース統計の取得方法:DBMS_STATS.GATHER_DATABASE_STATS

データベース全体の統計情報取得は「DBMS_STATS.GATHER_DATABASE_STATS 」で行えます。

BEGIN
	DBMS_STATS.GATHER_DATABASE_STATS (
		 OPTIONS          => '<オプション>'
		,ESTIMATE_PERCENT => <サンプルレートを指定>
		,DEGREE           => <並列度>
		,GRANULARITY      => 'パーティション表のオプション'
		,CASCADE          => <索引統計取得有無>
	);
END;
/

1行目の「OPTIONS」で統計情報取得時のオプションを指定します。

[table id=63 column_widths="30%|70%"/]

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_DATABASE_STATS (
 3             OPTIONS          => 'GATHER AUTO'
 4            ,DEGREE           => 2
 5    );
 6  END;
 7  /

PL/SQLプロシージャが正常に完了しました。

システム統計の取得:DBMS_STATS.GATHER_SYSTEM_STATS

システム統計情報取得は「DBMS_STATS.GATHER_SYSTEM_STATS 」で行えます。

BEGIN
	DBMS_STATS.GATHER_SYSTEM_STATS ;
END;
/

システム統計の取得タイミングは「DB構築後」と「表領域作成後」になります。

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_SYSTEM_STATS ;
 3  END;
 4  /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.90
  > set line 1000
  > set pages 1000
  > col SNAME for a30
  > col PNAME for a30
  > col PVAL2 for a30
  > select *
 2    from AUX_STATS$
 3   where SNAME in ( 'SYSSTATS_INFO', 'SYSSTATS_MAIN' )
 4   order by SNAME, DECODE(PNAME,'STATUS', '#');

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_INFO                  DSTOP                                     07-30-2018 14:15
SYSSTATS_INFO                  DSTART                                    07-30-2018 14:15
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  SLAVETHR
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  CPUSPEEDNW                           1404
SYSSTATS_MAIN                  IOTFRSPEED                           4096

ディクショナリ統計の取得方法:DBMS_STATS.GATHER_DICTIONARY_STATS

ディクショナリ統計の取得は「DBMS_STATS.GATHER_DICTIONARY_STATS」で行えます。

BEGIN
	DBMS_STATS.GATHER_DICTIONARY_STATS (
		 ESTIMATE_PERCENT => <サンプルレートを指定>
		,METHOD_OPT       => '<列統計のオプション>'
		,DEGREE           => <並列度>
		,GRANULARITY      => 'パーティション表のオプション'
		,CASCADE          => <インデックス統計取得有無>
		,OPTIONS          => '<取得オプション>'
	);
END;
/

オプションは表統計・データベース統計取得と同一のものを使用します。

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_DICTIONARY_STATS (
 3             METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO'
 4            ,DEGREE           => 2
 5            ,CASCADE          => TRUE
 6            ,OPTIONS          => 'GATHER AUTO'
 7    );
 8  END;
 9  /

PL/SQLプロシージャが正常に完了しました。

固定表統計の取得方法:DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

固定表の統計の取得は「DBMS_STATS.GATHER_FIXED_OBJECTS_STATS」で行えます。

BEGIN
	DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
END;
/

固定表の統計情報取得は「パッチ適用後」や「オブジェクトの大量作成後」に行いましょう。

以下サンプルになります。

  > BEGIN
 2    DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
 3  END;
 4  /

PL/SQLプロシージャが正常に完了しました。

参考マニュアル

「DBMS_STATS」は指定できるオプション数が多いので本記事は重要な部分のみ記載しました。

必要に応じてマニュアルを確認して最適な設定を行いましょう。

参考12cのDBMS_STATSマニュアル

 

関連記事

-Database, OracleDB, インフラ

Copyright© shimidai2100 , 2020 All Rights Reserved.