はじめに
パーティション表では、パーティションレベルとテーブル全体の統計（グローバル統計）が取得されます。
あるパーティションの統計情報が再収集対象になった場合、そのパーティションの統計情報がとられるだけではなく、グローバル統計も取得されます。このグローバル統計の取得が曲者で、パーティション表全体のレコード数が大量だと、統計情報の取得に長い時間がかかってしまいます。そこで増分統計の登場です。
増分統計では、パーティションの統計取得時にシノプシスという情報が採取され保存されます。グローバル統計はこのシノプシスを使って算出されるため、グローバル統計の取得が短い時間で完了することができます。
この増分統計は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で利用する場合は注意が必要なようです。