Oracle」カテゴリーアーカイブ

Flashback Dropの検証②

前回のおさらい

JPOUG20160223.001

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

オブジェクトはテーブルとインデックス、あわせて5つ。すべて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関数の結果を横に並べたものである。
(前者の結果しかないように見えるが、横スクロールして後者の結果と比べてみてほしい。)

1回目にORDER BYあり、2回目に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」オペレーションの負荷が甚大になることが予想されるので、両者の違いが顕著になるかもしれない。

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

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

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

 

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

今週の名言

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

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

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

前回は、stats$snapshot表からスナップショット一覧を取得するSQL文を紹介しました。
今回から、応用編としてこの表とパフォーマンス・データが格納された表を結合して、1日の統計情報の推移を見てみます。
最初は取っ付き易い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時間ごとにスナップショットを取得している環境です。

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
  --,os.SNAP_ID SNAP_ID1
  --,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」の3つの列から成っていることがわかります。

1
2
3
4
5
6
7
8
9
10
SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  from USER_CONS_COLUMNS
  where CONSTRAINT_NAME = 'STATS$SNAPSHOT_PK'
  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
  from USER_CONS_COLUMNS
  where CONSTRAINT_NAME = 'STATS$BUFFER_POOL_STATS_FK'
  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表と関連する表を結合してデータを取得しますが、結合列はこれら3つの列を必ず指定するようにしましょう。

続く

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

今週の名言

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

STATSPACKについて書かれた名著

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

私はこの本を自炊(スキャナで読み込んで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でグラフ化できれば、かなり高度な運用が可能になるかもしれません。

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

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

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

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

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

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

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

ある時間帯に実行されたスナップショットの番号(stats$snapshot.snap_id)と1つ前のスナップ番号の組み合わせが重要です。ある日付を指定し、スナップショット取得インターバルごとに2つのスナップショット番号を一覧化する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');

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

11行目の「1/48」は30分のインターバルという意味で、多くの場合の1時間のインターバルであれば「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
  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
  from
  7   perfstat.stats$snapshot os
  8  ,perfstat.stats$snapshot ns
  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)で作ったサンプルなので1日24時間で48レコードとなるはずなのに2つ足りないのはご愛嬌ですが、30分間隔で連続する2つのスナップ番号の組が得られていることがおわかりでしょうか?

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

続く

インスタンスのリスナーへの登録(その2)

今週の名言

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

前回のおさらい(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:5714: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構成で運用しているデータベースでは起動スクリプトにおいて、「データベース⇨リスナー」の順に起動している場合があるかもしれません。(私が15年前にDBAをしていた時のスクリプトはそうでした。)

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

終わり

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

続く

インスタンスのリスナーへの登録(その1)

今週の名言

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

今回もOracle Net関連

リスナーは重要

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

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

最近のWebアプリケーションは、コネクションプールにより常時セッションを張っていることが多いので、リスナーは1日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
$ 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:5313: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の結果は次回としたいと思います。

今回はここまで

TNS_ADMIN環境変数について

今週の名言

「もし、8時間で木を切り倒せと言われたら、私は7時間を斧の刃を研ぐことにあてる。」
エイブラハム・リンカーン

TNS_ADMIN環境変数とは?

TNS_ADMIN環境変数とは、Oracle Net関連設定ファイルを任意のディレクトリに配置する際に使用するものです。

マニュアルには以下の記述があります。

Oracle® Database Net Servicesリファレンス
11gリリース2 (11.2)
B56287-05
Oracle Net Listener構成ファイルの概要
デフォルトで、listener.oraファイルはORACLE_HOME/network/adminディレクトリに配置されます。listener.oraファイルは次の場所に格納される場合もあります。

  • 環境変数TNS_ADMINまたはレジストリ値で指定されたディレクトリ。
  • LinuxおよびUNIXオペレーティング・システムの場合は、グローバル構成ディレクトリ。たとえば、Solarisオペレーティング・システムの場合、このディレクトリは/var/opt/oracleです。

ちなみにOracle Linux 6の場合、グローバル構成ディレクトリは「/etc」となります。

  1. デフォルト・ディレクトリ
  2. TNS_ADMINで指定された任意のディレクトリ
  3. グローバル構成ディレクトリ

という3つのディレクトリの優先順位がどうなっているかを確認してみます。(念のため仕様が変わっていないかを確認するためにOracle12cで検証します。)

3つのlistener.oraを準備する。

3つのlistener.oraを用意しますが、2番目以降は違いがわかるようにパラメータを追加しています。

デフォルト・ディレクトリ

1
2
3
4
5
6
7
8
9
10
11
$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

任意のディレクトリ(RECV_BUF_SIZEを追加)

1
2
3
4
5
6
7
8
9
10
11
$ cat $ORACLE_HOME/network/admin/test/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (RECV_BUF_SIZE=11784)
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

グローバル構成ディレクトリ(SEND_BUF_SIZEを追加)

1
2
3
4
5
6
7
8
9
10
11
$ cat /etc/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (SEND_BUF_SIZE=11280)
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelinux6.onefact.jp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

リスナーを起動してすぐ停止する。

デフォルト状態

TNS_ADMIN環境変数に何も設定されていないことを確認してからリスナーを起動します。

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
$ env|grep TNS_ADMIN
$
$ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx
 
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
システム・パラメータ・ファイルは/etc/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(SEND_BUF_SIZE=11280))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(SEND_BUF_SIZE=11280))
 
(DESCRIPTION=(SEND_BUF_SIZE=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    xx-6月 -2015 xx:xx:xx
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /etc/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(SEND_BUF_SIZE=11280))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(SEND_BUF_SIZE=11280))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
$ lsnrctl stop
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
(DESCRIPTION=(SEND_BUF_SIZE=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
コマンドは正常に終了しました。

パラメータファイルが「/etc/listener.ora」であることがわかります。
設定されたパラメータ(SEND_BUF_SIZE)も反映されています。

TNS_ADMIN環境変数を設定する。

TNS_ADMIN環境変数に検証用listener.oraを配置したディレクトリを指定してからリスナーを起動します。

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
$ export TNS_ADMIN=$ORACLE_HOME/network/admin/test/list
$ env|grep TNS_ADMIN
TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/db_1/network/admin/test
[ora12c@oraclelinux6 etc]$ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-6月 -2015 22:38:14
 
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/test/listener.oraです。
ログ・メッセージを/u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xmlに書き込みました。
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(RECV_BUF_SIZE=11784))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(RECV_BUF_SIZE=11784))
 
(DESCRIPTION=(RECV_BUF_SIZE=11784)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    xx-6月 -2015 xx:xx:xx
稼働時間                  0 日 0 時間 0 分 0 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/12.1.0.2/db_1/network/admin/test/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/oraclelinux6/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxxx)(PORT=1521))(RECV_BUF_SIZE=11784))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))(RECV_BUF_SIZE=11784))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
$ lsnrctl stop
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
(DESCRIPTION=(RECV_BUF_SIZE=11784)(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
コマンドは正常に終了しました。

指定されたディレクトリ下のlistener.oraがパラメータ・ファイルとして有効になり、RECV_BUF_SIZEパラメータが設定されていることがわかります。

/etc/listener.oraを無効にする。

「/etc/listener.ora」をRenameして無効化した後、TNS_ADMIN環境変数をクリアし、リスナーを起動します。

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
$ mv /etc/listener.ora /etc/listener.ora.bak
$ ls /etc/listener.ora*
/etc/listener.ora.bak
$ unset TNS_ADMIN
$ env|grep TNS_ADMIN
$
$ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx
 
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=xxxxxxxxxx)(PORT=1521)))
リスニングしています: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 12.1.0.2.0 - Production
開始日                    xx-6月 -2015 xx:xx:xx
稼働時間                  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=xxxxxxxxxx)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
リスナーはサービスをサポートしていません。
コマンドは正常に終了しました。
$ lsnrctl stop
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on xx-6月 -2015 xx:xx:xx
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxx)(PORT=1521)))に接続中
コマンドは正常に終了しました。

パラメータ・ファイルはデフォルト・ディレクトリのlistener.oraで、追加のパラメータが設定されていないことがわかります。

結論:TNS_ADMINで指定されたディレクトリ > グローバル構成ディレクトリ > デフォルト・ディレクトリ の順にlistener.oraを探して起動する。

リスナーのパラメータを新たにテストする際、デフォルト・ディレクトリのlistener.oraを変更(編集)しなくても、TNS_ADMIN環境変数を一時的に設定し別のlistener.oraを使用してリスナーを起動することができます。

今日はここまで。

Oracleバージョンによるヒント句の変遷

今週の名言

「世間で頭角をあらわす人物は、自分の望む環境を自ら捜し求める人物であり、もしそれが見つからない時は自分で創り出す人物である。」
ジョージ・バーナード・ショー

今回もトリビアネタ

Oracleエラーを数える回ではとんだ墓穴を掘ってしまいましたが、懲りずに今回はヒント句の種類、しかもOracleのバージョンが進化していく間にどんなヒント句が追加されてきたのかという変遷をたどってみます。

環境は12cR1

今回使用する環境はOracle12cR1です。

1
2
3
4
5
6
7
8
9
SQL> SELECT BANNER FROM V$VERSION;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0    Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

使用するビューはV$SQL_HINT

V$SQL_HINTは11gからあるようですが、ヒント句にどんなのがあるのかを確認できるなかなか面白いビューです。
なぜか12cになってもアンドキュメントなビューなので気になって調べてみました。

ただ、一覧表示させるだけではつまらないので、バージョンごとにまとめて集計してみました。

Oracle8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> SELECT VERSION,CLASS,NAME HINT_NAME,INVERSE,SQL_FEATURE
  FROM V$SQL_HINT
  ORDER BY
  4   TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(VERSION,'\.','',1,2),'\.','',1,2),99.999)
  5  ,CLASS,NAME;
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.0.0      ACCESS                           CLUSTER                                                           QKSFM_CBO
                                            INDEX                            NO_INDEX                         QKSFM_INDEX
                                            QUEUE_CURR                                                        QKSFM_CBO
                                            QUEUE_ROWP                                                        QKSFM_CBO
                                            ROWID                                                             QKSFM_CBO
           ANTIJOIN                         NL_AJ                                                             QKSFM_JOIN_METHOD
           EXPR_CORR_CHECK                  EXPR_CORR_CHECK                                                   QKSFM_CBO
           MERGE                            NO_MERGE                         MERGE                            QKSFM_CVM
           MERGE_CONST_ON                   MERGE_CONST_ON                                                    QKSFM_CBO
           NO_MONITORING                    NO_MONITORING                                                     QKSFM_ALL
           NO_ORDER_ROLLUPS                 NO_ORDER_ROLLUPS                                                  QKSFM_TRANSFORMATION
           NO_STATS_GSETS                   NO_STATS_GSETS                                                    QKSFM_ALL
           ORDERED_PREDICATES               ORDERED_PREDICATES                                                QKSFM_CBO
           SEMIJOIN                         NL_SJ                                                             QKSFM_JOIN_METHOD
********** ******************************** --------------------------------
count                                                                     14

INDEXヒントは8.0.0からあります。
NL_AJ, NL_SJ, NO_MERGE なんかもありますね。
この頃はヒントはまだ14個しかなかったのでしょうか。

Oracle8i

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
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.0      ACCESS                           AND_EQUAL                                                         QKSFM_AND_EQUAL
                                            FULL                                                              QKSFM_FULL
                                            HASH                                                              QKSFM_ALL
                                            INDEX_ASC                        NO_INDEX                         QKSFM_INDEX_ASC
                                            INDEX_COMBINE                                                     QKSFM_INDEX_COMBINE
                                            INDEX_DESC                       NO_INDEX                         QKSFM_INDEX_DESC
                                            INDEX_FFS                                                         QKSFM_INDEX_FFS
           ANTIJOIN                         HASH_AJ                                                           QKSFM_JOIN_METHOD
                                            MERGE_AJ                                                          QKSFM_JOIN_METHOD
           APPEND                           APPEND                           NOAPPEND                         QKSFM_CBO
                                            NOAPPEND                         APPEND                           QKSFM_CBO
           BITMAP                           BITMAP                                                            QKSFM_CBO
           CACHE                            CACHE                            NOCACHE                          QKSFM_EXECUTION
                                            NOCACHE                          CACHE                            QKSFM_EXECUTION
           DEREF_NO_REWRITE                 DEREF_NO_REWRITE                                                  QKSFM_ALL
           DRIVING_SITE                     DRIVING_SITE                                                      QKSFM_ALL
           FACT                             FACT                             NO_FACT                          QKSFM_STAR_TRANS
                                            NO_FACT                          FACT                             QKSFM_STAR_TRANS
           JOIN                             USE_HASH                         NO_USE_HASH                      QKSFM_USE_HASH
                                            USE_MERGE                        NO_USE_MERGE                     QKSFM_USE_MERGE
                                            USE_NL                           NO_USE_NL                        QKSFM_USE_NL
           MERGE                            MERGE                            NO_MERGE                         QKSFM_CVM
           MODE                             ALL_ROWS                                                          QKSFM_ALL_ROWS
                                            CHOOSE                                                            QKSFM_CHOOSE
                                            FIRST_ROWS                                                        QKSFM_FIRST_ROWS
                                            RULE                                                              QKSFM_RBO
           NESTED_TABLE_GET_REFS            NESTED_TABLE_GET_REFS                                             QKSFM_ALL
           ORDERED                          ORDERED                                                           QKSFM_CBO
           PARALLEL_INDEX                   NO_PARALLEL_INDEX                PARALLEL_INDEX                   QKSFM_PQ
                                            PARALLEL_INDEX                   NO_PARALLEL_INDEX                QKSFM_PQ
           PIV_GB                           PIV_GB                                                            QKSFM_ALL
                                            TIV_GB                                                            QKSFM_ALL
           PIV_SSF                          PIV_SSF                                                           QKSFM_ALL
                                            TIV_SSF                                                           QKSFM_ALL
           PUSH_PRED                        NO_PUSH_PRED                     PUSH_PRED                        QKSFM_FILTER_PUSH_PRED
                                            PUSH_PRED                        NO_PUSH_PRED                     QKSFM_FILTER_PUSH_PRED
           PUSH_SUBQ                        PUSH_SUBQ                        NO_PUSH_SUBQ                     QKSFM_TRANSFORMATION
           REMOTE_MAPPED                    REMOTE_MAPPED                                                     QKSFM_ALL
           SEMIJOIN                         HASH_SJ                                                           QKSFM_JOIN_METHOD
                                            MERGE_SJ                                                          QKSFM_JOIN_METHOD
           SEMIJOIN_DRIVER                  SEMIJOIN_DRIVER                                                   QKSFM_CBO
           SHARED                           NOPARALLEL                       SHARED                           QKSFM_PARALLEL
                                            SHARED                           NO_PARALLEL                      QKSFM_PARALLEL
           STAR                             STAR                                                              QKSFM_STAR_TRANS
           STAR_TRANSFORMATION              STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           QKSFM_STAR_TRANS
           SWAP_JOIN_INPUTS                 SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              QKSFM_CBO
           USE_ANTI                         USE_ANTI                                                          QKSFM_CBO
           USE_CONCAT                       NO_EXPAND                        USE_CONCAT                       QKSFM_USE_CONCAT
                                            USE_CONCAT                       NO_EXPAND                        QKSFM_USE_CONCAT
           USE_SEMI                         USE_SEMI                                                          QKSFM_CBO
********** ******************************** --------------------------------
count                                                                     50

Oracle8iから一気に増えます。
FULLやINDEX関連のヒント、HASHヒントなんかもあります。RULEもありますね。

Oracle8i(8.1.5〜)

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
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.5      ACCESS                           INDEX_JOIN                                                        QKSFM_INDEX_JOIN
           BUFFER                           BUFFER                           NO_BUFFER                        QKSFM_CBO
                                            NO_BUFFER                        BUFFER                           QKSFM_CBO
           BYPASS_UJVC                      BYPASS_UJVC                                                       QKSFM_CBO
           CACHE                            CACHE_TEMP_TABLE                 NOCACHE                          QKSFM_ALL
           CACHE_CB                         CACHE_CB                         NOCACHE                          QKSFM_CBO
           CUBE_GB                          CUBE_GB                                                           QKSFM_CBO
           DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             DOMAIN_INDEX_SORT                QKSFM_CBO
                                            DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             QKSFM_CBO
           NESTED_TABLE_SET_SETID           NESTED_TABLE_SET_SETID                                            QKSFM_ALL
           NO_ACCESS                        NO_ACCESS                                                         QKSFM_ALL
           NO_INDEX                         NO_INDEX                         INDEX                            QKSFM_INDEX
           PQ_DISTRIBUTE                    PQ_DISTRIBUTE                                                     QKSFM_PQ_DISTRIBUTE
           RESTORE_AS_INTERVALS             RESTORE_AS_INTERVALS                                              QKSFM_CBO
           REWRITE                          NO_REWRITE                       REWRITE                          QKSFM_TRANSFORMATION
                                            REWRITE                          NO_REWRITE                       QKSFM_TRANSFORMATION
           SAVE_AS_INTERVALS                SAVE_AS_INTERVALS                                                 QKSFM_CBO
           SCN_ASCENDING                    SCN_ASCENDING                                                     QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     18
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.6      LEADING                          LEADING                                                           QKSFM_JOIN_ORDER
           SYS_PARALLEL_TXN                 SYS_PARALLEL_TXN                                                  QKSFM_CBO
           UNNEST                           NO_UNNEST                        UNNEST                           QKSFM_UNNEST
                                            UNNEST                           NO_UNNEST                        QKSFM_UNNEST
********** ******************************** --------------------------------
count                                                                      4
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.7      LIKE_EXPAND                      LIKE_EXPAND                                                       QKSFM_TRANSFORMATION
           OR_EXPAND                        OR_EXPAND                                                         QKSFM_OR_EXPAND
********** ******************************** --------------------------------
count                                                                      2

8.1.5以降はだいぶ枯れてきた感があります。LEADINGヒントはRULEベース的にFROM句の後のテーブルを記述した順に結合するヒントですが8.1.6からなんですね。

Oracle9i

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
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
9.0.0      ACCESS                           INDEX_RRS                                                         QKSFM_CBO
                                            INDEX_SS                         NO_INDEX_SS                      QKSFM_INDEX_SS
                                            INDEX_SS_ASC                     NO_INDEX_SS                      QKSFM_INDEX_SS_ASC
                                            INDEX_SS_DESC                    NO_INDEX_SS                      QKSFM_INDEX_SS_DESC
           ANTIJOIN                         ANTIJOIN                                                          QKSFM_TRANSFORMATION
           BYPASS_RECURSIVE_CHECK           BYPASS_RECURSIVE_CHECK                                            QKSFM_ALL
           CARDINALITY                      CARDINALITY                                                       QKSFM_STATS
           CPU_COSTING                      CPU_COSTING                      NO_CPU_COSTING                   QKSFM_CPU_COSTING
                                            NO_CPU_COSTING                   CPU_COSTING                      QKSFM_CPU_COSTING
           CURSOR_SHARING_EXACT             CURSOR_SHARING_EXACT                                              QKSFM_CBO
           DML_UPDATE                       DML_UPDATE                                                        QKSFM_CBO
           GBY_CONC_ROLLUP                  GBY_CONC_ROLLUP                                                   QKSFM_TRANSFORMATION
           HWM_BROKERED                     HWM_BROKERED                                                      QKSFM_CBO
           INLINE                           INLINE                           MATERIALIZE                      QKSFM_TRANSFORMATION
                                            MATERIALIZE                      INLINE                           QKSFM_TRANSFORMATION
           LOCAL_INDEXES                    LOCAL_INDEXES                                                     QKSFM_CBO
           MV_MERGE                         MV_MERGE                                                          QKSFM_TRANSFORMATION
           NO_PRUNE_GSETS                   NO_PRUNE_GSETS                                                    QKSFM_TRANSFORMATION
           OVERFLOW_NOMOVE                  OVERFLOW_NOMOVE                                                   QKSFM_CBO
           PQ_MAP                           PQ_MAP                           PQ_NOMAP                         QKSFM_PQ_MAP
                                            PQ_NOMAP                         PQ_MAP                           QKSFM_PQ_MAP
           SEMIJOIN                         NO_SEMIJOIN                      SEMIJOIN                         QKSFM_TRANSFORMATION
                                            SEMIJOIN                         NO_SEMIJOIN                      QKSFM_TRANSFORMATION
           SKIP_EXT_OPTIMIZER               SKIP_EXT_OPTIMIZER                                                QKSFM_CBO
           SQLLDR                           SQLLDR                                                            QKSFM_CBO
           USE_TTT_FOR_GSETS                USE_TTT_FOR_GSETS                                                 QKSFM_TRANSFORMATION
********** ******************************** --------------------------------
count                                                                     26
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
9.2.0      DYNAMIC_SAMPLING                 DYNAMIC_SAMPLING                                                  QKSFM_DYNAMIC_SAMPLING
           DYNAMIC_SAMPLING_EST_CDN         DYNAMIC_SAMPLING_EST_CDN                                          QKSFM_DYNAMIC_SAMPLING_EST_CDN
           EXPAND_GSET_TO_UNION             EXPAND_GSET_TO_UNION             NO_EXPAND_GSET_TO_UNION          QKSFM_TRANSFORMATION
                                            NO_EXPAND_GSET_TO_UNION          EXPAND_GSET_TO_UNION             QKSFM_TRANSFORMATION
           FORCE_XML_QUERY_REWRITE          FORCE_XML_QUERY_REWRITE          NO_XML_QUERY_REWRITE             QKSFM_XML_REWRITE
                                            NO_XML_QUERY_REWRITE             FORCE_XML_QUERY_REWRITE          QKSFM_XML_REWRITE
           IGNORE_WHERE_CLAUSE              IGNORE_WHERE_CLAUSE                                               QKSFM_ALL
           NO_QKN_BUFF                      NO_QKN_BUFF                                                       QKSFM_CBO
           PUSH_SUBQ                        NO_PUSH_SUBQ                     PUSH_SUBQ                        QKSFM_TRANSFORMATION
           REF_CASCADE_CURSOR               NO_REF_CASCADE                   REF_CASCADE_CURSOR               QKSFM_CBO
                                            REF_CASCADE_CURSOR               NO_REF_CASCADE                   QKSFM_CBO
           SYS_DL_CURSOR                    SYS_DL_CURSOR                                                     QKSFM_CBO
           SYS_RID_ORDER                    SYS_RID_ORDER                                                     QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     13

Oracle9iR1は当時400種類以上の新機能ができたなんて聞きましたが、ヒント句は8iの時と比べると半分くらいしか追加されていません。
マテリアライズド・ビュー関連のヒントが目立ちますが、ダイナミックサンプリングなんかも9iR2からですね。

Oracle10gR1

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
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.1.0.3   FBTSCAN                          FBTSCAN                                                           QKSFM_CBO
           GATHER_PLAN_STATISTICS           GATHER_PLAN_STATISTICS                                            QKSFM_GATHER_PLAN_STATISTICS
           IGNORE_OPTIM_EMBEDDED_HINTS      IGNORE_OPTIM_EMBEDDED_HINTS                                       QKSFM_ALL
           INCLUDE_VERSION                  INCLUDE_VERSION                                                   QKSFM_ALL
           MODEL_DONTVERIFY_UNIQUENESS      MODEL_DONTVERIFY_UNIQUENESS                                       QKSFM_TRANSFORMATION
           MODEL_MIN_ANALYSIS               MODEL_MIN_ANALYSIS                                                QKSFM_TRANSFORMATION
                                            MODEL_NO_ANALYSIS                                                 QKSFM_ALL
           MODEL_PUSH_REF                   MODEL_PUSH_REF                   NO_MODEL_PUSH_REF                QKSFM_TRANSFORMATION
                                            NO_MODEL_PUSH_REF                MODEL_PUSH_REF                   QKSFM_ALL
           NESTED_TABLE_FAST_INSERT         NESTED_TABLE_FAST_INSERT                                          QKSFM_ALL
           NO_INDEX_FFS                     NO_INDEX_FFS                     INDEX_FFS                        QKSFM_INDEX_FFS
           NO_INDEX_SS                      NO_INDEX_SS                      INDEX_SS                         QKSFM_INDEX_SS
           NO_PARTIAL_COMMIT                NO_PARTIAL_COMMIT                                                 QKSFM_CBO
           NO_QUERY_TRANSFORMATION          NO_QUERY_TRANSFORMATION                                           QKSFM_TRANSFORMATION
           NO_USE_HASH                      NO_USE_HASH                      USE_HASH                         QKSFM_USE_HASH
           NO_USE_MERGE                     NO_USE_MERGE                     USE_MERGE                        QKSFM_USE_MERGE
           NO_USE_NL                        NO_USE_NL                        USE_NL                           QKSFM_USE_NL
           OPAQUE_TRANSFORM                 OPAQUE_TRANSFORM                                                  QKSFM_TRANSFORMATION
           OPAQUE_XCANONICAL                OPAQUE_XCANONICAL                                                 QKSFM_TRANSFORMATION
           OPTIMIZER_FEATURES_ENABLE        OPTIMIZER_FEATURES_ENABLE                                         QKSFM_ALL
           OPT_ESTIMATE                     OPT_ESTIMATE                                                      QKSFM_OPT_ESTIMATE
           QB_NAME                          QB_NAME                                                           QKSFM_ALL
           RESTRICT_ALL_REF_CONS            RESTRICT_ALL_REF_CONS                                             QKSFM_ALL
           REWRITE                          NO_BASETABLE_MULTIMV_REWRITE     REWRITE                          QKSFM_ALL
                                            NO_MULTIMV_REWRITE               REWRITE                          QKSFM_ALL
                                            REWRITE_OR_ERROR                                                  QKSFM_TRANSFORMATION
           SET_TO_JOIN                      NO_SET_TO_JOIN                   SET_TO_JOIN                      QKSFM_SET_TO_JOIN
                                            SET_TO_JOIN                      NO_SET_TO_JOIN                   QKSFM_SET_TO_JOIN
           SHARED                           NO_PARALLEL                      SHARED                           QKSFM_CBO
           SKIP_UNQ_UNUSABLE_IDX            SKIP_UNQ_UNUSABLE_IDX                                             QKSFM_CBO
           STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           STAR_TRANSFORMATION              QKSFM_STAR_TRANS
           STREAMS                          STREAMS                                                           QKSFM_CBO
           SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              SWAP_JOIN_INPUTS                 QKSFM_CBO
           TABLE_STATS                      COLUMN_STATS                                                      QKSFM_STATS
                                            INDEX_STATS                                                       QKSFM_STATS
                                            TABLE_STATS                                                       QKSFM_STATS
           TRACING                          TRACING                                                           QKSFM_EXECUTION
           USE_NL_WITH_INDEX                USE_NL_WITH_INDEX                NO_USE_NL                        QKSFM_USE_NL_WITH_INDEX
           USE_WEAK_NAME_RESL               USE_WEAK_NAME_RESL                                                QKSFM_ALL
           VECTOR_READ                      VECTOR_READ                                                       QKSFM_CBO
           VECTOR_READ_TRACE                VECTOR_READ_TRACE                                                 QKSFM_CBO
           X_DYN_PRUNE                      X_DYN_PRUNE                                                       QKSFM_CBO
********** ******************************** --------------------------------
count                                                                     42

Oracle10gからはまたグッと増えてきました。
特に統計情報関連のヒントが気になります。

Oracle10gR2

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
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.1   ACCESS                           BITMAP_TREE                                                       QKSFM_BITMAP_TREE
           DBMS_STATS                       DBMS_STATS                                                        QKSFM_DBMS_STATS
           ELIMINATE_JOIN                   ELIMINATE_JOIN                   NO_ELIMINATE_JOIN                QKSFM_TABLE_ELIM
                                            NO_ELIMINATE_JOIN                ELIMINATE_JOIN                   QKSFM_TABLE_ELIM
           ELIMINATE_OBY                    ELIMINATE_OBY                    NO_ELIMINATE_OBY                 QKSFM_OBYE
                                            NO_ELIMINATE_OBY                 ELIMINATE_OBY                    QKSFM_OBYE
           INLINE_XMLTYPE_NT                INLINE_XMLTYPE_NT                                                 QKSFM_ALL
           MODEL_COMPILE_SUBQUERY           MODEL_COMPILE_SUBQUERY                                            QKSFM_TRANSFORMATION
           MODEL_DYNAMIC_SUBQUERY           MODEL_DYNAMIC_SUBQUERY                                            QKSFM_TRANSFORMATION
           NO_CARTESIAN                     NO_CARTESIAN                                                      QKSFM_ALL
           NO_SQL_TUNE                      NO_SQL_TUNE                                                       QKSFM_ALL
           NO_XML_DML_REWRITE               NO_XML_DML_REWRITE                                                QKSFM_XML_REWRITE
           OLD_PUSH_PRED                    OLD_PUSH_PRED                                                     QKSFM_OLD_PUSH_PRED
           OPT_PARAM                        OPT_PARAM                                                         QKSFM_ALL
           OUTLINE                          OUTLINE                                                           QKSFM_ALL
           OUTLINE_LEAF                     OUTLINE_LEAF                                                      QKSFM_ALL
           PRECOMPUTE_SUBQUERY              PRECOMPUTE_SUBQUERY                                               QKSFM_TRANSFORMATION
           PRESERVE_OID                     PRESERVE_OID                                                      QKSFM_ALL
           PULL_PRED                        NO_PULL_PRED                     PULL_PRED                        QKSFM_PULL_PRED
                                            PULL_PRED                        NO_PULL_PRED                     QKSFM_PULL_PRED
           PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                PX_JOIN_FILTER                   QKSFM_PX_JOIN_FILTER
                                            PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                QKSFM_PX_JOIN_FILTER
           RBO_OUTLINE                      RBO_OUTLINE                                                       QKSFM_RBO
           USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          USE_HASH_AGGREGATION             QKSFM_ALL
                                            USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     25
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.2   CONNECT_BY_COST_BASED            CONNECT_BY_COST_BASED            NO_CONNECT_BY_COST_BASED         QKSFM_TRANSFORMATION
                                            NO_CONNECT_BY_COST_BASED         CONNECT_BY_COST_BASED            QKSFM_TRANSFORMATION
           CONNECT_BY_FILTERING             CONNECT_BY_FILTERING             NO_CONNECT_BY_FILTERING          QKSFM_ALL
                                            NO_CONNECT_BY_FILTERING          CONNECT_BY_FILTERING             QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      4
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.3   ACCESS                           NUM_INDEX_KEYS                                                    QKSFM_CBO
           NATIVE_FULL_OUTER_JOIN           NATIVE_FULL_OUTER_JOIN           NO_NATIVE_FULL_OUTER_JOIN        QKSFM_ALL
                                            NO_NATIVE_FULL_OUTER_JOIN        NATIVE_FULL_OUTER_JOIN           QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      3
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.4   CONNECT_BY_COMBINE_SW            CONNECT_BY_COMBINE_SW            NO_CONNECT_BY_COMBINE_SW         QKSFM_ALL
                                            NO_CONNECT_BY_COMBINE_SW         CONNECT_BY_COMBINE_SW            QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      2
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.5   CONNECT_BY_CB_WHR_ONLY           CONNECT_BY_CB_WHR_ONLY           NO_CONNECT_BY_CB_WHR_ONLY        QKSFM_TRANSFORMATION
                                            NO_CONNECT_BY_CB_WHR_ONLY        CONNECT_BY_CB_WHR_ONLY           QKSFM_TRANSFORMATION
           GBY_PUSHDOWN                     GBY_PUSHDOWN                     NO_GBY_PUSHDOWN                  QKSFM_ALL
                                            NO_GBY_PUSHDOWN                  GBY_PUSHDOWN                     QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      4

10gR2ともなると、普段ほとんど使わないようなヒントばかりになってきますね!
これ以降はコメントのしようがないので、そのまま表示させます。

Oracle11g以降

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
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.1.0.6   ACCESS                           INDEX_RS_ASC                                                      QKSFM_INDEX_RS_ASC
                                            INDEX_RS_DESC                                                     QKSFM_INDEX_RS_DESC
                                            NLJ_BATCHING                     NO_NLJ_BATCHING                  QKSFM_EXECUTION
                                            NLJ_PREFETCH                     NO_NLJ_PREFETCH                  QKSFM_EXECUTION
                                            NO_NLJ_BATCHING                  NLJ_BATCHING                     QKSFM_EXECUTION
                                            NO_NLJ_PREFETCH                  NLJ_PREFETCH                     QKSFM_EXECUTION
           CHECK_ACL_REWRITE                CHECK_ACL_REWRITE                NO_CHECK_ACL_REWRITE             QKSFM_CHECK_ACL_REWRITE
           COST_XML_QUERY_REWRITE           COST_XML_QUERY_REWRITE           NO_COST_XML_QUERY_REWRITE        QKSFM_COST_XML_QUERY_REWRITE
           DB_VERSION                       DB_VERSION                                                        QKSFM_ALL
           DOMAIN_INDEX_FILTER              DOMAIN_INDEX_FILTER              NO_DOMAIN_INDEX_FILTER           QKSFM_CBO
           JOIN                             USE_MERGE_CARTESIAN                                               QKSFM_USE_MERGE_CARTESIAN
           MONITOR                          MONITOR                          NO_MONITOR                       QKSFM_ALL
                                            NO_MONITOR                       MONITOR                          QKSFM_ALL
           NO_CHECK_ACL_REWRITE             NO_CHECK_ACL_REWRITE             CHECK_ACL_REWRITE                QKSFM_CHECK_ACL_REWRITE
           NO_COST_XML_QUERY_REWRITE        NO_COST_XML_QUERY_REWRITE        COST_XML_QUERY_REWRITE           QKSFM_COST_XML_QUERY_REWRITE
           NO_DOMAIN_INDEX_FILTER           NO_DOMAIN_INDEX_FILTER           DOMAIN_INDEX_FILTER              QKSFM_CBO
           NO_LOAD                          NO_LOAD                                                           QKSFM_EXECUTION
           OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           OUTER_JOIN_TO_INNER              QKSFM_OUTER_JOIN_TO_INNER
                                            OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           QKSFM_OUTER_JOIN_TO_INNER
           PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                PLACE_GROUP_BY                   QKSFM_PLACE_GROUP_BY
                                            PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                QKSFM_PLACE_GROUP_BY
           RESULT_CACHE                     NO_RESULT_CACHE                  RESULT_CACHE                     QKSFM_EXECUTION
                                            RESULT_CACHE                     NO_RESULT_CACHE                  QKSFM_EXECUTION
           SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              SUBQUERY_PRUNING                 QKSFM_CBO
                                            SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              QKSFM_CBO
           USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         USE_INVISIBLE_INDEXES            QKSFM_INDEX
                                            USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         QKSFM_INDEX
           XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              XMLINDEX_REWRITE                 QKSFM_XMLINDEX_REWRITE
                                            NO_XMLINDEX_REWRITE_IN_SELECT    XMLINDEX_REWRITE_IN_SELECT       QKSFM_XMLINDEX_REWRITE
                                            XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              QKSFM_XMLINDEX_REWRITE
                                            XMLINDEX_REWRITE_IN_SELECT       NO_XMLINDEX_REWRITE_IN_SELECT    QKSFM_XMLINDEX_REWRITE
           XML_DML_RWT_STMT                 XML_DML_RWT_STMT                                                  QKSFM_XML_REWRITE
********** ******************************** --------------------------------
count                                                                     32
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.1.0.7   BIND_AWARE                       BIND_AWARE                       NO_BIND_AWARE                    QKSFM_CURSOR_SHARING
                                            NO_BIND_AWARE                    BIND_AWARE                       QKSFM_CURSOR_SHARING
           CHANGE_DUPKEY_ERROR_INDEX        CHANGE_DUPKEY_ERROR_INDEX                                         QKSFM_DML
           IGNORE_ROW_ON_DUPKEY_INDEX       IGNORE_ROW_ON_DUPKEY_INDEX                                        QKSFM_DML
           RETRY_ON_ROW_CHANGE              RETRY_ON_ROW_CHANGE                                               QKSFM_DML
********** ******************************** --------------------------------
count                                                                      5
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.1   APPEND_VALUES                    APPEND_VALUES                    NOAPPEND                         QKSFM_CBO
           COALESCE_SQ                      COALESCE_SQ                      NO_COALESCE_SQ                   QKSFM_COALESCE_SQ
                                            NO_COALESCE_SQ                   COALESCE_SQ                      QKSFM_COALESCE_SQ
           CONNECT_BY_ELIM_DUPS             CONNECT_BY_ELIM_DUPS             NO_CONNECT_BY_ELIM_DUPS          QKSFM_ALL
                                            NO_CONNECT_BY_ELIM_DUPS          CONNECT_BY_ELIM_DUPS             QKSFM_ALL
           DST_UPGRADE_INSERT_CONV          DST_UPGRADE_INSERT_CONV          NO_DST_UPGRADE_INSERT_CONV       QKSFM_ALL
                                            NO_DST_UPGRADE_INSERT_CONV       DST_UPGRADE_INSERT_CONV          QKSFM_ALL
           EXPAND_TABLE                     EXPAND_TABLE                     NO_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION
                                            NO_EXPAND_TABLE                  EXPAND_TABLE                     QKSFM_TABLE_EXPANSION
           FACTORIZE_JOIN                   FACTORIZE_JOIN                   NO_FACTORIZE_JOIN                QKSFM_JOINFAC
                                            NO_FACTORIZE_JOIN                FACTORIZE_JOIN                   QKSFM_JOINFAC
           NO_SUBSTRB_PAD                   NO_SUBSTRB_PAD                                                    QKSFM_EXECUTION
           PLACE_DISTINCT                   NO_PLACE_DISTINCT                PLACE_DISTINCT                   QKSFM_DIST_PLCMT
                                            PLACE_DISTINCT                   NO_PLACE_DISTINCT                QKSFM_DIST_PLCMT
           STATEMENT_QUEUING                NO_STATEMENT_QUEUING             STATEMENT_QUEUING                QKSFM_PARALLEL
                                            STATEMENT_QUEUING                NO_STATEMENT_QUEUING             QKSFM_PARALLEL
           TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        TRANSFORM_DISTINCT_AGG           QKSFM_TRANSFORMATION
                                            TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        QKSFM_TRANSFORMATION
           XMLINDEX_SEL_IDX_TBL             XMLINDEX_SEL_IDX_TBL                                              QKSFM_ALL
********** ******************************** --------------------------------
count                                                                     19
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.2   TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            TABLE_LOOKUP_BY_NL               QKSFM_TABLE_LOOKUP_BY_NL
                                            TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            QKSFM_TABLE_LOOKUP_BY_NL
           USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     USE_HASH_GBY_FOR_PUSHDOWN        QKSFM_ALL
                                            USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     QKSFM_ALL
           XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           XDB_FASTPATH_INSERT              QKSFM_ALL
                                            XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           QKSFM_ALL
********** ******************************** --------------------------------
count                                                                      6
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.3   FULL_OUTER_JOIN_TO_OUTER         FULL_OUTER_JOIN_TO_OUTER         NO_FULL_OUTER_JOIN_TO_OUTER      QKSFM_CBO
                                            NO_FULL_OUTER_JOIN_TO_OUTER      FULL_OUTER_JOIN_TO_OUTER         QKSFM_CBO
           NO_SEMI_TO_INNER                 NO_SEMI_TO_INNER                 SEMI_TO_INNER                    QKSFM_CBO
           OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            OUTER_JOIN_TO_ANTI               QKSFM_CBO
                                            OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            QKSFM_CBO
           SEMI_TO_INNER                    SEMI_TO_INNER                    NO_SEMI_TO_INNER                 QKSFM_CBO
********** ******************************** --------------------------------
count                                                                      6
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.4   ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             ENABLE_PARALLEL_DML              QKSFM_DML
                                            ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             QKSFM_DML
********** ******************************** --------------------------------
count                                                                      2
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
12.1.0.1   ANTIJOIN                         CUBE_AJ                                                           QKSFM_JOIN_METHOD
           AUTO_REOPTIMIZE                  AUTO_REOPTIMIZE                  NO_AUTO_REOPTIMIZE               QKSFM_AUTO_REOPT
                                            NO_AUTO_REOPTIMIZE               AUTO_REOPTIMIZE                  QKSFM_AUTO_REOPT
           BATCH_TABLE_ACCESS_BY_ROWID      BATCH_TABLE_ACCESS_BY_ROWID      NO_BATCH_TABLE_ACCESS_BY_ROWID   QKSFM_EXECUTION
                                            NO_BATCH_TABLE_ACCESS_BY_ROWID   BATCH_TABLE_ACCESS_BY_ROWID      QKSFM_EXECUTION
           BITMAP_AND                       BITMAP_AND                                                        QKSFM_BITMAP_TREE
           CLUSTERING                       CLUSTERING                       NO_CLUSTERING                    QKSFM_CLUSTERING
                                            NO_CLUSTERING                    CLUSTERING                       QKSFM_CLUSTERING
           CLUSTER_BY_ROWID                 CLUSTER_BY_ROWID                 NO_CLUSTER_BY_ROWID              QKSFM_CBO
                                            NO_CLUSTER_BY_ROWID              CLUSTER_BY_ROWID                 QKSFM_CBO
           DATA_SECURITY_REWRITE_LIMIT      DATA_SECURITY_REWRITE_LIMIT      NO_DATA_SECURITY_REWRITE         QKSFM_DATA_SECURITY_REWRITE
                                            NO_DATA_SECURITY_REWRITE         DATA_SECURITY_REWRITE_LIMIT      QKSFM_DATA_SECURITY_REWRITE
           DECORRELATE                      DECORRELATE                      NO_DECORRELATE                   QKSFM_DECORRELATE
                                            NO_DECORRELATE                   DECORRELATE                      QKSFM_DECORRELATE
           GATHER_OPTIMIZER_STATISTICS      GATHER_OPTIMIZER_STATISTICS      NO_GATHER_OPTIMIZER_STATISTICS   QKSFM_DBMS_STATS
                                            NO_GATHER_OPTIMIZER_STATISTICS   GATHER_OPTIMIZER_STATISTICS      QKSFM_DBMS_STATS
           JOIN                             NO_USE_CUBE                      USE_CUBE                         QKSFM_USE_CUBE
                                            USE_CUBE                         NO_USE_CUBE                      QKSFM_USE_CUBE
           PARTIAL_JOIN                     NO_PARTIAL_JOIN                  PARTIAL_JOIN                     QKSFM_PARTIAL_JOIN
                                            PARTIAL_JOIN                     NO_PARTIAL_JOIN                  QKSFM_PARTIAL_JOIN
           PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       PARTIAL_ROLLUP_PUSHDOWN          QKSFM_PQ
                                            PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       QKSFM_PQ
           PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           PQ_CONCURRENT_UNION              QKSFM_PQ
                                            PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           QKSFM_PQ
           PQ_DISTRIBUTE_WINDOW             PQ_DISTRIBUTE_WINDOW                                              QKSFM_PQ
           PQ_FILTER                        PQ_FILTER                                                         QKSFM_PQ
           PQ_REPLICATE                     NO_PQ_REPLICATE                  PQ_REPLICATE                     QKSFM_PQ_REPLICATE
                                            PQ_REPLICATE                     NO_PQ_REPLICATE                  QKSFM_PQ_REPLICATE
           PQ_SKEW                          NO_PQ_SKEW                       PQ_SKEW                          QKSFM_PQ
                                            PQ_SKEW                          NO_PQ_SKEW                       QKSFM_PQ
           PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            PX_FAULT_TOLERANCE               QKSFM_PQ
                                            PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            QKSFM_PQ
           SEMIJOIN                         CUBE_SJ                                                           QKSFM_JOIN_METHOD
           USE_HIDDEN_PARTITIONS            USE_HIDDEN_PARTITIONS                                             QKSFM_PARTITION
           WITH_PLSQL                       WITH_PLSQL                                                        QKSFM_ALL
           ZONEMAP                          NO_ZONEMAP                       ZONEMAP                          QKSFM_ZONEMAP
                                            ZONEMAP                          NO_ZONEMAP                       QKSFM_ZONEMAP
********** ******************************** --------------------------------
count                                                                     37
 
VERSION    CLASS                            HINT_NAME                        INVERSE                          SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
12.1.0.2   ADAPTIVE_PLAN                    ADAPTIVE_PLAN                    NO_ADAPTIVE_PLAN                 QKSFM_ADAPTIVE_PLAN
                                            NO_ADAPTIVE_PLAN                 ADAPTIVE_PLAN                    QKSFM_ADAPTIVE_PLAN
           ANSI_REARCH                      ANSI_REARCH                      NO_ANSI_REARCH                   QKSFM_ANSI_REARCH
                                            NO_ANSI_REARCH                   ANSI_REARCH                      QKSFM_ANSI_REARCH
           ELIM_GROUPBY                     ELIM_GROUPBY                     NO_ELIM_GROUPBY                  QKSFM_TRANSFORMATION
                                            NO_ELIM_GROUPBY                  ELIM_GROUPBY                     QKSFM_TRANSFORMATION
           INMEMORY                         INMEMORY                         NO_INMEMORY                      QKSFM_EXECUTION
                                            NO_INMEMORY                      INMEMORY                         QKSFM_EXECUTION
           INMEMORY_PRUNING                 INMEMORY_PRUNING                 NO_INMEMORY_PRUNING              QKSFM_EXECUTION
                                            NO_INMEMORY_PRUNING              INMEMORY_PRUNING                 QKSFM_EXECUTION
           USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        USE_VECTOR_AGGREGATION           QKSFM_VECTOR_AGG
                                            USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        QKSFM_VECTOR_AGG
           VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              VECTOR_TRANSFORM                 QKSFM_VECTOR_AGG
                                            VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              QKSFM_VECTOR_AGG
           VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         VECTOR_TRANSFORM_DIMS            QKSFM_VECTOR_AGG
                                            VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         QKSFM_VECTOR_AGG
           VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         VECTOR_TRANSFORM_FACT            QKSFM_VECTOR_AGG
                                            VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         QKSFM_VECTOR_AGG
********** ******************************** --------------------------------
count                                                                     18
 
332行が選択されました。

Oracle12cでは全部で332ものヒント句が存在していることがわかります。

SQL_FEATUREとは

上の一覧で一番右端のカラムは「SQL_FEATURE」となっていますが、これは何でしょうか?
マニュアルに掲載されている「V$SYS_OPTIMIZER_ENV」の記述を参照すると、手がかりが見つかります。

「SQL_FEATURE=関連機能の制御ID」とあるので、オプティマイザというのは多くの機能の集合体であって、初期化パラメータやヒント句は個々の機能(SQL_FEATURE)を制御するものなのだろうということが推察できます。

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
SQL> SELECT SQL_FEATURE,ID,NAME,ISDEFAULT,VALUE,DEFAULT_VALUE FROM V$SYS_OPTIMIZER_ENV
  ORDER BY SQL_FEATURE,ID;
 
SQL_FEATURE                              ID NAME                                     ISD VALUE           DEFAULT_VALUE
-------------------------------- ---------- ---------------------------------------- --- --------------- -------------
QKSFM_ADAPTIVE_PLAN                     411 optimizer_adaptive_features              YES true            true
 
QKSFM_ALL                                11 cpu_count                                YES 2               2
                                         12 active_instance_count                    YES 1               1
                                         14 hash_area_size                           YES 131072          131072
                                         15 bitmap_merge_area_size                   YES 1048576         1048576
                                         16 sort_area_size                           YES 65536           65536
                                         17 sort_area_retained_size                  YES 0               0
                                         24 pga_aggregate_target                     YES 286720 KB       286720 KB
                                         35 parallel_query_mode                      YES enabled         enabled
                                         36 parallel_dml_mode                        YES disabled        disabled
                                         37 parallel_ddl_mode                        YES enabled         enabled
                                         38 optimizer_mode                           YES all_rows        all_rows
                                        101 workarea_size_policy                     YES auto            auto
                                        167 optimizer_secure_view_merging            YES true            true
                                        212 result_cache_mode                        YES MANUAL          MANUAL
                                        218 transaction_isolation_level              YES read_commited   read_commited
                                        257 is_recur_flags                           YES 0               0
                                        264 db_file_multiblock_read_count            YES 69              69
                                        275 total_cpu_count                          YES 2               2
                                        286 dst_upgrade_insert_conv                  YES true            true
                                        378 PMO_altidx_rebuild                       YES 0               0
                                        389 total_processor_group_count              YES 1               1
 
QKSFM_AUTO_REOPT                        353 optimizer_adaptive_reporting_only        YES false           false
 
QKSFM_CBO                                 2 parallel_execution_enabled               YES true            true
                                          9 optimizer_features_enable                YES 12.1.0.2        12.1.0.2
                                         13 parallel_threads_per_cpu                 YES 2               2
                                         48 cursor_sharing                           YES exact           exact
                                         66 optimizer_index_cost_adj                 YES 100             100
                                         67 optimizer_index_caching                  YES 0               0
                                        105 optimizer_dynamic_sampling               YES 2               2
                                        112 statistics_level                         YES typical         typical
                                        114 skip_unusable_indexes                    YES true            true
                                        228 optimizer_use_pending_statistics         YES false           false
                                        238 optimizer_capture_sql_plan_baselines     YES false           false
                                        239 optimizer_use_sql_plan_baselines         YES true            true
                                        441 optimizer_inmemory_aware                 YES true            true
 
QKSFM_COMPILATION                       466 inmemory_size                            YES 0               0
 
QKSFM_EXECUTION                         262 cell_offload_processing                  YES true            true
                                        267 cell_offload_compaction                  YES ADAPTIVE        ADAPTIVE
                                        268 cell_offload_plan_display                YES AUTO            AUTO
                                        453 inmemory_force                           YES default         default
                                        454 inmemory_query                           YES enable          enable
 
QKSFM_INDEX                             258 optimizer_use_invisible_indexes          YES false           false
 
QKSFM_PARTITION                         323 deferred_segment_creation                YES true            true
 
QKSFM_PQ                                245 parallel_degree_policy                   YES manual          manual
                                        246 parallel_degree                          YES 0               0
                                        247 parallel_min_time_threshold              YES 10              10
                                        256 parallel_query_default_dop               YES 0               0
                                        272 parallel_degree_limit                    YES 65535           65535
                                        273 parallel_force_local                     YES false           false
                                        274 parallel_max_degree                      YES 4               4
                                        289 parallel_autodop                         YES 0               0
                                        290 parallel_ddldml                          YES 0               0
                                        317 parallel_execution_message_size          YES 16384           16384
                                        369 parallel_degree_level                    YES 100             100
                                        432 parallel_dblink                          YES 0               0
 
QKSFM_STAR_TRANS                         50 star_transformation_enabled              YES false           false
 
QKSFM_TRANSFORMATION                     70 query_rewrite_enabled                    YES true            true
                                         71 query_rewrite_integrity                  YES enforced        enforced
 
 
59行が選択されました。

まとめ

アクセスパスを固定化するためにヒント句でチューニングするということはよく行われていると思います。

しかし、12cで332種類(11gR2でも約280個)ものヒントがあることを考えると、ヒント句によるチューニングはオプティマイザのほんの一部を制御しているに過ぎないことを理解すべきです。

しかも、バージョンによってヒント句の数は全然違う、つまりオプティマイザの内部仕様はどんどん変わっているので、以前のバージョンで最適であった実行計画が今のバージョンでも最適である保証はないと考えるのが自然なのではないでしょうか。

今回はここまで。

oerrのメッセージを日本語で表示させる

今週の名言

「宇宙でもっとも強い力は、幅広い興味である。」
アルベルト・アインシュタイン

oerrとは?

前回からのつながりで、今回もエラー・メッセージについて考えてみたいと思います。

oerrという非常に便利なツールがあります。「ORA-4031」について意味を調べたい時は、以下の要領でエラーメッセージの意味だけでなく、原因や対処についての説明をシェルから次のコマンドで確認することができます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.

ORA-以外のエラーメッセージ、例えば「TNS-12541」も以下のように確認することができます。

1
2
3
4
5
6
7
8
$ oerr tns 12541
12541, 00000, "TNS:no listener"
// *Cause: The connection request could not be completed because the listener
// is not running.
// *Action: Ensure that the supplied destination address matches one of
// the addresses used by the listener - compare the TNSNAMES.ORA entry with
// the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
// go by way of an Interchange). Start the listener on the remote machine.

oerrコマンドの正体は?

oerrは実はシェル・スクリプトで、$ORACLE_HOME/bin 以下にあります。

1
2
$ which oerr
/u01/app/oracle/product/11.2.0.4/db_1/bin/oerr

スクリプトの内容は以下の110行から成るリストのとおりですが

  • 35行目に定義されたfacility.lisから接頭語と製品名の関連を取得
  • 93行目で製品名ディレクトリ配下にあるmsgファイルを指定
  • 104行目以下のawkコマンドにより該当メッセージの取得

という仕様になっています。

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
$ cat /u01/app/oracle/product/11.2.0.4/db_1/bin/oerr
#!/bin/sh
#
# $Id: oerr.sh /st_buildtools_11.2.0/1 2012/02/28 11:07:18 mrmehta Exp $
# Copyright (c) 1994, 2012, Oracle and/or its affiliates. All rights reserved.
#
# Usage: oerr facility error
#
# This shell script is used to get the description and the cause and action
# of an error from a message text file when a list of error numbers are passed
# to it.  It supports different language environments and errors from different
# facilities.
#
 
#
# Turn on script tracing if, requested
[ "$ORACLE_TRACE" = "T" ] && set -x
 
#
# If ORACLE_HOME is not set, we will not be able to locate
# the message text file.
if [ ! "$ORACLE_HOME" ]
then
echo "ORACLE_HOME not set.  Please set ORACLE_HOME and try again." 1>&2
exit 1
fi
 
#
# Ignore user locale
LC_ALL=C
export LC_ALL
 
#
# Definition script "constants"
Facilities_File=$ORACLE_HOME/lib/facility.lis
 
#
# Check script usage
if [ "$#" != "2" ]
then
exec 1>&2
echo 'Usage: oerr facility error'
echo
echo 'Facility is identified by the prefix string in the error message.'
echo 'For example, if you get ORA-7300, "ora" is the facility and "7300"'
echo 'is the error.  So you should type "oerr ora 7300".'
echo
echo 'If you get LCD-111, type "oerr lcd 111", and so on.'
exit 1
fi
 
#
# Pickup the command line arguments
Facility="$1"
Platform=`/bin/uname`
 
case $Platform in
SunOS)
SedPath='/usr/xpg4/bin/sed'
;;
*)
SedPath='/bin/sed'
;;
esac
Code=`echo $2|$SedPath 's/^[0]*//'`
 
#
# Get the facility information from the oerr data file
Fac_Info=`grep -i "^${Facility}:" $Facilities_File 2> /dev/null`
if [ $? -ne 0 ]
then
echo "oerr: Unknown facility '$Facility'" 1>&2
exit 1
fi
 
#
# Parse the components from the Fac_Info string into Shell variables
eval `echo "$Fac_Info" | awk -F: '{
if (index ($3, "*") == 0)
printf ("Facility=%s\n", $3);
else
printf ("Facility=%s\n", $1);
printf ("Component=%s\n", $2);
}'`
if [ -z "$Facility" -o -z "$Component" ]
then
echo "oerr: Invalid facilities entry '$Fac_Info'" 1>&2
exit 1
fi
 
#
# The message file searched is always the US English file
Msg_File=$ORACLE_HOME/$Component/mesg/${Facility}us.msg
if [ ! -r $Msg_File ]
then
echo "oerr: Cannot access the message file $Msg_File" 1>&2
exit 1
fi
 
#
# Search the message file for the error code, printing the message text
# and any following comments which should give the cause and action for
# the error.
awk "BEGIN { found = 0; }
/^[0]*$Code/    { found = 1; print ; next;}
/^\/\//        { if (found) { print; } next; }
{ if (found) { exit; } }" $Msg_File
 
exit 0

例えば、$OEACLE_HOME/rdbms/mesg/oraus.msg ファイルの内容は以下のようになっています。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1865 00000, 00000, "normal, successful completion"
1866 // *Cause:  Normal exit.
1867 // *Action: None.
1868 00001, 00000, "unique constraint (%s.%s) violated"
1869 // *Cause: An UPDATE or INSERT statement attempted to insert a duplicatee key.
1870 //         For Trusted Oracle configured in DBMS MAC mode, you may see
1871 //         this message if a duplicate entry exists at a different levell.
1872 // *Action: Either remove the unique restriction or do not insert the key.
1873 /0002        reserved for v2 compatibility (null column)
1874 /0003        reserved for v2 compatibility (column value truncated)
1875 /0004        reserved for v2 compatibility (end-of-fetch)
1876 /0009        reserved for v2 compatibility
1877 /
1878 / 10 - 49 user session and session switching errors
1879 /
1880 00017, 00000, "session requested to set trace event"
1881 // *Cause:  The current session was requested to set a trace event by another
1882 //          session.
1883 // *Action: This is used internally; no action is required.

oerrは英語圏の人にとっては非常に便利なツールですが、Oracle 8.1.6を最後に各国語サポートへは対応されなくなっています。

エラー・メッセージだけでも日本語化してみる

原因や対処までは無理としても、せめてエラー・メッセージの意味だけでも日本語で表示させるツールを作ってみたいと思いましたので、以下のようなストアド・ファンクションを作ってみました。
試作版なのですべての接頭語には対応していませんし、エラーハンドリングも考慮していません。(存在しないメッセージ番号を指定すると「該当メッセージはありません」という表示をさせています。)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> CREATE OR REPLACE FUNCTION show_errmsg (err_fac IN VARCHAR2,err_num IN PLS_INTEGER)
  RETURN VARCHAR2
  IS
  4   wrk_fac VARCHAR2(8);
  5   err_lan VARCHAR2(20) := 'japanese';
  6   err_msg VARCHAR2(512);
  7   i        PLS_INTEGER;
  BEGIN
  9   wrk_fac := UPPER(err_fac);
 10  CASE wrk_fac
 11     WHEN 'CRS'  THEN i := UTL_LMS.GET_MESSAGE(err_num,'crs',    err_fac,err_lan,err_msg);
 12     WHEN 'TNS'  THEN i := UTL_LMS.GET_MESSAGE(err_num,'network',err_fac,err_lan,err_msg);
 13     ELSE             i := UTL_LMS.GET_MESSAGE(err_num,'rdbms',  err_fac,err_lan,err_msg);
 14  END CASE;
 15  IF err_msg LIKE 'Message%not found%'
 16  THEN err_msg := wrk_fac || '-' || TO_CHAR(err_num,'FM00000') || ': 該当メッセージはありません';
 17  ELSE err_msg := wrk_fac || '-' || TO_CHAR(err_num,'FM00000') || ': ' || err_msg;
 18  END IF;
 19  RETURN err_msg;
 20  END;
 21  /
 
ファンクションが作成されました。

このファンクションの使用例は以下のようになります。

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
SQL> SELECT show_errmsg('ora',4031) ERROR_MESSAGE from dual;
 
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
ORA-04031: 共有メモリーの%sバイトを割当てできません("%s""%s""%s""%s")
 
SQL> SELECT show_errmsg('crs',214) ERROR_MESSAGE from dual;
 
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
CRS-00214: リソース'%s'を登録解除できません。
 
SQL> SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual;
facに値を入力してください: rman
numに値を入力してください: 567
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('rman',567) ERROR_MESSAGE from dual
 
ERROR_MESSAGE
--------------------------------------------------------------------------------
RMAN-00567: Recovery Managerで印刷できなかったエラー・メッセージがあります
 
SQL> /
facに値を入力してください: tns
numに値を入力してください: 12541
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('tns',12541) ERROR_MESSAGE from dual
 
ERROR_MESSAGE
--------------------------------------------------------------------------------
TNS-12541: TNS: リスナーがありません。
 
SQL> /
facに値を入力してください: ora
numに値を入力してください: 2
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('ora',2) ERROR_MESSAGE from dual
 
ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-00002: 該当メッセージはありません
 
SQL> /
facに値を入力してください: abc
numに値を入力してください: 1
旧   1: SELECT show_errmsg('&fac',&num) ERROR_MESSAGE from dual
新   1: SELECT show_errmsg('abc',1) ERROR_MESSAGE from dual
 
ERROR_MESSAGE
--------------------------------------------------------------------------------
ABC-00001: 該当メッセージはありません

UTL_LMS.GET_MESSAGEはどこからメッセージを取得しているのか?

oerrが参照していたmsgファイルと同じディレクトリに「msb」という拡張子が付いたバイナリファイルがあるのですが、これらのファイルの中にメッセージが格納されているようです。

それでは、日本語と英語メッセージが格納されているであろうファイルをRenameして、メッセージが取得できなくなるかどうか確認してみましょう。

1
2
3
$ cd $ORACLE_HOME/rdbms/mesg
$ mv oraja.msb oraja.msb.bak
$ mv oraus.msb oraus.msb.bak
1
2
3
4
5
SQL> SELECT show_errmsg('ora',4031) ERROR_MESSAGE from dual;
 
ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-04031: 該当メッセージはありません

やはり、msbファイルが関係していました。

oerrは単純なテキストファイルの内容を表示させる仕様でしたが、Oracle9i以降は英語以外の言語でエラー・メッセージを取得するインターフェースはUTL_LMS.GET_MESSAGEだけになりました。

msbファイルの中にはmsgファイルのような原因や対処に関する説明は書いてあるのでしょうか?こればかりはOracle社の中の人でなければわかりませんが、将来そのような情報が使えるようになるといいですね。

今日はここまで