はじめに
※対象は11gです。11gなんてもう使わないよという方にはあまり価値のない情報です。
大規模なパーティションテーブルで統計情報を取得すべきか考えてみました。
今回対象とするのは、全体で数十、数百億レコードぐらいあり日付でレンジパーティションを使用しているというケースです。
11gのデフォルトの動作に任せると、パーティションとテーブル（グローバルレベル）の両方の統計情報が自動で取得されます。このとき、パーティションは日付で分かれているので件数は大量にはなりませんが、テーブルは件数が多く非常に時間がかかります（はずです）。
これを回避するためには増分統計取得を有効化します。増分統計取得では、（詳細は省略しますが）個々のパーティションの統計情報（＋シノプシス）からテーブル（グローバルレベル）の統計情報を計算することができるため、非常に効率が良い手段になります。
ただし、以下の投稿でも記載しましたが、11gでは課題があり利用することは得策ではないケースが多くあります。
そこで、11gで増分統計取得が有効でないケースでは、統計情報を取得すべきかどうか検討してみます。
※11gというのが今更ですが、調べても断片的な情報しか見つからなかったので考えてみました。12.2の場合は素直に増分統計取得を利用しましょう。
そもそも統計情報は必要なのか
統計情報の情報を利用して実行計画が作成されるので、統計情報は取得し常に最新の状態にしておくのが基本です。
パーティションテーブルの場合も同様で、テーブル、パーティション（サブパーティション）の統計情報は最新の状態を維持した方が良いです。
パーティションの統計はどのように利用されるのか
パーティションの統計は、リテラルかバインドピークで1パーティションに限定された場合の検索を実行する場合に利用されます。当然ですが、パーティションが限定されるなら、テーブルよりも該当のパーティションの統計の方が正確な情報を持っています。例えばNDV（列内で異なる値の数）やレコード数などはパーティションの統計を見たほうが良いのは分かりやすいかと思います。
ここでパーティションの統計がない場合はどうなるかというと、テーブルの統計情報が利用されます。逆に言うとパーティションの統計がなくても、正確ではないが統計は用いられるということになります。この場合、テーブルとパーティションの間で統計に大きな乖離があれば実行計画に影響を与えるかもしれません。そうでなければパーテョションの統計がなくても問題ないということになります。
- パーテョション統計がなくても問題ない（と思われるケース）
- インデックスでしか検索しない場合
- レコードが多い場合（index scanが最も良い場合）
- データの傾向（NDV）がパーテョションごとに変わらない場合
- 複雑なジョインが発生しない場合
上の条件に合致する場合はパーテョション統計がなくても問題ないと思います。
というこか、このケースだと最初からindex scan固定になるようにしておけば良いだけかもしれませんが。
そもそもテーブルの統計が取得できないという問題が残ってしまいますが、DBMS_STATS.SET_TABLE_STATSプロシージャでテーブルの統計を手動で設定してあげることで解決できます。（SET_INDEX_STATSプロシージャとSET_COLUMN_STATSプロシージャも利用した方が良い？）
と、書いてみたものの同じ事を書いているサイトや情報が見つけられないので不安です。
そのため、（前述の意見と異なっており全く論理的ではありませんが）パーティションの統計情報は作成しておきます。1パーティション分の統計情報だけ取得しておき、それを新規のパーティションにコピーすることで大規模でも問題なく統計が取得できます。パーティションのレコード数が変わっていく場合は、コピー元のXX倍の統計としてコピーするということも可能です。
個人的な結論
- パーテョションの統計情報は取得した方が良いよ。
- index scanが最適なら、テーブルの統計だけでよい場合が多そう。
パーテョションの統計を扱わない場合は、テーブル統計を以下のように設定しておく。
- DBMS_STATS.SET_TABLE_STATSプロシージャでテーブルの統計を手動で設定する。
- DBMS_STATS.LOCK_TABLE_STATSプロシージャで、テーブルの統計はロックする。（テーブル統計をロックするとパーテョションの統計もロックされます）
考慮できていないこと（いつか検討するかも）
- ヒストグラムも考慮しないといけない？
- 11gは増分統計の課題があったから前述の対応を考えたが、12.2の場合は本当に増分統計で問題ないか？
設定方法
前述の結論に必要な作業を実際にやってみます。実行する操作は以下の内容になります。
- テーブルの統計情報をロックする
- テーブルの統計情報を手動で作成する
- パーティションの統計情報をコピーする
まず、2016年、2017年、2018年のレンジパーティションを持つINC_TESTテーブルとローカルパーティションインデックスを作成しています。
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')));
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);
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);
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);
commit;
create index idx_inc_test01 on inc_test(id) local;
INC_TESTテーブルの統計情報も取得しておきます。
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TESTUSER',TABNAME=>'INC_TEST');
テーブルの統計をロックします。
EXECUTE DBMS_STATS.LOCK_TABLE_STATS(ownname=>'TESTUSER',tabname=>'INC_TEST');
統計をロックした後、テーブル（user_tab_statistics）、インデックス（user_ind_statistics）の統計を検索してみると次のようになります。stattype_lockedが全て"ALL"になっており、
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, user_stats, stattype_locked, stale_stats from user_tab_statistics s;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ---------- --------- --- --- ----- ---
INC_TEST 3000000 20-OCT-19 YES NO ALL NO
INC_TEST P2016 1000000 20-OCT-19 YES NO ALL NO
INC_TEST P2017 1000000 20-OCT-19 YES NO ALL NO
INC_TEST P2018 1000000 20-OCT-19 YES NO ALL NO
SQL> select index_name, partition_name, num_rows, last_analyzed, global_stats, user_stats, stattype_locked, stale_stats from user_ind_statistics order by
4,5;
INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ---------- --------- --- --- ----- ---
IDX_INC_TEST01 3058865 20-OCT-19 YES NO ALL NO
IDX_INC_TEST01 P2018 1000000 20-OCT-19 YES NO ALL NO
IDX_INC_TEST01 P2017 1000000 20-OCT-19 YES NO ALL NO
IDX_INC_TEST01 P2016 1000000 20-OCT-19 YES NO ALL NO
統計はロックされているので、統計情報を取得しようとすると以下のようにエラーになります。
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TESTUSER',TABNAME=>'INC_TEST');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TESTUSER',TABNAME=>'INC_TEST'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1
次にSET_TABLE_STATSプロシージャを使用してテーブルの統計情報を手動で設定します。
SET_TABLE_STATSプロシージャの使用は以下を参照。
EXEC DBMS_STATS.SET_TABLE_STATS(ownname=>'testuser', tabname=>'INC_TEST', numrows=>'100', numblks=>'10', avgrlen=>'10', force=>TRUE);
また、インデックスとカラムの統計情報を設定するためには、以下のSET_INDEX_STATSプロシージャ、SET_COLUMN_STATSプロシージャを使用します。
変更した統計情報は以下のように、NUM_ROWS=100になります。今回はテーブルの統計情報を最初に取得していたので、でたらめな統計情報を設定しただけの意味がない操作になります。
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ---------- --------- --- --- ----- ---
INC_TEST 100 20-OCT-19 YES YES ALL NO
INC_TEST P2016 1000000 20-OCT-19 YES NO ALL NO
INC_TEST P2017 1000000 20-OCT-19 YES NO ALL NO
INC_TEST P2018 1000000 20-OCT-19 YES NO ALL NO
次にCOPY_TABLE_STATSプロシージャを用いてパーティションP2016からP2018へ統計情報をコピーしてみます。コピー時にscale_factor=>'3'で統計情報を3倍に設定しています。
EXECUTE DBMS_STATS.COPY_TABLE_STATS(ownname=>'TESTUSER',tabname=>'INC_TEST',srcpartname=>'P2016',dstpartname=>'P2018',scale_factor=>'3',flags=>null,force=>TRUE);
user_tab_statisticsでパーティションの統計情報を確認すると以下の結果になります。
P2018のNUM_ROWS(1000000)がコピー元のP2016の3倍(3000000)になっていることが分かります。
SQL> select table_name, partition_name, num_rows, last_analyzed, global_stats, user_stats, stattype_locked, stale_stats from user_tab_statistics s;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ---------- --------- --- --- ----- ---
INC_TEST 100 20-OCT-19 YES YES ALL NO
INC_TEST P2016 1000000 20-OCT-19 YES NO ALL NO
INC_TEST P2017 1000000 20-OCT-19 YES NO ALL NO
INC_TEST P2018 3000000 20-OCT-19 YES NO ALL NO
SQL> select index_name, partition_name, num_rows, last_analyzed, global_stats, user_stats, stattype_locked, stale_stats from user_ind_statistics order by
4,5;
INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANAL GLO USE STATT STA
------------------------------ ------------------------------ ---------- --------- --- --- ----- ---
IDX_INC_TEST01 3024347 20-OCT-19 YES NO ALL YES
IDX_INC_TEST01 P2018 3000000 20-OCT-19 YES NO ALL NO
IDX_INC_TEST01 P2017 1000000 20-OCT-19 YES NO ALL NO
IDX_INC_TEST01 P2016 1000000 20-OCT-19 YES NO ALL NO
パーティションの統計が存在しなくても、動的サンプリングが行われていないことの確認
新しいパーティションを作成し、データを投入します。
ALTER TABLE inc_test ADD PARTITION p2019 VALUES LESS THAN (to_date('2020-01-01', 'yyyy-mm-dd'));
insert into inc_test select rownum, to_date('2019-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 1000), (select level from DUAL connect by level <= 1000);
commit;
次に作成したパーティションのデータを検索するSQLの実行計画を表示しています。
SQL> select * from inc_test where id = 1 and cdate = to_date('2019-01-01', 'yyyy-mm-dd');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1svwkz8v2s99p, child number 0
-------------------------------------
select * from inc_test where id = 1 and cdate = to_date('2019-01-01',
'yyyy-mm-dd')
Plan hash value: 2329286271
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | | | 1 |00:00:00.01 | 5 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PARTITION RANGE SINGLE | | 1 | 1 | 45 | 4 (0)| 00:00:01 | 4 | 4 | 1 |00:00:00.01 | 5 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| INC_TEST | 1 | 1 | 45 | 4 (0)| 00:00:01 | 4 | 4 | 1 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | IDX_INC_TEST01 | 1 | 3 | | 3 (0)| 00:00:01 | 4 | 4 | 1 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / INC_TEST@SEL$1
3 - SEL$1 / INC_TEST@SEL$1
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CDATE"=TIMESTAMP' 2019-01-01 00:00:00')
3 - access("ID"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "CDATE"[TIMESTAMP,11], "INC_TEST"."CONTENTS"[VARCHAR2,30]
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - "ID"[NUMBER,22], "CDATE"[TIMESTAMP,11], "INC_TEST"."CONTENTS"[VARCHAR2,30]
3 - "INC_TEST".ROWID[ROWID,10], "ID"[NUMBER,22]
36 rows selected.
実行計画を見ると、Pstart=Pstop=4となっておりパーティションプルーニングが効いていることが分かります。パーティションの統計はないですが動的サンプリングにはなっていません。
E-Rows（推定行数）=3となっており何らかの統計が使われているようです。統計情報があればE-Rows=1になるはずなので、3パーティション分で見積もられていることになります。3パーティションということは最初に統計を取得したときのテーブル（グローバル）の統計ということになり、パーティションの統計がない場合はテーブルの統計が使用されているということになります。
パーティションが増えるとE-Rows（推定行数）が増えるのも気持ちが悪いので、修正するならSET_INDEX_STATSプロシージャを用います。
以下のようにnumrowsとnumdistを3000000に設定すれば、E-Rows（推定行数）が"1"になるはずです。
EXEC DBMS_STATS.SET_INDEX_STATS(ownname=>'testuser', indname=>'idx_inc_test01', numrows=>3000000, numlblks=>10000, numdist=>3000000, force=>TRUE);