Database Support Blog

Database Support Blog>単一レベル・パーティション表で増分統計収集を行う方法

  • Oracle Database
2015.11.02

単一レベル・パーティション表で増分統計収集を行う方法

増分統計収集

今回は、単一レベル・パーティションで増分統計収集を行う方法について解説します。

SQLを実行する際、コストベース・オプティマイザ(以下CBO)が統計情報を元に実行計画を生成しますが、CBOが最適な実行計画を選択するためには最新の統計情報を維持する必要があります。しかし、パーティション化を行うような大規模な表では、表全体をスキャンして統計情報を収集するのは負荷の高い処理になります。

一般的にパーティション表を利用する場合、データは新しいパーティションに追加されます。Oracle Database 11g Release 1からは、データが変更されたパーティションのみ統計情報を収集する「増分統計収集」の機能が追加されており、統計情報を収集するパーティションを限定することで時間短縮とリソース消費を抑えることが可能です。

増分統計収集を行うための条件

増分統計収集を行うためには次の条件を満たす必要があります。

  • パーティション表のINCREMENTAL値がTRUEである(デフォルトはFALSE)
    全表スキャンを実行することなく、パーティション表のグローバルな統計情報をメンテナンスできるか否かを指定
  • パーティション表のPUBLISH値がTRUEである(デフォルトはTRUE)
    収集された統計情報を即座にディクショナリに反映させるか否かを指定
  • DBMS_STATSパッケージを使用して統計情報を取得する際に、ESTIMATE_PERCENTにAUTO_SAMPLE_SIZEを指定
  • DBMS_STATSパッケージを使用して統計情報を取得する際に、GRANULARITYにGLOBALが含まれる値を指定

※パーティションの機能を利用するにはEnterprise Edition、かつPartitioning の追加ライセンスが必要です。

パーティション表のINCREMENTAL値とPUBLISH値の確認/変更方法

増分統計収集を行うための前提条件として、パーティション表のINCREMENTAL値とPUBLISH値がTRUEである必要があります。確認にはDBMS_STATS.GET_TABLE_PREFSプロシージャを使用します。

SQL> CREATE TABLE taono.part_tab
  2  ( id NUMBER(6)
  3   , date_p DATE
  4  )
  5  PARTITION BY RANGE (date_p)
  6  ( PARTITION date_p_2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
  7  , PARTITION date_p_2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD'))
  8  , PARTITION date_p_2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD'))
  9  );
 
SQL> SELECT DBMS_STATS.GET_PREFS('INCREMENTAL', 'TAONO', 'PART_TAB') FROM dual;
 
DBMS_STATS.GET_PREFS('INCREMENTAL','TAONO','PART_TAB')
------------------------------------------------------
FALSE
 
SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH', 'TAONO', 'PART_TAB') FROM dual;
 
DBMS_STATS.GET_PREFS('PUBLISH','TAONO','PART_TAB')
------------------------------------------------------
TRUE 


PUBLISH値のデフォルトはTRUEですが、INCREMENTAL値のデフォルトはFALSEです。いずれもDBMS_STATS.SET_TABLE_PREFSプロシージャで値を変更できます。

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('TAONO','PART_TAB','INCREMENTAL','TRUE');
SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('TAONO','PART_TAB','PUBLISH','TRUE'); 


増分統計収集の実行

増分統計収集のテストを行う前に、表全体の統計情報を収集しておきます。表全体で取得しているため、LAST_ANALYZED列の値(最後に統計情報を収集した日付)は全パーティションで同じです。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'TAONO', -
> tabname => 'PART_TAB', -
> estimate_percent => 100, -
> granularity => 'ALL', -
> no_invalidate => false);
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> SELECT
  2  table_name,partition_name,subpartition_name,
  3  object_type,global_stats,last_analyzed
  4  FROM user_tab_statistics WHERE table_name = 'PART_TAB';
 
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME OBJECT_TYP GLOBAL_ST LAST_ANALYZED
---------- -------------- ----------------- ---------- --------- -------------------
PART_TAB                                    TABLE      YES       2015-06-17 21:34:54
PART_TAB   DATE_P_2013                      PARTITION  YES       2015-06-17 21:34:54
PART_TAB   DATE_P_2014                      PARTITION  YES       2015-06-17 21:34:54
PART_TAB   DATE_P_2015                      PARTITION  YES       2015-06-17 21:34:54
 


DATE_P_2015のパーティションにのみデータを投入します。

SQL> SELECT count(*) FROM part_tab PARTITION(DATE_P_2015);
 
COUNT(*)
----------
1
 
SQL> INSERT INTO part_tab VALUES(1,to_date('2015-01-01','YYYY-MM-DD'));
SQL> INSERT INTO part_tab VALUES(2,to_date('2015-01-02','YYYY-MM-DD'));
SQL> INSERT INTO part_tab VALUES(3,to_date('2015-01-03','YYYY-MM-DD'));
SQL> INSERT INTO part_tab VALUES(4,to_date('2015-01-04','YYYY-MM-DD'));
SQL> COMMIT;
 
コミットが完了しました。
 
SQL> SELECT count(*) FROM part_tab PARTITION(DATE_P_2015);
 
COUNT(*)
----------
5
 


ESTIMATE_PERCENTにAUTO_SAMPLE_SIZE、GRANULARITYにAUTOを指定し、増分統計収集を実行します。 データを投入したパーティションと表のLAST_ANALYZED列が更新され、増分で統計が収集されたことがわかります。今回はAUTOで実行しましたが、 GRANULARITYはGLOBALが含まれていれば増分統計収集が実行されます(AUTO/ALL/GLOBAL/GLOBAL AND PARTITION)。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'TAONO', -
> tabname => 'PART_TAB', -
> estimate_percent => dbms_stats.auto_sample_size, -
> granularity => 'AUTO', -
> no_invalidate => false);
 
PL/SQLプロシージャが正常に完了しました。
 
SQL> SELECT
  2  table_name,partition_name,subpartition_name,
  3  object_type,global_stats,last_analyzed
  4  FROM user_tab_statistics WHERE table_name = 'PART_TAB';
 
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME OBJECT_TYP GLOBAL_ST LAST_ANALYZED
---------- -------------- ----------------- ---------- --------- -------------------
PART_TAB                                    TABLE      YES       2015-06-17 21:48:22
PART_TAB   DATE_P_2013                      PARTITION  YES       2015-06-17 21:34:54
PART_TAB   DATE_P_2014                      PARTITION  YES       2015-06-17 21:34:54
PART_TAB   DATE_P_2015                      PARTITION  YES       2015-06-17 21:48:22
 


増分統計収集では表全体のスキャンを実行していませんが、PART_TAB表のGLOBAL_STATSがYES(グローバル統計が正確)となっています。これは、増分統計収集の前提である、表のINCREMENTAL値をTRUEとすることで、各パーティションのシノプシス(列の統計メタデータ)がSYSAUX表領域に保存されるためです。

表全体のスキャンを行わなくても、各パーティションのシノプシスを集約することで最小限の負荷で正確なグローバル統計を生成することが可能です。そのため、パーティションが新しく追加された場合でも、追加したパーティションの統計を収集するだけでグローバル統計は最新の状態になります。

この動作の詳細はSQLチューニング・ガイドで解説されていますので、興味の有る方はご参照ください。

筆者情報

大野 高志

サービス事業部 サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポート業務の傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されている調査のノウハウを社内外に伝える活動を行っている。

関連している記事

  • Oracle Database
2016.09.21

再現ケースを簡単に作成!SQLテスト・ケース・ビルダーの使用方法

再現ケースに必要な情報を一括で容易に取得できる「SQLテスト・ケース・ビルダー」の使用方法を紹介します。

  • Oracle Database
2016.08.31

DataGuard構築後に使うSQLコマンドまとめ(逆引き)

DataGuard構築後に使うSQLコマンドを逆引きでまとめました。DataGuardを構築した方、これから構築する方は押さえておきましょう。トラブル発生時に焦らないように。

  • Oracle Database
2016.08.12

仮想列関連エラーORA-54032/ORA-54033の原因と対処方法

仮想列に関するエラー(ORA-54032、ORA-54033等)が発生した際の対処方法をご紹介します。Oracle Database 12cでは仮想列が自動で作成されるため、このようなエラーにつながるケースがあります。

カテゴリ一覧

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る