前回のおさらい
前々回は「オプティマイザ統計の保留」の基本的な動作を紹介しました。
前回は保留中の統計情報がどのように格納されるのかを探るために、必要な情報を整理しました。
簡単にまとめると
- 保留中の統計上はSYSAUX表領域にあるSYS.WRI$_OPTSTAT_TAB_HISTORY表に格納される。（プライベート・エリア）
- 実は統計情報の履歴もこのSYS.WRI$_OPTSTAT_TAB_HISTORY表に格納される。
- オプティマイザで使用されるテーブルの統計情報はSYTEM表領域のSYS.TAB$表に格納される。（ディクショナリ）
今回はこれらの領域に格納されている情報を確認するためのSQL文showstat.sqlを使用して、統計情報がどのように管理されているのかを可視化していきます。
1. 検証用テーブルの削除
まず最初に今までの検証で使用したテーブルを削除（DROP）し確認用SQLを実行してみます。
SQL> drop table test purge; Table dropped. SQL> @showstat no rows selected no rows selected
当然のことながら、テーブル自体がありませんので結果が返ってきません。
2. テーブル、インデックスの再作成と初期データインサート後のディクショナリの状況
検証用テーブルを作成し、テストデータを1,000件インサートし、インデックスを作成します。
その状態でディクショナリの状況を確認します。
SQL> create table test(col1 number,col2 varchar2(20));
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into test values (i,'TEST DATA');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test;
COUNT(*)
----------
1000
SQL> create index test_idx on test (col1);
Index created.
SQL> @showstat
no rows selected
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST
TEST表はディクショナリに存在していますが、統計情報はまだ取得されていないのでLAST_ANALYZED列およびその他の列はNullとなっています（31行目）。
3. 統計情報の取得（１回目）
レコード件数1000件の状態で統計情報を取得します。この段階でのテーブル統計情報公開属性はデフォルトの「TRUE=公開」です。
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
統計情報が取得され、即時(12:30:29)に公開されていることがわかります（16行目）。
SYS.WRI$_OPTSTAT_TAB_HISTORY表（プライベート・エリア）にはSAVE_TIME列のみに公開された日時が記録されています（12行目）。
4. テーブル統計情報公開属性を「FALSE=保留」に変更
SQL> begin
2 DBMS_STATS.SET_TABLE_PREFS('TEST','TEST','PUBLISH','FALSE');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> col prefs for a10
SQL> select DBMS_STATS.GET_PREFS('PUBLISH','TEST','TEST') prefs from dual;
PREFS
----------
FALSE
SSQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
属性を変更するだけでは、プライベート・エリアおよびディクショナリに変化はありません。
5. テーブルのTRUNCATE
SQL> truncate table test; Table truncated. SQL> @showstat STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME ------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- ------------------- PUBLISH TEST TEST 2015-02-xx 12:30:29 STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED ------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
テーブルをTRUNCATEしても、プライベート・エリアおよびディクショナリの状態は変わりません。
これが統計情報公開属性によるものなのか、そもそものデフォルトの動作なのか、ここでは結論を出さないでおきます。
6. データ50,000件インサート
SQL> begin
2 for i in 1..50000 loop
3 insert into test values (i,'TEST DATA');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test;
COUNT(*)
----------
50000
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
テーブルにレコードを50,000件インサートして確かに件数は確認できていますが、統計情報を取得していないのであくまでもこのテーブルの持つ統計情報は1,000件です。
従って、バッチ処理で大量のデータが作成されたとしても統計情報を取得しなければ、処理前の小量データ状態での統計情報がオプティマイザによって使用されます。これは非常に重要なポイントです。
7. 統計情報の取得（２回目）
レコード件数50,000件の状態で統計情報を取得します。そしてテーブル統計情報公開属性は「FALSE=保留」です。
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PENDING TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 3000-12-01 01:00:00
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
ここがこの検証における最大のポイントです。
ハイライト表示させた13行目に注目すると、SAVE_TIME列が「3000-12-01 01:00:00」となった新しい行が作成されました。
この日時は現在日付よりも確実に未来日付です。Oracle11g以降はこの状態を「保留状態」の統計情報として管理しているようです。
そして、ディクショナリの方は全く変化がありません。つまり、実行計画に変化を及ぼすことはありません。
8. 現セッションのみ保留統計情報を使用できるようにする
SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE; Session altered. SQL> @showstat STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME ------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- ------------------- PUBLISH TEST TEST 2015-02-xx 12:30:29 PENDING TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 3000-12-01 01:00:00 STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED ------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをセッション単位で変更しましたが、ディクショナリには変化はありません。
つまり、このパラメータをTRUEにしてもオプティマイザは一時的に保留統計情報を使うだけです。
9. OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをデフォルトに戻す
保留統計情報を公開する前に、OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをデフォルトに戻しておきます。
SQL> conn test/test Connected. SQL> show parameter OPTIMIZER_USE_PENDING_STATISTICS NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_pending_statistics boolean FALSE SQL> @showstat STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME ------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- ------------------- PUBLISH TEST TEST 2015-02-xx 12:30:29 PENDING TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 3000-12-01 01:00:00 STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED ------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- CURRENT TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29
念のためプライベート・エリア、ディクショナリ共に変化がないことを確認しておきます。
10. 保留統計情報を公開する
ここが次のポイントです。
保留統計情報を手動で公開し、状況を確認します。
SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('TEST','TEST');
PL/SQL procedure successfully completed.
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39
いろいろなところが同時に更新されているので整理します。
- ディクショナリには50,000件の統計情報が公開されました（14行目）。統計情報が取得された時刻「12:33:39」（LAST_ANALYZED列）はDBMS_STATS.GATHER_TABLE_STATSが実行された時刻です。
- プライベート・エリアの最終行（10行目）はSAVE_TIME列が「12:38:27」に更新されており、これはDBMS_STATS.PUBLISHが実行された時刻です。そして過去時刻となったことで保留統計情報ではなく公開統計情報の履歴となっていることがわかります。
- さらに、この行は1,000行データの統計情報となっていることがわかります。（LAST_ANALYZED=12:30:27）
- そして、統計情報を取得する前の状態も保存されています。（9行目、SAVE_TIME=12:30:29）
11. レコードを削除し50,000→1,000件とした後に統計情報を取得する（３回目）。
50,000件の状態から49,000件を削除します。その後統計情報を取得します。
SQL> delete from test where col1 > 1000;
49000 rows deleted.
SQL> select count(*) from test;
COUNT(*)
----------
1000
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
PENDING TEST TEST 1000 244 14 1000 2015-02-xx 12:41:37 3000-12-01 01:00:00
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39
DELETEしただけでは統計情報に変化はありません。DBMS_STATS.GATHER_TABLE_STATSを実行して統計情報を新たに取得しますが（23行目-）、統計情報公開属性が「FALSE」のままなのでディクショナリには変化はありません（41行目）。
プライベート・エリアの方は「SAVE_TIME=”3000-12-01 01:00:00″」となる1,000件データの保留統計情報が追加されていることがわかります（36行目）。
12. 統計情報を公開する
SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('TEST','TEST');
PL/SQL procedure successfully completed.
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
PUBLISH TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 2015-02-xx 12:42:22
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 244 14 1000 2015-02-xx 12:41:37
1,000件データの保留統計情報はディクショナリに反映されましたが、公開された統計情報の履歴にも1件追加されました（11行目）。
統計情報を取得し公開する度に統計情報の履歴は増えていきます。
13. 統計情報公開属性を「TRUE」に戻し、テーブルをTRUNCATEする。
SQL> begin
2 DBMS_STATS.SET_TABLE_PREFS('TEST','TEST','PUBLISH','TRUE');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> col prefs for a10
SQL> select DBMS_STATS.GET_PREFS('PUBLISH','TEST','TEST') prefs from dual;
PREFS
----------
TRUE
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
PUBLISH TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 2015-02-xx 12:42:22
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 244 14 1000 2015-02-xx 12:41:37
SQL> truncate table test;
Table truncated.
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
PUBLISH TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 2015-02-xx 12:42:22
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 1000 244 14 1000 2015-02-xx 12:41:37
テーブルのTRUNCATEでレコード件数は0件になったはずですが、ディクショナリの統計情報は1,000件データのままです。
即ち、統計情報公開属性に関わらず（デフォルトのままでも）TRUNCATE後もディクショナリ統計情報はそのままです。
14. 統計情報を取得する（４回目）。
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> @showstat
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST TEST 2015-02-xx 12:30:29
PUBLISH TEST TEST 1000 5 14 1000 2015-02-xx 12:30:29 2015-02-xx 12:38:27
PUBLISH TEST TEST 50000 244 15 50000 2015-02-xx 12:33:39 2015-02-xx 12:42:22
PUBLISH TEST TEST 1000 244 14 1000 2015-02-xx 12:41:37 2015-02-xx 12:56:07
STAT OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST TEST 0 0 0 0 2015-02-xx 12:56:07
TRUNCATEした0件データの状態で最後の統計情報を取得します。
統計情報習得と同時に即時に公開されます。
以上のように、SAVE_TIME列の日付を巧みに使い分けることによって、統計情報の保留と履歴が上手く管理されていることがわかります。
データ・ディクショナリ・ビューの定義を解析することで統計情報管理の秘密を解明することができました。
ところで公開された統計情報は履歴がどんどん溜まっていきますが、保留中の統計情報は履歴が記録されるのでしょうか？
それから、「統計情報の保留」と「統計情報のロック」は違うものなのでしょうか？
検証して新たな疑問点がいくつか出てきましたので次回さらに検証を続けたいと思います。
続く