LoginSignup
3

More than 3 years have passed since last update.

パーティション表で大規模データを格納する場合は増分統計を有効にしよう(特に12.2以上なら)

Last updated at Posted at 2019-05-19

はじめに

パーティション表では、パーティションレベルとテーブル全体の統計(グローバル統計)が取得されます。
あるパーティションの統計情報が再収集対象になった場合、そのパーティションの統計情報がとられるだけではなく、グローバル統計も取得されます。このグローバル統計の取得が曲者で、パーティション表全体のレコード数が大量だと、統計情報の取得に長い時間がかかってしまいます。そこで増分統計の登場です。

増分統計では、パーティションの統計取得時にシノプシスという情報が採取され保存されます。グローバル統計はこのシノプシスを使って算出されるため、グローバル統計の取得が短い時間で完了することができます。
この増分統計は11gから利用することができます。

設定

増分統計収集が行われるための条件は以下のとおりです。

  • パーティション表のINCREMENTALがTRUEになっている
  • パーティション表のPUBLISHがTRUEになっている(デフォルト)
  • DBMS_STATS.GATHER_xxx_STATSプロシージャのESTIMATE_PERCENTがAUTO_SAMPLE_SIZEになっている(デフォルト)
  • DBMS_STATS.GATHER_xxx_STATSプロシージャのGRANULARITYがAUTOになっている

増分統計収集を試す

増分統計収集は11gから利用できますが、12c(12.2)で機能改善されていますので、今回は12c(12.2)環境で試してみたいと思います。

まず、テスト用のテーブルを作成し、データを挿入します。

SQL> create table inc_test (id number, cdate timestamp, contents varchar2(30)) partition by range (cdate) (
        partition p2016
    values less than
        (to_date('2017-01-01', 'yyyy-mm-dd')), partition p2017
    values less than
        (to_date('2018-01-01', 'yyyy-mm-dd')), partition p2018
    values less than
        (to_date('2019-01-01', 'yyyy-mm-dd')));
表が作成されました。

SQL> insert into inc_test select rownum, to_date('2016-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 1000), (select level from DUAL connect by level <= 1000);

1000000行が作成されました。

SQL> insert into inc_test select rownum, to_date('2017-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 1000), (select level from DUAL connect by level <= 1000);

1000000行が作成されました。

SQL> insert into inc_test select rownum, to_date('2018-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 1000), (select level from DUAL connect by level <= 1000);

1000000行が作成されました。

commit;

次に統計情報を取得します。
「ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO'」を指定していますが、これは指定しなくても(今回は)同じです。

SQL> exec dbms_stats.gather_table_stats('HR','INC_TEST',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO');

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

取得した統計情報を確認します。

SET LINESIZE 300;
SET PAGESIZE 100;

COLUMN table_name FORMAT A20;
COLUMN partition_name FORMAT A20;
COLUMN object_type FORMAT A20;
COLUMN last_analyzed FORMAT A20;
select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000000     1000000 2019-05-18 09:33:05  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:33:05  YES       NO
INC_TEST             P2018                PARTITION               1000000     1000000 2019-05-18 09:33:06  YES       NO
INC_TEST                                  TABLE                   3000000     3000000 2019-05-18 09:33:06  YES       NO

各パーティションは100万レコードずつ入っていることが分かります。
次にバッファキャッシュを見てみます。各パーティションがバッファキャッシュに入っていることが分かります。

COLUMN object_name FORMAT        A20;
COLUMN subobject_name FORMAT        A20;
SQL> select o.object_name, o.subobject_name, count(*) blocks
 from DBA_OBJECTS o, V$BH bh
 where o.data_object_id = bh.objd
   and o.owner = 'HR'
   and bh.status != 'free'
 group by o.object_Name, o.subobject_name
 order by count(*);

OBJECT_NAME          SUBOBJECT_NAME           BLOCKS
-------------------- -------------------- ----------
INC_TEST             P2018                      6640
INC_TEST             P2016                      6640
INC_TEST             P2017                      6640

次にP2018パーティションに20万レコード挿入します。

insert into inc_test select rownum, to_date('2018-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 1000), (select level from DUAL connect by level <= 200);
200000行が作成されました。

commit;

統計情報を確認すると、P2018パーティションのSTALE_STAが"YES"となっており、統計が無効になっていることが分かります。

select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000000     1000000 2019-05-18 09:33:05  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:33:05  YES       NO
INC_TEST             P2018                PARTITION               1000000     1000000 2019-05-18 09:33:06  YES       YES
INC_TEST                                  TABLE                   3000000     3000000 2019-05-18 09:33:06  YES       NO

ここで一旦バッファキャッシュをクリアして、統計情報取得でどのパーティションがどの程度読み込まれたかを確認しています。

SQL> alter system flush buffer_cache;

システムが変更されました。

SQL> exec dbms_stats.gather_table_stats('HR','INC_TEST',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO');

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

SQL> select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000000     1000000 2019-05-18 09:34:38  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:34:38  YES       NO
INC_TEST             P2018                PARTITION               1200000     1200000 2019-05-18 09:34:38  YES       NO
INC_TEST                                  TABLE                   3200000     3200000 2019-05-18 09:34:39  YES       NO

SQL> select o.object_name, o.subobject_name, count(*) blocks
 from DBA_OBJECTS o, V$BH bh
 where o.data_object_id = bh.objd
   and o.owner = 'HR'
   and bh.status != 'free'
 group by o.object_Name, o.subobject_name
 order by count(*);

OBJECT_NAME          SUBOBJECT_NAME           BLOCKS
-------------------- -------------------- ----------
INC_TEST             P2017                      6532
INC_TEST             P2016                      6532
INC_TEST             P2018                      7828

統計情報は、P2018パーティションのSTALE_STAが"NO"に変わっていることが分かります。

次に増分統計情報取得を有効にして同じことを実施してみます。
テーブルは現在取得した統計情報ではシノプシスが作成されていないため、一旦削除してから実施しています。
※増分統計情報取得を有効にして統計情報取得すればシノプシスは作成されるのですが、今回はテーブル作成からやり直しています。

増分統計を有効にするためにはINCREMENTALの値を"TRUE"に設定します。
DBMS_STATS.GET_PREFSプロシージャで、現在のINCREMENTALの値を確認すると"FALSE"になっていますので、これを"TRUE"に変更します。
次にINC_TESTテーブルの統計情報を取得します。ここでシノプシスが作成されます。

SQL> select DBMS_STATS.GET_PREFS('INCREMENTAL', 'HR', 'INC_TEST') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','HR','INC_TEST')
--------------------------------------------------------------------------------
FALSE


SQL> exec dbms_stats.set_table_prefs('HR','INC_TEST','INCREMENTAL','TRUE');

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

SQL> exec dbms_stats.gather_table_stats('HR','INC_TEST',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO');

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

SQL> select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;
TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000000     1000000 2019-05-18 09:37:58  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       NO
INC_TEST             P2018                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       NO
INC_TEST                                  TABLE                   3000000     3000000 2019-05-18 09:37:59  YES       NO

P2018パーティションに20万レコードを挿入すると、該当パーティションの統計情報が無効になります。

SQL> insert into inc_test select rownum, to_date('2018-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 1000), (select level from DUAL connect by level <= 200);

200000行が作成されました。

SQL> commit;

SQL> select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000000     1000000 2019-05-18 09:37:58  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       NO
INC_TEST             P2018                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       YES
INC_TEST                                  TABLE                   3000000     3000000 2019-05-18 09:37:59  YES       NO

後で増分統計収集でバッファキャッシュにどの程度読み込まれたかを確認するため、一旦バッファキャッシュをクリアします。

SQL> alter system flush buffer_cache;

システムが変更されました。

ここで統計収集を実行。

exec dbms_stats.gather_table_stats('HR','INC_TEST',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO');

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

結果は以下のとおりで、変更したパーティションとテーブルのLAST_ANALYZEDだけが更新されています。
バッファキャッシュは統計情報が無効化になっていたP2018パーティションだけが読み込まれており、他は1ブロックだけです。
これはグローバル統計が、更新がかかっていないパーティションからはシノプシスから算出されているということだと考えられます。

SQL> select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000000     1000000 2019-05-18 09:37:58  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       NO
INC_TEST             P2018                PARTITION               1200000     1200000 2019-05-18 09:38:56  YES       NO
INC_TEST                                  TABLE                   3200000     3200000 2019-05-18 09:38:56  YES       NO

SQL> select o.object_name, o.subobject_name, count(*) blocks
 from DBA_OBJECTS o, V$BH bh
 where o.data_object_id = bh.objd
   and o.owner = 'HR'
   and bh.status != 'free'
 group by o.object_Name, o.subobject_name
 order by count(*);

OBJECT_NAME          SUBOBJECT_NAME           BLOCKS
-------------------- -------------------- ----------
INC_TEST             P2017                         1
INC_TEST             P2016                         1
INC_TEST             P2018                      7828

増分統計収集って素晴らしい!!
ただし、実はこのままだと問題があります。

P2016パーティションに1レコードだけ追加して、同様のことを実行してみます。
1レコードしか変更されていないので、統計情報を収集してもP2016パーティションは更新されないで欲しいですね。

SQL> insert into inc_test values(1, to_date('2016-01-01', 'yyyy-mm-dd'), 'TEST DATA');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> alter system flush buffer_cache;

システムが変更されました。

SQL> exec dbms_stats.gather_table_stats('HR','INC_TEST',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO');

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

SQL> select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000001     1000001 2019-05-18 09:41:35  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       NO
INC_TEST             P2018                PARTITION               1200000     1200000 2019-05-18 09:38:56  YES       NO
INC_TEST                                  TABLE                   3200001     3200001 2019-05-18 09:41:35  YES       NO

SQL> select o.object_name, o.subobject_name, count(*) blocks
 from DBA_OBJECTS o, V$BH bh
 where o.data_object_id = bh.objd
   and o.owner = 'HR'
   and bh.status != 'free'
 group by o.object_Name, o.subobject_name
 order by count(*);

OBJECT_NAME          SUBOBJECT_NAME           BLOCKS
-------------------- -------------------- ----------
INC_TEST             P2018                         1
INC_TEST             P2017                         1
INC_TEST             P2016                      6532

実際に試してみるとP2016パーティション統計情報が収集されてしまいます。
どうも11gや12cのデフォルト設定ではこのような動きをしてしまうようです。

ただし、12.2(12.1は知りません)では以下に記載のとおり、'INCREMENTAL_STALENESS'に'USE_STALE_PERCENT'を指定することで収集されるかを制御することができます。
'USE_STALE_PERCENT'を指定するとデフォルトで10%以上変更されたパーティションのみが収集対象になります。

INCREMENTAL_STALENESS
パーティションまたはサブパーティションが失効したとみなされる状況を指定します。このパラメータは、'USE_STALE_PERCENT'、'USE_LOCKED_STATS'などの列挙値を取ります。'USE_STALE_PERCENT, USE_LOCKED_STATS, ALLOW_MIXED_FORMAT'などのように複数の値を指定することもできます。

パラメータは次の値を受け入れます。

USE_STALE_PERCENT - DMLの変更がSTALE_PERCENTプリファレンスで設定されたしきい値を下回る場合、パーティションまたはサブパーティションが失効しているとはみなされません。
たとえば、STALE_PERCENTが10である場合を想定します。INCREMENTAL_STALENESSにUSE_STALE_PERCEMENTを指定します。パーティションで5%のDMLの変更が生じています。データベースで統計情報が再収集されることはありません。

それでは'INCREMENTAL_STALENESS'を'USE_STALE_PERCENT'に変更して試してみます。

exec dbms_stats.set_table_prefs('HR','INC_TEST','INCREMENTAL_STALENESS','USE_STALE_PERCENT');

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

SQL> insert into inc_test values(1, to_date('2016-01-01', 'yyyy-mm-dd'), 'TEST DATA');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> alter system flush buffer_cache;

システムが変更されました。

SQL> exec dbms_stats.gather_table_stats('HR','INC_TEST',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'AUTO');

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

SQL> select table_name, partition_name, object_type, num_rows, sample_size, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, global_stats, stale_stats from user_tab_statistics where table_name = 'INC_TEST' order by partition_name;

TABLE_NAME           PARTITION_NAME       OBJECT_TYPE            NUM_ROWS SAMPLE_SIZE LAST_ANALYZED        GLOBAL_ST STALE_STA
-------------------- -------------------- -------------------- ---------- ----------- -------------------- --------- ---------
INC_TEST             P2016                PARTITION               1000003     1000003 2019-05-18 09:48:13  YES       NO
INC_TEST             P2017                PARTITION               1000000     1000000 2019-05-18 09:37:59  YES       NO
INC_TEST             P2018                PARTITION               1200000     1200000 2019-05-18 09:38:56  YES       NO
INC_TEST                                  TABLE                   3200003     3200003 2019-05-18 09:55:13  YES       NO


SQL> select o.object_name, o.subobject_name, count(*) blocks
 from DBA_OBJECTS o, V$BH bh
 where o.data_object_id = bh.objd
   and o.owner = 'HR'
   and bh.status != 'free'
 group by o.object_Name, o.subobject_name
 order by count(*);

OBJECT_NAME          SUBOBJECT_NAME           BLOCKS
-------------------- -------------------- ----------
INC_TEST             P2018                         1
INC_TEST             P2016                         1
INC_TEST             P2017                         1

最後のバッファキャッシュの結果だけ見ると分かるように、グローバル統計が取得されても、各パーティションは1ブロックしかバッファキャッシュに載っていません(なぜ0ではなく1ブロックとなるのかは分かりません・・・)。

まとめ

  • 大規模なパーティション表では増分統計収集を利用した方が良い(ケースが多いと思う)
  • 11gでは増分統計収集だと1レコードでも変更されたパーティションが統計収集の対象となってしまう。そのため、日付のレンジパーティションで常に最新のパーティションが使われる場合は問題ないが、多くのパーティションが更新されるケースでは統計情報取得に時間がかかる。 (動的サンプリングを使う手もあるが、いまいちか?)
  • 12cでは統計情報の取得し対象となるパーティションについて設定(10%以下の変更など)できるようになっており、改善されている。

その他、以下を見ると12.1では増分統計収集に問題がありOFFに設定した事例もあり、12.1で利用する場合は注意が必要なようです。

参考

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3