今週の名言

「学べば学ぶほど、自分が何も知らなかった事に気づく、気づけば気づくほどまた学びたくなる。」
－アルベルト・アインシュタイン－

前回のおさらい

前回は、取得された統計情報が統計情報公開属性の違いによって、どこにどのように格納されるのかということを、実際に確かめてみました。
ちょっと複雑になってきたので、イメージでおさらいをしておきます。

デフォルト状態では、DBMS_STATSプロシージャによって取得された統計情報は、プライベート・エリアに履歴として格納されます。（上図①前半）
1.と同時に、取得された最新の統計情報はディクショナリにも格納されます。（上図①後半、赤枠のデータ）
ディクショナリに格納された統計情報を基にコスト・ベース・オプティマイザ（CBO）は実行計画を算出します。言い換えるとSQLが実行される時にディクショナリの統計情報が変更されていれば、新たな実行計画が算出されます。（上図②）
統計情報公開属性がFALSEの場合、取得された統計情報は保留統計情報としてプライベート・エリアに格納されますが、ディクショナリには反映されません。従って実行計画に影響を与えることもありません。（上図③）
保留中の統計情報は（公開）日時が「3000/12/01 01:00:00」のデータとして扱われます。（青字行）
DBMS_STATS.PUBLISH_PENDING_STATSプロシージャによって、保留統計情報は公開されディクショナリに反映されます。（上図④）
セッション単位でOPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをTRUEに変更すると、CBOは保留統計情報を使用した実行計画を一時的に算出します。（上図⑤）これにより、管理者は保留統計情報を使用した実行計画の方がより優れているかどうかを判断することができます。

疑問点をまとめてみる

「オプティマイザ統計の保留」の基本的な仕様（統計情報の管理と内部動作）はご理解いただけたかと思いますが、もう少し細かい部分を確認していきます。

1. 保留統計情報は履歴を持つか？

公開統計情報は、収集の都度履歴として保存されます。今回の検証では触れませんが保存された履歴はずっとプライベート・エリアに残り続けるのではなく、自動化メンテナンス・タスクの中で削除ルールに従って古いものから自動的に削除されるようになっています。

それでは、統計情報が保留されている状態で新たに統計情報を取得すると、その統計情報は公開属性がFALSEであれば新たな保留統計情報として保存されるはずですが、今まで保持されていた保留統計情報は履歴として残るのでしょうか？それとも保留統計情報は最新のものが１つだけ保持されるのでしょうか？あるいは別の保存のされ方があるのでしょうか？

その辺の動きを確かめてみたいと思います。

2. スキーマ単位、テーブル単位で公開属性を設定する場合の動きは？

「オプティマイザ統計の保留」の検証（その１）で紹介しましたが、公開属性は、スキーマまたはテーブル単位で変更することができます。
スキーマ単位で属性を変更した後に、個々のテーブル単位で設定を変更することは可能なのでしょうか？
あるいは、個々にテーブル単位で設定した状態で、スキーマ単位で設定変更すればすべてのテーブルに反映されるのでしょうか？

これも実際に確かめてみたいと思います。

3. 統計情報をロックした状態で「オプティマイザ統計の保留」はどのように機能するのだろうか？

テーブル単位で統計情報をロックすると、テーブルの統計情報は固定されます。
統計情報が固定されると実行計画は変更されないので、実行計画が変更されては困る場合に「統計情報のロック」はよく使われるのですが、この機能は「オプティマイザ統計の保留」に影響を与えるのでしょうか？

実際に確かめてみます。

4. 公開統計と保留統計の比較について

「オプティマイザ統計の保留」の検証（その１）でコマンドを、（その２）では結果を紹介しましたが、公開統計と保留統計を比較するレポートを再度確認します。
特に運用の場面で活用することを意識して使い方を考えてみたいと思います。

検証開始

0. 確認スクリプトをちょっと修正

実は前回まで使用していた確認スクリプトは、この後使うにはちょっと足りないので修正を加えます。

テーブル名が「TEST」で始まるものを対象とする。（27,46行目）
SAVE_TIMEの昇順でソート表示する。（28行目）

SQL> get showstat.sql
  1  SET LINESIZE      150
  2  COLUMN OWNER      FORMAT a6
  3  COLUMN TABLE_NAME FORMAT a11
  4  select
  5    case when h.savtime >  systimestamp
  6          then 'PENDING'
  7         when h.savtime <= systimestamp
  8          then 'PUBLISH'
  9    end as        STAT
 10  , u.name        OWNER
 11  , o.name        TABLE_NAME
 12  , h.rowcnt      NUM_ROWS
 13  , h.blkcnt      BLOCKS
 14  , h.avgrln      AVG_ROW_LEN
 15  , h.samplesize  SAMPLE_SIZE
 16  , to_char(h.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
 17  , to_char(h.savtime,'yyyy-mm-dd hh24:mi:ss')     SAVE_TIME
 18  From
 19    sys.user$                     u
 20  , sys.obj$                      o
 21  , sys.wri$_optstat_tab_history  h
 22  where h.obj#    = o.obj#
 23  and   o.type#   = 2
 24  and   o.owner#  = u.user#
 25  and   u.name    = 'TEST'
 26  and   o.name    like 'TEST%'
 27  order by h.savtime
 28  ;
 29  select
 30    'CURRENT'     STAT
 31  , u.name        OWNER
 32  , o.name        TABLE_NAME
 33  , t.rowcnt      NUM_ROWS
 34  , t.blkcnt      BLOCKS
 35  , t.avgrln      AVG_ROW_LEN
 36  , t.samplesize  SAMPLE_SIZE
 37  , to_char(t.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
 38  from
 39    sys.user$ u
 40  , sys.tab$  t
 41  , sys.obj$  o
 42  where o.owner#  = u.user#
 43  and   o.obj#    = t.obj#
 44  and   u.name    = 'TEST'
 45* and   o.name    like 'TEST%'

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

1. 保留統計情報は履歴を持つか？

上に挙げた４つの疑問点のうち、今回は最初の項目について確認したいと思います。
前回までの検証で使用したTEST表をまた使っていきます。

1. 検証用テーブルの再作成

まず最初にTESTテーブルを再作成するところから始めます。
今回はデータをInsertする前にインデックスまで作成します。

SQL> drop table test purge;

Table dropped.

SQL> create table test(col1 number,col2 varchar2(20));

Table created.

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

2. テーブル統計情報公開属性を「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

2. データ1,000件インサートおよび統計情報の取得（１回目）

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> 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
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PENDING TEST   TEST              1000          5          14        1000 2015-03-01 17:20:09 3000-12-01 01:00:00

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST   TEST

SAVE_TIME=「3000-12-01 01:00:00」となるデータ件数1,000件の保留統計情報が格納されていることが確認できます。

3. データ1,000件インサート（計2,000件）および統計情報の取得（２回目）

さらに1,000件インサートして、公開属性が「保留」のまま、再度統計情報を取得しましょう。

SQL> begin
  2   for i in 1001..2000 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(*)
----------
      2000

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
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PENDING TEST   TEST              2000         13          14        2000 2015-03-01 17:22:40 3000-12-01 01:00:00

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST   TEST

SAVE_TIME=「3000-12-01 01:00:00」となるデータ件数2,000件の保留統計情報が格納されていることが確認できます。
つまり、先ほどの保留統計情報は上書きされ１件のみが保持されています。保留統計情報は履歴を持たないようです。

4. データ1,000件インサート（計3,000件）および統計情報の取得（３回目）

念のため、もう一度1,000件インサートして統計情報を取得してみます。

SQL> begin
  2   for i in 2001..3000 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(*)
----------
      3000

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
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PENDING TEST   TEST              3000         13          14        3000 2015-03-01 17:24:57 3000-12-01 01:00:00

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST   TEST

やはりSAVE_TIME=「3000-12-01 01:00:00」となるデータ件数3,000件の保留統計情報で上書きされていることがわかります。

保留統計情報はこのように１件のみが保持されるので、公開統計情報のように履歴データがSYSAUX表領域を圧迫する危険性がありません。
実際の運用で「オプティマイザ統計の保留」をどのように活用したら良いのかということはこのシリーズの最後でまとめる予定ですが、以上のように領域管理上のメリットということはあまり認識されていないことかもしれません。

次回は、スキーマ単位、テーブル単位で公開属性を設定する場合の動きを見ていきます。

続く

サイクル＆オラクル

サイクリングとデータベースを楽しむサイト

日別アーカイブ: 2015/03/01

「オプティマイザ統計の保留」の検証（その５）

今週の名言

「学べば学ぶほど、自分が何も知らなかった事に気づく、気づけば気づくほどまた学びたくなる。」
－アルベルト・アインシュタイン－

前回のおさらい

疑問点をまとめてみる

1. 保留統計情報は履歴を持つか？

2. スキーマ単位、テーブル単位で公開属性を設定する場合の動きは？

3. 統計情報をロックした状態で「オプティマイザ統計の保留」はどのように機能するのだろうか？

4. 公開統計と保留統計の比較について

検証開始

0. 確認スクリプトをちょっと修正

1. 保留統計情報は履歴を持つか？

1. 検証用テーブルの再作成

2. テーブル統計情報公開属性を「FALSE=保留」に変更

2. データ1,000件インサートおよび統計情報の取得（１回目）

3. データ1,000件インサート（計2,000件）および統計情報の取得（２回目）

4. データ1,000件インサート（計3,000件）および統計情報の取得（３回目）

2015年3月
月	火	水	木	金	土	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

今週の名言

「学べば学ぶほど、自分が何も知らなかった事に気づく、気づけば気づくほどまた学びたくなる。」 － アルベルト・アインシュタイン －

前回のおさらい

疑問点をまとめてみる

1. 保留統計情報は履歴を持つか？

2. スキーマ単位、テーブル単位で公開属性を設定する場合の動きは？

3. 統計情報をロックした状態で「オプティマイザ統計の保留」はどのように機能するのだろうか？

4. 公開統計と保留統計の比較について

検証開始

0. 確認スクリプトをちょっと修正

1. 保留統計情報は履歴を持つか？

1. 検証用テーブルの再作成

2. テーブル統計情報公開属性を「FALSE=保留」に変更

2. データ1,000件インサートおよび統計情報の取得（１回目）

3. データ1,000件インサート（計2,000件）および統計情報の取得（２回目）

4. データ1,000件インサート（計3,000件）および統計情報の取得（３回目）

「学べば学ぶほど、自分が何も知らなかった事に気づく、気づけば気づくほどまた学びたくなる。」
－アルベルト・アインシュタイン－