パーティション表の統計情報には、パーティション毎のパーティション統計と、表や索引全体のグローバル統計の二種類がある。
表の統計情報収集を行う dbms_stats.gather_table_stats の引数にはパーティション名を指定することができるが、実はデフォルトの状態だとここにパーティション名を指定したとしてもパーティション統計のほかにグローバル統計も取得する動作となっており、フルスキャンが発生して統計情報の取得に時間を要するらしい。
その問題を解消するために、11g以降では増分統計という機能が追加され、パーティション統計からグローバル統計を算出させることが可能となり、都度フルスキャンが発生することを防げる、らしい。本当に？と思ったので実際の動きとしてどうなのか見てみる。
前提条件
以下のようにレンジパーティション表を作り、適当なデータを入れておく。
drop table tab_range purge;
create table tab_range(col1 number, col2 char(100)) partition by range(col1) (
partition p1 values less than (500000),
partition p2 values less than (1000000),
partition p3 values less than (1500000),
partition p4 values less than (2000000),
partition p5 values less than (2500000)
);
create index ind_range on tab_range(col1);
begin
for i in 1..2000000 loop
insert into tab_range values(i, 'A');
end loop;
end;
/
commit;この時点では、当然統計情報はとられていない。
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ________________ _______________ ______________
TAB_RANGE NO
TAB_RANGE P1 NO
TAB_RANGE P2 NO
TAB_RANGE P3 NO
TAB_RANGE P4 NO
TAB_RANGE P5 NO
6行が選択されました。 また、増分統計も無効となっている。
SQL> select DBMS_STATS.GET_PREFS('INCREMENTAL', 'TESTUSER', 'TAB_RANGE') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','TESTUSER','TAB_RANGE')
_____________________________________________________________
FALSE 表全体の統計を取る場合
おおよそ6秒程度の時間を要する。これは何度か表の作成と統計の取得を繰り返しても同じ結果だった。
SQL> set timing on
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:06.924
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ______________________ _______________ ______________
TAB_RANGE 2000000 2025-05-04 09:04:16 YES NO
TAB_RANGE P1 499999 2025-05-04 09:04:13 YES NO
TAB_RANGE P2 500000 2025-05-04 09:04:14 YES NO
TAB_RANGE P3 500000 2025-05-04 09:04:15 YES NO
TAB_RANGE P4 500000 2025-05-04 09:04:16 YES NO
TAB_RANGE P5 1 2025-05-04 09:04:16 YES NO
6行が選択されました。 増分統計無効でパーティションを指定して統計を取る場合
明示的にパーティションを指定していたとしても、初回の実行には表全体の統計を取ったときと同じく約6秒かかる。
実際、表自体のLAST_ANALYZED列も更新されておりテーブル全体の行数も取得されていることから、表全体のフルスキャンは実行されている状況。
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P1');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:06.461
SQL>
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ______________________ _______________ ______________
TAB_RANGE 2000000 2025-05-04 09:07:14 YES NO
TAB_RANGE P1 499999 2025-05-04 09:07:13 YES NO
TAB_RANGE P2 NO
TAB_RANGE P3 NO
TAB_RANGE P4 NO
TAB_RANGE P5 NO
6行が選択されました。 連続して他のパーティションの統計を取ると、多少時間はかからなくなるように見えるが、これはバッファキャッシュにキャッシュされているためと思われる。
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P2');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:02.765
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P3');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:02.667バッファキャッシュや共有プールをフラッシュすると、やはり表全体の統計取得と同様の時間を要することがわかる。
SQL> alter system flush buffer_cache;
System FLUSHが変更されました。
経過時間: 00:00:00.263
SQL> alter system flush shared_pool;
System FLUSHが変更されました。
経過時間: 00:00:01.334
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P4');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:06.685増分統計有効でパーティションを指定して統計を取る場合
増分統計を有効にするには、パーティション表の統計プリファレンスを以下のように設定する。
SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('TESTUSER', 'TAB_RANGE', 'INCREMENTAL', 'TRUE');
PL/SQLプロシージャが正常に完了しました。
SQL> select DBMS_STATS.GET_PREFS('INCREMENTAL', 'TESTUSER', 'TAB_RANGE') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','TESTUSER','TAB_RANGE')
_____________________________________________________________
TRUE この状態で、パーティションを指定して統計を取得する。
いずれにせよ初回の取得時はグローバル統計がとられるようで、表全体の統計を取ったときとかかる時間は変わらないどころか、若干長い印象。
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P1');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:10.543
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ______________________ _______________ ______________
TAB_RANGE 2000000 2025-05-04 09:47:16 YES NO
TAB_RANGE P1 499999 2025-05-04 09:47:08 YES NO
TAB_RANGE P2 500000 2025-05-04 09:47:13 YES NO
TAB_RANGE P3 500000 2025-05-04 09:47:11 YES NO
TAB_RANGE P4 500000 2025-05-04 09:47:11 YES NO
TAB_RANGE P5 1 2025-05-04 09:47:13 YES NO 無効時と違うのは、パーティション指定で取得したにもかかわらず、全てのパーティションの統計がとられているという点。
今度は一度データベースを再起動してから別のパーティションの統計を取ってみたが…微妙。時間の短縮は見られず。というか、むしろ長くなっている。2回目以降は、指定したパーティション統計と表の統計のみ更新される動きになっているが、増分統計によってパフォーマンスが向上するようには見えなかった。
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P2');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:09.008
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ______________________ _______________ ______________
TAB_RANGE 2000000 2025-05-04 09:49:05 YES NO
TAB_RANGE P1 499999 2025-05-04 09:47:08 YES NO
TAB_RANGE P2 500000 2025-05-04 09:49:03 YES NO
TAB_RANGE P3 500000 2025-05-04 09:47:11 YES NO
TAB_RANGE P4 500000 2025-05-04 09:47:11 YES NO
TAB_RANGE P5 1 2025-05-04 09:47:13 YES NO
6行が選択されました。 よくわからないのは、この状態でテーブルのデータを全消去した後にパーティション指定で統計を取ってみると、すべてのパーティションに対して統計がとられる動きになった点。これではあえてパーティション指定した意味がないのだが、統計の失効状態を見て取得するかどうか決めているということなのだろうか。
SQL> delete from tab_range;
2,000,000行削除されました。
経過時間: 00:01:18.446
SQL> commit;
コミットが完了しました。
経過時間: 00:00:00.002
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ______________________ _______________ ______________
TAB_RANGE 2000000 2025-05-04 09:54:07 YES NO
TAB_RANGE P1 499999 2025-05-04 09:47:08 YES YES
TAB_RANGE P2 500000 2025-05-04 09:49:03 YES YES
TAB_RANGE P3 500000 2025-05-04 09:54:05 YES YES
TAB_RANGE P4 500000 2025-05-04 09:47:11 YES YES
TAB_RANGE P5 1 2025-05-04 09:47:13 YES YES
6行が選択されました。
経過時間: 00:00:02.119
SQL> exec dbms_stats.gather_table_stats('TESTUSER','TAB_RANGE', 'P1');
PL/SQLプロシージャが正常に完了しました。
経過時間: 00:00:05.644
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, stale_stats from user_tab_statistics;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLOBAL_STATS STALE_STATS
_____________ _________________ ___________ ______________________ _______________ ______________
TAB_RANGE 0 2025-05-04 09:56:52 YES NO
TAB_RANGE P3 0 2025-05-04 09:56:49 YES NO
TAB_RANGE P1 0 2025-05-04 09:56:47 YES NO
TAB_RANGE P2 0 2025-05-04 09:56:51 YES NO
TAB_RANGE P4 0 2025-05-04 09:56:49 YES NO
TAB_RANGE P5 0 2025-05-04 09:56:51 YES NO
6行が選択されました。
経過時間: 00:00:00.525この辺は増分統計の利用条件になっている GRANULARITY=>’auto’ なども関連しているのだろうか。
今回はデータ量も少ないので処理時間に関しては参考程度。実際どのレベルでのスキャンが発生しているかなどは厳密には検証していない。
ただ、増分統計を有効化していたとしても、単一のパーティションに対してのみ統計を取っているつもりが他のパーティションの情報も更新される動きがある＝必ずしも処理時間の向上にはつながらない可能性があることには気を付けたほうが良いかもしれない。
コメント