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

今週の名言

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

前回のおさらい

  • 統計情報公開属性（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エラーは発生しません。（今回は取得できないことを確認するためにテーブル単位で取得しました。）

まとめ

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

今回はここまで