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

今週の名言

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

前回のおさらい

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

確認項目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」に再設定する必要がある。

今日はここまで。

続く