今週の名言

「最も強い者が生き残るのではなく､最も賢い者が生き延びるのでもない｡唯一生き残るのは､変化できる者である｡」
－ チャールズ・ダーウィン －

前回のおさらい

• 統計情報公開属性（PUBLISH属性）はテーブルあるいはスキーマ単位で変更することができる。（正確な仕様はマニュアル参照）
• スキーマ単位で公開属性を「FALSE」に変更しても、その後に作成されるテーブルの属性はデフォルト「TRUE」のままなので、そのテーブル単位あるいはスキーマ単位で「FALSE」に再設定する必要がある。

統計情報の保留とロックの関係は？

実行計画を統計情報の取得によって変動させないための機能として「統計情報のロック」という機能があります。（Oracle10g以降で使用可能）
ロックが設定されたテーブルに対して統計情報を取得しようとするとORA-20005エラーが発生し、収集ができないようになっています。
これは、公開属性を保留に設定されたテーブルに対してはどのように作用するでしょうか？
ロックが設定されていたとしても、保留統計情報が取得できればよいと思うのですが実際はどうなのでしょうか？
早速確認してみましょう。

1. 検証準備

最初に検証用のテーブルとインデックスを作成し、初期状態を確認します。

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

表が作成されました。

SQL> create index test1_idx on test1 (col1);

索引が作成されました。

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

表が作成されました。

SQL> create index test2_idx on test2 (col1);

索引が作成されました。

SQL> @showstat

レコードが選択されませんでした。

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

テーブル（インデックス）は存在していますが、データは０件です。

2. 初期データ1,000件作成と統計情報取得

今までの検証と同様に1,000件の初期データをInsertし統計情報を取得します。

SQL> begin
  2   for i in 1..1000 loop
  3    insert into test1 values (i,'TEST DATA');
  4    commit;
  5   end loop;
  6  end;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> begin
  2   for i in 1..1000 loop
  3    insert into test2 values (i,'TEST DATA');
  4    commit;
  5   end loop;
  6  end;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> select count(*) from test1;

  COUNT(*)
----------
      1000

SQL> select count(*) from test2;

  COUNT(*)
----------
      1000

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST1');
  3  end;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST2');
  3  end;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL> @showstat

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED       SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST   TEST1                                                                         2015-03-xx 10:59:54
PUBLISH TEST   TEST2                                                                         2015-03-xx 11:00:08

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST   TEST1             1000          5          14        1000 2015-03-xx 10:59:54
CURRENT TEST   TEST2             1000          5          14        1000 2015-03-xx 11:00:08

3. TEST1表をロックする

それでは、一方のテーブルTEST1表をロックし、USER_TAB_STATISTICSビューからステータスを確認します。

SQL> EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('TEST','TEST1');

PL/SQLプロシージャが正常に完了しました。

SQL> select TABLE_NAME,STATTYPE_LOCKED from USER_TAB_STATISTICS;

TABLE_NAME  STATTYPE_L
----------- ----------
TEST1       ALL
TEST2

比較のためにTEST2表はそのままにしておきます。

4. データを1,000件追加し、再度統計情報を取得する。

両方のテーブルにデータを1,000件ずつ追加し、再度統計情報を取得します。
TEST1表はロックされているので、ORA-20005エラーが発生するはずです。

SQL> begin
  2   for i in 1001..2000 loop
  3    insert into test1 values (i,'TEST DATA');
  4    commit;
  5   end loop;
  6  end;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> begin
  2   for i in 1001..2000 loop
  3    insert into test2 values (i,'TEST DATA');
  4    commit;
  5   end loop;
  6  end;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> select count(*) from test1;

  COUNT(*)
----------
      2000

SQL> select count(*) from test2;

  COUNT(*)
----------
      2000

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST1');
  3  end;
  4  /
begin
*
行1でエラーが発生しました。:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: "SYS.DBMS_STATS", 行20337
ORA-06512: "SYS.DBMS_STATS", 行20360
ORA-06512: 行2

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST2');
  3  end;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL> @showstat

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED       SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST   TEST1                                                                         2015-03-xx 10:59:54
PUBLISH TEST   TEST2                                                                         2015-03-xx 11:00:08
PUBLISH TEST   TEST1             1000          5          14        1000 2015-03-xx 10:59:54 2015-03-xx 11:01:21
PUBLISH TEST   TEST2             1000          5          14        1000 2015-03-xx 11:00:08 2015-03-xx 11:05:46

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST   TEST1             1000          5          14        1000 2015-03-xx 10:59:54
CURRENT TEST   TEST2             2000         13          14        2000 2015-03-xx 11:05:46

予想通りTEST1表でORA-20005エラーが発生しました。（40行目）
TEST2表は1,000件の統計情報が履歴として残り（59行目）、2,000件の統計情報が新たに公開されています。（64行目）

5. スキーマ単位で公開属性を「FALSE」に設定する

SQL> begin
  2   DBMS_STATS.SET_SCHEMA_PREFS('TEST','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- ----------
TEST1       FALSE
TEST2       FALSE

6. データをさらに1,000件追加し、再度統計情報を取得する。

両方のテーブルの公開属性を「保留＝FALSE」に設定しました。
ロック中のTEST1表において保留統計情報は取得できるのでしょうか？

SQL> begin
  2   for i in 2001..3000 loop
  3    insert into test1 values (i,'TEST DATA');
  4    commit;
  5   end loop;
  6  end;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> begin
  2   for i in 2001..3000 loop
  3    insert into test2 values (i,'TEST DATA');
  4    commit;
  5   end loop;
  6  end;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> select count(*) from test1;

  COUNT(*)
----------
      3000

SQL> select count(*) from test2;

  COUNT(*)
----------
      3000

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST1');
  3  end;
  4  /
begin
*
行1でエラーが発生しました。:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: "SYS.DBMS_STATS", 行20337
ORA-06512: "SYS.DBMS_STATS", 行20360
ORA-06512: 行2

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST2');
  3  end;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL> @showstat

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED       SAVE_TIME
------- ------ ----------- ---------- ---------- ----------- ----------- ------------------- -------------------
PUBLISH TEST   TEST1                                                                         2015-03-xx 10:59:54
PUBLISH TEST   TEST2                                                                         2015-03-xx 11:00:08
PUBLISH TEST   TEST1             1000          5          14        1000 2015-03-xx 10:59:54 2015-03-xx 11:01:21
PUBLISH TEST   TEST2             1000          5          14        1000 2015-03-xx 11:00:08 2015-03-xx 11:05:46
PENDING TEST   TEST2             3000         13          14        3000 2015-03-xx 11:11:07 3000-12-01 01:00:00

STAT    OWNER  TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
------- ------ ----------- ---------- ---------- ----------- ----------- -------------------
CURRENT TEST   TEST1             1000          5          14        1000 2015-03-xx 10:59:54
CURRENT TEST   TEST2             2000         13          14        2000 2015-03-xx 11:05:46

やはり、TEST1表でORA-20005エラーが発生しました。（40行目）
また、TEST2表では保留統計情報が取得されていますが、TEST1表では取得されていません。（60行目）

つまり、統計情報がロックされていると、保留統計情報も取得されないことがわかります。

ちなみに、スキーマ単位で統計情報を取得する

begin
 DBMS_STATS.GATHER_SCHEMA_STATS('TEST');
end;
/

を使用すると、ロックされたテーブルがあっても、内部的に取得動作がスキップされるようでORA-20005エラーは発生しません。（今回は取得できないことを確認するためにテーブル単位で取得しました。）

まとめ

統計情報をロックしたテーブルでも、保留統計情報を取得できるか確認しましたが、ロックはやはりロックでした。
今回の検証で、ロックされたテーブルにおいては保留統計情報であっても取得されないことを確認することができました。

今回はここまで

今週の名言

 「過去から学び、今日のために生き、未来に対して希望を持つ。大切なことは、何も疑問を持たない状態に陥らないことである。」
－ アルベルト・アインシュタイン －

前回のおさらい

前回は、保留統計情報が履歴を持つかということを確認し、最後に収集された統計情報のみが保持されることを確認しました。
これは公開統計情報が、自動化メンテナンスタスクによって削除されるまで履歴が残り続けるのと比べて、領域を圧迫する危険性がより少ないことを意味します。
保留統計情報の隠れたメリットとも言えるかもしれません。

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

今までの検証ではテーブル単位で公開属性を変更していましたが、スキーマ単位でも公開属性を変更することができます。
実際の運用を考えるとスキーマに含まれるテーブルは数百を超えることもあるので、スキーマ単位で一括して属性を変更できた方が効率的です。
今回は、スキーマ単位で公開属性を変更する時の動きを確認します。

1. 検証準備

前回までの検証で使用したTEST表を削除し、同じ構造のTEST1表、TEST2表を作成しそれぞれインデックスを作成します。
作成後に（デフォルトの）公開属性を確認します。

SQL> drop table test purge;

Table dropped.

SQL> select tname from tab;

no rows selected

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

Table created.

SQL> create index test1_idx on test1 (col1);

Index created.

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

Table created.

SQL> create index test2_idx on test2 (col1);

Index created.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       TRUE
TEST2       TRUE

デフォルトの公開属性は「TRUE=公開」となっています。

2. TEST1表の公開属性をFALSEに変更する。

それでは、今までと同じようにTEST1表のみ「TRUE→FALSE」に変更してみます。

SQL> begin
  2   DBMS_STATS.SET_TABLE_PREFS('TEST','TEST1','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       FALSE
TEST2       TRUE

変更していないTEST2表はTRUEのままです。

3. スキーマ単位で公開属性をFALSEに変更する。

今度はTESTスキーマに対して公開属性を「FALSE」に設定変更します。

SQL> begin
  2   DBMS_STATS.SET_SCHEMA_PREFS('TEST','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       FALSE
TEST2       FALSE

TEST2表も「FALSE」となっていることがわかります。

4. テーブル単位で公開属性をTRUEに変更する。

次に、「FALSE→TRUE」も見てみます。まずTEST2表のみ「TRUE」に変更します。

SQL> begin
  2   DBMS_STATS.SET_TABLE_PREFS('TEST','TEST2','PUBLISH','TRUE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       FALSE
TEST2       TRUE

5. スキーマ単位で公開属性をTRUEに変更する。

SQL> begin
  2   DBMS_STATS.SET_SCHEMA_PREFS('TEST','PUBLISH','TRUE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       TRUE
TEST2       TRUE

SQL> begin
  2   DBMS_STATS.SET_SCHEMA_PREFS('TEST','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

スキーマ単位でも「FALSE→TRUE」の変更が可能であることがわかります。

6. スキーマ単位で公開属性をFALSEにしておいてから、新規にテーブルを作成する。

スキーマ単位で公開属性を保留にしておけば、次に新規作成するテーブルに公開属性は反映されるのでしょうか？
早速やってみましょう。

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       FALSE
TEST2       FALSE

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

Table created.

SQL> create index test3_idx on test3 (col1);

Index created.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       FALSE
TEST2       FALSE
TEST3       TRUE

新規作成したテーブルの公開属性は、何もしなければ「TRUE」に設定されていることがわかります。

7. 公開属性が「TRUE」であるテーブルの有無を確認し、スキーマ単位で「FALSE」にするには。

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES
  3  where DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) = 'TRUE';

TABLE_NAME  PUBLISH
----------- -----------
TEST3       TRUE

SQL> begin
  2   DBMS_STATS.SET_SCHEMA_PREFS('TEST','PUBLISH','FALSE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,DBMS_STATS.GET_PREFS('PUBLISH','TEST',TABLE_NAME) PUBLISH
  2  from USER_TABLES;

TABLE_NAME  PUBLISH
----------- -----------
TEST1       FALSE
TEST2       FALSE
TEST3       FALSE

まとめ

• テーブル単位でもスキーマ単位でも公開属性を変更することができる。
• テーブル作成時の公開属性は「TRUE」なので、スキーマ全体で公開属性を「FALSE 」にするような運用をしているような場合、テーブル単位あるいはスキーマ単位で「FALSE」に再設定する必要がある。

今日はここまで。

続く

今週の名言

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

前回のおさらい

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

1. デフォルト状態では、DBMS_STATSプロシージャによって取得された統計情報は、プライベート・エリアに履歴として格納されます。（上図①前半）
2. 1.と同時に、取得された最新の統計情報はディクショナリにも格納されます。（上図①後半、赤枠のデータ）
3. ディクショナリに格納された統計情報を基にコスト・ベース・オプティマイザ（CBO）は実行計画を算出します。言い換えるとSQLが実行される時にディクショナリの統計情報が変更されていれば、新たな実行計画が算出されます。（上図②）
4. 統計情報公開属性がFALSEの場合、取得された統計情報は保留統計情報としてプライベート・エリアに格納されますが、ディクショナリには反映されません。従って実行計画に影響を与えることもありません。（上図③）
5. 保留中の統計情報は（公開）日時が「3000/12/01 01:00:00」のデータとして扱われます。（青字行）
6. DBMS_STATS.PUBLISH_PENDING_STATSプロシージャによって、保留統計情報は公開されディクショナリに反映されます。（上図④）
7. セッション単位で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表領域を圧迫する危険性がありません。
実際の運用で「オプティマイザ統計の保留」をどのように活用したら良いのかということはこのシリーズの最後でまとめる予定ですが、以上のように領域管理上のメリットということはあまり認識されていないことかもしれません。

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

続く

前回のおさらい

前々回は「オプティマイザ統計の保留」の基本的な動作を紹介しました。
前回は保留中の統計情報がどのように格納されるのかを探るために、必要な情報を整理しました。

簡単にまとめると

• 保留中の統計上は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

いろいろなところが同時に更新されているので整理します。

1. ディクショナリには50,000件の統計情報が公開されました（14行目）。統計情報が取得された時刻「12:33:39」（LAST_ANALYZED列）はDBMS_STATS.GATHER_TABLE_STATSが実行された時刻です。
2. プライベート・エリアの最終行（10行目）はSAVE_TIME列が「12:38:27」に更新されており、これはDBMS_STATS.PUBLISHが実行された時刻です。そして過去時刻となったことで保留統計情報ではなく公開統計情報の履歴となっていることがわかります。
3. さらに、この行は1,000行データの統計情報となっていることがわかります。（LAST_ANALYZED=12:30:27）
4.  そして、統計情報を取得する前の状態も保存されています。（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列の日付を巧みに使い分けることによって、統計情報の保留と履歴が上手く管理されていることがわかります。
データ・ディクショナリ・ビューの定義を解析することで統計情報管理の秘密を解明することができました。

ところで公開された統計情報は履歴がどんどん溜まっていきますが、保留中の統計情報は履歴が記録されるのでしょうか？
それから、「統計情報の保留」と「統計情報のロック」は違うものなのでしょうか？

検証して新たな疑問点がいくつか出てきましたので次回さらに検証を続けたいと思います。

続く

前回のおさらい

前回は「オプティマイザ統計の保留」をシナリオに沿って動作を検証しました。

テーブルの統計情報公開属性を「FALSE」（=保留）に設定すれば、統計情報を取得したとしても現在の実行計画に影響を与えず、保留状態に置かれます。

そして、保留状態の統計情報を手動で公開すれば、次回実行計画を算出されるタイミングでその公開された統計情報が使用されて新しい実行計画が作成されることを確認しました。

この機能の概要は前々回の記事「オプティマイザ統計の保留」の検証（その１）で紹介しましたが、別のマニュアルにある記事も紹介しておきます。

Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス
11g リリース2(11.2)
B56262-06
保留中の統計情報

「保留中の統計情報」のポイント

• デフォルトでは収集された統計情報はディクショナリに格納される。
• テーブルのPUBLISHオプション（統計情報公開属性）を「FALSE」に設定すると、統計情報はディクショナリではなくシステムのプライベート・エリアに格納される。
• プライベート・エリアに格納された統計情報は原則的にはコストベース・オプティマイザ（CBO）で使用されない。
• パラメータOPTIMIZER_USE_PENDING_STATISTICSをセッション（またはシステム）レベルで「TRUE」に設定変更すると、保留中の統計情報が新しい実行計画に影響を与えるか否かの確認を行うことができる。

プライベート・エリアはどこにあるのか？

上のポイントに出てきた保留中の統計情報が格納されるプライベート・エリアとは一体どこなのでしょうか？
前回の検証で、保留中の統計情報はデータ・ディクショナリ・ビュー「XXX_TAB_PENDING_STATS」で参照することを紹介しました。
まずは、このビュー定義からどのような実表に情報が格納されるのかを見ていきましょう。

ビュー定義を確認する

ビュー定義はDBA_VIEWSのTEXT列を確認することで得られます。以下はDBA_TAB_PENDING_STATSのビュー定義です。

SQL> set long 2000000000
SQL> set pages 200
SQL> select TEXT from dba_views where VIEW_NAME = 'DBA_TAB_PENDING_STATS';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null, h.rowcnt, h.blkcnt, h.avgrln,
         h.samplesize, h.analyzetime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
    and  h.savtime > systimestamp
  union all
  -- partitions
  select u.name, o.name, o.subname, null, h.rowcnt, h.blkcnt,
         h.avgrln, h.samplesize, h.analyzetime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
    and  h.savtime > systimestamp
  union all
  -- sub partitions
  select u.name, osp.name, ocp.subname, osp.subname, h.rowcnt,
         h.blkcnt, h.avgrln, h.samplesize, h.analyzetime
  from  sys.user$ u,  sys.obj$ osp, obj$ ocp,  sys.tabsubpart$ tsp,
        sys.wri$_optstat_tab_history h
  where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj# and
        tsp.pobj# = ocp.obj# and osp.owner# = u.user#
    and h.savtime > systimestamp

このビューは３つの部分（テーブル、パーティション、サブパーティション）の問い合わせから成っていることがわかります。
今回の検証ではテーブルのみを対象とするので、１番最初のパートに着目します。
それから、ハイライト表示した11行目の「h.savtime > systimestamp」というWHERE条件はこの先重要になりますので覚えておいてください。

さて、保留中の統計情報はWRI$_OPTSTAT_TAB_HISTORY（所有者はSYSユーザ）という実表に格納されるようです。
このテーブルの構成を確認しておきましょう。

SQL> desc sys.wri$_optstat_tab_history
 名前               NULL?    型
 ----------------- -------- ----------------------------
 OBJ#              NOT NULL NUMBER
 SAVTIME                    TIMESTAMP(6) WITH TIME ZONE
 FLAGS                      NUMBER
 ROWCNT                     NUMBER
 BLKCNT                     NUMBER
 AVGRLN                     NUMBER
 SAMPLESIZE                 NUMBER
 ANALYZETIME                DATE
 CACHEDBLK                  NUMBER
 CACHEHIT                   NUMBER
 LOGICALREAD                NUMBER
 SPARE1                     NUMBER
 SPARE2                     NUMBER
 SPARE3                     NUMBER
 SPARE4                     VARCHAR2(1000)
 SPARE5                     VARCHAR2(1000)
 SPARE6                     TIMESTAMP(6) WITH TIME ZONE

またこのテーブルが格納されている表領域を確認しておきます。

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME
  2  from dba_tables
  3  where OWNER = 'SYS' and TABLE_NAME = 'WRI$_OPTSTAT_TAB_HISTORY';

OWNER  TABLE_NAME                     TABLESPACE_NAME
------ ------------------------------ ----------------
SYS    WRI$_OPTSTAT_TAB_HISTORY       SYSAUX

SYSAUX表領域であることがわかります。つまり「プライベート・エリア」というのはSYSAUX表領域にあるSYS.WRI$_OPTSTAT_TAB_HISTORY表のようです。

実は、このSYS.WRI$_OPTSTAT_TAB_HISTORYを元表とするビューは他にも存在します。
以下は、DBA_TAB_STATS_HISTORYビューの定義を確認したところです。

SQL> select TEXT from dba_views where VIEW_NAME = 'DBA_TAB_STATS_HISTORY';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null, h.savtime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
    and  h.savtime <= systimestamp  -- exclude pending statistics
  union all
  -- partitions
  select u.name, o.name, o.subname, null, h.savtime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
    and  h.savtime <= systimestamp  -- exclude pending statistics
  union all
  -- sub partitions
  select u.name, osp.name, ocp.subname, osp.subname, h.savtime
  from  sys.user$ u,  sys.obj$ osp, obj$ ocp,  sys.tabsubpart$ tsp,
        sys.wri$_optstat_tab_history h
  where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj#
    and tsp.pobj# = ocp.obj# and osp.owner# = u.user#
    and h.savtime <= systimestamp  -- exclude pending statistics
  union all
  -- fixed tables
  select 'SYS', t.kqftanam, null, null, h.savtime
  from  sys.x$kqfta t, sys.wri$_optstat_tab_history h
  where t.kqftaobj = h.obj#
    and h.savtime <= systimestamp  -- exclude pending statistics

驚いたことに、DBA_TAB_PENDING_STATSビューと定義がほぼ同じで、8行目のWHERE条件が正反対になっています。

実は
13.5.3 前のバージョンの統計のリストア
Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06

に出てくる統計情報の履歴管理と、統計情報の保留はほぼ同じ仕組みで実装されているようです。

そこで、以下の「保留中の統計情報」と「統計情報の履歴」を同時に確認できるSQL文を使って、統計情報取得に伴う情報の管理のされ方を追いかけて行きたいと思います。

showstat.sql

COLUMN TABLE_NAME FORMAT a11
COLUMN OWNER      FORMAT a6
select
  case when h.savtime >  systimestamp
        then 'PENDING'
       when h.savtime <= systimestamp
        then 'PUBLISH'
  end as        STAT
, u.name        OWNER
, o.name        TABLE_NAME
, h.rowcnt      NUM_ROWS
, h.blkcnt      BLOCKS
, h.avgrln      AVG_ROW_LEN
, h.samplesize  SAMPLE_SIZE
, to_char(h.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
, to_char(h.savtime,'yyyy-mm-dd hh24:mi:ss')     SAVE_TIME
From
  sys.user$                     u
, sys.obj$                      o
, sys.wri$_optstat_tab_history  h
where h.obj#    = o.obj#
and   o.type#   = 2
and   o.owner#  = u.user#
and   u.name    = 'TEST'
and   o.name    like 'TEST%'
;
select
  'CURRENT'     STAT
, u.name        OWNER
, o.name        TABLE_NAME
, t.rowcnt      NUM_ROWS
, t.blkcnt      BLOCKS
, t.avgrln      AVG_ROW_LEN
, t.samplesize  SAMPLE_SIZE
, to_char(t.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
from
  sys.user$ u
, sys.tab$  t
, sys.obj$  o
where o.owner#  = u.user#
and   o.obj#    = t.obj#
and   u.name    = 'TEST'
and   o.name    like 'TEST%'
/

解説：
4-8行目のCASE式により、h.savtime列の値が現在日時に対して大小関係がどうなっているかで表示を変えています。
また27行目以降は、現在オプティマイザで使用されているディクショナリ（SYS.TAB$）に格納された統計情報の状況を確認するための２番目のクエリーです。

ちなみにSYS.TAB$は、以下のとおりSYSTEM表領域に格納されるディクショナリ表です。

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME
  2  from dba_tables
  3  where OWNER = 'SYS' and TABLE_NAME = 'TAB$';

OWNER  TABLE_NAME  TABLESPACE_NAME
------ ----------- ----------------
SYS    TAB$        SYSTEM

長くなったので、検証結果は次回に紹介します。

続く

検証開始！

前回示した検証シナリオに沿って、「オプティマイザ統計の保留」の基本的な機能を確認していきます。

1. 検証準備（スキーマの作成）

ここでは検証用スキーマとして「TEST」を作成し、必要な権限を与えます。特にデータ・ディクショナリを確認できるように「SELECT ANY DICTIONARY」権限を与えておきます。

SQL> create user test identified by "test" default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> grant select any dictionary to test;

Grant succeeded.

SQL> grant plustrace to test;

Grant succeeded.

2. テーブル作成、データ作成、インデックス作成および統計情報の取得

簡単な構造の検証用テーブル「TEST」を作成、データを1,000件インサートし、さらにインデックスを作成します。
その後、統計情報を取得します。

SQL> conn test/test
Connected.

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

Table created.

SQL> desc test
 Name                  Null?    Type
 --------------------- -------- ------------------
 COL1                           NUMBER
 COL2                           VARCHAR2(20)

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> begin
  2  DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
  3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows from user_tables
  2  where table_name = 'TEST';

LAST_ANA   NUM_ROWS
-------- ----------
20:44:35       1000

3. 実行計画の確認

それではこの状態で、以下の問い合わせを実行してみましょう。（この後同じ問い合わせを実行し、実行計画がどのようになるかを確認します。）

SQL> set autot traceonly
SQL> set lines 200
SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL1">999)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        625  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

「INDEX RANGE SCAN」が選択され、1件のデータを取得していることがわかります。

4. テーブルの統計情報公開属性を「FALSE=保留」に設定変更する。

デフォルトではテーブルの統計情報公開属性は「TRUE=公開」になっています。
以下のプロシージャにより属性を「FALSE=保留」に設定変更します。
（テーブルの属性を変更しますが「ALTER TABLE」文を使用しないことに注意してください。）

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

5. テーブルをTRUNCATE後、データを50,000件インサートする。

SQL> truncate table test;

Table truncated.

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

6. 統計情報を取得し、内容を確認する。

デフォルトではテーブルをTRUNCATEした時点で、統計情報も削除されます。その後データを50,000件インサートしても統計情報を再取得しない限り統計情報は「なし」のままです。
「オプティマイザ統計の保留」が有効になっている状態で、統計情報がどうなるかを確認しましょう。

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows from user_tables
  2  where table_name = 'TEST';

LAST_ANA   NUM_ROWS
-------- ----------
20:44:35       1000

非常に興味深い結果となりましたが、TRUNCATEする前の統計情報がそのまま「保留」されていることがわかります。
TRUNCATEではデータを削除するのではなく、同じ構造で空のテーブルを再作成するような内部動作が行われます（オブジェクトIDが変更される）が、テーブルに紐付く（公開されている）統計情報はテーブル定義とは別の場所に保持されていることがわかります。
しかも、統計情報を再取得してもこの保留されている統計情報を上書きすることはありません。

7. 保留されている統計情報および公開情報との違いを確認する。

「オプティマイザ統計の保留」が有効になっているテーブルは「USER_TAB_PENDHING_STATS」ビューによって保留中の統計情報を確認することができます。
さらに、「DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING」ファンクションで、公開中と保留中の統計情報を比較することができます。

SQL> col table_name for a12
SQL> select table_name, to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows
  2  from user_tab_pending_stats where table_name = 'TEST';

TABLE_NAME   LAST_ANA   NUM_ROWS
------------ -------- ----------
TEST         20:49:44      50000

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('TEST','TEST'));

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : TEST
OWNER         : TEST
SOURCE A      : Current Statistics in dictionary (注：公開中の統計情報）
SOURCE B      : Pending Statistics               (注：保留中の統計情報）
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

TEST                        T   A   1000       5          14         1000
                                B   50000      244        15         50000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
                    (=Number of Distinct Value)
...............................................................................

COL1            A   1000    .001       NO   0       4    C102  C20B  1000
                B   50000   .00002     NO   0       5    C102  C306  50000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX:TEST_IDX
                                 ...............

TEST_IDX        I   A   1000    3       1000    1     1     3       1   1000
                    B   50000   99      50000   1     1     137     NUL 50000
###############################################################################

SQL> set head on

7. 同じ問い合わせを実行し、実行計画を確認する。

データ件数が50倍になったところで、同じ問い合わせを実行し実行計画を確認します。

SQL> set autot traceonly
SQL> set timing on
SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1">999)

Statistics
----------------------------------------------------------
         26  recursive calls
          0  db block gets
       6791  consistent gets
          0  physical reads
          0  redo size
    1578657  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      49001  rows processed

50,000件のうち49,001件を取得する問い合わせなので、理想的には「TABLE ACCESS FULL」が選択されるべきですが、統計情報上は1,000件のテーブルなので最初の問い合わせ時と同じ「INDEX RANGE SCAN」が選択されています。
取得された49,001個のrowidに対してそれぞれ「TABLE ACCESS BY INDEX ROWID」が実行されるため、あまり効率的ではないI/Oが発生してしまいます。

8. 保留中の統計情報を使って同じ問い合わせを実行し、実行計画を確認する。

次に、今接続中のこのセッションのみ保留中の統計情報を使うように設定（「OPTIMIZER_USE_PENDING_STATISTICS」初期化パラメータ）を変更し、再度同じ問い合わせを実行してみます。

SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

Session altered.

SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
        592  recursive calls
          0  db block gets
       4413  consistent gets
          0  physical reads
        124  redo size
    1078861  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
        160  sorts (memory)
          0  sorts (disk)
      49001  rows processed

今度は「TABLE ACCESS FULL」が選択されていることがわかります。

9. 再接続しセッション情報をクリアした後に同じ問い合わせを実行する。

SQL> conn test/test
Connected.
SQL> show parameter OPTIMIZER_USE_PENDING_STATISTICS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
SQL> set autot traceonly
SQL> set timing on
SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6743  consistent gets
          0  physical reads
          0  redo size
    1578657  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49001  rows processed

CONNECTコマンドで再接続すると、「ALTER SESSION」文で変更した「OPTIMIZER_USE_PENDING_STATISTICS」初期化パラメータの値がデフォルトの「FALSE」に戻ります。
その状態で同じ問い合わせを実行すると、非効率な「INDEX RANGE SCAN」を使用した実行計画に戻ってしまうことがわかります。

10. 保留中の統計情報を公開した後に同じ問い合わせを実行する。

今度は保留中の統計情報をDBMS_STATS.PUBLISH_PENDING_STATSプロシージャによって永続的に公開し、再度同じ問い合わせを実行して実行計画を確認します。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('TEST','TEST');

PL/SQL procedure successfully completed.

SQL> select * from test where col1 > 999;

49001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6743  consistent gets
          0  physical reads
          0  redo size
    1578657  bytes sent via SQL*Net to client
      36446  bytes received via SQL*Net from client
       3268  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49001  rows processed

SQL> set autot off

永続的に公開された統計情報を使った実行計画なので、他のセッションから同じ問い合わせが実行されてもこの実行計画が選択されます。

11. レコードを削除し件数を1,000件とした後に同じ問い合わせを実行する。

次に50,000件のレコードから49,000件を削除（DELETE）し、同じ問い合わせを実行して実行計画を確認します。

SQL> delete from test where col1 > 1000;

49000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
      1000

SQL> set autot traceonly
SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        101  consistent gets
          0  physical reads
          0  redo size
        625  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off

統計情報上はこのテーブルはまだ50,000件のデータがあるとみなされているため、同じように「TABLE ACCESS FULL」が選択されています。
（蛇足ですが、実行計画ツリーでRowsが「49002」となっていることが興味深いです。）

12. 統計情報を再取得し、公開済みと保留中の統計情報を比較する。

SQL> begin
  2   DBMS_STATS.GATHER_TABLE_STATS('TEST','TEST');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows from user_tables
  2  where table_name = 'TEST';

LAST_ANA   NUM_ROWS
-------- ----------
20:49:44      50000

SQL> select table_name, to_char(last_analyzed,'hh24:mi:ss') last_analyzed, num_rows
  2  from user_tab_pending_stats where table_name = 'TEST';

TABLE_NAME   LAST_ANA   NUM_ROWS
------------ -------- ----------
TEST         21:18:42       1000

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('TEST','TEST'));

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : TEST
OWNER         : TEST
SOURCE A      : Current Statistics in dictionary
SOURCE B      : Pending Statistics
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

TEST                        T   A   50000      244        15         50000
                                B   1000       244        14         1000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

COL1            A   50000   .00002     NO   0       5    C102  C306  50000
                B   1000    .001       NO   0       4    C102  C20B  1000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                                 INDEX:TEST_IDX
                                 ...............

TEST_IDX        I   A   50000   99      50000   1     1     137     1   50000
                    B   1000    2       1000    1     1     3       NUL 1000
###############################################################################

SQL> set head on

DELETEの結果レコード件数が50,000件から1,000件になっても、ブロック数が「244」のままとなっていることに注目してください。
つまり、「TABLE ACCESS FULL」によって「244」ブロックもの無駄なI/Oが発生することになります。

13. テーブルの統計情報公開属性を「TRUE=公開」に設定変更し、同じ問い合わせを実行する。

SQL> begin
  2   DBMS_STATS.SET_TABLE_PREFS('TEST','TEST','PUBLISH','TRUE');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select DBMS_STATS.GET_PREFS('PUBLISH','TEST','TEST') prefs from dual;

PREFS
----------
TRUE

SQL> set autot traceonly
SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49002 |   717K|    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 49002 |   717K|    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL1">999)

Statistics
----------------------------------------------------------
         19  recursive calls
          0  db block gets
        234  consistent gets
          0  physical reads
          0  redo size
        621  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

テーブルの統計情報公開属性を「TRUE=公開」に設定変更したとしても、保留中の統計情報は即時に公開されるわけではないことに注目してください。

14. 保留中の統計情報を公開し、同じ問い合わせを実行する。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('TEST','TEST');

PL/SQL procedure successfully completed.

SQL> select * from test where col1 > 999;

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    14 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    14 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL1">999)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        191  consistent gets
          0  physical reads
          0  redo size
        621  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

保留中の統計情報は公開して初めて実行計画の算出に使われるようになります。
テーブル件数に応じた適切な実行計画が選択されました。

今回はマニュアルに記載のある基本的な動作について確認しました。
次回は保留中の統計情報がどのように保持されているのかを詳しく見てみることにします。

続く

「オプティマイザ統計の保留」とは？

今回から「オプティマイザ統計の保留」というOracle 11g以降で使えるようになった機能を紹介・検証していきます。

以下はマニュアルへのリンクです。
13.5.1 統計の保留
Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06

機能概要

• 統計情報は収集後即時に公開（PUBLISH）される。（デフォルトの動作）
• 公開された統計はXXXX_TAB_STATISTICSやXXXX_IND_STATISTICSデータ・ディクショナリ・ビューに格納される。
• オプティマイザは原則的にデータ・ディクショナリ・ビューに格納されている公開済の統計を使用する。
• 従って即時に公開された新しい統計情報は実行計画に影響を与える可能性がある。
• 新しい統計を「保留中」として保存することができる。（統計の保留）
• PUBLISH設定は、スキーマまたはテーブル・レベルで変更することができる。
• 保留中の統計は、XXXX_TAB_PENDING_STATSやXXXX_IND_PENDING_STATSビューに格納される。
• 保留された統計情報は実行計画に反映されない。
• OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをTRUEに設定（セッション単位で変更可）すると、保留中の統計情報を実行計画に反映することができる。
• 現在の公開済み統計情報と新しく保留された統計情報を比較することができる。
• 保留中の統計情報を手動で公開することができる。

関連コマンド等

検証を行う前に、関連するコマンド等を整理しておきます。

1. PUBLISH設定を変更するには

PUBLISH属性は、スキーマあるいはテーブルに関する属性になりますが、「保留中」に変更するには以下の要領でDBMS_STATS.SET_TABLE_PREFSパッケージ（プロシージャ）を使用して行います。（ALTER TABLEは使用しません。）

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('<スキーマ名>', '<テーブル名>;', 'PUBLISH', 'false');

例：

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');

2. 現在のPUBLISH設定を確認するには

現在設定されているPUBLISH属性の状態を確認するためには、データ・ディクショナリ・ビューへの問い合わせではなく、DBMS_STATS.GET_PREFSパッケージ（ファンクション）を使用して行います。

SQL> select DBMS_STATS.GET_PREFS('PUBLISH', '<スキーマ名>', '<テーブル名>') prefs from dual;

例：

SQL> select DBMS_STATS.GET_PREFS('PUBLISH', 'SH', 'CUSTOMERS') prefs from dual;

3. 保留中の統計を一時的に使用して実行計画を作成するには

SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

このコマンド実行後のセッションではオプティマイザは保留中の統計を使用して実行計画を作成します。

4. 現在の公開済み統計と新しく保留された統計を比較するには

この場合は、DBMS_STATS.DIFF_TABLE_STATS_IN_PENDINGパッケージ（ファンクション）を使用して行います。

SQL> set long 10000
SQL> set pages 9999
SQL> set head off
SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('<スキーマ名>', '<テーブル名>'));

例：

SQL> select report from table (DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('SH', 'CUSTOMERS'));

5. 保留中の統計を永続的に公開するには

DBMS_STATS.PUBLISH_PENDING_STATSパッケージ（プロシージャ）を使用して行います。公開された統計はもはや保留中ではありません。オプティマイザは次に実行計画を作成する際にこの統計を使用します。

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('<スキーマ名>', '<テーブル名>');

例：

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('SH', 'CUSTOMERS');

検証シナリオ

それでは、この機能を検証するためのシナリオを以下に紹介します。

1. テーブル作成
   • TEST表（ID, VALUE）
2. 少量データ作成（1,000件）
3. インデックス作成
   • ID列に対して
4. クエリー実行①
   • インデックス・レンジ検索（1件取得）
5. テーブルTRUNCATE
6. 大量データ作成（50,000件）
7. クエリー実行②
   • 4.と同じクエリー（ただし、検索レンジが異なる）
8. 大量データ削除（50,000→1,000件）
9. クエリー実行③
   • 4, 7と同じクエリー

レコード件数が変化したタイミングで同じクエリーを実行し実行計画を確認しますが、統計情報の取得によって実行計画がどのように変化する（あるいは変化しないか）を検証していきます。

次回へ続く

自動化の落とし穴

グローバル社会は２４時間眠らない。

「９時から５時まで働いて、土日は完全休日。」正月からスーパーが開いている現代ではそんな古き良き時代の働き方は皆無と言えるかもしれません。

私の場合、以前某官公庁で大型汎用計算機の運用に携わっていた頃は、朝７時に当番が早めに出勤してコンピュータを立ち上げ、夕方１８時くらいには電源を落として帰るような運用をしていました。（当然土日は休みです。）

しかし、DBAとして本格的に働き始めた2000年ごろから、２４時間止められない運用とその運用を支える仕組みを直に担当するようになりました。

24×365（２４時間３６５日）運用というものは携わった者でなければ理解できないシビアな世界です。ちょうどこの頃はインターネットをビジネスで使うということが当たり前のようになっていった時期であり、世界規模（グローバル）にビジネスを展開することが多くの企業で求められ始めた頃でもありました。

私はスペシャリストとしてオンコールで対応する立場でしたが、運用エンジニアは３交代シフトで２４時間３６５日運用を行うような体制でした。

この会社は主に日本国内の企業を対象にしていましたが、それでも２４時間システムを維持管理する必要があります。

特にグローバルに事業を展開している企業はより高いレベルでサービスを提供するため、例えばOracle社のサポートは当時日本、米国、英国に拠点を持ち、サポート案件を拠点間でハンドリングすることで、シームレスで高度なサポートを提供しているというようなことを聞きました。

その他にも外国為替市場のように、業界全体がグローバルに広がっている場合の運用もシビアです。

あるFX会社におけるパフォーマンス問題

Oracle10gは2004年にR1がリリースされましたが、以下は比較的早い時期にOracle9iから10gR1にアップグレードした、とあるFX（外国為替証拠金取引）会社で実際に起きた事例を若干の脚色を加えて再現したものです。

FXにおける繁忙時間帯は夜の21時です。なぜなら世界の主要取引市場である東京、ロンドン、ニューヨーク市場はそれぞれ朝7時に開かれます。ニューヨーク市場は元々の規模が大きことや、東京・ロンドン市場の取引傾向の影響を受けるために、日本国内のFX個人投資家にとっても14時間の時差があるニューヨーク市場の動向は非常に気になるのです。仕事から帰ってきたサラリーマンが自宅のパソコンで取引を始める時間帯ということもあり、21時からの数時間は日本のFX業者にとって最もクリティカルな時間帯なのです。

問題は21時にニューヨーク市場が開いて、トランザクション数が次第に伸びてきた22時過ぎに起きました。

さっきまで何の問題もなかったOLTPの取引処理が全く無応答になってしまったのです。

秒単位以下で変動する為替レートに対しリアルタイムに処理が完了しなければならないのに、この状況は致命的でした。

注文や約定と呼ばれる処理が遅れてしまっているため、焦った個人投資家がさらに取引処理を行おうとしたのかもしれません。滞留したトランザクションが膨大になり、状況はさらに悪化していきました。

このようなタイミングでアプリケーションの変更を行うはずがありません。性能劣化の原因が不明のまま時間だけが過ぎていきます。

ついに運用責任者がデータベースの強制再起動を決断しました。

未処理のトランザクションはすべてロールバックされ、せっかくの注文処理が無効になってしまいました。
そればかりか、レートが大きく変動したことによる損失を少しでも小さくするための決済処理が無効になってしまったため、投資家によっては大きな損失が発生してしまったかもしれません。

ほとんどの投資家はネット上で取引を行っているため、深刻なシステム障害はネットの掲示板等で瞬時に広まります。業者にとって投資家に対する補償等だけでなく、悪い評判が拡散してしまうということも大変にダメージが大きなことなのです。

障害の真相

後日、障害の原因を特定することができたのですが、それは唖然とするものでした。

まず初めに障害の背景となった事情について触れておきます。

Oracle10gからルール・ベース・オプティマイザ（RBO）がサポートされなくなりました。RBOで開発していた頃は、FROM句の後に記述するテーブルの順序を入れ替えただけで実行計画がガラッと変わり、パフォーマンスが大きく異なることがよくありました。

具体的には、レコード件数の多い順に記述するような原則で

FROM TBL-A（件数大）,TBL-B（件数中）,TBL-C（件数小）

のような感じで記述します。（この最適な順番を見つけるのが開発者の腕の見せどころでもありました。）
従って、テーブル件数が大きく変動するような場合は、なかなか開発者の意図した結果とならず苦労したという経験は、ベテランのOracle開発者であれば誰でも１つや２つ持っているのではないでしょうか？

ところが、Oracle10g 以降でコスト・ベース・オプティマイザ（CBO）のみになってから、世界は大きく変わりました。データ件数が変動しても統計情報を正しく取得していれば、最適な実行計画を必要に応じて算出してくれる、という理想のオプティマイザがCBOなのです。

前回のルート検索の例で言うと、渋滞情報をリアルタイムに収集して場合に応じた経路を示してくれるという機能がCBOに求められているのです。
つまり、地図上の最短距離だけでルートを示してくれるのがRBOで、渋滞情報を加味したルートを示すのがCBOなのです。

また、カーナビの渋滞情報が変わることに対するルート計算は比較的柔軟に再実行できるような気がしますが、実行計画算出（解析）は負荷の高い処理故、Oracleでは解析済みSQL文を共有することでその負荷がなるべく小さくなるような仕組みになっています。
つまり、渋滞がされた後でもずっと迂回路を案内してしまうような難しさをCBOは併せ持っているのです。

しかも、「正しく統計情報が取得されている」状態でなければ最適な実行計画は算出されないのです。Oracle社はこれに対してテーブル・インデックスの統計情報をデフォルトで自動的に収集する機能を実装しました。

収集の対象となるテーブル・インデックスを特定する条件はありますが、自動統計情報収集処理は10gの場合毎日22時に起動されています。

ほとんどの企業にとって夜の22時はオンライン業務が終了し、バッチ処理も終わっている頃、というのが自動統計情報収集の仕組みを作った開発者の頭の中にあったかどうかはわかりませんが、デフォルト設定がそのようになっているというのは事実です。

日本のFX業者にとってこの仕様が最悪の結果をもたらす可能性があるということは残念ながらあまり知られていませんでした。

詳細は関知していないのですが、このパフォーマンス問題が発生した経緯や背景事情を以下の通り想像を交えてまとめてみます。

1. 繁忙時間帯が夜21時過ぎから日付が変わるあたりまでということは前述の通りですが、ニューヨーク市場がクローズされてから東京市場がオープンされる翌朝7時までのごく短い間にバッチ処理は終了するようになっています。
2. 通常の問題のない日においては22時に自動統計情報収集処理が起動されていましたが、実行計画に影響を与えるようなデータの変動がバッチ処理で行われなかったため、統計情報が大きく変更され実行計画が突然入れ替わってしまうようなことはありませんでした。
3. 問題の起きた日は、朝のバッチ処理でデータの大きな変動がありました。
4. 21時の時点では前日の統計情報により良好な実行計画でOLTPが動いていました。
5. 22時に通常と同じように自動統計情報収集処理が起動され、結果として良好な実行計画が変更されてしまいました。
6. 実行計画算出処理（SQL文解析処理）は元々CPUリソースを消費するものでもあるので、それがOLTPに対して少なからず影響を与えていたというのも事実です。

再発防止策は？

障害の大きな原因として考えられるのは以下の２つです。

• 22時という極めてクリティカルな時間帯であるにも関わらず、自動統計情報収集処理がデフォルトのまま起動されてしまった。
• 実行計画に影響を与えるようなデータの変更があり、実行計画が変更されてしまった。

このケースでは「実行計画が意図せず変更されてしまった。」という問題に対する対策としては、

1. 自動統計情報収集処理を業務影響の少ない時間帯に変更する。
2. 統計情報収集処理を必要最小限の実行にとどめ、毎日は実施しない。
3. 実行計画に影響を与えることが予めわかっているテーブルは統計情報が変更されないようにロックする。

のような項目を実施したと聞いています。

変動するデータに応じて最適な実行計画を立案する仕組みが、Oracle10g以降の「自動化」機能で実現されたわけですが、自動化というのは決して万能ではありません。

上の対策はある意味せっかくの自動化を意図的に殺すことで、（最適ではないかもしれないけれど）安定した運用を実現するものです。

私は「自動化の落とし穴」というテーマで、今までこの事例をいろいろな機会において紹介してきたのですが、反面自動化を選択するか否かの二者択一という考え方で本当に良いのかということをずっと考えてきました。

つまり、自動化と非自動化の中間に「半自動化」という概念があっても良いのではないかというのがその結論であり、次回紹介する「オプティマイザ統計の保留」というあまり注目されていない機能の特徴でもあります。

次回へ続く

オプティマイザの正体

「HOW型」「WHAT型」コンピュータとは？

コンピュータを大きく分類すると「HOW型」と「WHAT型」という２つのタイプに分けることができるという説があります。

これは私が勝手に言っていることではなく１９８０年代にTRONを提唱した東大・坂村健教授の言葉です。（新版 TRONで変わるコンピュータ P.44〜、TRONプロジェクト Wikipedia）

「HOW型」というのは、現在使われているコンピュータつまりノイマン型コンピュータと呼ばれるコンピュータのことで、コンピュータがどのように振る舞うかを人間がプログラムという形で指示するものです。コンピュータが行う複雑な処理の一つ一つを厳密に定義しなければならないためプログラムを作るのは大変ですが、いったんプログラムができてしまえばコンピュータはそれを忠実に実行するだけというものです。

コンピュータ制御された車を考えると、「次の角を左に曲がれ。」とか「国道１号線を東京方面に進め。」等の指示を次々に与えながら目的地に誘導していくのが「HOW（どうやってやる）型」です。

一方「WHAT型」というのは１９８０年代当時研究されていた第五世代コンピュータとか、人工知能専用コンピュータに該当するもので、コンピュータ・カーの例で言うと「道路の左側を車線に沿って走れ。」とか「赤信号では止まること。」のように基本的なルール（専門的には知識ベース）だけを先に教えておいて、「何処何処へ行け！」という指示だけで目的地に向かわせるのが「WHAT（何をする）型」です。

第五世代コンピュータは当時最高の頭脳を結集させたプロジェクトだったようですが、結局は成果を出すことができずに終了してしまいました。まだコンピュータのパワーが非力だったということが最大の原因だったと思いますが、とりあえずムチャクチャに動いて（プログラムなしでコンピュータを動かすのは大変な事）最終的に結果を出せば良いというアプローチにやはり無理があったようです。

オプティマイザは「HOW型」でもあり「WHAT型」でもある

オプティマイザはリレーショナル・データベースの中で最も重要な機能と言っても過言ではありません。正しい結果をより早く返すためのアプローチを最適化する（optimize）機能・プログラムがオプティマイザ（optimizer）です。

それは当然「HOW型」コンピュータの上で動くプログラムですが、「WHAT型」しての性格も色濃く持っています。それはSQL（Structured Query Language）がまさに「（求める）結果＝WHAT」の構造を記述するものだからです。

これはカーナビで経路検索をすることに似ています。例えば「日本橋から横浜ランドマークタワー」まで車で行きたい場合、Google Mapで検索すると

1. 首都高速1号羽田線 と 首都高速神奈川1号横羽線 経由：35.1km、46分（31分）
2. 首都高速3号渋谷線 と 第三京浜道路 経由：41.8km、49分（41分）
3. 第二京浜/国道1号線 経由：35.2km、1時間8分（50分）

という結果が返ってきました。

（イメージと記事の内容は異なります。）

この２点間を結ぶ道は無数にあります（遠回りして新宿駅経由のルートでも目的地には着くことができます）が

1. どんなにお金がかかっても最短時間で着くことができるルート
2. 有料道路でも若干安いルート
3. 有料道路を使わないルート

というような基準でそれぞれのルートを算出し（文字通り計算で求め）たのが上の結果です。ちなみにこれらは渋滞情報も加味されていて（カッコ）内は渋滞なしの場合の所要時間です。（これは実に興味深いことなので次回で取り上げます。）

カーナビで検索されたルートに該当するのが「アクセスパス」です。オプティマイザが最終的に１つに決定したアクセスパスに従って、実際のデータにアクセスされ、加工され、結果が返されます。

言い換えると、オプティマイザの役割は最適なアクセスパスを算出するところまでで、実際の物理的なI/Oやメモリ間操作などはオプティマイザの関知するところではありません。運転前にルートを検索することと実際にそのルートに従って車を運転することが違うことに相当します。

オプティマイザの計算量は膨大

オプティマイザは前述のように、決められたアルゴリズムに従って最適解を得ると言った面では「HOW型」と言えますが、ユーザの求める結果を実現するための「アクセスパス」を最終的に１つに決定するということでは「WHAT型」です。

２点間を結ぶルート検索であれば選択すべき経路はそれほど多くないのですが、複数のテーブルから求める結果を得るということは想像以上に大変なことです。

A、Bという２つのテーブルを結合して結果を得る場合、最初にAテーブルにアクセスしてその結果を基にBテーブルにアクセスすることを「A→B」と表現すると、A→BとB→Aという２通りのアクセスパスが存在します。

さらにA、B、Cの３つのテーブルでは、A→B、A→C、B→A、B→C、C→A、C→Bの６通りになります。（結合は原則的に２つのテーブルあるいは結果セット同士になります。）

テーブル数が増えるごとにアクセスパスは多くなり、簡単に説明すると（テーブル数）！：テーブル数の階乗となります。つまり１０個のテーブルを結合するSQL文の場合は実に 3,628,800通りとなってしまいます。この中から最適なものを１つだけ選択しなければならないのでオプティマイザの計算量は膨大なものとなってしまいます。

実は、Oracleのオプティマイザは300万通り以上の組み合わせを律儀に評価するようなことはしません。「OPTIMIZER_MAX_PERMUTATIONS」というOracle8から導入された初期化パラメータによって評価する組み合わせの上限値が決められています。（これは解析時間を短縮するための苦肉の策と思われます。）

Oracle8と8iではこの値は「80,000」でしたが、9i以降では「2,000」となり、さらに10g以降では隠しパラメータ「_optimizer_max_permutations」となったため基本的に変更しないパラメータとなってしまいました。

SQL> select
  2   a.ksppinm  "Parameter"
  3  ,b.ksppstvl "Value"
  4  from
  5   x$ksppi  a
  6  ,x$ksppcv b
  7  where a.indx    = b.indx
  8  and   a.ksppinm like '%optimizer_max_permutations%';

Parameter                      Value
------------------------------ ----------
_optimizer_max_permutations    2000

７個以上のテーブルを結合するとアクセスパス算出が不十分になる？

「OPTIMIZER_MAX_PERMUTATIONS（または _OPTIMIZER_MAX_PERMUTATIONS）」パラメータが2,000であることの意味を考えてみましょう。

前述のとおり複数テーブルを結合する組み合わせの数は「（テーブル数）！」となります。テーブル数６の場合6!=720、７の場合7!=5,040であるので、７つ以上のテーブルを結合する場合、すべての組み合わせを評価して真に最適なアクセスパスを算出する前に、オプティマイザが評価を諦めてしまう可能性があります。

以前、ある企業のコンサルティングを行った際「テーブルの結合は５つまでとする。」というルールを定めているのを目にしたことがあります。
これは恐らく本パラメータを意識したルールでなかなか興味深い考え方だなと記憶しているのですが、原則的には間違った発想だと思います。

一般的に５つのテーブルを結合するような複雑なクエリーを書くことは珍しいかもしれませんが

.....
from
 emp  e1
,emp  e2
,emp  e3
,dept d
.....

のように、FROM句の後に同じテーブルを複数記述するようなことは簡単にできてしまうので、５つという制限は意味のない足かせになるかもしれませんし、そもそも結合を減らすためにせっかく正規化したテーブルを非正規化するようなことは本末転倒です。

それではもし、正規化された７つ以上のテーブルをどうしても結合しなければならない場合はどうしたらよいでしょうか？

• LEADINGヒントやORDEREDヒントにより、FROM句の後に記述された順にテーブルが結合されるようオプティマイザに情報を与える。
  • デメリット：テーブル順を間違えると悪い結果をもたらす。統計情報が変動した場合どうする？
• PL/SQLでカーソルを定義し（例えば４テーブルのSELECT文）、カーソル・ループの中で残りのテーブルを参照する。
  • デメリット：想像のとおりプログラムが複雑になり、手間の割には成果が少ないかもしれない。
• 一時的に_OPTIMIZER_MAX_PERMUTATIONSパラメータの値を変更する。
  • デメリット：解析済みSQLがキャッシュアウトされる度にパラメータが変更できるか？実行計画を固定化する高度なスキルが必要。

いろいろ考えられるのですが、一長一短ありでなかなか単純ではありません。（あえて言えば３番目が一番スマートでしょう。）

次回へ

「オプティマイザ統計の保留」というあまり注目されていない機能を取り上げ（検証し）ようとしているのですが、オプティマイザについて語ると脱線してしまってなかなかたどり着けません。

次回「序章②」として、オプティマイザを理解する上で前提として押さえておきたいことを説明し、次々回で検証に入りたいと思います。