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

増分統計収集

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

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

一般的にパーティション表を利用する場合、データは新しいパーティションに追加されます。Oracle Database 11ｇ 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 => 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: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®、Java及びMySQLは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。

【Oracle AI Database 26ai】同時リフレッシュでON COMMITマテリアライズド・ビューのenqueue待ちにサヨナラ！OLTP／DWHスループットを実測検証

本記事では、従来のON COMMIT MViewが抱えてきたこのようなスループット低下や待機イベントのボトルネックを振り返りつつ、Oracle AI Database 26aiが提供する「同時リフレッシュ（Concurrent Refresh）」によって、OLTP／DWHそれぞれのユースケースでどのような改善が見込めるのかを検証していきます。