「データベース」カテゴリーアーカイブ

Flashback Dropの検証④（最終回）

Flashback Dropのまとめ

Flashback Dropに関して、基本機能と容量管理について簡単な検証で確認をしたが最後におさらいをする。

  • Flashback DropはRECYCLEBIN初期化パラメータがON（デフォルト）の場合に有効な機能であり、OFFの場合はOracle9iまでと同じ動作となる。
  • テーブルをPURGEオプションなしでDropすると、テーブルは物理的に削除されずにリサイクルビンで管理される。これはリサイクルビン用の表領域に移動されるのではなく、同じ表領域のままデータ・ディクショナリから見えなくなるような仕組みで管理されることを意味する。
  • リサイクルビンで管理されているテーブルは「BIN$」で始まる名前にRenameされる。
  • テーブルに紐付くインデックスおよびPK制約もリサイクルビンで管理される対象であり、同様に「BIN$」で始まる名前にRenameされる。
  • FK制約はDropとともに削除される。
  • FLASHBACK TABLE <テーブル名> TO BEFORE DROP文でリサイクルビンにあるテーブルを削除直前に復元することができる。（名前は特に指定しないかぎり元に戻る）
  • 同時にインデックスとPK制約も復元される。（ただし名前は「BIN$」で始まる名前のまま。）従って一意制約は有効であり重複行をInsertしようとするとエラーになる。
  • FK制約は削除されているので復元されることはない。
  • リサイクルビンで管理されているオブジェクトは、セグメントを解放しない状態で表領域内に存在している。
  • 療養域の空きがなくなるまでセグメントは解放されないが、新たなオブジェクトのために領域が必要になると、削除時のSCNが古いセグメントから解放される。
  • この動作はユーザの操作を必要とせず自動的に行われる。

Flashback Dropは使える機能なのか？

上で述べたとおりFlashback Dropはデフォルトで使用できる機能であるが、積極的にオフにすべきなのだろうか？

検証した限り、私にはオフにする正当な理由が見つからない。というかオフにしてはいけない機能だと思う。

ところが、最近Amazon RDSでOracle11gR2 SE1環境をお試しで構築したところ、Recyclebin初期化パラメータが「OFF」になっていた。

パラメータグループを作成し、デフォルト値の「ON」に変更することは可能なはずなので問題はないのだが、なぜこのような設定値になっているのかは不明だ。

現行踏襲の呪縛

この検証シリーズは、JPOUG Tech Talk Night #6のライトニングトークで披露させていただいたネタなのだが、その中でこの「現行踏襲の呪縛」という言葉に対する反応が意外によかった。

つまり、アップグレードで多くの新機能が使えるようになっても、「新たなバグを引きたくない。」というような消極的な理由から、新機能を使わないということが現場では非常に多い。

「新機能にむやみに飛びつくのは素人だ。」と言わんばかりにベテランDBAが結果として間違った方向にミスリードし、小さな問題を大きくしてしまうとすればこれほど残念なことはない。

  • マニュアルをよく読んで誤解をしない。
  • 自分で検証して内部動作・仕様を確認する。

こういうことを怠って、自分の思い込みだけで判断するから、技術の進歩を素直に認めない空気が醸成されるのだ。

もう２０年近く前のことで私がまだ経験の浅いエンジニアだったころ、Oracle Master の保有率が国内トップクラスというある開発会社に転職した時の話である。

「ウチの会社では外部キー制約は使わないことになっているから」と言われて面食らった。

リレーショナル・データベースというのものを正しく理解せずに実装しているシステムがどんな結末をたどるか、ということをこの会社にいる間に身をもって経験した。

ある地方におけるプロジェクトが炎上し、何人もの在京エンジニアが投入されたが皆討ち死にするような状況となった末、その会社は遠方で苦しんでいるメンバーに対し、残りの社員全員で励ましの寄せ書きを送ることにした。

今思えばとんでもないブラックな会社だったと思うが、強烈な違和感を持った私はやがて別の会社に転職をした。

その決断は間違っていなかったと確信している。

おまけ

大事なことを忘れたのだが、この検証はWindows7 64bit環境で以下のバージョンで実施した。

1
2
3
4
5
6
7
8
9
10
11
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 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

10gではメタデータがリサイクルビンに残っていた？

古い話だが、Flashback Dropが出た10gでは不思議な仕様だった。

以下は、オペレーション・ログからの抜粋だが、リサイクルビンの中で一番古いオブジェクトのUSER_RECYCLEBIN.SPACEが「0」のものがあることに気がついた。

試しにこのテーブルをフラッシュバックしたところ、テーブル構造（メタデータ）が復元されたにも関わらずデータが０件だった。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> select ORIGINAL_NAME,TYPE,SPACE from user_recyclebin
  2  where CREATETIME = (select min(CREATETIME) from user_recyclebin);
 
ORIGINAL_NAME                    TYPE                           SPACE
-------------------------------- ------------------------- ----------
TABLE********                    TABLE                              0
INDEX************                INDEX                              0
PK_*************                 INDEX                              0
 
SQL> flashback table TABLE******** to before drop rename to TABLE********_RCV;
 
フラッシュバックが完了しました。
 
SQL> desc TABLE********_RCV
 名前                                        NULL?    型
 ----------------------------------------- -------- ------------------
 COL1                                      NOT NULL NUMBER(22)
 COL2                                               NUMBER(22)
 COL3                                               NUMBER(22)
 COL4                                               DATE
 COL5                                               DATE
 COL6                                               NUMBER(22)
 COL7                                               DATE
 
SQL> select * from TABLE********_RCV;
 
レコードが選択されませんでした。

よくよく確認したところ、メタデータが５万件以上もリサイクルビンに溜まっていた。

どうやら夜間バッチ処理の中でDROPを沢山発行していたようだった。（9iからのスクリプトでPURGEオプションなしでDROPを実行していた。）

これをPURGE RECYCLEBINコマンドで一気に削除したのだが、わずか数秒で終わってしまった。

今では見ることができない不思議な現象だった。

終わり

Flashback Dropの検証③

容量管理はどうなっているか？

前回はFlashback Dropの基本的な動作を確認した。

テーブルを削除すると「リサイクルビン」という特別な場所に移動されるわけではなく、同じ表領域にセグメントとして存在しつつもエクステント情報から見えなくなるだけであることがわかった。

また、削除されたテーブルとそれに紐づくインデックスとPK制約は、BIN$で始まる名前にRenameされる。
そして、Flashback Dropによって削除前に戻されたテーブルは、元の名前に戻るが（任意の名前にすることは可能）インデックスとPK制約はBIN$で始まる名前から元に戻ることがないこともわかった。

それでは削除されたオブジェクトが「リサイクルビン」セグメントとして表領域の中に溜まっていった場合、有限な表領域はいずれ一杯になってしまうはずである。
WindowsやMacOSの場合明示的にゴミ箱を空にしないとディスク領域は解放されないが、OracleのリサイクルビンもPERGEコマンドを発行しないと解放されないのであろうか？

マニュアルをよく読めば答えは書いてあるのだが、実際に確かめてみよう。

JPOUG20160223.001

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
初期状態
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        60
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_SEG%
----------
        60
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60
 
 
レコードが選択されませんでした。

EMP表をコピーしてEMP1表を作成する

CTAS(Create Table As Select)でEMP表からEMP1表を作成する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SQL> create table EMP1 as select * from EMP;
 
表が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
6行が選択されました。
 
 
  TOT_EXT%
----------
        70
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
6行が選択されました。
 
 
  TOT_SEG%
----------
        70
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70
 
 
レコードが選択されませんでした。

EMP1表にインデックスを作成する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
SQL> create unique index PK_EMP1 on EMP1 (EMPNO);
 
索引が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
7行が選択されました。
 
 
  TOT_EXT%
----------
        80
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP1                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP1                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
7行が選択されました。
 
 
  TOT_SEG%
----------
        80
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP1                          EMP1
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- -----------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80
 
 
レコードが選択されませんでした。

インデックスPK_EMP1を作成したところまでを図にすると以下のようになる。

JPOUG20160223.018

EMP1表をDropする

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SQL> drop table EMP1;
 
表が削除されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        60
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
7行が選択されました。
 
 
  TOT_SEG%
----------
        80
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

JPOUG20160223.019
エクステントの空き領域が初期状態と同じ40%になっているが、セグメントの空き領域が20%となっていることに注目してもらいたい。

EMP2表とインデックスを作成する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
SQL> create table EMP2 as select * from EMP;
 
表が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
6行が選択されました。
 
 
  TOT_EXT%
----------
        70
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
8行が選択されました。
 
 
  TOT_SEG%
----------
        90
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES
 
 
SQL> create unique index PK_EMP2 on EMP2 (EMPNO);
 
索引が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
7行が選択されました。
 
 
  TOT_EXT%
----------
        80
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP2                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP2                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
9行が選択されました。
 
 
  TOT_SEG%
----------
       100
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP2                          EMP2
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES

JPOUG20160223.020
この時点で、セグメント使用率が100%に達していることがわかる。

EMP2表をDropする

ここでEMP2表を削除して表領域使用率を初期状態と同じ60%にしてみよう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
SQL> drop table EMP2;
 
表が削除されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        60
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
9行が選択されました。
 
 
  TOT_SEG%
----------
       100
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$YDcoyDClRYqEdI1NlIY3ng==$0   PK_EMP1         DROP       INDEX      TS_SMALL      3324538 NO     YES
BIN$fwT/IhomQvm3rrv7SMAEIA==$0   EMP1            DROP       TABLE      TS_SMALL      3324542 YES    YES
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

JPOUG20160223.021
表領域としては40%の空きが確保できたが、セグメントは100%使用されている状態である。

この状態でさらにオブジェクトを作成してどうなるかを確認しよう。

EMP3表を作成する

ここからはオブジェクトを1つずつ作成する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
SQL> create table EMP3 as select * from EMP;
 
表が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
6行が選択されました。
 
 
  TOT_EXT%
----------
        70
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
8行が選択されました。
 
 
  TOT_SEG%
----------
        90
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                56              80           70
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

まず、EMP1に該当するリサイクルビン・セグメントが解放され、EMP1に紐づくPK_EMP1も解放される。

これはDROPSCNの小さい、つまり先にDropされたものから解放される仕様となっているので、正確にはPK_EMP1 → EMP1の順に解放される。

20%分が解放され10%分が消費されたので、セグメントは差し引き90%使用されていることになる。

PK_EMP3を作成する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
SQL> create unique index PK_EMP3 on EMP3 (EMPNO);
 
索引が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
7行が選択されました。
 
 
  TOT_EXT%
----------
        80
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   INDEX           TS_SMALL                 8
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
9行が選択されました。
 
 
  TOT_SEG%
----------
       100
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                64              80           80
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$SyavBrvpRz6HQHACx2Ln7Q==$0   PK_EMP2         DROP       INDEX      TS_SMALL      3324984 NO     YES
BIN$c3jA6BZKTGOkGUgJI69p8g==$0   EMP2            DROP       TABLE      TS_SMALL      3324988 YES    YES

JPOUG20160223.022

図はリサイクルビンの２つのオブジェクトを解放し、新たな２つのオブジェクトを作成したところである。

EMP4およびPK_EMP4を作成する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
SQL> create table EMP4 as select * from EMP;
 
表が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
8行が選択されました。
 
 
  TOT_EXT%
----------
        90
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
8行が選択されました。
 
 
  TOT_SEG%
----------
        90
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90
 
 
レコードが選択されませんでした。
 
SQL> create unique index PK_EMP4 on EMP4 (EMPNO);
 
索引が作成されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
9行が選択されました。
 
 
  TOT_EXT%
----------
       100
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
PK_EMP4                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
9行が選択されました。
 
 
  TOT_SEG%
----------
       100
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
PK_EMP4                          EMP4
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                80              80          100
 
 
レコードが選択されませんでした。

JPOUG20160223.023

EMP2、PK_EMP2に該当するリサイクルビン・オブジェクトが解放され新たなオブジェクトが作成された。表領域の使用率は100%である。

ここまで、明示的なPURGE RECYCLEBINコマンドは一度も発行していない。つまりリサイクルビン・オブジェクトにより表領域中のセグメントが100%使用されていたとしても、Oracleは古いものから適宜解放し、新たなオブジェクトを作成する領域を自動的に確保する。

PK_EMP4のみ削除する

テーブルを削除するとテーブルに紐づくインデックスも自動的にリサイクルビンで管理されることはわかったが、インデックスのみ削除した場合はどうなるだろうか？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SQL> drop index PK_EMP4;
 
索引が削除されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
8行が選択されました。
 
 
  TOT_EXT%
----------
        90
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
EMP3                             TABLE           TS_SMALL                 8
EMP4                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
PK_EMP3                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
8行が選択されました。
 
 
  TOT_SEG%
----------
        90
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
PK_EMP3                          EMP3
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                72              80           90
 
 
レコードが選択されませんでした。

JPOUG20160223.024

インデックスのみを削除してもリサイクルビンでは管理されない。単純に削除されて領域も解放される。

これは考えてみれば簡単なのだが、テーブルがあればインデックスは何度でも再作成可能なためである。

今日はここまで

Flashback Dropの検証②

前回のおさらい

JPOUG20160223.001

Flashback Dropの検証を行う環境の初期状態である。

オブジェクトはテーブルとインデックス、あわせて５つ。すべて1エクステント＝8ブロック＝64KBずつを占めている。これの他に表領域の管理情報と思われる1エスクテント分を合せて480KBの領域となるので、全体（640KB）に対する使用率は60%、空き領域は40%となっている。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        60
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_SEG%
----------
        60
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60
 
 
レコードが選択されませんでした。

EMP表をDropする。

それでは、まず最初にEMP表をDropして状況を確認してみる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SQL> drop table EMP;
 
表が削除されました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        40
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   TABLE           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_SEG%
----------
        60
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                32              80           40
 
 
--- リサイクルビン情報 ---
OBJECT_NAME                      ORIGINAL_NAME   OPERATION  TYPE       TS_NAME       DROPSCN CAN_UN CAN_PU
-------------------------------- --------------- ---------- ---------- ---------- ---------- ------ ------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   PK_EMP          DROP       INDEX      TS_SMALL      3323577 NO     YES
BIN$TduxVYG8Q5mq7BFKwzmeuw==$0   EMP             DROP       TABLE      TS_SMALL      3323581 YES    YES

テーブルとインデックスが「BIN$」で始まる名前にRenameされているだけでなく、PK制約もRenameされていることがわかる。

しかし、リサイクルビン情報にはFK制約の情報はない。つまり削除されている。

また、USER_RECYCLEBIN.CAN_UNDROP列を見ると、テーブルは「YES」となっているのに、インデックスの方は「NO」となっている。この違いについては次の操作で明らかになるので追って解説する。

さらに、セグメント情報を見ると、Dropされたオブジェクトは依然として「TS_SMALL」表領域内に存在していることがわかる。つまり、Drop（削除）されてゴミ箱（リサイクルビン）に入るというのは概念的なことであって、物理的には同じ表領域に存在しているのである。

SYSAUX表領域などに移動されるわけではない。

EMP表をDropした状態を図に示すと以下のようになる。

JPOUG20160223.012

JPOUG20160223.013
参照整合性制約（FK_EMP）については、リサイクルビンで管理されることなく即時に削除されていることがわかる。

Flashback Drop実行

それでは「FLASHBACK TABLE」コマンドにより、EMP表を削除前に戻してみよう。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SQL> flashback table EMP to before drop;
 
フラッシュバックが完了しました。
 
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        60
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   INDEX           TS_SMALL                 8
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_SEG%
----------
        60
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
BIN$IM3P5MOwSlazE4G+zy6X3w==$0   EMP
PK_DEPT                          DEPT
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
BIN$YIfQE0ilRXy+VvPcGtg1WA==$0   EMP                              P  BIN$IM3P5MOwSlazE4G+zy6X3w==$0   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60
 
 
レコードが選択されませんでした。
 
SQL> Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10);
Insert into FD.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('82-01-23','RR-MM-DD'),1300,null,10)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(FD.BIN$YIfQE0ilRXy+VvPcGtg1WA==$0)に反しています

エクステントが復活したために、空き領域は初期状態の40%に戻っている。

テーブルは削除前と同じ名前に戻ったが、インデックスはRenameされたままである。さらにPK制約もRenameされたままであることがわかる。つまり、USER_RECYCLEBIN.CAN_UNDROP列が「YES」のものは削除前のオブジェクト名に戻るが、「NO」のものは戻らないことを示している。

さらに、参照整合性制約は削除されたままで元に戻ることはないがPK制約は有効であるので、制約違反となるレコードをInsertしようとするとORA-0001エラーが発生する。

JPOUG20160223.014

JPOUG20160223.015

インデックスやPK制約はテーブルが存在すれば、削除／再作成で元の名前に戻すことは可能であるため、Flashbck Dropの中ではRenameに関する動作が行われないのかもしれない。

今回はここまで。

Flashback Dropの検証①

Oracle10gからある地味な機能だが。。。

Flashback DropはOracle10gから登場した機能だが、正直必要性を感じることはなかった。
テーブルを間違って削除してしまうようなことは通常考えられないし、自分自身そのようなことで困ったことがなかったからだ。

ところが、過去3年間に2件ものヒューマン・エラーによるテーブルの削除事例を目にした。

いずれの事例でも幸いなことにバックアップが取得されていたので、Point-in-Timeリカバリによって最終的にはリカバリに成功したのだが、うち1件は本番環境での誤操作で、極めて莫大な損害が出たと聞いた。
他の事例も最終テスト段階での誤操作だったので、リカバリまでにテストを止めざるを得ず大きな影響を与えたらしい。

両方の事例で関係者に状況をヒアリングしたのだが、いずれも「Recyclebin」初期化パラメータはデフォルトの「on」から意図的に「off」となっており、Flashback Dropは使えない状態だった。

もし、Flashback Dropが使えていたら

もし、両方の事例で「Recyclebin」パラメータをデフォルトのままとしていたら、誤って削除してしまったテーブルを簡単な操作で、しかもほんの数秒で元に戻すことができたはずである。

一度でもFlashback Dropを体験したことのある人であればその効果が計り知れないことは容易に理解できるのであるが、残念なことにそれらでは共に使えなく、何時間もかけてバックアップから戻すはめになったのだ。

なぜRecyclebinをoffにしていたのか？

結論から言えばデフォルトのままで何ら問題はないのだが、なぜ意図的に「Recyclebin 」が「off」になっていたのかその理由を聞いてみた。

  • テーブルを削除しても「ゴミ箱」に残るということは、SYSAUXかどこかにある「ゴミ箱」領域に移動するのではないか？余計なI/Oが発生するのは嫌だ。
  • 「ゴミ箱」がいつの間にか一杯になって無駄な領域を占有するのは嫌だ。どうせ「ゴミ」なんだからさっさと消してしまいたい。
  • 新機能にはいつも泣かされてきたから、こいつもきっと悪さをするに違いない。9iまではこんな機能なかったのだから要らない。
  • 今のバージョンは11gだけど、10gの時からoffにしているからoffのまま。
  • そもそも、間違ってDropするなんてことはしないので、余計な機能は要らない。

マニュアルを熟読すれば全くの誤解であることがわかるのだが、一度誤解してしまうとベテランと言われるエンジニアほど頑ななままになる。

だから、デフォルトからわざわざ変更することになる。

検証してみた

「マニュアルにはこう書いてある。」と言っても誤解を解くのは難しいし、自分自身どのような仕組みで動いているのか知りたかったので検証してみることにした。

準備

容量管理をどのようにしているのかを確認したいので、640KBという小さな表領域を作成することにした。

環境は、Oracle 12c EE (12.1.0.2)for Windows(Non-PDB)を使用した。

1
2
3
4
CREATE TABLESPACE TS_SMALL
DATAFILE 'C:\APP\ORACLEHOME\ORADATA\ORA12C\TS_SMALL01.DBF' SIZE 640K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

データ作成

データはSCOTTスキーマのオブジェクトをインポートすることで簡単に作成した。

JPOUG20160223.001

JPOUG20160223.002

確認用スクリプト

stsck.sql

1
2
3
4
5
6
@@uext
@@useg
@@uind
@@ucon
@@usep
@@rbin

uext.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
TTITLE LEFT '--- エクステント情報 ---'
col EXTENT_NAME for a32
col EXTENT_TYPE for a15
select
 SEGMENT_NAME EXTENT_NAME
,SEGMENT_TYPE EXTENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_EXTENTS
order by
 EXTENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_EXT%"
from USER_EXTENTS
/

useg.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
TTITLE LEFT '--- セグメント情報 ---'
col SEGMENT_NAME for a32
col SEGMENT_TYPE for a15
select
 SEGMENT_NAME
,SEGMENT_TYPE
,TABLESPACE_NAME
,BLOCKS
from
 USER_SEGMENTS
order by
 SEGMENT_NAME
;
TTITLE OFF
select
 (sum(BLOCKS)+8)/80*100 "TOT_SEG%"
from
 USER_SEGMENTS
/

uind.sql

1
2
3
4
5
6
7
8
9
10
11
12
TTITLE LEFT '--- インデックス情報 ---'
col INDEX_NAME for a32
col TABLE_NAME for a32
select
 INDEX_NAME
,TABLE_NAME
from
 USER_INDEXES
order by
 INDEX_NAME
;
TTITLE OFF

ucon.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
TTITLE LEFT '--- 制約情報 ---'
col CONSTRAINT_NAME for a32
col TABLE_NAME for a32
col INDEX_NAME for a32
select
 CONSTRAINT_NAME
,TABLE_NAME
,CONSTRAINT_TYPE
,INDEX_NAME
,STATUS
from
 USER_CONSTRAINTS
order by
 CONSTRAINT_NAME
;
TTITLE OFF

usep.sql

1
2
3
4
TTITLE LEFT '--- 表領域使用率 ---'
select * from DBA_TABLESPACE_USAGE_METRICS
where TABLESPACE_NAME = 'TS_SMALL'
/

rbin.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
TTITLE LEFT '--- リサイクルビン情報 ---'
select
 OBJECT_NAME
,ORIGINAL_NAME
,OPERATION
,TYPE
,TS_NAME
,DROPSCN
,CAN_UNDROP
,CAN_PURGE
from
 USER_RECYCLEBIN
order by
 DROPSCN
/

初期状態

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SQL> @stsck
 
--- エクステント情報 ---
EXTENT_NAME                      EXTENT_TYPE     TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_EXT%
----------
        60
 
 
--- セグメント情報 ---
SEGMENT_NAME                     SEGMENT_TYPE    TABLESPACE_NAME     BLOCKS
-------------------------------- --------------- --------------- ----------
DEPT                             TABLE           TS_SMALL                 8
EMP                              TABLE           TS_SMALL                 8
PK_DEPT                          INDEX           TS_SMALL                 8
PK_EMP                           INDEX           TS_SMALL                 8
SALGRADE                         TABLE           TS_SMALL                 8
 
 
  TOT_SEG%
----------
        60
 
 
--- インデックス情報 ---
INDEX_NAME                       TABLE_NAME
-------------------------------- --------------------------------
PK_DEPT                          DEPT
PK_EMP                           EMP
 
 
--- 制約情報 ---
CONSTRAINT_NAME                  TABLE_NAME                       CO INDEX_NAME                       STATUS
-------------------------------- -------------------------------- -- -------------------------------- ----------------
FK_DEPTNO                        EMP                              R                                   ENABLED
PK_DEPT                          DEPT                             P  PK_DEPT                          ENABLED
PK_EMP                           EMP                              P  PK_EMP                           ENABLED
 
 
--- 表領域使用率 ---
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------- ---------- --------------- ------------
TS_SMALL                48              80           60
 
 
レコードが選択されませんでした。

続く

Oracle12c 新機能：APPROX_COUNT_DISTINCT関数

APPROX_COUNT_DISTINCT関数とは

APPROX_COUNT_DISTINCT関数とは、重複していない値の近似カウントを集計する関数で、例えば次のような問い合わせにおいて、prod_idでグループ化したうちの重複していないcust_idのカウントを集計する。

1
2
3
4
SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

これを従来のCOUNT関数を使って書くと

1
2
3
4
SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id;
ORDER BY prod_id;

となる。

マニュアルからの抜粋

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

には以下の記述がある。

1.2 重複していない値の近似カウント

最適化された新しいSQL関数APPROX_COUNT_DISTINCT()により、重複していない値の近似カウントが集約されます。大量のデータの処理速度が完全集約よりも大幅に上がりますが、これは特に、重複していない値が多数含まれるデータ・セットの場合に顕著であり、完全集約結果との偏差は無視できる程度です。

現在のデータ分析では、一般的な操作で重複していない値をカウントすることが求められます。処理時間およびリソース消費を桁違いに最適化すると同時にほとんど完全な結果を提供することにより、既存の処理速度を上げ、分析の洞察力を数段高めることができます。

実際に試してみた

マニュアルに書いてある「桁違いの最適化」とは一体どんなものなのか気になったので、サンプルスキーマの「SH.SALES」表（918,843件）を題材に実際に確認してみた。

以下は、COUNT関数とAPPROX_COUNT_DISTINCT関数の結果を横に並べたものである。
（前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。）

１回目にORDER BYあり、２回目にORDER BYなしの結果をそれぞれ確認した。

なお、各測定前に共有プールとバッファ・キャッシュのフラッシュを行ってから、SQL文の実行を行っている。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
00001 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00001 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                 
00002  |  2  FROM sales                                                                                               ||00002  |  2  FROM sales                                                                                              
00003  |  3  GROUP BY prod_id                                                                                         ||00003  |  3  GROUP BY prod_id                                                                                        
00004  |  4  ORDER BY prod_id;                                                                                        ||00004  |  4  ORDER BY prod_id;                                                                                       
00005  |                                                                                                              ||00005  |                                                                                                             
00006  |   PROD_ID Number of Customers                                                                                ||00006  |   PROD_ID Number of Customers                                                                               
00007  |---------- -------------------                                                                                ||00007  |---------- -------------------                                                                               
00008 L|        13                2492                                                                                ||00008 R|        13                2516                                                                               
00009 L|        14                2039                                                                                ||00009 R|        14                2030                                                                               
00010 L|        15                2122                                                                                ||00010 R|        15                2105                                                                               
00011 L|        16                2384                                                                                ||00011 R|        16                2367                                                                               
00012 L|        17                2100                                                                                ||00012 R|        17                2093                                                                               
00013 L|        18                3028                                                                                ||00013 R|        18                2975                                                                               
00014 L|        19                2617                                                                                ||00014 R|        19                2630                                                                               
00015 L|        20                3795                                                                                ||00015 R|        20                3791                                                                               
00016 L|        21                2334                                                                                ||00016 R|        21                2365                                                                               
00017 L|        22                1416                                                                                ||00017 R|        22                1408                                                                               
00018 L|        23                5183                                                                                ||00018 R|        23                5104                                                                               
00019  |                                                                                                              ||00019  |                                                                                                             
00020  |   PROD_ID Number of Customers                                                                                ||00020  |   PROD_ID Number of Customers                                                                               
00021  |---------- -------------------                                                                                ||00021  |---------- -------------------                                                                               
00022 L|        24                4817                                                                                ||00022 R|        24                4754                                                                               
00023 L|        25                5068                                                                                ||00023 R|        25                4991                                                                               
00024 L|        26                4948                                                                                ||00024 R|        26                4894                                                                               
00025 L|        27                3801                                                                                ||00025 R|        27                3726                                                                               
00026 L|        28                4572                                                                                ||00026 R|        28                4537                                                                               
00027 L|        29                2295                                                                                ||00027 R|        29                2305                                                                               
00028 L|        30                6154                                                                                ||00028 R|        30                6134                                                                               
00029 L|        31                5586                                                                                ||00029 R|        31                5463                                                                               
00030 L|        32                4100                                                                                ||00030 R|        32                4025                                                                               
00031 L|        33                5389                                                                                ||00031 R|        33                5278                                                                               
00032 L|        34                4192                                                                                ||00032 R|        34                4142                                                                               
00033  |                                                                                                              ||00033  |                                                                                                             
00034  |   PROD_ID Number of Customers                                                                                ||00034  |   PROD_ID Number of Customers                                                                               
00035  |---------- -------------------                                                                                ||00035  |---------- -------------------                                                                               
00036 L|        35                4965                                                                                ||00036 R|        35                4950                                                                               
00037 L|        36                3302                                                                                ||00037 R|        36                3327                                                                               
00038 L|        37                4802                                                                                ||00038 R|        37                4735                                                                               
00039 L|        38                3395                                                                                ||00039 R|        38                3452                                                                               
00040 L|        39                3433                                                                                ||00040 R|        39                3404                                                                               
00041 L|        40                5972                                                                                ||00041 R|        40                5858                                                                               
00042 L|        41                3738                                                                                ||00042 R|        41                3759                                                                               
00043 L|        42                3951                                                                                ||00043 R|        42                3938                                                                               
00044 L|        43                3016                                                                                ||00044 R|        43                3056                                                                               
00045 L|        44                2571                                                                                ||00045 R|        44                2587                                                                               
00046 L|        45                3549                                                                                ||00046 R|        45                3499                                                                               
00047  |                                                                                                              ||00047  |                                                                                                             
00048  |   PROD_ID Number of Customers                                                                                ||00048  |   PROD_ID Number of Customers                                                                               
00049  |---------- -------------------                                                                                ||00049  |---------- -------------------                                                                               
00050 L|        46                4107                                                                                ||00050 R|        46                4054                                                                               
00051 L|        47                3198                                                                                ||00051 R|        47                3176                                                                               
00052 L|        48                6010                                                                                ||00052 R|        48                5883                                                                               
00053 L|       113                4640                                                                                ||00053 R|       113                4598                                                                               
00054 L|       114                4230                                                                                ||00054 R|       114                4183                                                                               
00055 L|       115                3847                                                                                ||00055 R|       115                3832                                                                               
00056 L|       116                4929                                                                                ||00056 R|       116                4924                                                                               
00057 L|       117                4672                                                                                ||00057 R|       117                4632                                                                               
00058 L|       118                4214                                                                                ||00058 R|       118                4200                                                                               
00059 L|       119                4898                                                                                ||00059 R|       119                4858                                                                               
00060 L|       120                5224                                                                                ||00060 R|       120                5135                                                                               
00061  |                                                                                                              ||00061  |                                                                                                             
00062  |   PROD_ID Number of Customers                                                                                ||00062  |   PROD_ID Number of Customers                                                                               
00063  |---------- -------------------                                                                                ||00063  |---------- -------------------                                                                               
00064 L|       121                3517                                                                                ||00064 R|       121                3499                                                                               
00065 L|       122                1572                                                                                ||00065 R|       122                1559                                                                               
00066 L|       123                4491                                                                                ||00066 R|       123                4441                                                                               
00067 L|       124                4309                                                                                ||00067 R|       124                4310                                                                               
00068 L|       125                4679                                                                                ||00068 R|       125                4650                                                                               
00069 L|       126                4253                                                                                ||00069 R|       126                4202                                                                               
00070 L|       127                4757                                                                                ||00070 R|       127                4710                                                                               
00071 L|       128                5196                                                                                ||00071 R|       128                5135                                                                               
00072 L|       129                2425                                                                                ||00072 R|       129                2419                                                                               
00073 L|       130                5428                                                                                ||00073 R|       130                5336                                                                               
00074 L|       131                5013                                                                                ||00074 R|       131                4907                                                                               
00075  |                                                                                                              ||00075  |                                                                                                             
00076  |   PROD_ID Number of Customers                                                                                ||00076  |   PROD_ID Number of Customers                                                                               
00077  |---------- -------------------                                                                                ||00077  |---------- -------------------                                                                               
00078 L|       132                4770                                                                                ||00078 R|       132                4688                                                                               
00079 L|       133                5201                                                                                ||00079 R|       133                5114                                                                               
00080 L|       134                3629                                                                                ||00080 R|       134                3594                                                                               
00081 L|       135                3996                                                                                ||00081 R|       135                3943                                                                               
00082 L|       136                 397                                                                                ||00082 R|       136                 395                                                                               
00083 L|       137                3993                                                                                ||00083 R|       137                3977                                                                               
00084 L|       138                2763                                                                                ||00084 R|       138                2716                                                                               
00085 L|       139                4217                                                                                ||00085 R|       139                4106                                                                               
00086 L|       140                5097                                                                                ||00086 R|       140                5032                                                                               
00087 L|       141                3546                                                                                ||00087 R|       141                3499                                                                               
00088 L|       142                3136                                                                                ||00088 R|       142                3071                                                                               
00089  |                                                                                                              ||00089  |                                                                                                             
00090  |   PROD_ID Number of Customers                                                                                ||00090  |   PROD_ID Number of Customers                                                                               
00091  |---------- -------------------                                                                                ||00091  |---------- -------------------                                                                               
00092 L|       143                2375                                                                                ||00092 R|       143                2395                                                                               
00093 L|       144                2289                                                                                ||00093 R|       144                2254                                                                               
00094 L|       145                2814                                                                                ||00094 R|       145                2807                                                                               
00095 L|       146                4796                                                                                ||00095 R|       146                4716                                                                               
00096 L|       147                3050                                                                                ||00096 R|       147                3073                                                                               
00097 L|       148                5150                                                                                ||00097 R|       148                5049                                                                               
00098  |                                                                                                              ||00098  |                                                                                                             
00099  |72行が選択されました。                                                                                        ||00099  |72行が選択されました。                                                                                       
00100  |                                                                                                              ||00100  |                                                                                                             
00101 L|経過: 00:00:01.27                                                                                             ||00101 R|経過: 00:00:00.92                                                                                            
00102  |                                                                                                              ||00102  |                                                                                                             
00103  |実行計画                                                                                                      ||00103  |実行計画                                                                                                     
00104  |----------------------------------------------------------                                                    ||00104  |----------------------------------------------------------                                                   
00105 L|Plan hash value: 932069919                                                                                    ||00105 R|Plan hash value: 4109827725                                                                                  
00106  |                                                                                                              ||00106  |                                                                                                             
00107 L|------------------------------------------------------------------------------------------------------------  ||00107 R|----------------------------------------------------------------------------------------------               
00108 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00108 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |               
00109 L|------------------------------------------------------------------------------------------------------------  ||00109 R|----------------------------------------------------------------------------------------------               
00110 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00110 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00111 L||   1 |  SORT GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00111 R||   1 |  SORT GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00112 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00112 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00113 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00113 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00114 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00114 R|----------------------------------------------------------------------------------------------               
00115 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                             
00116 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                             
00117  |                                                                                                              ||00115  |                                                                                                             
00118  |                                                                                                              ||00116  |                                                                                                             
00119  |統計                                                                                                          ||00117  |統計                                                                                                         
00120  |----------------------------------------------------------                                                    ||00118  |----------------------------------------------------------                                                   
00121 L|       1936  recursive calls                                                                                  ||00119 R|       1956  recursive calls                                                                                 
00122  |          0  db block gets                                                                                    ||00120  |          0  db block gets                                                                                   
00123 L|       4914  consistent gets                                                                                  ||00121 R|       4921  consistent gets                                                                                 
00124 L|       1772  physical reads                                                                                   ||00122 R|       1729  physical reads                                                                                  
00125  |          0  redo size                                                                                        ||00123  |          0  redo size                                                                                       
00126 L|       2133  bytes sent via SQL*Net to client                                                                 ||00124 R|       2134  bytes sent via SQL*Net to client                                                                
00127  |        595  bytes received via SQL*Net from client                                                           ||00125  |        595  bytes received via SQL*Net from client                                                          
00128  |          6  SQL*Net roundtrips to/from client                                                                ||00126  |          6  SQL*Net roundtrips to/from client                                                               
00129  |        136  sorts (memory)                                                                                   ||00127  |        136  sorts (memory)                                                                                  
00130  |          0  sorts (disk)                                                                                     ||00128  |          0  sorts (disk)                                                                                    
00131  |         72  rows processed                                                                                   ||00129  |         72  rows processed                                                                                  
00132  |                                                                                                              ||00130  |                                                                                                             
00133  |                                                                                                              ||00131  |                                                                                                             
00134 L|SQL> SELECT prod_id, COUNT(DISTINCT cust_id) AS "Number of Customers"                                         ||00132 R|SQL> SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"                                 
00135  |  2  FROM sales                                                                                               ||00133  |  2  FROM sales                                                                                              
00136  |  3  GROUP BY prod_id;                                                                                        ||00134  |  3  GROUP BY prod_id;                                                                                       
00137  |                                                                                                              ||00135  |                                                                                                             
00138  |   PROD_ID Number of Customers                                                                                ||00136  |   PROD_ID Number of Customers                                                                               
00139  |---------- -------------------                                                                                ||00137  |---------- -------------------                                                                               
00140 L|        22                1416                                                                                ||00138 R|        22                1408                                                                               
00141 L|        25                5068                                                                                ||00139 R|        25                4991                                                                               
00142 L|        30                6154                                                                                ||00140 R|        30                6134                                                                               
00143 L|        34                4192                                                                                ||00141 R|        34                4142                                                                               
00144 L|        42                3951                                                                                ||00142 R|        42                3938                                                                               
00145 L|        43                3016                                                                                ||00143 R|        43                3056                                                                               
00146 L|       123                4491                                                                                ||00144 R|       123                4441                                                                               
00147 L|       129                2425                                                                                ||00145 R|       129                2419                                                                               
00148 L|       138                2763                                                                                ||00146 R|       138                2716                                                                               
00149 L|        13                2492                                                                                ||00147 R|        13                2516                                                                               
00150 L|        28                4572                                                                                ||00148 R|        28                4537                                                                               
00151  |                                                                                                              ||00149  |                                                                                                             
00152  |   PROD_ID Number of Customers                                                                                ||00150  |   PROD_ID Number of Customers                                                                               
00153  |---------- -------------------                                                                                ||00151  |---------- -------------------                                                                               
00154 L|        29                2295                                                                                ||00152 R|        29                2305                                                                               
00155 L|        44                2571                                                                                ||00153 R|        44                2587                                                                               
00156 L|        47                3198                                                                                ||00154 R|        47                3176                                                                               
00157 L|       113                4640                                                                                ||00155 R|       113                4598                                                                               
00158 L|       116                4929                                                                                ||00156 R|       116                4924                                                                               
00159 L|       120                5224                                                                                ||00157 R|       120                5135                                                                               
00160 L|       128                5196                                                                                ||00158 R|       128                5135                                                                               
00161 L|       147                3050                                                                                ||00159 R|       147                3073                                                                               
00162 L|       121                3517                                                                                ||00160 R|       121                3499                                                                               
00163 L|       134                3629                                                                                ||00161 R|       134                3594                                                                               
00164 L|       144                2289                                                                                ||00162 R|       144                2254                                                                               
00165  |                                                                                                              ||00163  |                                                                                                             
00166  |   PROD_ID Number of Customers                                                                                ||00164  |   PROD_ID Number of Customers                                                                               
00167  |---------- -------------------                                                                                ||00165  |---------- -------------------                                                                               
00168 L|        14                2039                                                                                ||00166 R|        14                2030                                                                               
00169 L|        20                3795                                                                                ||00167 R|        20                3791                                                                               
00170 L|        21                2334                                                                                ||00168 R|        21                2365                                                                               
00171 L|        26                4948                                                                                ||00169 R|        26                4894                                                                               
00172 L|        31                5586                                                                                ||00170 R|        31                5463                                                                               
00173 L|       132                4770                                                                                ||00171 R|       132                4688                                                                               
00174 L|       148                5150                                                                                ||00172 R|       148                5049                                                                               
00175 L|       141                3546                                                                                ||00173 R|       141                3499                                                                               
00176 L|        24                4817                                                                                ||00174 R|        24                4754                                                                               
00177 L|        32                4100                                                                                ||00175 R|        32                4025                                                                               
00178 L|        46                4107                                                                                ||00176 R|        46                4054                                                                               
00179  |                                                                                                              ||00177  |                                                                                                             
00180  |   PROD_ID Number of Customers                                                                                ||00178  |   PROD_ID Number of Customers                                                                               
00181  |---------- -------------------                                                                                ||00179  |---------- -------------------                                                                               
00182 L|       117                4672                                                                                ||00180 R|       117                4632                                                                               
00183 L|       119                4898                                                                                ||00181 R|       119                4858                                                                               
00184 L|       133                5201                                                                                ||00182 R|       133                5114                                                                               
00185 L|       137                3993                                                                                ||00183 R|       137                3977                                                                               
00186 L|       143                2375                                                                                ||00184 R|       143                2395                                                                               
00187 L|        17                2100                                                                                ||00185 R|        17                2093                                                                               
00188 L|        23                5183                                                                                ||00186 R|        23                5104                                                                               
00189 L|        35                4965                                                                                ||00187 R|        35                4950                                                                               
00190 L|        37                4802                                                                                ||00188 R|        37                4735                                                                               
00191 L|        38                3395                                                                                ||00189 R|        38                3452                                                                               
00192 L|        48                6010                                                                                ||00190 R|        48                5883                                                                               
00193  |                                                                                                              ||00191  |                                                                                                             
00194  |   PROD_ID Number of Customers                                                                                ||00192  |   PROD_ID Number of Customers                                                                               
00195  |---------- -------------------                                                                                ||00193  |---------- -------------------                                                                               
00196 L|       125                4679                                                                                ||00194 R|       125                4650                                                                               
00197 L|       135                3996                                                                                ||00195 R|       135                3943                                                                               
00198 L|        33                5389                                                                                ||00196 R|        33                5278                                                                               
00199 L|        40                5972                                                                                ||00197 R|        40                5858                                                                               
00200 L|        41                3738                                                                                ||00198 R|        41                3759                                                                               
00201 L|        45                3549                                                                                ||00199 R|        45                3499                                                                               
00202 L|       114                4230                                                                                ||00200 R|       114                4183                                                                               
00203 L|       130                5428                                                                                ||00201 R|       130                5336                                                                               
00204 L|       131                5013                                                                                ||00202 R|       131                4907                                                                               
00205 L|       136                 397                                                                                ||00203 R|       136                 395                                                                               
00206 L|       140                5097                                                                                ||00204 R|       140                5032                                                                               
00207  |                                                                                                              ||00205  |                                                                                                             
00208  |   PROD_ID Number of Customers                                                                                ||00206  |   PROD_ID Number of Customers                                                                               
00209  |---------- -------------------                                                                                ||00207  |---------- -------------------                                                                               
00210 L|        18                3028                                                                                ||00208 R|        18                2975                                                                               
00211 L|        27                3801                                                                                ||00209 R|        27                3726                                                                               
00212 L|        36                3302                                                                                ||00210 R|        36                3327                                                                               
00213 L|       115                3847                                                                                ||00211 R|       115                3832                                                                               
00214 L|       146                4796                                                                                ||00212 R|       146                4716                                                                               
00215 L|       124                4309                                                                                ||00213 R|       124                4310                                                                               
00216 L|       142                3136                                                                                ||00214 R|       142                3071                                                                               
00217 L|       145                2814                                                                                ||00215 R|       145                2807                                                                               
00218 L|        15                2122                                                                                ||00216 R|        15                2105                                                                               
00219 L|        19                2617                                                                                ||00217 R|        19                2630                                                                               
00220 L|        39                3433                                                                                ||00218 R|        39                3404                                                                               
00221  |                                                                                                              ||00219  |                                                                                                             
00222  |   PROD_ID Number of Customers                                                                                ||00220  |   PROD_ID Number of Customers                                                                               
00223  |---------- -------------------                                                                                ||00221  |---------- -------------------                                                                               
00224 L|       118                4214                                                                                ||00222 R|       118                4200                                                                               
00225 L|       126                4253                                                                                ||00223 R|       126                4202                                                                               
00226 L|       127                4757                                                                                ||00224 R|       127                4710                                                                               
00227 L|        16                2384                                                                                ||00225 R|        16                2367                                                                               
00228 L|       122                1572                                                                                ||00226 R|       122                1559                                                                               
00229 L|       139                4217                                                                                ||00227 R|       139                4106                                                                               
00230  |                                                                                                              ||00228  |                                                                                                             
00231  |72行が選択されました。                                                                                        ||00229  |72行が選択されました。                                                                                       
00232  |                                                                                                              ||00230  |                                                                                                             
00233 L|経過: 00:00:00.90                                                                                             ||00231 R|経過: 00:00:00.73                                                                                            
00234  |                                                                                                              ||00232  |                                                                                                             
00235  |実行計画                                                                                                      ||00233  |実行計画                                                                                                     
00236  |----------------------------------------------------------                                                    ||00234  |----------------------------------------------------------                                                   
00237 L|Plan hash value: 2475333094                                                                                   ||00235 R|Plan hash value: 3604305554                                                                                  
00238  |                                                                                                              ||00236  |                                                                                                             
00239 L|------------------------------------------------------------------------------------------------------------  ||00237 R|----------------------------------------------------------------------------------------------               
00240 L|| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |  ||00238 R|| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |               
00241 L|------------------------------------------------------------------------------------------------------------  ||00239 R|----------------------------------------------------------------------------------------------               
00242 L||   0 | SELECT STATEMENT       |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00240 R||   0 | SELECT STATEMENT     |       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00243 L||   1 |  HASH GROUP BY         |           |    72 |  1224 |       |  2638   (2)| 00:00:01 |       |       |  ||00241 R||   1 |  HASH GROUP BY APPROX|       |    72 |   648 |   537   (6)| 00:00:01 |       |       |               
00244 L||   2 |   VIEW                 | VM_NWVW_1 |   359K|  5966K|       |  2638   (2)| 00:00:01 |       |       |  ||00242 R||   2 |   PARTITION RANGE ALL|       |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00245 L||   3 |    HASH GROUP BY       |           |   359K|  3158K|    17M|  2638   (2)| 00:00:01 |       |       |  ||00243 R||   3 |    TABLE ACCESS FULL | SALES |   918K|  8075K|   514   (1)| 00:00:01 |     1 |    28 |               
00246 L||   4 |     PARTITION RANGE ALL|           |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||00244 R|----------------------------------------------------------------------------------------------               
00247 L||   5 |      TABLE ACCESS FULL | SALES     |   918K|  8075K|       |   514   (1)| 00:00:01 |     1 |    28 |  ||       |                                                                                                             
00248 L|------------------------------------------------------------------------------------------------------------  ||       |                                                                                                             
00249  |                                                                                                              ||00245  |                                                                                                             
00250  |                                                                                                              ||00246  |                                                                                                             
00251  |統計                                                                                                          ||00247  |統計                                                                                                         
00252  |----------------------------------------------------------                                                    ||00248  |----------------------------------------------------------                                                   
00253 L|       2432  recursive calls                                                                                  ||00249 R|       2313  recursive calls                                                                                 
00254  |          0  db block gets                                                                                    ||00250  |          0  db block gets                                                                                   
00255 L|       5669  consistent gets                                                                                  ||00251 R|       5458  consistent gets                                                                                 
00256 L|       1794  physical reads                                                                                   ||00252 R|       1765  physical reads                                                                                  
00257  |          0  redo size                                                                                        ||00253  |          0  redo size                                                                                       
00258 L|       2133  bytes sent via SQL*Net to client                                                                 ||00254 R|       2134  bytes sent via SQL*Net to client                                                                
00259  |        595  bytes received via SQL*Net from client                                                           ||00255  |        595  bytes received via SQL*Net from client                                                          
00260  |          6  SQL*Net roundtrips to/from client                                                                ||00256  |          6  SQL*Net roundtrips to/from client                                                               
00261 L|        239  sorts (memory)                                                                                   ||00257 R|        229  sorts (memory)                                                                                  
00262  |          0  sorts (disk)                                                                                     ||00258  |          0  sorts (disk)                                                                                    
00263  |         72  rows processed                                                                                   ||00259  |         72  rows processed                                                                                  
00264 L|                                                                                                              ||       |                                                                                                             

何が違うのか？

行番号の後に「L」あるいは「R」がついている行は、左右の結果が異なるものを示している。

近似値というからには両者はほとんど同じ結果でたまに違う値となることを予想していたのだが、あにはからんや（72行という結果は同じだが）すべての行で異なるカウント値となった。

また、統計情報はほとんど同じだが、実行計画が大きく異なっている。

COUNT関数の場合「PARTITION RANGE ALL」で全行を取得した後、「HASH GROUP BY」で約35万9千行のビユーを作成（その際17MBのHASH AREAを使用）し、さらに「SORT GROUP BY」で72行に絞り込んでいる。

一方、APPROX_COUNT_DISTINCT関数の場合全行を取得するまでは同じだが、「SORT GROUP BY APPROX」という新たなオペレーションによって一気に72行まで絞り込んでいる。その際コストの上積み分もほとんどない。（514→538）

今回の場合、テーブルサイズがそれほど大きくないので、実行時間で大きな違いはなかったが、巨大なテーブルの場合は「HASH GROUP BY」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。

正確なカウントは必要なのか？

検索条件を入力して「◯件ヒットしました。」と表示させるアプリケーションは多いが、その値の正確性を１桁単位で求めてもあまり意味のないことが多い。

チューニングの現場ではCOUNT関数の負荷が問題となっていることが多いので、このAPPROX_COUNT_DISTINCT関数は意外と使えるかもしれない。

 

Amazon RDSをさわって考えた

やっと再開

新しい年になってもなかなかブログが捗らなかったが、今年から「ですます調」から「である調」に変えて再開することにした。

Amazon RDSを使ってみた

今までクラウドでデータベースを使うということに性能面でどうしても違和感を持っていたので、あまり積極的に関心を寄せなかったのだが、仕事の関係でPostgreSQL on RDSを使う機会があったので、個人的にも無料の範囲で試してみることにした。Amazon RDS

詳しい構築手順等は検索すればいろいろ見つかるので割愛するが、ゆっくり確認しながらでも３０分程度でDBインスタンスが構築できてしまうのは驚異的である。

インスタンス作成中

RDSとOSS

当然のことながらRDSとOSSは相性がいい。ライセンス費用が発生しないからだ。一方商用製品であるOracleとMSSQLはライセンス費用を考慮する必要がある。

例えばOracle Enterprise EditionをRDSで使用する場合は、自前のライセンスを用意する必要がある。

バックアップ

RDSではバックアップウィンドウを指定するだけで勝手にバックアップを取得してくれる。万一バックアップからリカバリする場合は、今までのインスタンスとは別にインスタンスを作成しそちらにリカバリするなど多少運用を考える必要があるが、フルマネージドRDBサービスとしての重要な機能のうちの１つであるであることに間違いはない。

監視

AWSで提供されるCloudwatchによって、パフォーマンスデータの取得及び可視化（グラフ化）が可能である。

監視項目（メトリック）の数も充実している。

冗長構成

無料お試し版では使えないが、マルチAZ配置とすれば異なるアベイラビリティ・ゾーン間で「プライマリ」と「スタンバイ」レプリカの構成を自動的に構成・管理することができる。

「プライマリ」側に何らかの異常があった場合でも「スタンバイ」へ自動的に切り替えることで運用を継続することができる。

Oracleの世界で言うところのData Guardのような仕様で冗長性を担保する。

Oracle RACのサポートについて

将来サポートされるかどうかは不明だが、現在RDSではOracle RACはサポートされていない。

これは技術的なハードルというよりもむしろRDSの冗長性に対する考え方によるものなのかもしれない。

Oracle RACのようなActive-Active構成を実現するためには、複数ノードから同時アクセス可能なASMのような仕組みを実装必要があるが、「１つのインスタンスは１つのノードでしか動かさない」という割り切りをすれば、そのような仕組みは不要でありより一般的なファイルシステムを使用することができる。

また、Oracle RACにおいてはノード間通信であるキャッシュフュージョンが発生すると性能が劣化することはよく知られている。

だからノードを増やすスケールアウトで性能を担保するというのは実は難しく、マシンスペックを上げて対応するスケールアップで性能を担保する方が設計上は確実であるという考えにRDSは至ったのかもしれない。

どこまでユーザに変更を許すか

RDSはSSHによりOS領域にログインすることはできない仕様となっている。従ってユーザができることは限られており、逆にユーザの不適切なオペレーションによる環境破壊等を防止する仕組みであると考えても良いのかもしれない。

パラメータもマシンスペック（インスタンスクラス）に応じてチューニングされたものが自動的に設定されるため、ユーザは基本的にパラメータを変更する必要はない。

どうしてもデフォルトパラメータから変更したい場合は、パラメータグループを作成して反映させるようになっている。

考えてみれば、パラメータはデフォルトのまま運用するべきではないと言っていたのは昔の話で、例えばOracleではパラメータの自動チューニングが進化しているので、パラメータを変更してチューニングするという機会はどんどん減っている。

Amazon RDSは使えるか

クラウド環境に大切なデータを預けるのは如何なものか的な考え方は未だに根強いが、十分なコストメリットとセキュリティの妥当性を実感できればRDSのような環境を業務に活用するという流れは加速されるだろう。

私が漠然と持っていた性能面に関する懸念も杞憂に終わるのではないかとさえ思えるほど、RDSの内容と選択の多様性は充実している。

 

STATSPACKデータをExcelでグラフ化する②〜DBバッファ編1〜

今週の名言

「自分が多数派の側にいると気付いたら、もう意見を変えてもいい頃だ。」
マーク・トウェイン

最近、マーク・トウェインの名言が多いですが、偶然です。

DBバッファの統計を調査する。

前回は、stats$snapshot表からスナップショット一覧を取得するSQL文を紹介しました。
今回から、応用編としてこの表とパフォーマンス・データが格納された表を結合して、１日の統計情報の推移を見てみます。
最初は取っ付き易いDBバッファについて調べてみましょう。

DBバッファヒット率の計算式

DBバッファの目的は、頻繁にアクセスされるDBブロックをなるべくメモリ（DBバッファ）に保持しておくことで、低速なディスクI/Oを回避することです。DBバッファ・ヒット率とは、要求されたDBブロックがバッファ内に存在していた割合を示すもので、以下の計算式によります。

DBバッファヒット率
＝((CONSISTENT_GETS + DB_BLOCK_GETS)-(PHYSICAL_READS))➗(CONSISTENT_GETS + DB_BLOCK_GETS)✖️100

データ取得SQL文

DBバッファヒット率を取得する方法はいくつかありますが、ここではSTATS$BUFFER_POOL_STATISTICS表を使用する方法を紹介します。
この表はV$BUFFER_POOL_STATISTICSのスナップショットを保持しているものです。
また、DBバッファヒット率だけではなくBuffer Busy Wait統計情報も一緒に取得します。

今回のSTATSPACK環境は前回とは違って１時間ごとにスナップショットを取得している環境です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
select
os.INSTANCE_NUMBER
,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
--,os.SNAP_ID SNAP_ID1
--,ns.SNAP_ID SNAP_ID2
,nb.NAME buffer_pool_name
,decode(( (nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)),0,0,  -- 0除算回避
(trunc((((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
         )
         -(nb.PHYSICAL_READS  - ob.PHYSICAL_READS)
        )
        /((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
         )*100,1
       )
)
) bhr
,nb.BUFFER_BUSY_WAIT - ob.BUFFER_BUSY_WAIT bbw
from
STATS$SNAPSHOT os
,STATS$SNAPSHOT ns
,STATS$BUFFER_POOL_STATISTICS ob
,STATS$BUFFER_POOL_STATISTICS nb
where 1=1
and trunc(os.SNAP_TIME,'mi') between to_date('xxxx/xx/xx 00','yyyy/mm/dd hh24')
                                 and to_date('xxxx/xx/xx 23','yyyy/mm/dd hh24')
and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/24
and os.SNAP_ID               = ob.SNAP_ID
and os.DBID                  = ob.DBID
and os.INSTANCE_NUMBER       = ob.INSTANCE_NUMBER
and ns.SNAP_ID               = nb.SNAP_ID
and ns.DBID                  = nb.DBID
and os.INSTANCE_NUMBER       = ns.INSTANCE_NUMBER
and os.INSTANCE_NUMBER       = nb.INSTANCE_NUMBER
and os.INSTANCE_NUMBER       = 1
order by
os.instance_number
,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

実行例

実行例は以下のようになります。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
SQL> set pages 50
SQL> col BHR for 990.0
SQL> select
  2   os.INSTANCE_NUMBER
  3  ,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
  4  --,os.SNAP_ID SNAP_ID1
  5  --,ns.SNAP_ID SNAP_ID2
  6  ,nb.NAME buffer_pool_name
  7  ,decode(( (nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
  8           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)),0,0,  -- 0除算回避
  9   (trunc((((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
10           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
11           )
12           -(nb.PHYSICAL_READS  - ob.PHYSICAL_READS)
13          )
14          /((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
15           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
16           )*100,1
17         )
18   )
19  ) bhr
20  ,nb.BUFFER_BUSY_WAIT - ob.BUFFER_BUSY_WAIT bbw
21  from
22   STATS$SNAPSHOT os
23  ,STATS$SNAPSHOT ns
24  ,STATS$BUFFER_POOL_STATISTICS ob
25  ,STATS$BUFFER_POOL_STATISTICS nb
26  where 1=1
27  and trunc(os.SNAP_TIME,'mi') between to_date('2015/07/28 00','yyyy/mm/dd hh24')
28                                   and to_date('2015/07/28 23','yyyy/mm/dd hh24')
29  and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/24
30  and os.SNAP_ID               = ob.SNAP_ID
31  and os.DBID                  = ob.DBID
32  and os.INSTANCE_NUMBER       = ob.INSTANCE_NUMBER
33  and ns.SNAP_ID               = nb.SNAP_ID
34  and ns.DBID                  = nb.DBID
35  and os.INSTANCE_NUMBER       = ns.INSTANCE_NUMBER
36  and os.INSTANCE_NUMBER       = nb.INSTANCE_NUMBER
37  and os.INSTANCE_NUMBER       = 1
38  order by
39   os.instance_number
40  ,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');
 
INSTANCE_NUMBER SDATE            BUFFER_POOL_NAME        BHR        BBW
--------------- ---------------- -------------------- ------ ----------
              1 2015/07/28 00:00 DEFAULT               100.0          0
              1 2015/07/28 01:00 DEFAULT               100.0          1
              1 2015/07/28 02:00 DEFAULT               100.0          0
              1 2015/07/28 03:00 DEFAULT               100.0          0
              1 2015/07/28 04:00 DEFAULT               100.0          0
              1 2015/07/28 05:00 DEFAULT               100.0          0
              1 2015/07/28 06:00 DEFAULT               100.0          1
              1 2015/07/28 07:00 DEFAULT               100.0          0
              1 2015/07/28 08:00 DEFAULT               100.0          0
              1 2015/07/28 09:00 DEFAULT               100.0          0
              1 2015/07/28 10:00 DEFAULT               100.0          0
              1 2015/07/28 11:00 DEFAULT               100.0          0
              1 2015/07/28 12:00 DEFAULT               100.0          0
              1 2015/07/28 13:00 DEFAULT               100.0          0
              1 2015/07/28 14:00 DEFAULT               100.0          1
              1 2015/07/28 15:00 DEFAULT               100.0          0
              1 2015/07/28 16:00 DEFAULT               100.0          2
              1 2015/07/28 17:00 DEFAULT                99.9          5
              1 2015/07/28 18:00 DEFAULT               100.0          0
              1 2015/07/28 19:00 DEFAULT               100.0          0
              1 2015/07/28 20:00 DEFAULT               100.0          0
              1 2015/07/28 21:00 DEFAULT               100.0          0
              1 2015/07/28 22:00 DEFAULT               100.0          0
              1 2015/07/28 23:00 DEFAULT               100.0          0
 
24行が選択されました。

この環境はアクティビティがほとんどないので、DBバッファヒット率はほぼ100%ですが、お手元のSTATSPACKではどのような結果が得られるでしょうか？
この結果を、MS Excelのシートにコピー＆ペーストしてグラフにしていくのですが、その要領は次回で紹介します。

テーブルの結合列について

STATS$SNAPSHOT表の主キーを構成する列を確認すると「SNAP_ID,DBID,INSTANCE_NUMBER」の３つの列から成っていることがわかります。

1
2
3
4
5
6
7
8
9
10
SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  2  from USER_CONS_COLUMNS
  3  where CONSTRAINT_NAME = 'STATS$SNAPSHOT_PK'
  4  order by POSITION;
 
TABLE_NAME      CONSTRAINT_NAME    COLUMN_NAME       POSITION
--------------- ------------------ --------------- ----------
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  SNAP_ID                  1
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  DBID                     2
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  INSTANCE_NUMBER          3

一方、結合表であるSTATS$BUFFER_POOL_STATISTICS表の外部キーを確認すると、同様に「SNAP_ID,DBID,INSTANCE_NUMBER」となっていることがわかります。

1
2
3
4
5
6
7
8
9
10
SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  2  from USER_CONS_COLUMNS
  3  where CONSTRAINT_NAME = 'STATS$BUFFER_POOL_STATS_FK'
  4  order by POSITION;
 
TABLE_NAME                    CONSTRAINT_NAME              COLUMN_NAME       POSITION
----------------------------- ---------------------------- --------------- ----------
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   SNAP_ID                  1
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   DBID                     2
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   INSTANCE_NUMBER          3

STATSPACKデータのグラフ化は、基本的にSTATS$SNAPSHOT表と関連する表を結合してデータを取得しますが、結合列はこれら３つの列を必ず指定するようにしましょう。

続く

STATSPACKデータをExcelでグラフ化する①

今週の名言

「成功の秘訣は、職業をレジャーとみなすことだ。」
マーク・トウェイン

STATSPACKについて書かれた名著

私の手元に１０年以上前に買った素晴らしい本があります。ORACLE9i ハイパフォーマンスチューニング―STATSPACK編という本で、実に１３年近く前に出版されすでに絶版となっていますが、古本市場ではまだ入手できるようです。

私はこの本を自炊（スキャナで読み込んでPDF化）しiPadに入れているのですが、今でも時々読み返すことがあります。

Oracleは今や12cとなっていますが、個人的にはOracleの基本的なアーキテクチャは8iくらいで出来上がり、9iは各種管理機能の自動化が進み始めたバージョンという認識です。

まだ、ASMは登場しておらずRACもあまり洗練されたものではありませんでしたが、インスタンス単体レベルの基本機能は9iでかなり完成されているので、9i時代に出版された本は今でも十分読むに価する内容です。また、8iや9i時代に出版されたOracle関連本は内容の濃さからより深いOracleを理解する上で非常に役に立ちます。

その中でもこの本にはSTATSPACKデータを活用することで、データベースの状態を把握するノウハウが満載です。

特に、インスタンスの情報をMS Excelでグラフ化することで傾向を分析し、問題が起きる前にプロアクティブな対応をする情報を得るテクニックはかなり有益です。

Oracle Enterprise Manager(OEM)でもAWRデータをグラフ表示させることは可能ですが、特にコスト上の理由からStandard Editionを使っている環境でAWRが使用できない場合でも、STATSPACKデータをExcelでグラフ化できれば、かなり高度な運用が可能になるかもしれません。

定期的にスナップショットを取って、２つのスナップショット間の差分データでレポートを作成するという仕組みはSTATSPACKもAWRも同じなので、実はグラフ化自体はAWRデータでも可能です。

しかし、このブログではORACLE9i ハイパフォーマンスチューニング―STATSPACK編で紹介されたSTATSPACKデータを活用するというコンセプトを継承しつつ、私が考案した独自スクリプトを紹介していきたいと思います。

ちなみに、この方法は以前実際に業務で使用したこともあります。

基本はスナップ番号のリスト

STATSPACKは、AWRのように最初から使えるようになっているものではないので、自分でインストールする必要がありますし、スナップショットを定期的に自動取得する設定も自分で行う必要があります。

この辺の要領は割愛しますが、STATSPACK がインストールされ３０分ごとにスナプッショトが取得されている環境を例に説明を始めます。

スナップショットが実行される度に、stats$snapshot表に１行レコードがインサートされます。

ある時間帯に実行されたスナップショットの番号（stats$snapshot.snap_id）と１つ前のスナップ番号の組み合わせが重要です。ある日付を指定し、スナップショット取得インターバルごとに２つのスナップショット番号を一覧化するSQLスクリプトが以下となります。

スナップ番号一覧取得スクリプト

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
 os.instance_number
,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
,os.SNAP_ID SNAP_ID1
,ns.SNAP_ID SNAP_ID2
from
 perfstat.stats$snapshot os
,perfstat.stats$snapshot ns
where 1=1
and trunc(os.SNAP_TIME,'mi') between to_date('xxxx/xx/xx 00','yyyy/mm/dd hh24') and to_date('xxxx/xx/xx+1 00','yyyy/mm/dd hh24') -- 日付で抽出
and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/48 -- インターバル分だけずらす
and os.instance_number = ns.instance_number
order by
 os.instance_number
,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

実際は、１０行目の「xxxx/xx/xx」の部分に特定の日付を指定します。

１１行目の「1/48」は３０分のインターバルという意味で、多くの場合の１時間のインターバルであれば「1/24」となります。

もし、RAC環境でSTATSPACKを使っている場合は、「os.instance_number =1」などのインスタンス番号を指定する条件を追加してください。

実行例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
06:59:43 SQL> run
  1  select
  2   os.instance_number
  3  ,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
  4  ,os.SNAP_ID SNAP_ID1
  5  ,ns.SNAP_ID SNAP_ID2
  6  from
  7   perfstat.stats$snapshot os
  8  ,perfstat.stats$snapshot ns
  9  where 1=1
 10  and trunc(os.SNAP_TIME,'mi') between to_date('2015/07/21 00','yyyy/mm/dd hh24') and to_date('2015/07/22 00','yyyy/mm/dd hh24') -- 日付で抽出
 11  and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/48 -- インターバル分だけずらす
 12  and os.instance_number = ns.instance_number
 13  order by
 14   os.instance_number
 15* ,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi')
 
INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 00:27         62         63
              1 2015/07/21 00:57         63         64
              1 2015/07/21 01:27         64         65
              1 2015/07/21 01:57         65         66
              1 2015/07/21 02:27         66         67
              1 2015/07/21 02:57         67         68
              1 2015/07/21 03:27         68         69
              1 2015/07/21 03:57         69         70
              1 2015/07/21 04:27         70         71
              1 2015/07/21 04:57         71         72
              1 2015/07/21 05:27         72         73
 
INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 05:57         73         74
              1 2015/07/21 06:27         74         75
              1 2015/07/21 06:57         75         76
              1 2015/07/21 07:27         76         77
              1 2015/07/21 07:57         77         78
              1 2015/07/21 08:27         78         79
              1 2015/07/21 08:57         79         80
              1 2015/07/21 09:58         81         82
              1 2015/07/21 10:28         82         83
              1 2015/07/21 10:58         83         84
              1 2015/07/21 11:28         84         85
 
INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 11:58         85         86
              1 2015/07/21 12:28         86         87
              1 2015/07/21 12:58         87         88
              1 2015/07/21 13:28         88         89
              1 2015/07/21 13:58         89         90
              1 2015/07/21 14:28         90         91
              1 2015/07/21 14:58         91         92
              1 2015/07/21 15:28         92         93
              1 2015/07/21 15:58         93         94
              1 2015/07/21 16:28         94         95
              1 2015/07/21 16:58         95         96
 
INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 17:28         96         97
              1 2015/07/21 17:58         97         98
              1 2015/07/21 18:28         98         99
              1 2015/07/21 18:58         99        100
              1 2015/07/21 19:28        100        101
              1 2015/07/21 19:58        101        102
              1 2015/07/21 20:28        102        103
              1 2015/07/21 20:58        103        104
              1 2015/07/21 21:28        104        105
              1 2015/07/21 22:29        111        112
              1 2015/07/21 22:59        112        113
 
INSTANCE_NUMBER SDATE              SNAP_ID1   SNAP_ID2
--------------- ---------------- ---------- ----------
              1 2015/07/21 23:29        113        114
              1 2015/07/21 23:59        114        115
 
46行が選択されました。

手元の環境（ノートPC）で作ったサンプルなので１日２４時間で４８レコードとなるはずなのに２つ足りないのはご愛嬌ですが、３０分間隔で連続する２つのスナップ番号の組が得られていることがおわかりでしょうか？

次回以降で、この基本的な問い合わせをベースとして様々なパフォーマンス・データをグラフ化していきます。

続く

インスタンスのリスナーへの登録（その２）

今週の名言

「今日できることを明日にまで延ばすな｡」
フィリップ・ チェスターフィールド

前回のおさらい（Oracle 11gR2）

  1. リスナーにインスタンスがサービスとして登録されて初めて、インスタンス（データベース）に対するネット接続が可能となります。
  2. リスナーを後から起動すると、インスタンスが登録するまでにある程度の時間がかかります。（PMONが定期的にリスナーに登録しにいくので最長60秒程度かかる場合があります。）
  3. リスナーを先に起動しておくと、インスタンスは起動直後にリスナーに登録されます。
  4. 2.の場合でも「ALTER SYSTEM REGISTER」コマンドを実行すると、インスタンスを即時にリスナーへ登録することができます。

前回は、インスタンスのリスナーへの登録に関する挙動についてOracle11gR2で確認をしてみましたが、Oracle12cR1で同様の検証をしてみます。

サービス登録の動作を確認する（12cR1）

1. インスタンスを先に起動し、リスナーを後から起動する。

インスタンスを起動する

1
2
3
4
5
6
7
8
9
10
11
xx:xx:xx SQL> startup
ORACLEインスタンスが起動しました。
 
Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             608176880 bytes
Database Buffers          222298112 bytes
Redo Buffers                5455872 bytes
データベースがマウントされました。
データベースがオープンされました。
14:37:15 SQL>

アラートログ

2015-07-05 14:37:15.408000 +09:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN
CJQ0 started with pid=31, OS id=3721

14:37:15 にインスタンス（データベース）が起動されました。

（正確には、インスタンスとはプロセス群とメモリから成るため、メモリ情報が表示された時がインスタンスの起動時刻となりますが、ここではインスタンス＝データベースの意味で使っていますので、データベースがオープンされた時刻をインスタンス起動と言っています。

このタイミングで tnsping と接続の確認を行ってみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[xx:xx:xx] $ tnsping ora12c
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 14:38:16
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
パラメータ・ファイルを使用しました:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
 
エイリアスを解決するためにTNSNAMESアダプタを使用しました。
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora12c)))に接続の試行中
TNS-12541: TNS: リスナーがありません。
[14:38:16] $
 
[xx:xx:xx] $ sqlplus /nolog
 
SQL*Plus: Release 12.1.0.2.0 Production on 日 7月 5 14:39:24 2015
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12541: TNS: リスナーがありません
 
14:39:33 SQL>

リスナーが起動していないので、tnspingも接続も失敗します。
14:39:33 にネット接続を試みましたが、成功しませんでした。

リスナーを起動する

インスタンスの次にリスナーを起動します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[xx:xx:xx] $ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 14:40:54
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...
 
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    05-7月 -2015 14:40:54
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[14:40:54] $

14:40:54 にリスナーが起動しました。

リスナー起動直後に別コンソールからネット接続を試みます。

1
2
3
4
5
6
7
8
9
10
11
12
13
xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
14:40:57 SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
14:41:06 SQL> conn scott/tiger@ora12c
接続されました。
14:41:17 SQL>

14:40:57 と 14:41:06 にネット接続の試みは失敗し、14:41:17 に成功しました。
リスナー起動から23秒後に接続を確認できました。

リスナーログ

05-7月 -2015 14:40:57 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24694)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:41:06.873000 +09:00
05-7月 -2015 14:41:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24713)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:41:09.718000 +09:00
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
05-7月 -2015 14:41:09 * service_register * ora12c * 0
2015-07-05 14:41:17.722000 +09:00
05-7月 -2015 14:41:17 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24735)) * establish * ora12c * 0
05-7月 -2015 14:41:18 * service_update * ora12c * 0

リスナーログから、正確には 14:41:09 に「service_register」確認できていますので、リスナー起動後 15秒でインスタンスが登録されたことがわかります。

2. リスナーを先に起動し、インスタンスを後から起動する

リスナーがすでに起動されている状態から、インスタンスを起動します。

1
2
3
4
5
6
7
8
9
10
11
xx:xx:xx SQL> startup
ORACLEインスタンスが起動しました。
 
Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             608176880 bytes
Database Buffers          222298112 bytes
Redo Buffers                5455872 bytes
データベースがマウントされました。
データベースがオープンされました。
14:47:10 SQL>

14:47:10 にインスタンスが起動しました。

別コンソールから、インスタンス起動前と起動後にネット接続を試みます。

1
2
3
4
5
6
7
8
xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
14:47:03 SQL> conn scott/tiger@ora12c
接続されました。
14:47:13 SQL>

14:47:03 に接続失敗していますが、14:47:13 には成功しています。

2015-07-05 14:46:19.450000 +09:00
05-7月 -2015 14:46:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24780)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:47:03.841000 +09:00
05-7月 -2015 14:47:03 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24783)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
05-7月 -2015 14:47:04 * service_register * ora12c * 0
2015-07-05 14:47:07.074000 +09:00
05-7月 -2015 14:47:07 * service_update * ora12c * 0
2015-07-05 14:47:08.779000 +09:00
05-7月 -2015 14:47:08 * service_update * ora12c * 0
2015-07-05 14:47:10.028000 +09:00
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
05-7月 -2015 14:47:10 * service_update * ora12c * 0
05-7月 -2015 14:47:10 * service_update * ora12c * 0
05-7月 -2015 14:47:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24787)) * establish * ora12c * 0
2015-07-05 14:47:13.033000 +09:00
05-7月 -2015 14:47:13 * service_update * ora12c * 0
2015-07-05 14:47:16.037000 +09:00
05-7月 -2015 14:47:16 * service_update * ora12c * 0
2015-07-05 14:47:46.056000 +09:00
05-7月 -2015 14:47:46 * service_update * ora12c * 0

3. インスタンスを先に起動し、リスナーを後から起動する。その後サービス登録コマンドを実行する。

インスタンスは起動済みですが、リスナーは起動されていない状態から始めます。

リスナーを起動する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[xx:xx:xx] $ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 14:54:43
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
/u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnrを起動しています。お待ちください...
 
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    05-7月 -2015 14:54:43
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[14:54:43] $

14:54:43 にリスナーが起動しました。

サービス登録コマンドを実行する

1
2
3
xx:xx:xx SQL> alter system register;
 システムが変更されました。
14:54:49 SQL>

14:54:49 にサービス登録コマンドを実行しました。

別コンソールから接続確認を行う。

確認を行った時間に注目してください。

1
2
3
4
5
6
7
8
9
10
11
12
13
xx:xx:xx SQL> conn scott/tiger@ora12c
ERROR:
ORA-12541: TNS: リスナーがありません
 
警告: Oracleにはもう接続されていません。
14:54:14 SQL> conn scott/tiger@ora12c
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
14:54:46 SQL> conn scott/tiger@ora12c
接続されました。
14:54:55 SQL>

14:54:14 は、まだリスナーが起動されていません。
14:54:46 は、リスナーが起動されていますが、インスタンスが登録されていません。
14:54:55 は、インスタンスが登録されたので接続に成功しています。

リスナーログ

05-7月 -2015 14:54:46 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24826)) * establish * ora12c * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 14:54:49.477000 +09:00
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
05-7月 -2015 14:54:49 * service_register * ora12c * 0
2015-07-05 14:54:55.269000 +09:00
05-7月 -2015 14:54:55 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora12c)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux6.onefact.jp)(USER=ora12c))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.18)(PORT=24846)) * establish * ora12c * 0
05-7月 -2015 14:54:55 * service_update * ora12c * 0
2015-07-05 14:55:34.498000 +09:00
05-7月 -2015 14:55:34 * service_update * ora12c * 0
2015-07-05 14:57:10.577000 +09:00
05-7月 -2015 14:57:10 * service_update * ora12c * 0

12cでも、状況を見る限りでは11gと同じ挙動が確認できました。

リスナー登録（LREG）プロセスについて深掘りする

サービス登録の挙動に関しては、11g と 12c で違いはありませんでしたが、12cから新しくできたLREGプロセスについてもっと深く調べてみます。

まずは、いきなりですがサービスが登録された状態からLREGプロセスをkillしてみます。

LREGをkillしてみる

1
2
3
4
5
[xx:xx:xx] $ ps -ef | grep lreg
ora12c    3982     1  0 14:47 ?        00:00:00 ora_lreg_ora12c
ora12c    4347  3108  0 15:01 pts/4    00:00:00 grep lreg
[xx:xx:xx] $ kill -9 3982
[15:01:22] $

15:01:22 にプロセスがkillされました。

アラートログ

2015-07-05 15:01:23.798000 +09:00
Instance Critical Process (pid: 19, ospid: 3982, LREG) died unexpectedly
PMON (ospid: 3946): terminating the instance due to error 500
System state dump requested by (instance=1, osid=3946 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_diag_3960_20150705150123.trc
Dumping diagnostic data in directory=[cdmp_20150705150123], requested by (instance=1, osid=3946 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 3946

LREGの異常終了を検知して、最終的にPMONがインスタンスをダウンさせています。

つまり、LREGは必須プロセスです。

リスナーログ

2015-07-05 15:00:55.732000 +09:00
05-7月 -2015 15:00:55 * service_update * ora12c * 0
2015-07-05 15:01:22.006000 +09:00
リスニングしていません: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
05-7月 -2015 15:01:22 * service_died * ora12c * 12537

LREGが異常終了してインスタンスがダウンすると、リスナーには「service_died」のステータスが伝達され、インスタンスの登録が抹消されます。

まとめ

リスナーにインスタンスがサービスとして登録されるところを見てきましたが、HA構成で運用しているデータベースでは起動スクリプトにおいて、「データベース⇨リスナー」の順に起動している場合があるかもしれません。（私が１５年前にDBAをしていた時のスクリプトはそうでした。）

RACになって、特に11g移行はGrid Infrastructureの一部としてリスナーはデータベースの前に起動する仕組みになっていますが、まだまだHA構成のデータベースは世の中にあると思いますので、フェイルオーバー時にサービス登録が速やかに行われるよう「alter system register」コマンドが実行されるようになっているかを確認しても良いのではないでしょうか？

終わり

次回は、このプロセスの内部動作に迫ってみます。

続く

インスタンスのリスナーへの登録（その１）

今週の名言

「すべて商売は売りて喜び、買いて喜ぶようにすべし。売りて喜び、買いて喜ばざるは道にあらず。貸借の道もまた貸して喜び、借りて喜ばざるは道にあらず。」
二宮尊徳

今回もOracle Net関連

リスナーは重要

Oracleデータベースはよほどのことがない限り、クライアントからネットワーク経由で接続して使います。従って接続に伴う問題は非常に深刻です。

Oracleサポートの現場でも、接続障害の解決に求められる緊急度は特に高いようです。
Oracle Databaseで接続障害が発生した際の調査手法

最近のWebアプリケーションは、コネクションプールにより常時セッションを張っていることが多いので、リスナーは１日１回早朝等に再接続をするときだけ起動していればよいという考え方もあります。

しかし、データベースが正常に稼働していたとしても、リスナーが起動していなければ新たな接続すらできないので、リスナー・プロセスの起動とそれを監視することはやはり必要です。

インスタンスはリスナーに登録されなければならない

起動されたインスタンス（データベース）は、リスナーにサービスとして登録されていなければなりません。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ lsnrctl status
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-7月 -2015 16:01:58
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    05-7月 -2015 14:54:43
稼働時間                  0 日 1 時間 7 分 15 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux6.onefact.jp)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oraclelinux6.onefact.jp)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
サービスのサマリー...
サービス"ora12c"には、1件のインスタンスがあります。
  インスタンス"ora12c"、状態READYには、このサービスに対する1件のハンドラがあります...
サービス"ora12cXDB"には、1件のインスタンスがあります。
  インスタンス"ora12c"、状態READYには、このサービスに対する1件のハンドラがあります...
コマンドは正常に終了しました。

Oracle 12cからサービス登録が変わった？

マニュアルを見ていて、11gと12cの微妙な違いに気がつきました。

Oracle® Database Net Services管理者ガイド
11gリリース2 (11.2)
B56288-04

サービス登録

PMONプロセスがリスナーに自動的に情報を登録する機能。

Oracle® Database Net Services管理者ガイド
12cリリース1 (12.1)
B71288-03

サービス登録

リスナー登録(LREG)プロセスがリスナーに自動的に情報を登録する機能です。

12cになって、従来のPMONに代わって新規に実装されたLREGというプロセスがサービス（リスナー）登録を行うようになったようですが、サービス登録の内部動作が変わったりしたのでしょうか？
気になったので確認してみることにします。

サービス登録の動作を確認する

11gR2

1. インスタンスを先に起動し、リスナーを後から起動する。

インスタンスとリスナーのどちらを先に起動させるかで、挙動が異なるかを確認します。
最初にデータベースを起動します。

データベースを起動する。

1
2
3
4
5
6
7
8
9
10
11
xx:xx:xx SQL> startup
ORACLEインスタンスが起動しました。
 
Total System Global Area  835104768 bytes
Fixed Size            2257840 bytes
Variable Size          536874064 bytes
Database Buffers      289406976 bytes
Redo Buffers            6565888 bytes
データベースがマウントされました。
データベースがオープンされました。
13:45:46 SQL>

アラートログ

2015-07-05 13:45:46.982000 +09:00
Completed: ALTER DATABASE OPEN
db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
CJQ0 started with pid=21, OS id=4628

13:45:46 にインスタンスが起動しました。

このタイミングで、tnspingと接続の確認を行ってみます。

1
2
3
4
5
6
7
8
9
[xx:xx:xx] $ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.4.0 Production on 日 7月 5 13:47:49 2015
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
13:47:49 SQL> conn scott/tiger@orcl
ERROR:
ORA-12541: TNS: リスナーがありません

リスナーが起動していないので、tnspingも接続も失敗することがわかります。

リスナーを起動する。

インスタンスの次にリスナーを起動します。

1
2
3
4
[xx:xx:xx] $ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-7月 -2015 13:48:51
............................................................................

リスナー起動直後に別コンソールからネット接続を試みます。

1
2
3
4
5
6
7
8
9
10
11
xx:xx:xx SQL> conn scott/tiger@orcl
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
13:48:53 SQL> conn scott/tiger@orcl
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
13:49:10 SQL>

13:48:53 と 13:49:10 にネット接続を試みましたが、成功しませんでした。

リスナーログ

05-7月 -2015 13:48:51 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oraclelinux.onefact.jp)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0
2015-07-05 13:48:53.488000 +09:00
05-7月 -2015 13:48:53 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60167)) * establish * orcl.onefact.jp * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません
2015-07-05 13:49:10.279000 +09:00
05-7月 -2015 13:49:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60185)) * establish * orcl.onefact.jp * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません

13:48:51 にリスナーを起動しましたが、ネット接続を試みた時点でリスナーがサービスを認識していないことはリスナーログからも確認することができます。

それでも、接続を試みます。

1
2
3
xx:xx:xx SQL> conn scott/tiger@orcl
接続されました。
13:49:46 SQL>

13:49:46 にやっとネット接続が成功しました。

リスナーログ抜粋

2015-07-05 13:49:43.579000 +09:00
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
05-7月 -2015 13:49:43 * service_register * orcl * 0
2015-07-05 13:49:46.453000 +09:00
05-7月 -2015 13:49:46 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60205)) * establish * orcl.onefact.jp * 0
05-7月 -2015 13:49:46 * service_update * orcl * 0
2015-07-05 13:50:28.619000 +09:00

リスナーログを見ると、13:49:43 に「service_register」というのが確認できます。
これがリスナーがインスタンスをサービスとして認識したことを表し、以降ネット接続が可能となります。

リスナーを起動してからサービスが登録されるまで、52秒もかかっています。

2. リスナーを先に起動し、インスタンスを後から起動する。

時間の前後が逆になってしまっていますが、今度はリスナーを先に起動するパターンを確認します。

リスナー起動前の状態

1
2
3
4
5
6
7
8
9
10
11
12
[13:37:19] $ tnsping orcl
 
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-7月 -2015 13:37:45
 
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
 
パラメータ・ファイルを使用しました:
/u01/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora
 
エイリアスを解決するためにTNSNAMESアダプタを使用しました。
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux.onefact.jp)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.onefact.jp)))に接続の試行中
TNS-12541: TNS: リスナーがありません。

リスナーを起動する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[13:36:38] $ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-7月 -2015 13:38:18
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
/u01/app/oracle/product/11.2.0.4/db_1/bin/tnslsnrを起動しています。お待ちください...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 11.2.0.4.0 - Production
開始日                    05-7月 -2015 13:38:18
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[13:38:18] $

13:38:18 にリスナーを起動しましたが、インスタンスはまだ起動されていないので、当然サービスとして登録されていません。

リスナーログ抜粋

2015-07-05 13:38:18.674000 +09:00
システム・パラメータ・ファイルは/u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux/listener/alert/log.xmlに書き込みました。
トレース情報を/u01/app/oracle/diag/tnslsnr/oraclelinux/listener/trace/ora_4467_140172889650944.trcに書き込みました。
トレース・レベルは現在0です。

pid=4467で起動しました
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
05-7月 -2015 13:38:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oraclelinux.onefact.jp)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0

ここでtnspingを試してみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
[13:37:45] $ tnsping orcl
 
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-7月 -2015 13:39:17
 
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
 
パラメータ・ファイルを使用しました:
/u01/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora
 
エイリアスを解決するためにTNSNAMESアダプタを使用しました。
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux.onefact.jp)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.onefact.jp)))に接続の試行中
OK (0ミリ秒)
[13:39:17] $

リスナーが起動済みなので、tnspingは成功します。
つまり、tnspingはインスタンスへの接続を確認するものではなく、リスナーが機能しているかを確認する目的で使うものです。

リスナーログ抜粋

2015-07-05 13:39:17.149000 +09:00
05-7月 -2015 13:39:17 * ping * 0

インスタンスを起動する

リスナーが起動されているのを確認できたので、次にインスタンスを起動します。

1
2
3
4
5
6
7
8
9
10
11
13:36:03 SQL> startup
ORACLEインスタンスが起動しました。
 
Total System Global Area  835104768 bytes
Fixed Size            2257840 bytes
Variable Size          536874064 bytes
Database Buffers      289406976 bytes
Redo Buffers            6565888 bytes
データベースがマウントされました。
データベースがオープンされました。
13:41:36 SQL>

13:41:36 にインスタンスが起動されました。

アラートログ抜粋

2015-07-05 13:41:36.771000 +09:00
Completed: ALTER DATABASE OPEN
db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
CJQ0 started with pid=22, OS id=4541

別コンソールからネット接続を試みます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[xx:xx:xx] $ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.4.0 Production on 日 7月 5 13:41:09 2015
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
13:41:09 SQL> conn scott/tiger@orcl
ERROR:
ORA-12528: TNS:リスナー:
該当するインスタンスはすべて、新規接続をブロックしています
 
13:41:31 SQL> conn scott/tiger@orcl
接続されました。
13:41:40 SQL>

startupコマンド投入直後である、13:41:09 においてリスナーは起動済みですが、まだインスタンスは新規接続を受け付けない状態です。（ORA-12528）
ただし、インスタンス起動完了直後の 13:41:40 には接続が成功していることがわかります。

リスナーを後から起動した場合と比べて明らかに早いです。

2015-07-05 13:41:29.890000 +09:00
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
05-7月 -2015 13:41:29 * service_register * orcl * 0
2015-07-05 13:41:31.303000 +09:00
2015-07-05 13:41:31.303000 +09:00
05-7月 -2015 13:41:31 * service_update * orcl * 0
05-7月 -2015 13:41:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60132)) * establish * orcl.onefact.jp * 12528
TNS-12528: TNS:リスナー: 該当するインスタンスはすべて、新規接続をブロックしています
2015-07-05 13:41:35.882000 +09:00
05-7月 -2015 13:41:35 * service_update * orcl * 0
2015-07-05 13:41:37.360000 +09:00
05-7月 -2015 13:41:37 * service_update * orcl * 0
2015-07-05 13:41:40.363000 +09:00
05-7月 -2015 13:41:40 * service_update * orcl * 0
05-7月 -2015 13:41:40 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60135)) * establish * orcl.onefact.jp * 0

3. インスタンスを先に起動し、リスナーを後から起動する。その後サービス登録コマンドを実行する。

インスタンスは起動済みですが、リスナーは起動されていない状態から始めます。
念のため、ネット接続を試みてみます。

1
2
3
4
5
xx:xx:xx SQL> conn scott/tiger@orcl
ERROR:
ORA-12541: TNS: リスナーがありません
 
13:59:47 SQL>

リスナーを起動する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[xx:xx:xx] $ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-7月 -2015 14:00:17
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
/u01/app/oracle/product/11.2.0.4/db_1/bin/tnslsnrを起動しています。お待ちください...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
システム・パラメータ・ファイルは/u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 11.2.0.4.0 - Production
開始日                    05-7月 -2015 14:00:17
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelinux.onefact.jp)(PORT=1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
[14:00:17] $

リスナー起動直後に接続確認をする。

1
2
3
4
5
6
xx:xx:xx SQL> conn scott/tiger@orcl
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
 
14:00:19 SQL>

リスナーログ抜粋

05-7月 -2015 14:00:17 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=oraclelinux.onefact.jp)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0
2015-07-05 14:00:19.326000 +09:00
05-7月 -2015 14:00:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60262)) * establish * orcl.onefact.jp * 12514
TNS-12514: TNS: リスナーは現在、接続識別子でリクエストされているサービスを認識していません

14:00:17 にリスナーを起動しましたが、起動直後はまだインスタンスを認識していないため接続は ORA(TNS)-12514 エラーで失敗します。

インスタンスを明示的にリスナーに登録する。

1
2
3
4
5
6
7
8
9
xx:xx:xx SQL> alter system register;
 
システムが変更されました。
 
13:59:31 SQL> alter system register;
 
システムが変更されました。
 
14:00:25 SQL>

14:00:25 に投入した「ALTER SYSTEM REGISTER」コマンドにより、1.のように52秒も待たずにインスタンスをリスナーへ登録することができます。
実は、リスナー起動前の 13:59:31 にもこのコマンドを実行していますが、インスタンス登録先のリスナーがないにもかかわらず特にエラーにならずに終了しています。

2015-07-05 14:00:25.437000 +09:00
05-7月 -2015 14:00:25 * service_register * orcl * 0
2015-07-05 14:00:33.233000 +09:00
05-7月 -2015 14:00:33 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.onefact.jp)(CID=(PROGRAM=sqlplus)(HOST=oraclelinux.onefact.jp)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.15)(PORT=60278)) * establish * orcl.onefact.jp * 0
2015-07-05 14:00:34.445000 +09:00
05-7月 -2015 14:00:34 * service_update * orcl * 0
1
2
3
conn scott/tiger@orcl
接続されました。
14:00:33 SQL>

インスタンスがリスナーに登録されたので、接続できるようになりました。

インスタンスを停止する。

インスタンスがリスナーに登録された状態から、インスタンスを停止させます。

1
2
3
4
5
14:00:25 SQL> shutdown immediate
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
14:14:36 SQL>

リスナーログ抜粋

2015-07-05 14:14:34.271000 +09:00
05-7月 -2015 14:14:34 * service_died * orcl * 12537

インスタンス（プロセス）が停止すると、リスナーから見てサービスは死んだとみなされ「service_died」がログに出力されます。

まとめ

  1. リスナーにインスタンスがサービスとして登録されて初めて、インスタンス（データベース）に対するネット接続が可能となります。
  2. リスナーを後から起動すると、インスタンスが登録するまでにある程度の時間がかかります。（PMONが定期的にリスナーに登録しにいくので最長60秒程度かかる場合があります。）
  3. リスナーを先に起動しておくと、インスタンスは起動直後にリスナーに登録されます。
  4. 2.の場合でも「ALTER SYSTEM REGISTER」コマンドを実行すると、インスタンスを即時にリスナーへ登録することができます。

長くなってしまったので、Oracle12cの結果は次回としたいと思います。

今回はここまで