今週の名言

「事を行うにあたって、いつから始めようかなどと考えているときには、すでに遅れをとっているのだ。」
クインティリアヌス

前々回のおさらい

前回は別のテーマだったので、前々回の内容をおさらいしておきます。

1. SQLトレースの設定
   • テーブル作成時に内部的にどのようなSQLが実行されているかを確かめるためにSQLトレースを事前に設定しておきます。
2. Identity Column付きテーブルの作成（２種類）
   • 「ALWAYS」と「BY DEFAULT ON NULL」それぞれのオプションでテーブルを作成します。
3. USER_TAB_IDENTITY_COLSの確認
   • TABLE_NAME列とSEQUENCE_NAME列からテーブルと順序の関係を確認します。
4. テーブルの削除
   • ２つのテーブルのうち１つをパージ・オプション付き、もう片方をパージ・オプションなしで削除します。
5. SQLトレースの終了
   • SQLトレースの取得を終了し、1 〜 5 の間に実行されたSQLの情報を確認する準備を行います。
6. USER_TAB_IDENTITY_COLSの定義を確認する。
   • SQLトレースの確認を行うため、ビューを構成する実表を予め確かめておきます。
   • 「sys.idnseq$」表および「sys.col$」表が調査対象であることを確認しました。

sys.idnseq$

SQLトレースを直接確認する前にtkprofで整形した結果から調査対象の実表を検索します。

tkprofの結果から

まず、Identity Columnの実装で最も重要と思われる sys.idnseq$表を探すと以下のInsert文が見つかりました。

SQL ID: cdzktrqhtspgg Plan Hash: 0

insert into idnseq$(obj#, intcol#, seqobj#, startwith)
values
 (:1, :2, :3, :4)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          2          6           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          6           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  IDNSEQ$ (cr=1 pr=0 pw=0 time=125 us)

２つのテーブルを作成したので、Parse, Executeともに２回ずつ実行されています。
また、バインド変数が４つあることがわかります。

次にSQLトレースを直接参照してバインド変数の内容を確認します。

SQLトレースの内容

tkprofの結果から確認した SQLID=cdzktrqhtspgg によって、SQLトレースの中を探します。
先ほど確認したInsert文を含む以下の部分が見つかりました。

PARSING IN CURSOR #140330607060416 len=78 dep=1 uid=0 oct=2 lid=0 tim=118633564085 hv=2711377391
 ad='722200c8' sqlid='cdzktrqhtspgg'
insert into idnseq$(obj#, intcol#, seqobj#, startwith) values (:1, :2, :3, :4)
END OF STMT
PARSE #140330607060416:c=1000,e=220,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=118633564084
BINDS #140330607060416:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=7fa143f98fb8  bln=22  avl=04  flg=05
  value=91832
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7fa143f98fd0  bln=22  avl=02  flg=01
  value=1
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=7fa143f98fe8  bln=22  avl=04  flg=01
  value=91833
 Bind#3
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7523f68e  bln=22  avl=02  flg=09
  value=1
EXEC #140330607060416:c=0,e=634,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,plh=0,tim=118633564764
STAT #140330607060416 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  IDNSEQ$ (cr=1 pr=0 pw=0 time=160 us)'
CLOSE #140330607060416:c=0,e=3,dep=1,type=0,tim=118633564815

OBJID=91832がobj#に、OBJID=91833がseqobj#にそれぞれ代入されていることがわかります。

これにより、テーブルと順序の紐付けができています。

Delete文を探す

次に、テーブルをパージ・オプション付きで削除した際に発行される、Idenitty Column に関するDelete文を探してみましょう。

PARSING IN CURSOR #140330605974448 len=32 dep=1 uid=0 oct=7 lid=0 tim=118957259659 hv=933734747 ad='724c30b8' sqlid='59vjj34vugaav'
delete from obj$ where obj# = :1
END OF STMT
PARSE #140330605974448:c=0,e=556,p=0,cr=0,cu=2,mis=1,r=0,dep=1,og=4,plh=0,tim=118957259659
BINDS #140330605974448:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fa143d49440  bln=22  avl=04  flg=05
  value=91833
EXEC #140330605974448:c=2000,e=1700,p=0,cr=2,cu=11,mis=1,r=1,dep=1,og=4,plh=225269600,tim=118957261436
STAT #140330605974448 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  OBJ$ (cr=2 pr=0 pw=0 time=410 us)'
STAT #140330605974448 id=2 cnt=1 pid=1 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=37 us cost=2 size=103 card=1)'
CLOSE #140330605974448:c=0,e=2,dep=1,type=3,tim=118957261516
FETCH #140330606193888:c=0,e=10,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=2429035722,tim=118957261705
STAT #140330606193888 id=1 cnt=1 pid=0 pos=1 obj=106 op='TABLE ACCESS BY INDEX ROWID BATCHED IDNSEQ$ (cr=3 pr=0 pw=0 time=13 us cost=0 size=26 card=1)'
STAT #140330606193888 id=2 cnt=1 pid=1 pos=1 obj=107 op='INDEX RANGE SCAN I_IDNSEQ1 (cr=2 pr=0 pw=0 time=14 us cost=0 size=0 card=1)'
CLOSE #140330606193888:c=0,e=12,dep=1,type=1,tim=118957261795
=====================
PARSING IN CURSOR #140330606969696 len=39 dep=1 uid=0 oct=7 lid=0 tim=118957261833 hv=1857173601 ad='74087130' sqlid='7h2gtvxrb4f31'
delete from sys.idnseq$ where obj# = :1
END OF STMT
PARSE #140330606969696:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3651899373,tim=118957261832
BINDS #140330606969696:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fa1445eea30  bln=22  avl=04  flg=05
  value=91832
EXEC #140330606969696:c=0,e=141,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,plh=3651899373,tim=118957262014
STAT #140330606969696 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  IDNSEQ$ (cr=1 pr=0 pw=0 time=83 us)'
STAT #140330606969696 id=2 cnt=1 pid=1 pos=1 obj=107 op='INDEX RANGE SCAN I_IDNSEQ1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=26 card=1)'
CLOSE #140330606969696:c=0,e=9,dep=1,type=1,tim=118957262089

最初に、OBJID=91833である順序がobj$表から削除され、OBJID=91832のエントリがsys.idnseq$表から削除され、Identity Columnの定義が削除されたことがわかります。

オプションはどのように管理されているのか？

USER_TAB_IDENTITY_COLSの定義の中で、decodeとbitand関数を使った部分があります。（以下のSQL文の4-6行目）
この部分はオプションを管理している部分です。ポイントは sys.obj$表のPROPERTY列にあります。

わかりやすいように、PROPERTY列を並べてどのような値が格納されているかを確認してみました。

SQL> select
  2    o.name TABLE_NAME
  3  , c.name COLUMN_NAME
  4  , decode( bitand(c.property, 137438953472 + 274877906944)
  5          , 137438953472, 'ALWAYS'
  6          , 274877906944, 'BY DEFAULT') GENERATION
  7  , c.property
  8  , so.name SEQUENCE_NAME
  9  from
 10    sys.idnseq$ i
 11  , sys.obj$ o
 12  , sys.col$ c
 13  , sys.seq$ s
 14  , sys.obj$ so
 15  where o.owner# = userenv('SCHEMAID')
 16  and o.obj# = i.obj#
 17  and c.intcol# = i.intcol#
 18  and c.obj# = i.obj#
 19  and s.obj# = i.seqobj#
 20  and so.obj# = i.seqobj#;

TABLE_NAME  COLUMN_NAME  GENERATION      PROPERTY SEQUENCE_NAME
----------- ------------ ---------- ------------- ---------------
T1          C11          ALWAYS      171798691840 ISEQ$$_91838
T2          C21          BY DEFAULT  377957122048 ISEQ$$_91840

以下は、PROPERYT列の十進数を二進数に変換してbitand演算を行ったところを説明したものです。

123456789012345678901234567890123456789
---------+---------+---------+---------
010100000000000000000000000000000000000 171798691840
110000000000000000000000000000000000000 137438953472 + 27487790694
---------------------------------------
010000000000000000000000000000000000000 137438953472 : 'ALWAYS'
 *

101100000000000000000000000000000000000 377957122048
110000000000000000000000000000000000000 137438953472 + 27487790694
---------------------------------------
100000000000000000000000000000000000000 274877906944 : 'BY DEFAULT'
*

この例では39bitのbit列における上位2bit目に1が立っていれば「ALWAYS」、1bit目に1が立っていれば「BY DEFAULT」となっていることがわかります。

データディクショナリ・ビューの定義を確認すると、このようにプロパティ列にビットフラグを立てて属性を管理しているのをよく見かけます。

あまり実業務には役に立たない雑学ですが、Oracleの深いところを理解するには興味深いきっかけになるかもしれません。

次回は、もう少し検証っぽいことをしてみようと思います。

続く

今週の名言

「人にものを教えることはできない。できることは、相手のなかにすでにある力を見いだすこと、その手助けである。」
ガリレオ・ガリレイ

今回は小ネタで

前回に引き続き「Identity Column」のつもりでしたが、事情により今回は別のトリビア的なネタを紹介します。

アラートログ監視の難しさ

Oracleの運用監視において、アラートログの中の「ORA-」で始まるメッセージを通知するということは当たり前に行われていると思いますが、エラー・メッセージ・マニュアルで「処置: 処置は必要ありません。」のように説明されているアラートが真夜中にエスカレーションされた経験はないでしょうか？

Oracleにある程度詳しい人であれば、経験から「これはとりあえず様子見でよいかな。」というような判断を下すことができるかもしれませんが、２４時間待機のオペレータにそこまでの判断を求めることは難しいのではないかと思います。

そこで、「このメッセージは監視対象から外す」というようなルールを決めているところは多いと思います。
私もそのような取り組みに関わったことがあるのですが、システムごとに発生するエラーの傾向が異なるので、どのシステムでも使える普遍的な「監視対象メッセージ一覧」を作るのは現実的にはかなり難しいのではないだろうかという実感を持っています。

ところでOracleエラーっていくつあるの？

いつまで経っても「監視対象メッセージ一覧」を作ることができないことに業を煮やし、Oracleエラーが一体いくつ存在しているのか気になって調べてみたことがあります。
エラー・メッセージ・マニュアルを頭から数えるのはかなり心が折れる作業になりそうなので、以下のPL/SQLスクリプトを作って調べてみました。

Oracle 11.2.0.4の場合

SQL> DECLARE
  2   sql_code NUMBER;
  3   str_pos  NUMBER;
  4   cnt      NUMBER;
  5  BEGIN
  6   str_pos := 0;
  7   cnt := 0;
  8   FOR sql_code IN 1..99999 LOOP
  9    str_pos := INSTR(SQLERRM(-sql_code),'not found;',1,1);
 10    IF str_pos = 0 THEN
 11     cnt := cnt + 1;
 12    END IF;
 13   END LOOP;
 14   DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 15  END;
 16  /
Nuber of Error Code : 54387

PL/SQL procedure successfully completed.

54,387個のメッセージという結果が得られました。

大訂正！！(2015.05.23)

Oracleエラーがこんなに多いはずがないとどうも腑に落ちなかったのですが、とんでもない間違いをしていたことに気付きました。

SQL> BEGIN
  2   DBMS_OUTPUT.PUT_LINE(SQLERRM(-99999));
  3  END;
  4  /
99999: non-ORACLE exception

PL/SQL procedure successfully completed.

この「non-ORACLE exception」をカウントに入れてました。orz
これでは純粋なORA-エラーになりません。

とりあえず修正版

この不要なメッセージも除外するようにコードを書き直して実行してみたのが以下の結果です。

SQL> DECLARE
  2   sql_code NUMBER;
  3   cnt      NUMBER :=0;
  4   err_msg  VARCHAR2(2000);
  5  BEGIN
  6  FOR sql_code IN 1..99999 LOOP
  7    err_msg := SQLERRM(-sql_code);
  8    IF err_msg NOT LIKE '%not found;%' AND err_msg NOT LIKE '%non-ORACLE exception%' THEN
  9     cnt := cnt + 1;
 10    END IF;
 11  END LOOP;
 12  DBMS_OUTPUT.PUT_LINE('Nuber of Error Code : ' || cnt);
 13  END;
 14  /
Nuber of Error Code : 19923

PL/SQL procedure successfully completed.

だいぶ、数が減りました。

実は、まだ無効なメッセージを含んでいる

ところが、以下のように番号は予約されているけれどメッセージが空欄のものが相当あることがわかりました。

SQL> BEGIN
  2   DBMS_OUTPUT.PUT_LINE(SQLERRM(-12428));
  3   DBMS_OUTPUT.PUT_LINE(SQLERRM(-12498));
  4   DBMS_OUTPUT.PUT_LINE(SQLERRM(-12499));
  5  END;
  6  /
ORA-12428:
ORA-12498:
ORA-12499:

PL/SQL procedure successfully completed.

こんなのも一緒にカウントしてたら正確なエラーの数はわかりませんね。

結局、SQLERRM関数を使って調査することは諦めることにします。

従って、上で挙げたエラー数は間違いですので無視してください。お騒がせして申し訳ありません。

その代わりもっとよい方法があるのでそちらを別途紹介することにします。

ORAエラーはいくつある？訂正しました。

今週の名言

「一時間の浪費を何とも思わない人は、まだ人生の価値を何も見つけていない。」
チャールズ・ダーウィン

前回のまとめ

12c新機能である「Identity Column」（日本語マニュアルでは「IDENTITY列」）の検証をしています。
前回はこの機能の基本的な動作を確認しました。

テーブル作成時にID列（主キー列）にIdentity Columnの設定を行えば、暗黙的にシーケンス・オブジェクト（順序ジェネレータ、以下順序）が作成されます。

そして、テーブルにレコードをインサートする場合、ID列にどのような値を設定するかを全く意識することがなく（つまりSQL文実行者としては結果的にNullを指定してインサート）ても、レコードを一意に識別できるシーケンス番号が自動的に払い出されインサートが行われます。（サロゲート・キーの生成）

これは、ANSI準拠の他RDBMS（SQL Server、DB2等）からの移行を容易にするものでもあります。

テーブルと順序は互いに独立したオブジェクトだけど

前回、Identity Columnを設定したテーブルを削除（Drop）しても順序は順序として機能することを確認しました。
また、順序の名称も「ISEQ$$_91795」のようにテーブル名とは全く関係のないものになっているので、例えばEMP表の順序であれば「SEQ_EMP」のような名称が自然だと思っていた私にはこの機能は「ちょっと使えないんじゃないか？」という第一印象がありました。

ところが、テーブルは削除しても実体はリサイクルビンといういわゆるゴミ箱で管理されていますが、このリサイクルビンを明示的にパージ（空に）したところ、該当する順序も自動的に削除されていたので、Identity Columnで作成された順序と元テーブルにはこれらを紐付ける何らかの仕組みがあるのではと気付きました。

実は、USER_TAB_IDENTITY_COLSというディクショナリ・ビューでこの関係性を確認することができるのですが、今回はSQLトレースを取得することでどのような内部表にこの情報が格納されているのかを探ってみたいと思います。

1. SQLトレースの準備

それでは、まずSQLトレースを取得する準備をします。トレースファイルはtraceディレクトリ以下に大量に出力されていますので、「IDNCOL」という識別子を付けて該当するファイルが簡単にわかるようにしておきます。
トレースファイルのフルパス名称はV$DIAG_INFOから確認することができます。

SQLトレースは、バインド変数とその内容を確認したいので、レベル４を指定します。

SQL> SHOW USER

ユーザーは"TEST"です。

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='IDNCOL';

セッションが変更されました。

SQL> SELECT NAME,VALUE FROM V$DIAG_INFO
  2  WHERE NAME = 'Default Trace File';

NAME                 VALUE
-------------------- ---------------------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_31051_IDNCOL.trc

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

セッションが変更されました。

2. Identity Column付きテーブルの作成（２種類）

前回ご紹介した要領でテーブルを作成しますが、「ALWAYS」と「BY DEFAULT ON NULL」でそれぞれT1表、T2表を作成します。
また、テーブル作成後にそれぞれ主キーを作成します。

SQL> CREATE TABLE t1
  2  (c11 NUMBER       GENERATED ALWAYS AS IDENTITY
  3  ,c12 VARCHAR2(10)
  4  );

表が作成されました。

SQL> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (c11);

表が変更されました。

SQL> CREATE TABLE t2
  2  (c21 NUMBER       GENERATED BY DEFAULT ON NULL AS IDENTITY
  3  ,c22 VARCHAR2(10)
  4  );

表が作成されました。

SQL> ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (c21);

表が変更されました。

SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE
  2  FROM USER_OBJECTS
  3  ORDER BY OBJECT_ID;

OBJECT_NAME   OBJECT_ID OBJECT_TYPE
------------ ---------- -----------------------
T1                91832 TABLE
ISEQ$$_91832      91833 SEQUENCE
PK_T1             91834 INDEX
T2                91835 TABLE
ISEQ$$_91835      91836 SEQUENCE
PK_T2             91837 INDEX

6行が選択されました。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91832               1         20           1
ISEQ$$_91835               1         20           1

USER_OBJECTSを見てわかるように、順序の名称「ISEQ$$_91832」の数字部分は元表のオブジェクトIDです。
そして順序自身のオブジェクトIDは+1した「91833」となっています。
テーブルと順序の作成はCREATE TABLE時１トランザクションで実行されるので、OBJECT_IDは必ず続き番号になるようです。（ALTER TABLE時を除く）

このような仕組みが分かっていればテーブルと順序の関係性はすぐにわかるのですが、これがもっと便利にわかるのが「USER_TAB_IDENTITY_COLS」ビューです。

3. USER_TAB_IDENTITY_COLS

このビューの説明はALL_TAB_IDENTITY_COLSを参照してください。

SQL> SELECT TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,SEQUENCE_NAME,IDENTITY_OPTIONS
  2  FROM USER_TAB_IDENTITY_COLS
  3  ORDER BY TABLE_NAME;

TABLE_NAME  COLUMN_NAME  GENERATION SEQUENCE_NAME   IDENTITY_OPTIONS
----------- ------------ ---------- --------------- ----------------------------------------
T1          C11          ALWAYS     ISEQ$$_91832    START WITH: 1, INCREMENT BY: 1, MAX_VALU
                                                    E: 9999999999999999999999999999, MIN_VAL
                                                    UE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
                                                    DER_FLAG: N
T2          C21          BY DEFAULT ISEQ$$_91835    START WITH: 1, INCREMENT BY: 1, MAX_VALU
                                                    E: 9999999999999999999999999999, MIN_VAL
                                                    UE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
                                                    DER_FLAG: N

このビューを確認すれば、TABLE_NAME列とSEQUENCE_NAME列からテーブルと順序の関係性を知ることができます。

GENERATION列からは「ALWAYS」「BY DEFAULT (ON NULL)」のオプションの違いを確認することができます。

さらに、IDENTITY_OPTIONS列によって順序定義の詳細がわかります。

4. テーブルの削除

次にT1表をパージ・オプション付きで、T2表をパージ・オプションを付けないで削除します。

SQL> DROP TABLE t1 PURGE;

表が削除されました。

SQL> DROP TABLE t2;

表が削除されました。

SQL> SELECT SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  FROM USER_SEQUENCES;

SEQUENCE_NAME   INCREMENT_BY CACHE_SIZE LAST_NUMBER
--------------- ------------ ---------- -----------
ISEQ$$_91835               1         20           1

SQL> SELECT TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,SEQUENCE_NAME,IDENTITY_OPTIONS
  2  FROM USER_TAB_IDENTITY_COLS
  3  ORDER BY TABLE_NAME;

TABLE_NAME  COLUMN_NAME  GENERATION SEQUENCE_NAME   IDENTITY_OPTIONS
----------- ------------ ---------- --------------- ----------------------------------------
BIN$Em/lpxe C21          BY DEFAULT ISEQ$$_91835    START WITH: 1, INCREMENT BY: 1, MAX_VALU
feUvgUwoLqM                                         E: 9999999999999999999999999999, MIN_VAL
B1cw==$0                                            UE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, OR
                                                    DER_FLAG: N

パージ・オプションを付けないで削除した場合、テーブルは「BIN$Em/lpxefeUvgUwoLqMB1cw==$0」のように「BIN$」で始まる名前にRenameされます。
ただし、テーブルのデータを含めた実体はまだ存在していますので、順序との関係性が維持されているわけです。

5. SQLトレースの終了

以下の要領で、SQLトレースを終了します。さらにコマンドプロンプトからtkprofを実行し、トレースファイルを整形しておきます。

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

セッションが変更されました。

-----
$ cd /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace
$ tkprof ora12c_ora_31051_IDNCOL.trc IDNCOL.txt

6. USER_TAB_IDENTITY_COLSの定義を確認する。

SQLトレースの解析は次回にしますが、今回はそのためにUSER_TAB_IDENTITY_COLSの定義を予め確認しておくことにします。

SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'USER_TAB_IDENTITY_COLS';

TEXT
--------------------------------------------------------------------------------
select o.name, c.name,
       decode(bitand(c.property, 137438953472 + 274877906944),
                     137438953472, 'ALWAYS',
                     274877906944, 'BY DEFAULT'),
       so.name,
       'START WITH: '     || i.startwith ||
       ', INCREMENT BY: ' || s.increment$ ||
       ', MAX_VALUE: '    || s.maxvalue ||
       ', MIN_VALUE: '    || s.minvalue ||
       ', CYCLE_FLAG: '   || decode (s.cycle#, 0, 'N', 1, 'Y') ||
       ', CACHE_SIZE: '   || s.cache ||
       ', ORDER_FLAG: '   || decode (s.order$, 0, 'N', 1, 'Y')
from sys.idnseq$ i, sys.obj$ o, sys.col$ c,
     sys.seq$ s, sys.obj$ so
where o.owner# = userenv('SCHEMAID')
and o.obj# = i.obj#
and c.intcol# = i.intcol#
and c.obj# = i.obj#
and s.obj# = i.seqobj#
and so.obj# = i.seqobj#
;

12cから新しくできた「sys.idnseq$」表という実表が鍵を握っているようです。

また、「ALWAYS」「BY DEFAULT (ON NULL)」のオプションはsys.col$.property列に格納された情報から判断することができるようです。

次回はこの辺を詳しく見てみましょう。

続く

今週の名言

「自分がわずかなことしか知らないということを知るためには、多くのことを知る必要がある。」
モンテーニュ

Oracle 12c新機能について

今週からOracle 12cの新機能を検証します。

実はOracle 12c 発表されたと思ったらリリース１(R1)はもうターミナル・リリースになるようです。（Database In-Memoryなど大幅に機能拡張：Oracle Database 12cR1の最新パッチセット12.1.0.2がリリースされました）

最近のOracle RDBMSは、R1のうちはイノベーター、アーリーアダプターまでが採用する傾向にあって、R2以降本格的にアーリーマジョリティ他に普及していくような都市伝説（？）があるように思います。

私が関わっている案件もほとんどが11gR2で、本番環境で12cに移行するというものをあまり聞いたことはありません。エンドユーザも状況を見ているのではないかという印象を持っています。

従って、12c新機能ネタはまだ新鮮味がある気がしますので、筆者が興味を持った機能を今後積極的に紹介していきたいと思います。

「Identity Column」とは？

12c新機能というと、プラガブル・データベースやDatabase In-Memoryなどが注目されていますが、このブログではどちらかというとあまり陽の当たらない機能で面白そうなものを紹介していこうと思います。

それも、単に機能を紹介するのではなく、実際に使う立場で検証してみたいと思います。

さて「Identity Column」ですが、ANSI準拠のIDENTITY Columnを実装したもので、平たく言うとInsert文における自動生成キー取り出し機能のことです。

マニュアルは以下を参照してください。

Oracle® Database新機能ガイド
12cリリース1 (12.1)
B71327-03
2.1.6.3 IDENTITY列

この機能は上記マニュアルの記述に先行した

• 2.1.6.1 Oracleの順序に基づく列のデフォルト値
• 2.1.6.2 明示的なNULL挿入での列のDEFAULT値

の組み合わせで使用する機能であるとも言えます。

「百聞は一見に如かず」とりあえず実際のDDL文から確認していきましょう。

基本動作の確認

検証環境

検証環境はOracle Linux 6.5とOracle12R1です。

SQL> !uname -r
3.8.13-16.2.1.el6uek.x86_64

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

CREATE TABLE文

検証用のTESTユーザはすでに作成済みです。CREATE SEQUENCE権限が付与されていることが必要です。

SQL> show user
ユーザーは"TEST"です。

SQL> create table t1
2 (c1 number GENERATED BY DEFAULT ON NULL AS IDENTITY
3 ,c2 varchar2(10)
4 );

表が作成されました。

「GENERATED 〜 AS IDENTITY」がIDENTITY句と呼ばれる記述ですが、「BY DEFAULT ON NULL」はオプションです。これを省略するとデフォルトの「ALWAYS」となります。（先で機能を説明するため、あえてこの設定にします。）

シーケンス確認

IDENTITY句を指定すると、裏で自動的にシーケンス・オブジェクト（順序）が作られます。
USER_SEQUENCEからその状況を確認しておきましょう。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  ,CYCLE_FLAG,ORDER_FLAG
  3  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER C O
------------------------- ------------ ---------- ----------- - -
ISEQ$$_91795                         1         20           1 N N

ISEQ$$_91795というシステムが命名した順序が作成されているのがわかります。
キャッシュ・サイズはデフォルトの「20」です。

１行インサートする。

それでは、まずは１行インサートしてみましょう。C1列には何も指定せずC2列のみ値を設定してインサートします。
さらに、直後の順序の状況を確認します。

SQL> insert into t1(c2) values ('abc');

1行が作成されました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795                         1         20          21

LAST_NUMBERが 1+20(CACHE_SIZE)となっています。つまり最初の生成で1〜20までの数字がキャッシュされ、このインサートでは「1」が払い出されます。（次の生成では21〜40までがキャッシュされる予定です。）

もう１行インサートする。

さらにもう１行インサートします。

SQL> insert into t1(c2) values ('xyz');

1行が作成されました。

SQL> select c1,c2 from t1;

        C1 C2
---------- ----------
         1 abc
         2 xyz

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795                         1         20          21

２行作成されていることがわかります。キャッシュされている番号から払い出されているため順序の内容は変化していません。

一度ロールバックし、再度２行インサートする。

２行インサートした状態でコミットせずにロールバックすると、０行となります。
ここで、先ほど行った２行のインサートを行いC1列にどのような値が入るか確認します。
さらに今度はコミットします。

SQL> rollback;

ロールバックが完了しました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795                         1         20          21

SQL> insert into t1(c2) values ('abc');

1行が作成されました。

SQL> insert into t1(c2) values ('xyz');

1行が作成されました。

SQL> select c1,c2 from t1;

        C1 C2
---------- ----------
         3 abc
         4 xyz

SQL> commit;

コミットが完了しました。

テーブルはロールバックされましたが、順序「ISEQ$$_91795」には何の影響も与えません。
キャッシュされている次の番号「3」と「4」が続けて払い出されているのがわかります。

テーブルをドロップして順序の状況を確認する。

SQL> drop table t1;

表が削除されました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91795                         1         20          21

SQL> select ISEQ$$_91795.nextval from dual;

   NEXTVAL
----------
         5

テーブルを削除しても、順序はそのまま残っていることがわかります。
しかも、キャッシュされているシーケンス番号から次の番号が何の問題もなく払い出せることが確認できます。

リサイクルビンのパージ

テーブルを削除してもリサイクルビンで管理されるだけなので、念のためリサイクルビンを空にして順序の状況を確認します。

SQL> purge recyclebin;

リサイクルビンがパージされました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

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

リサイクルビンを空にして、つまり本当にテーブルを削除すると、テーブル作成時に自動的に作成された順序も削除されるようです。

「BY DEFAULT ON NULL」の意味を確認する。

「IDENTITY Column」の特徴は、値を指定しないで（つまりNullで）インサートしても自動的に連番が設定されることができるというものですが、明示的に値を設定することもできるいうのが「BY DEFAULT ON NULL」オプションです。

結論から言うと、どちらでもよいというのは意味がなくむしろ別の問題の原因となるのでこのオプションを指定すべきではないと考えます。
デフォルトの「ALWAYS」を指定すべきでしょう。

以下に、その理由を説明する検証を行っていきます。

キャッシュサイズを最小にする。

順序のキャッシュサイズをデフォルトの20にしておくと、次に払い出されるシーケンス番号が見えにくくなるので、キャッシュサイズを最小にしてみます。（パフォーマンス上の理由からキャッシュサイズはデフォルトの20より小さい値にすべきではありませんが、検証上あえて小さくします。）

SQL> create table t2
  2  (c1 number       GENERATED BY DEFAULT ON NULL AS IDENTITY (CACHE 1)
  3  ,c2 varchar2(10)
  4  );
create table t2
*
行1でエラーが発生しました。:
ORA-04010: CACHEには1より大きい値を指定する必要があります

おっと！「CACHE 1」という設定は許されていないようです。

それでは「NOCACHE」でやり直します。

SQL> create table t2
  2  (c1 number       GENERATED BY DEFAULT ON NULL AS IDENTITY (NOCACHE)
  3  ,c2 varchar2(10)
  4  );

表が作成されました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798                         1          0           1

今度はうまくいきました。

C1列に主キーを設定する。

C1列は本来であればID列であるので主キーを設定します。

SQL> alter table t2 add constraint pk_t2 primary key (c1);

表が変更されました。

C1列に値を指定せずにインサートする

C1列は順序が設定されている列なので、値を指定せずにインサートするのが通常のインサートです。
とりあえず最初のレコードをインサートします。

SQL> insert into t2(c2) values ('abc');

1行が作成されました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798                         1          0           2

SQL> select c1,c2 from t2;

        C1 C2
---------- ----------
         1 abc

C1列に値を指定してインサートする。

今度は、明示的にC1列に値を指定してインサートしてみます。

SQL> insert into t2(c1,c2) values (2,'xyz');

1行が作成されました。

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798                         1          0           2

特に問題なくインサートできます。
（ALWAYSの場合はここでエラーとなります。）
また、LAST_NUMBERが「2」のままであることも注目すべきです。つまり次に払い出されるシーケンス番号は「2」です。

今度はC1列に値を指定しないでインサートする。

SQL> insert into t2(c2) values ('xyz');
insert into t2(c2) values ('xyz')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(TEST.PK_T2)に反しています

SQL> select c1,c2 from t2;

        C1 C2
---------- ----------
         1 abc
         2 xyz

主キーの重複エラーが発生します。
なぜなら、既に「C1=2」となるレコードをインサートしているので、順序が払い出した「2」が重複してしてしまいエラーとなりました。

もう一度C1列に値を指定しないでインサートする。

ORA-00001エラーが発生するとそのレコードはインサートされませんが、もう一度値を指定しないでインサートするレコードはどうなるでしょうか？

SQL> insert into t2(c2) values ('def');

1行が作成されました。

SQL> select c1,c2 from t2;

        C1 C2
---------- ----------
         1 abc
         2 xyz
         3 def

SQL> select SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE,LAST_NUMBER
  2  from user_sequences;

SEQUENCE_NAME             INCREMENT_BY CACHE_SIZE LAST_NUMBER
------------------------- ------------ ---------- -----------
ISEQ$$_91798                         1          0           4

今度は、次に払い出されるシーケンス番号が「3」となり重複しないので、エラーは発生しません。

結論：IDENTITY列は「GENERATED ALWAYS AS IDENTITY」で設定すべき。

「Identity Column」のメリットあるいは注意点に関する考察は次回以降に回したいと思いますが、今回確認した中での結論はID列に値を指定する選択を許す「BY DEFAULT ON NULL」オプションは使うべきではないということです。
言い換えると「BY DEFAULT ON NULL」とは、値を指定しなければ（Null）順序から番号を払い出し、指定すればその値でインサートするという仕様と考えることもできます。
値を指定するとエラーとなるデフォルトの「ALWAYS」を使うべきだと思います。

create table t1
(c1 number       GENERATED ALWAYS AS IDENTITY  -- (ALWAYS)は省略可
,c2 varchar2(10)
);

続く

今週の名言

「人は日本の歴史に50ページ書いてもらうより､世界の歴史に1ページ書いてもらうことを心掛けねばならぬ｡」
後藤新平

シリーズまとめ

「オプティマイザ統計の保留」について検証してきた今回のシリーズも序章を含めると１０回目になりました。

この機能は他にもいろいろ検証して確認したいことはあるのですが、他のテーマも追いかけたいので区切りがよいところでまとめておきたいと思います。

新しい機能にどう向き合うか

私はITというものに対して、ウンチクを語る対象ではなく道具としてビジネスに貢献するものでなければならないという強い思いがあります。

従ってある新機能を目にした時には「これは何の役にたつのだろうか？」とか「あの問題に対してよい解決策になるのではないか？」という視点で確認してみたいという気持ちになります。

Oracle10g以降ルールベース・オプティマイザ（RBO）がサポートされなくなりましたが、クルマがマニュアル・シフトからオートマチック・シフトになったのと同じくらいのインパクトがあったのではないでしょうか。

パラメータ・チューニングやヒント句を駆使して性能改善をバリバリ行ってきたエンジニアにとって、Oracle10gは大きな変換点だったのではないかと思います。
先日もある飲み会で「Oracle9iは結構面白いバージョンだった。」というような意見を一人のベテランエンジニアから聞きました。

Oracle10g以降自動化が進んで、DBAが手を動かして問題解決するという機会は確かに減りましたが、自動化を過信したいわゆる「自動化の落とし穴」という問題も深刻になってきました。

このシリーズは「自動化の落とし穴」にまつわる混乱を「オプティマイザ統計の保留」の検証（序章②）で紹介するところから始めました。

コストベース・オプティマイザ（CBO）というそれまでとは違った仕組みに対して、実行計画が意図せず急変してしまったと言う理由で、統計情報を取ることに消極的であったりヒント句で実行計画をガチガチに固めてしまっていたりする例を未だによく見かけます。

もうすぐOracle12c R2がリリースされるという現在となっては「オプティマイザ統計の保留」はすでに新機能とは呼べなくなっているかもしれませんが、CBOの仕組みをよく理解し、この機能を活用することによって「失敗のない運用」を行うことは十分可能であると思います。

「オプティマイザ統計の保留」の特徴

それではシリーズを振り返ってこの機能の特徴を整理していきます。

1. 保留統計情報は実行計画に影響を与えない

実行計画は公開されたディクショナリ統計情報によって算出されます。従ってプライベート・エリアに格納されディクショナリ統計を更新しない保留統計情報は実行計画に何ら影響を与えることがないというのが一番の特徴です。

一度算出された実行計画を変動させる要因がないということは、実行計画は事実上固定されます。

2. 保留統計情報は履歴を持たない

保留統計は履歴を持ちません。これは以下のような２つの意味を持ちます。

• 最新の統計情報のみが保持される
• プライベート・エリア（SYSAUX表領域）を圧迫しない

定期的に統計情報を取得しても、古い統計情報の履歴を削除する必要はありません。

3. セッション単位で保留統計情報を使った実行計画が確認できる

OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをセッション単位で一時的に「TRUE」に設定し、SQLを実行すると保留統計情報を使用した実行計画が作成されます。

これをExplain PlanやSQL*PlusのAutotrace機能で確認することで、公開統計情報を使用した実行計画と比較することができます。

4. 保留統計情報は手動で公開することができる

もし、保留統計情報を使用した実行計画の方が優れていることを確認できれば、DBMS_STATS.PUBLISH_PENDING_STATSプロシージャを実行することにより任意のタイミングで保留統計情報を公開することができます。

これにより、より優れた実行計画を永続的に使用することができます。

5. 保留統計情報はStandard Editionでも使うことができる

今まで触れてこなかったのですが、この機能はStandard Editionでも使用することができます。

Enterprise EditionではSQL計画管理（SPM:SQL Plan Management）により、統計情報に左右されない安定した実行計画の選択を行うことができます。

SPMのように高度な実行計画の管理はできませんが、SEでも「オプティマイザ統計の保留」によって同じような運用を行うことができます。（手動で運用する分のコストはかかります。）

ベスト・プラクティス

それでは、「オプティマイザ統計の保留」を活用した運用のベスト・プラクティスを考えてみましょう。

もちろん、環境によって個別の要件があるので、柔軟に対応することは重要です。

1. 初期状態では統計情報は「公開」しておく

全く統計情報を取得しない状態で「保留」にすることは可能ですが、統計情報を取得してそれを使用した実行計画が作成されるまでは「公開」のままにしておいた方がよいかもしれません。

2. 実行計画に問題がないことを確認し、テーブル単位で公開属性を「保留」に変更する

公開属性をテーブル単位で「保留」に変更すれば、少なくともそのテーブルに関する実行計画は新たな統計情報によって不安定になることなく固定されます。

ただし、一つのテーブルは多くのSQL文で使用されていますので、あるテーブルの属性変更がどのSQLに関係しているのかを把握するためには、以前このブログで紹介した「V$SQL_PLANでCRUD表モドキを作ってみる②」を参考にして調査をしてもよいかもしれません。

3. 定期的に統計情報を取得して評価する

公開属性が「保留」になっていれば、どんなに統計情報を取得したとしても実行計画が変更されることはありません。従って統計情報を定期的に取得することは継続すべきであると考えます。

これは自動統計情報収集機能にまかせてもよいし、DBAが任意にスケジュールしてもよいかもしれません。

「公開」と「保留」統計情報を比較し、より正しい実行計画が得られることが明らかであれば、その保留統計を都度公開していけばよいのです。

4. 統計情報のロックをうまく使い分ける

揮発性の高いテーブル、つまり一日中に頻繁にTRANCATEが実行されたり、バルク・インサートの対象となっているような、すなわち定期的な統計情報の取得でSELECT時使用される正確な統計情報を収集することができないテーブルに対しては、保留統計情報を使用しても最適な結果が得られないことが考えられます。

そのような場合は、統計情報を削除した後にロックし統計情報がない状態で運用した方がよいかもしれません。

このようなテーブルに対しては、SQL文実行時に動的統計が自動的に収集されます。（ダイナミック・サンプリング）

統計情報の「保留」と「ロック」を必要に応じて使い分けることでより最適な運用を図ることができます。

今回でこのシリーズは終わりにしますが、まだ検証していないことも沢山ありますので、またこのテーマを再開させるかもしれません。

次回は

次回は12cのある新機能に注目して検証していきたいと思います。

今週の名言

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

前回のおさらい

• 統計情報公開属性（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列の日付を巧みに使い分けることによって、統計情報の保留と履歴が上手く管理されていることがわかります。
データ・ディクショナリ・ビューの定義を解析することで統計情報管理の秘密を解明することができました。

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

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

続く