2025-02-21

UNDO表領域の拡張エラー（ORA-30036）を検証する

Oracle

今回はUNDO表領域の拡張エラー（ORA-30036）について検証しました。

ORA-30036はUNDO表領域に使用可能な領域がなくなり、新たにエクステントを確保（＝UNDOセグメント拡張）できない場合に発生します。
Oracleはデータ変更（更新、削除など）時に変更前のデータ（UNDOデータ）を生成し、UNDOセグメントに書き込んで一定期間保持します（UNDOデータは主にトランザクションのロールバックや読取り一貫性で使用します）
たとえば、DELETE文実行時に生成したUNDOデータをUNDOセグメントに書き込んでいる途中でUNDOセグメントがいっぱいになり、
新たにエクステントを確保しようとしたがUNDO表領域に使用可能な領域がないためUNDOセグメント拡張に失敗するというケースもあります。

1. Oracle側での対応案
1-1. UNDO表領域のサイズを拡張する
UNDO表領域のサイズを手動拡張する。ユーザが安全と見積もったサイズで拡張するためORA-30036の発生確率は低くなる
1-2. UNDO表領域の自動拡張をONにする
UNDOセグメント不足時、事前に定義した最大サイズまでUNDO表領域を一定サイズごとに自動拡張する。必要に応じて自動拡張するためORA-30036の発生確率は低くなる
1-3. UNDO保存の保証を無効にする
UNDO保存の保証とは、最小UNDO保存期間内のUNDOデータは上書きされないことを保証する機能。有効の場合、最小UNDO保存期間内のUNDOデータは上書されず、他の領域に書き込まれるためORA-30036の発生確率が高くなる
無効の場合、UNDOセグメント不足時は最小UNDO保存期間内のUNDOデータであっても上書きされるためORA-30036の発生確率は低くなる
1-4. 最小UNDO保存期間の値を小さくする
UNDO保存の保証が有効の場合、最小UNDO保存期間内のUNDOデータは上書きされないためエクステントの使用効率が悪くなる。
最小UNDO保存期間の値を小さくする（UNDOデータが上書きされない期間を短くする）ことでエクステントの使用効率が向上するためORA-30036の発生確率は低くなる
※自動拡張OFF、かつ、UNDO保存の保証が無効の場合、最小UNDO保存期間の値は無視される（UNDO保存期間はOracleで自動チューニングされる）

2 ユーザ側での対応案
2-1. データ処理件数を制御
データの処理件数を絞ってデータ変更する。1回のトランザクションで生成するUNDOデータが減るためORA-30036の発生確率は低くなる
2-2. TRUNCATE文を使用する
DELETE文ではなくTRUNCATE文でデータを削除（切り捨て）する。TRUNCATE文はUNDOデータを生成しないためORA-30036は発生しない

検証環境
OS：Oracle Linux 6.5
DB：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition

前提
・自動UNDO管理
・UNDO保存の保証：無効
・UNDO保存期間：900秒　※今回は自動拡張OFF、かつ、UNDO保存の保証が無効のため、この値は無視されます。

検証シナリオ
●パターン１
削除件数を指定せず、全データを一気に削除（データ削除時に生成されたUNDOデータがUNDO表領域に収まらないパターン）
手順１－１．データ削除前のUNDO情報を確認
手順１－２．データを一気に削除
手順１－３．データ削除後のUNDO情報を確認

●パターン２
削除件数を指定し、全データを複数回に分けて削除（データ削除時に生成されたUNDOデータがUNDO表領域に収まるパターン）
手順２－１．データ削除前のUNDO情報を確認
手順２－２．データを50,000件（約5MB）ずつ削除
手順２－３．データ削除後のUNDO情報を確認

検証結果
●パターン１
削除件数を指定せず、全データを一気に削除（データ削除時に生成されたUNDOデータがUNDO表領域に収まらないパターン）
→全データ削除の途中でORA-30036が発生した。

●パターン２
削除件数を指定し、全データを複数回に分けて削除（データ削除時に生成されたUNDOデータがUNDO表領域に収まるパターン）
→ORA-30036は発生せず、全データを削除できた。

検証ログ
●パターン１
削除件数を指定せず、全データを一気に削除（データ削除時に生成されたUNDOデータがUNDO表領域に収まらないパターン）

手順１－１．データ削除前のUNDO情報を確認

SQL> --UNDO表領域の使用可能サイズと使用可能ブロック数
SQL> SELECT
2 TABLESPACE_NAME
3 , BYTES / 1024 / 1024 as TOTAL_MB
4 , BLOCKS
5 , USER_BYTES / 1024 / 1024 as AVAIL_MB
6 , USER_BLOCKS as AVAIL_BLKS
7 FROM
8 DBA_DATA_FILES
9 WHERE
10 TABLESPACE_NAME = 'UNDOTBS1';

TABLESPACE_NAME TOTAL_MB BLOCKS AVAIL_MB AVAIL_BLKS
------------------------- ------------------ ---------- ---------- ----------
UNDOTBS1 20.0 2560 19 2432
★実際ユーザーが使用可能サイズ（AVAIL_MB）は19MB、使用可能ブロック数（AVAIL_BLKS）は2432です。

SQL> --UNDO表領域（ACTIVE/UNEXPIREDのUNDOデータ）の使用状況
SQL> SELECT
2 DUE.TABLESPACE_NAME
3 , ROUND(DDF.TOTAL_BYTES / 1024 / 1024, 1) as TOTAL_MB
4 , ROUND(DUE.USED_BYTES / 1024 / 1024, 1) as USED_MB
5 , ROUND((DUE.USED_BYTES / DDF.TOTAL_BYTES) * 100, 2) as RATIO_PCT
6 FROM
7 (
8 SELECT
9 TABLESPACE_NAME
10 , SUM(BYTES) AS USED_BYTES
11 FROM
12 DBA_UNDO_EXTENTS
13 WHERE
14 STATUS IN('ACTIVE', 'UNEXPIRED')
15 AND TABLESPACE_NAME = 'UNDOTBS1'
16 GROUP BY
17 TABLESPACE_NAME
18 ) DUE,
19 (
20 SELECT
21 TABLESPACE_NAME
22 , SUM(BYTES) AS TOTAL_BYTES
23 FROM
24 DBA_DATA_FILES
25 WHERE
26 TABLESPACE_NAME = 'UNDOTBS1'
27 GROUP BY
28 TABLESPACE_NAME
29 ) DDF
30 WHERE
31 DUE.TABLESPACE_NAME = DDF.TABLESPACE_NAME
32 ORDER BY
33 DUE.TABLESPACE_NAME;

TABLESPACE_NAME TOTAL_MB USED_MB RATIO_PCT
------------------------- ------------------ ------------------ ---------
UNDOTBS1 20.0 0.6 3.10
★UNDO表領域の使用サイズ（USED_MB）は0.6MBです。

SQL> --UNDOデータのステータスごとのエクステント使用状況
SQL> SELECT
2 TABLESPACE_NAME
3 , STATUS
4 , COUNT(*) as USED_EXTS
5 , SUM(BYTES) / 1024 / 1024 as USED_MB
6 , SUM(BLOCKS) as USED_BLKS
7 FROM
8 DBA_UNDO_EXTENTS
9 GROUP BY
10 TABLESPACE_NAME
11 , STATUS
12 ORDER BY
13 TABLESPACE_NAME
14 , STATUS;

TABLESPACE_NAME STATUS USED_EXTS USED_MB USED_BLKS
------------------------- -------------------- ---------- ------------------ ----------
UNDOTBS1 EXPIRED 10 0.6 80
UNDOTBS1 UNEXPIRED 10 0.6 80
★EXPIRED/UNEXPIREDのUNDOデータがあり、それぞれ0.6MBずつ使用しています。

SQL> --UNDOセグメントの使用状況
SQL> SELECT
2 TABLESPACE_NAME
3 , SUM(BLOCKS) as USED_BLKS
4 , ROUND(SUM(BYTES) / 1024 / 1024, 1) as USED_MB
5 , SUM(EXTENTS) as USED_EXTS
6 FROM
7 DBA_SEGMENTS
8 WHERE
9 TABLESPACE_NAME = 'UNDOTBS1'
10 GROUP BY
11 TABLESPACE_NAME;

TABLESPACE_NAME USED_BLKS USED_MB USED_EXTS
------------------------- ---------- ------------------ ----------
UNDOTBS1 160 1.3 20
★使用ブロック数（USED_BLKS）は160、使用サイズ(USED_MB)は1.3MB、使用エクステント数（USED_EXTS）は20です。

SQL> --UNDOブロックの生成状況
SQL> SELECT
2 TO_CHAR(BEGIN_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_TM
3 , TO_CHAR(END_TIME,'YYYY-MM-DD HH24:MI:SS') END_TM
4 , UNDOBLKS
5 , SSOLDERRCNT
6 , NOSPACEERRCNT
7 , TUNED_UNDORETENTION
8 FROM
9 V$UNDOSTAT
10 ORDER BY
11 END_TM DESC;

BEGIN_TM END_TM UNDOBLKS SSOLDERRCNT NOSPACEERRCNT TUNED_UNDORETENTION
-------------------- -------------------- ---------- ----------- ------------- -------------------
2023-07-28 16:10:00 2023-07-28 16:12:11 0 0 0 29847
★データ削除前のUNDOブロック生成数（UNDOBLKS）は0です（UNDOブロックは生成されていない）

手順１－２．データを一気に削除
SQL> --データ削除前の件数確認
SQL> SELECT COUNT(*) FROM APP_TBL;
COUNT(*)
----------
300000
★APP_TBL表には300,000件（約30MB）のデータが入っています。

SQL> --データ削除
SQL> DELETE FROM APP_TBL;
DELETE FROM APP_TBL
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
★削除件数を指定せず、APP_TBL表から300,000件（約30MB）のデータを一気に削除します（約30MBのUNDOデータが生成されます）
　DELETE文で生成されたUNDOデータはUNDO表領域に収まらないため、ORA-30036が発生する想定です。
　DELETE文実行後、しばらくしてORA-30036が発生しました。

SQL> --データ削除後の件数確認
SQL> SELECT COUNT(*) FROM APP_TBL;
COUNT(*)
----------
300000
★データ削除に失敗したため、APP_TBL表のデータ件数も変わっていません。

手順１－３．データ削除後のUNDO情報を確認
※UNDO情報の確認SQLは手順１－１で使用したものと同じです。
SQL> --UNDO表領域（UNDOデータのステータスACTIVE/UNEXPIRED）の使用状況
TABLESPACE_NAME TOTAL_MB USED_MB RATIO_PCT
------------------------- ------------------ ------------------ ---------
UNDOTBS1 20.0 18.6 93.10
★UNDO表領域の使用サイズ（USED_MB）は18.6MBです。

SQL> --UNDOデータのステータスごとのエクステント使用状況
TABLESPACE_NAME STATUS USED_EXTS USED_MB USED_BLKS
------------------------- --------- ---------- ------------------ ----------
UNDOTBS1 EXPIRED 6 0.4 48
UNDOTBS1 UNEXPIRED 103 18.6 2384
★EXPIRED/UNEXPIREDのUNDOデータが合計19MB使用しています。手順１－１で確認した使用可能サイズ（19MB）に達しています。

SQL> --UNDOセグメントの使用状況
TABLESPACE_NAME USED_BLKS USED_MB USED_EXTS
------------------------- ---------- ------------------ ----------
UNDOTBS1 2432 19.0 64
★使用ブロック数と使用サイズが、手順１－１で確認した使用可能ブロック数（2432）と使用可能サイズ（19MB）に達しており、
　UNDOセグメントが枯渇していたということが分かります。

SQL> --UNDOブロック生成状況
BEGIN_TM END_TM UNDOBLKS SSOLDERRCNT NOSPACEERRCNT TUNED_UNDORETENTION
-------------------- -------------------- ---------- ----------- ------------- -------------------
2023-07-28 16:10:00 2023-07-28 16:14:37 2282 0 1 202
★データ削除時、UNDOブロックが2,282ブロック（約18MB）生成されたタイミングでUNDOセグメント拡張に失敗したため、
　NOSPACEERRCNT列は1になりました（拡張に失敗するとインクリメントされる）

●パターン２
削除件数を指定し、全データを複数回に分けて削除（データ削除時に生成されたUNDOデータがUNDO表領域に収まるパターン）
※事前に検証用UNDO表領域を再作成しています。

手順２－１．データ削除前のUNDO情報を確認
※UNDO情報の確認SQLは手順１－１で使用したものと同じです。
SQL> --UNDO表領域（UNDOデータのステータスACTIVE/UNEXPIRED）の使用状況
TABLESPACE_NAME TOTAL_MB USED_MB RATIO_PCT
------------------------- ------------------ ------------------ ---------
UNDOTBS1 20.0 0.6 3.10
★UNDO表領域の使用サイズ（USED_MB）は0.6MBです。

SQL> --UNDOデータのステータスごとのエクステント使用状況
TABLESPACE_NAME STATUS USED_EXTS USED_MB USED_BLKS
------------------------- -------------------- ---------- ------------------ ----------
UNDOTBS1 EXPIRED 10 0.6 80
UNDOTBS1 UNEXPIRED 10 0.6 80
★EXPIRED/UNEXPIREDのUNDOデータがあり、それぞれ0.6MBずつ使用しています。

SQL> --UNDOセグメントの使用状況
TABLESPACE_NAME USED_BLKS USED_MB USED_EXTS
------------------------- ---------- ------------------ ----------
UNDOTBS1 160 1.3 20
★使用ブロック数（USED_BLKS）は160、使用サイズ(USED_MB)は1.3MB、使用エクステント数（USED_EXTS）は20です。

SQL> --UNDOブロック生成状況
BEGIN_TM END_TM UNDOBLKS SSOLDERRCNT NOSPACEERRCNT TUNED_UNDORETENTION
-------------------- -------------------- ---------- ----------- ------------- -------------------
2023-07-28 16:30:00 2023-07-28 16:33:27 0 0 0 1043
2023-07-28 16:20:00 2023-07-28 16:30:00 30 0 0 949
2023-07-28 16:10:00 2023-07-28 16:20:00 2287 0 1 486
★データ削除前のUNDOブロック生成数（UNDOBLKS）は0です（UNDOブロックは生成されていない）

手順２－２．データを50,000件（約5MB）ずつ削除
SQL> --データ削除前の件数確認
SQL> SELECT COUNT(*) FROM APP_TBL;
COUNT(*)
----------
300000
★APP_TBL表には300,000件（約30MB）のデータが入っています。

SQL> --データ削除
SQL> DELETE FROM APP_TBL WHERE ROWNUM <= 50000;

50000 rows deleted.

SQL> COMMIT;

Commit complete.
★1回のDELETE文で50,000件（約5MB）ずつ削除し、コミットします。この作業を全データ分（合計6回）繰り返します。
　1回のDELETE文で生成されたUNDOデータはUNDO表領域に収まり、コミットすることでUNDOデータを上書きできる状態にするため、ORA-30036は発生しない想定です。

SQL> --データ削除後の件数確認（DELETE＆COMMITを6回繰り返した後）
SQL> SELECT COUNT(*) FROM APP_TBL;
COUNT(*)
----------
0
★全データが削除されました。

手順２－３．データ削除後のUNDO情報を確認
※UNDO情報の確認SQLは手順１－１で使用したものと同じです。
SQL> --UNDO表領域（UNDOデータのステータスACTIVE/UNEXPIRED）の使用状況
TABLESPACE_NAME TOTAL_MB USED_MB RATIO_PCT
------------------------- ------------------ ------------------ ---------
UNDOTBS1 20.0 18.7 93.40
★UNDO表領域の使用サイズ（USED_MB）は18.7MBです。
　手順１－３同様にUNDO表領域の使用状況を見るだけでは判断できないため、エクステント使用状況やセグメント使用状況も確認します。

SQL> --UNDOデータのステータスごとのエクステント使用状況
TABLESPACE_NAME STATUS USED_EXTS USED_MB USED_BLKS
------------------------- -------------------- ---------- ------------------ ----------
UNDOTBS1 EXPIRED 5 0.3 40
UNDOTBS1 UNEXPIRED 104 18.7 2392
★EXPIRED/UNEXPIREDのUNDOデータが合計19MB使用しています。今回も手順１－１で確認した使用可能サイズ（19MB）に達しています。

SQL> --UNDOセグメントの使用状況
TABLESPACE_NAME USED_BLKS USED_MB USED_EXTS
------------------------- ---------- ------------------ ----------
UNDOTBS1 2432 19.0 109
★使用ブロック数と使用サイズが、手順１－１で確認した使用可能ブロック数（2432）と使用可能サイズ（19MB）に達しており、
　今回もUNDOセグメントが枯渇していたということが分かります。
　しかし、使用エクステント数は109（ORA-30036発生時は64）となっており、UNDOセグメントが枯渇間近になりながらもエクステントを使い回せていたということが分かります。

SQL> --UNDOブロック生成状況
BEGIN_TM END_TM UNDOBLKS SSOLDERRCNT NOSPACEERRCNT TUNED_UNDORETENTION
-------------------- -------------------- ---------- ----------- ------------- -------------------
2023-07-28 16:30:00 2023-07-28 16:35:40 7898 0 0 74
2023-07-28 16:20:00 2023-07-28 16:30:00 30 0 0 949
2023-07-28 16:10:00 2023-07-28 16:20:00 2287 0 1 486

参考資料
オラクルマスター教科書 Silver DBA Oracle Database Administration I
https://blogs.oracle.com/otnjp/post/shibacho-028

2025-02-21

Oracleのエクステント確保時の動作

Oracle

今回はOracleのエクステント確保時の動作について検証しました。

Oracleはデータ登録やテーブル作成などのタイミングでエクステントを確保します。
登録したデータのサイズがエクステント割当てサイズより大きくなった場合、新しくエクステントを確保します（新しくエクステントが確保されたタイミングで表領域の使用量も増えます）
しかし、登録したデータのサイズがエクステント割当てサイズより小さい場合、データ自体は登録されているがエクステントは確保されず表領域の使用量も増えません。

検証環境
OS：Oracle Linux 6.5
DB：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition

前提
・標準ブロックサイズ：8KB
・表領域の自動拡張：OFF
・エクステント管理方法：ローカル管理表領域
・エクステント割当てサイズ：均一（UNIFORM）※割当てサイズは1MB
・セグメント領域管理方法：自動セグメント領域管理
・bigfile表領域

検証手順
●パターン１
データを複数件登録（登録したデータサイズがエクステント割当てサイズ(1MB)より小さいパターン）
手順１－１．データ登録前の表領域の使用量を確認
手順１－２．データ登録（約500KB）
手順１－３．データ登録後の表領域の使用量を確認

●パターン２
データを複数件登録（登録したデータサイズがエクステント割当てサイズ(1MB)より大きいパターン）
手順２－１．データ登録前の表領域の使用量を確認
手順２－２．データ登録（約1.5MB）
手順２－３．データ登録後の表領域の使用量を確認

検証結果
●パターン１
データを複数件登録（登録したデータサイズがエクステント割当てサイズ(1MB)より小さいパターン）
→エクステントは確保されず、表領域の使用量も増えていないように見えた。

●パターン２
データを複数件登録（登録したデータサイズがエクステント割当てサイズ(1MB)より大きいパターン）
→新しくエクステントが確保され、表領域の使用量も増えた。

検証ログ
●パターン１
データを複数件登録（登録したデータサイズがエクステント割当てサイズ(1MB)より小さいパターン）

手順１－１．データ登録前の表領域の使用量を確認
SQL> SELECT
2 DDF.TABLESPACE_NAME
3 , ROUND(DDF.TOTAL_BYTES / 1024 / 1024, 1) AS TOTAL_MB
4 , ROUND((DDF.TOTAL_BYTES - DFS.FREE_TOTAL_BYTES) / 1024 / 1024, 1) AS USED_MB
5 , ROUND(DFS.FREE_TOTAL_BYTES / 1024 / 1024, 1) AS FREE_MB
6 , ROUND(NVL((DDF.TOTAL_BYTES - DFS.FREE_TOTAL_BYTES) / DDF.TOTAL_BYTES * 100, 100), 2) AS RATIO_PCT
7 FROM
8 (
9 SELECT
10 TABLESPACE_NAME
11 , SUM(BYTES) TOTAL_BYTES
12 FROM
13 DBA_DATA_FILES
14 GROUP BY
15 TABLESPACE_NAME
16 ) DDF,
17 (
18 SELECT
19 TABLESPACE_NAME FREE_TABLESPACE_NAME
20 , SUM(BYTES) FREE_TOTAL_BYTES
21 FROM
22 DBA_FREE_SPACE
23 GROUP BY
24 TABLESPACE_NAME
25 ) DFS
26 WHERE
27 DDF.TABLESPACE_NAME = DFS.FREE_TABLESPACE_NAME(+)
28 AND DDF.TABLESPACE_NAME = 'APP_TS'
29 ORDER BY
30 DDF.TABLESPACE_NAME;

TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB RATIO_PCT
------------------------------ ------------------ ------------------ ------------------ ---------
APP_TS 20.0 8.0 12.0 40.00
★データ登録前はAPP_TS表領域の使用量は8MBです。

SQL> --エクステント使用状況確認
SQL> SELECT
2 SEGMENT_NAME
3 , TABLESPACE_NAME
4 , BLOCKS
5 , ROUND(BYTES / 1024 / 1024, 1) AS USED_MB
6 , EXTENTS
7 FROM
8 DBA_SEGMENTS
9 WHERE
10 SEGMENT_NAME = 'APP_TBL';

SEGMENT_NAME TABLESPACE_NAME BLOCKS USED_MB EXTENTS
------------------------- ------------------------------ ---------- ------------------ ----------
APP_TBL APP_TS 128 1.0 1
★データ登録前はAPP_TBL表のエクステント確保サイズは1MBです。
　1つのエクステントで128ブロック使用しているというのが分かります（エクステント割当てサイズ(1,024KB) / ブロックサイズ(8KB) = 128）

手順１－２．データ登録（約500KB）
SQL> INSERT INTO APP_TBL
2 SELECT LPAD(LEVEL, 50, '0'), 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' FROM DUAL CONNECT BY LEVEL <= 5000;

5000 rows created.

SQL> commit;

Commit complete.
★APP_TBL表に約500KB（100byteデータ×5,000回登録）のデータを登録します。
　登録したデータサイズがエクステント割当てサイズより小さいため、表領域の使用量は増えていないように見える想定です。

手順１－３．データ登録後の表領域の使用量を確認
※表領域の使用量の確認SQLは手順１－１で使用したものと同じです。
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB RATIO_PCT
------------------------------ ------------------ ------------------ ------------------ ---------
APP_TS 20.0 8.0 12.0 40.00

※エクステント使用状況の確認SQLは手順１－１で使用したものと同じです。
SEGMENT_NAME TABLESPACE_NAME BLOCKS USED_MB EXTENTS
------------------------- ------------------------------ ---------- ------------------ ----------
APP_TBL APP_TS 128 1.0 1
★登録したデータはエクステント割当てサイズより小さいため、新たにエクステントは確保されず、表領域の使用量も増えていないように見えました。

●パターン２
データを複数件登録（登録したデータサイズがエクステント割当てサイズ(1MB)より大きいパターン）
※事前に検証用表領域・検証用テーブルを再作成し、検証用テーブルはデータが登録されていない状態にしています。

手順２－１．データ登録前の表領域の使用量を確認
※表領域の使用量の確認SQLは手順１－１で使用したものと同じです。
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB RATIO_PCT
------------------------------ ------------------ ------------------ ------------------ ---------
APP_TS 20.0 8.0 12.0 40.0

手順２－２．データ登録（約1.5MB）
SQL> INSERT INTO APP_TBL
2 SELECT LPAD(LEVEL, 50, '0'), 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' FROM DUAL CONNECT BY LEVEL <= 15000;

15000 rows created.

SQL> commit;

Commit complete.
★APP_TBL表に約1.5MB（100byteデータ×15,000回登録）のデータを登録します。
　登録したデータサイズがエクステント割当てサイズより大きいため、表領域の使用量は増える想定です。

手順２－３．データ登録後の表領域の使用量を確認
※表領域の使用量の確認SQLは手順１－１で使用したものと同じです。
TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB RATIO_PCT
------------------------------ ------------------ ------------------ ------------------ ---------
APP_TS 20.0 9.0 11.0 45.00

※エクステント使用状況の確認SQLは手順１－１で使用したものと同じです。
SEGMENT_NAME TABLESPACE_NAME BLOCKS USED_MB EXTENTS
------------------------- ------------------------------ ---------- ------------------ ----------
APP_TBL APP_TS 256 2.0 2
★登録したデータはエクステント割当てサイズより大きいため、新しくエクステントが1つ確保され、表領域の使用量も1MB増えました。

参考資料
オラクルマスター教科書 Silver DBA Oracle Database Administration I

2024-12-16

SELECTなのに更新処理？！～遅延ブロッククリーンアウトの動きに迫る～

Oracle Oracle-パフォーマンス

この記事は、JPOUG Advent Calendar 2024 16日目の記事です。
15日目は Shinjiro Fujita さんの記事「Oracle Database 23ai 新機能「リアルタイムSQL計画管理 (SPM)」を試してみた #oracle - Qiita」でした。
今回はOracle Databaseの遅延ブロッククリーンアウトについて調査、検証してみました。
なぜ遅延ブロッククリーンアウトが気になったのか忘れたのですが、半年ぐらい前にとあるWebサイトで遅延ブロッククリーンアウトについて書かれている記事を見つけ、その不思議な動きに興味を持ったのだと思います。
しかし、仕組みが（私にとっては）非常に複雑で初めて読んだときはまったく理解できませんでした。
私自身は過去の業務で遅延ブロッククリーンアウトに遭遇したことはないのですが、Oracle界隈では割と有名な話だそうで、自身のナレッジの1つとして調査、検証してみました。

ブロッククリーンアウトとは？
遅延ブロッククリーンアウトについて調べる前にそもそもブロッククリーンアウトとは何か調べてみました。
ブロッククリーンアウトは、データブロックのITL（データブロックを更新しているトランザクションのリスト）に登録されているトランザクションの状態をコミット済みとして記録します。
※ブロッククリーンアウトとチェックポイントは同時には発生しないため、この時点ではバッファとデータファイルで同期されていない可能性があります。
【補足】
ITL（Interested Transaction List）
「Interested」という単語自体知らなかったので辞書で調べてみました。直訳すると「興味があるトランザクションの一覧」になります。
「Oracleが管理するトランザクションの一覧」といったニュアンスでしょうか。
ITLはデータブロックヘッダーに存在し、データブロックを更新しているトランザクションをエントリとして管理します。
なお、OracleはITLエントリの情報から未コミット/コミット済みを判断します。

遅延ブロッククリーンアウトとは？
上記の内容を前提として、遅延ブロッククリーンアウトについて調べてみました。
遅延ブロッククリーンアウトは、未コミット状態となっているデータブロックのITLエントリ（トランザクションの状態）をコミット発行したタイミングではなく、別トランザクションが次回該当データブロックにアクセスしたときにコミット済みに更新（記録）します。
バッチなどで大量のデータを処理する際、未コミットやコミット済みのデータブロックが溜まり続けることでバッファ・キャッシュ領域が枯渇する場合があります。
その場合、Oracleはバッファ・キャッシュ領域に空きを確保するため、トランザクションが未コミット/コミット済みに関わらずデータファイルに書き込みます。
コミット発行された場合、ITLエントリ（トランザクションの状態）をコミット済みに更新する必要がありますが、退避したデータブロックを再度バッファ・キャッシュ領域にロードせず、UNDOセグメントヘッダーのトランザクション表（TRN TBL）にコミット済みであることを記録しておきます。
しかし、この時点ではデータファイルのデータブロックのITLエントリは未コミット状態のままです。
ではデータファイルのデータブロックのITLエントリは誰がいつコミット済みに更新するのでしょうか？
それは次回該当データブロックにアクセスしたトランザクション（SELECT含む）です。
次回該当データブロックにアクセスしたトランザクションがトランザクション表を参照し、コミット発行済みであると判断し、データブロックのITLエントリをコミット済みに更新します。
更新処理（ITLの書き換え）が発生するため、たとえSELECTでもREDOデータが発生します。
なお、トランザクション表を参照する際、シングルブロックリードとなります。（Oracleの仕様）

こんがらがってきたので下記のように整理してみました。
・遅延ブロッククリーンアウトはバッファ・キャッシュ領域の枯渇により発生する
・コミット発行後、ITLエントリ更新のために退避したデータブロックを再度バッファ・キャッシュ領域にはロードしない（コミット済みであることをUNDOセグメントヘッダーに記録しておく）
・次回該当データブロックにアクセスしたトランザクションがITLエントリをコミット済みに更新する（REDOデータが発生する）
・トランザクション表へのアクセスはシングルブロックリードとなる（Oracle ExadataのSmart Scanも効かないという弊害がある）

文章や図だけだといまいちピンと来ないので、実際に検証してみました。
※図の例はUPDATEですが、検証はINSERTで実施しています。

検証環境
OS：Oracle Linux 7.9
DB：Oracle Database 19c Release Update 3 (19.3.0.0.0) Enterprise Edition

前提
・バッファ・キャッシュサイズは約1.5GB
・事前に検証用表領域、検証用テーブルは作成済み（表領域名：APP_TS、テーブル名：APL_TBL）

検証シナリオ
●パターン１（通常のブロッククリーンアウト）
手順１．トランザクション実行
手順２．コミット発行
手順３．バッファ・キャッシュのフラッシュ
手順４．ブロック・ダンプ取得
手順５．別セッションにて該当データブロックにアクセス（SELECT）

●パターン２（遅延ブロッククリーンアウト）
手順１．トランザクション実行
手順２．バッファ・キャッシュのフラッシュ
手順３．コミット発行
手順４．ブロック・ダンプ取得（遅延ブロッククリーンアウト発生前）
手順５．別セッションにて該当データブロックにアクセス
手順６．ブロック・ダンプ取得（遅延ブロッククリーンアウト発生後）
手順７．別セッションにて該当データブロックにアクセス（遅延ブロッククリーンアウトの解消確認）

検証ログ
●パターン１（通常のブロッククリーンアウト）
手順１．トランザクション実行
トランザクション（今回は検証用テーブルにデータINSERT(1000件)）を開始します。
後述のITLエントリのトランザクションとの突合用にトランザクション情報も取得しておきます。

[oracle@vm-oel7 ~]$ sqlplus / as sysdba
SQL> INSERT INTO APP_TBL SELECT LPAD(LEVEL, 1000, '0') FROM DUAL CONNECT BY LEVEL <= 1000;

1000 rows created.

SQL> --アクティブ・トランザクション確認
SQL> select
2 xid,
3 xidusn,
4 xidslot,
5 xidsqn,
6 ubablk,
7 ubasqn,
8 ubarec,
9 status,
10 start_ubablk,
11 start_ubasqn,
12 start_ubarec,
13 used_ublk,
14 used_urec,
15 start_scn
16 from
17 v$transaction
18 order by
19 xid;

XID XIDUSN XIDSLOT XIDSQN UBABLK UBASQN UBAREC STATUS START_UBABLK START_UBASQN START_UBAREC USED_UBLK USED_UREC START_SCN
---------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ------------ ------------ ------------ ---------- ---------- ----------
0A0010009E040000 10 16 1182 1839 276 55 ACTIVE 1835 276 28 4 250 2711411

手順２．コミット発行
コミットを実行し、トランザクションを終了します。

SQL> commit;

Commit complete.

手順３．バッファ・キャッシュのフラッシュ
バッファ・キャッシュをフラッシュし、疑似的にコミット発行後にバッファ・キャッシュが枯渇した状態にします。
※未コミット/コミット済みに関わらずデータブロックはデータファイルに書き込まれます。

SQL> alter system flush buffer_cache;

System altered.

手順４．ブロック・ダンプ取得
コミット発行後にバッファ・キャッシュをフラッシュした状態のデータブロックのITLを確認するため、検証用テーブルのデータブロックの情報をダンプします。
※検証ログのダンプ結果はトランザクションが存在するエントリのみ表示しています。

SQL> --検証テーブルが使っているブロック特定
SQL> select
2 segment_name
3 , file_id
4 , extent_id
5 , block_id
6 , blocks --extent size(uniform) / block size
7 , round(bytes / 1024 / 1024, 2) as used_mb
8 from
9 dba_extents
10 where
11 segment_name = 'APP_TBL'
12 order by
13 extent_id,
14 block_id;

SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS USED_MB
------------------------- ---------- ---------- ---------- ---------- -------------------
APP_TBL 5 0 8696 8 0.06
（中略）
APP_TBL 5 19 8848 8 0.06

SQL> --ブロック・ダンプ取得
SQL> alter session set tracefile_identifier = 'pt1';

Session altered.

SQL> alter system dump datafile 5 block min 8696 block max 8855;

System altered.

最初のブロックのエントリ番号 0x01 の Xid（0x000a.010.0000049e）のピリオドで区切られた値をそれぞれ10進表記にすると、アクティブ・トランザクションのXIDUSN、XIDSLOT、XIDSQNの値と一致していることが分かります。
Uba もピリオドで区切られた値をそれぞれ10進表記にすると、アクティブ・トランザクションの値と一致していることが分かります。
・0x0100072b は 0x01 を除いた値（00072b）はSTART_UBABLK
・0114 はSTART_UBASQN
・1c はSTART_UBAREC
と一致していることが分かります。
なお、最後のブロックのエントリ番号 0x01 の
・0x0100072b は 0x01 を除いた値（00072f）はUBABLK
・0114 はUBASQN
・37 はUBAREC
とXidは固定なのに対し、Ubaはブロック番号とレコード番号が可変になっています。
おそらくUNDOセグメントは循環利用なので可変になっているのではと思います。
上記からアクティブ・トランザクションはトランザクション開始時のUNDOデータとカレントのUNDOデータの両方を持っていることが分かりました。
（UNDOブロックもダンプしてみようと思ったのですが、そこまで検証時間を確保できませんでした…）
また、各エントリの Flag は --U-（コミット済み）になっていることも確認できました。
Scn にも値が入っており、エントリのトランザクションは終了していることも確認できました。

手順５．別セッションにて該当データブロックにアクセス
コミット発行後にバッファ・キャッシュをフラッシュした状態のデータブロックにアクセス（今回はSELECT）します。
アクセスしたときの実行統計や待機イベントも確認してみます。

[oracle@vm-oel7 ~]$ sqlplus / as sysdba
SQL> set autotrace on
SQL> select count(*) from app_tbl;

COUNT(*)
----------
1000

Execution Plan
----------------------------------------------------------
Plan hash value: 1770987

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| APP_TBL | 1200 | 42 (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
236 consistent gets
152 physical reads
0 redo size
550 bytes sent via SQL*Net to client
390 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select
2 wait_class,
3 event,
4 total_waits,
5 total_waits_fg
6 from
7 v$system_event
8 order by
9 wait_class,
10 total_waits desc;

WAIT_CLASS EVENT TOTAL_WAITS TOTAL_WAITS_FG
-------------------- ---------------------------------------- ----------- --------------
Administrative index (re)build lock or pin object 102 102
Administrative OFS operation completion 1 1
Commit log file sync 6 5
（中略）
User I/O db file sequential read 4610 2803
User I/O db file scattered read 292 118
User I/O Disk file operations I/O 236 70

単なるSELECTなのでREDOデータは発生していませんでした。
遅延ブロッククリーンアウトは発生していない想定のため、「db file sequential read」は他起因によるものと思います。

●パターン２（遅延ブロッククリーンアウト）
手順１．トランザクション発行
トランザクション（今回は検証用テーブルにデータINSERT(1000件)）を開始します。
後述のITLエントリのトランザクションとの突合用にトランザクション情報も取得しておきます。
※パターン２検証前に状態をクリアするため、検証用テーブル再作成＆DB再起動しています。

[oracle@vm-oel7 ~]$ sqlplus / as sysdba
SQL> INSERT INTO APP_TBL SELECT LPAD(LEVEL, 1000, '0') FROM DUAL CONNECT BY LEVEL <= 1000;

1000 rows created.

XID XIDUSN XIDSLOT XIDSQN UBABLK UBASQN UBAREC STATUS START_UBABLK START_UBASQN START_UBAREC USED_UBLK USED_UREC START_SCN
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ------------ ------------ ------------ ---------- ---------- ----------
04001B0012050000 4 27 1298 89968 337 58 ACTIVE 89956 337 31 4 250 2719034

手順２．バッファ・キャッシュのフラッシュ
バッファ・キャッシュをフラッシュし、疑似的にコミット発行前にバッファ・キャッシュが枯渇した状態にします。
※未コミット/コミット済みに関わらずデータブロックはデータファイルに書き込まれます。

SQL> alter system flush buffer_cache;

System altered.

手順３．コミット発行
コミットを実行し、トランザクションを終了します。

SQL> commit;

Commit complete.

手順４．ブロック・ダンプ取得（遅延ブロッククリーンアウト発生前）
コミット発行前にバッファ・キャッシュをフラッシュした状態のデータブロックのITLを確認するため、検証用テーブルのデータブロックの情報をダンプします。
※検証ログのダンプ結果はトランザクションが存在するエントリのみ表示しています。

SQL> --ブロック・ダンプ取得
SQL> alter session set tracefile_identifier = 'pt2_before_delayed_block_cleanout';

Session altered.

SQL> alter system dump datafile 5 block min 8696 block max 8855;

System altered.

[oracle@vm-oel7 ~]$ ls -ltr /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_*.trc
-rw-r----- 1 oracle oinstall 3663965 11月 14 10:26 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_11954_pt1.trc
-rw-r----- 1 oracle oinstall 10690 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12167.trc
-rw-r----- 1 oracle oinstall 20035 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12204.trc
-rw-r----- 1 oracle oinstall 1214 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12292.trc
-rw-r----- 1 oracle oinstall 1182 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12302.trc
-rw-r----- 1 oracle oinstall 3657511 11月 14 10:35 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12754_pt2_before_delayed_block_cleanout.trc
[oracle@vm-oel7 ~]$ egrep "^ Itl|^0x0|^Block header dump" /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12754_pt2_before_delayed_block_cleanout.trc
Block header dump: 0x000021fb
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f64.0151.23 ---- 7 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x000021fc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f64.0151.25 ---- 7 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
（中略）
Block header dump: 0x0000228f
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f70.0151.2e ---- 7 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x00002296
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f70.0151.3a ---- 6 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

XidやUbaの見方はパターン１で説明してみたので割愛します。
コミット発行したにも関わらず、エントリの Flag は ----（未コミット）になっていました。
また、Scn も値が入っておらず、トランザクションは終了していない状態になっていました。

[oracle@vm-oel7 ~]$ sqlplus / as sysdba
SQL> set autotrace on
SQL> select count(*) from app_tbl;

COUNT(*)
----------
1000

Execution Plan
----------------------------------------------------------
Plan hash value: 1770987

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
379 consistent gets
152 physical reads
12628 redo size
550 bytes sent via SQL*Net to client
390 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select
2 wait_class,
3 event,
4 total_waits,
5 total_waits_fg
6 from
7 v$system_event
8 order by
9 wait_class,
10 total_waits desc;

WAIT_CLASS EVENT TOTAL_WAITS TOTAL_WAITS_FG
-------------------- ---------------------------------------- ----------- --------------
Administrative index (re)build lock or pin object 102 102
Administrative OFS operation completion 1 1
Commit log file sync 5 5
（中略）
User I/O db file sequential read 11591 7756
User I/O db file scattered read 301 117
User I/O Disk file operations I/O 249 71

単なるSELECTなのにREDOデータが生成されていることが確認できました。
また、フルスキャンなのにパターン１に比べ、「db file sequential read」が増えていることも確認できました。（UNDOセグメントにアクセスしている想定）
（AWRやASHレポートも見てみようと思ったのですが、そこまで検証時間を確保できませんでした…）

手順６．ブロック・ダンプ取得（遅延ブロッククリーンアウト発生後）
遅延ブロッククリーンアウト発生後のデータブロックのITLを確認するため、検証用テーブルを構成するブロックの情報をダンプします。
※検証ログのダンプ結果はトランザクションが存在するエントリのみ表示しています。

SQL> --ブロック・ダンプ取得
SQL> alter session set tracefile_identifier = 'pt2_after_delayed_block_cleanout';

Session altered.

SQL> alter system dump datafile 5 block min 8696 block max 8855;

System altered.

SQL> exit
[oracle@vm-oel7 ~]$ ls -ltr /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_*.trc
-rw-r----- 1 oracle oinstall 3663965 11月 14 10:26 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_11954_pt1.trc
-rw-r----- 1 oracle oinstall 10690 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12167.trc
-rw-r----- 1 oracle oinstall 20035 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12204.trc
-rw-r----- 1 oracle oinstall 1214 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12292.trc
-rw-r----- 1 oracle oinstall 1182 11月 14 10:28 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12302.trc
-rw-r----- 1 oracle oinstall 3657511 11月 14 10:35 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_12754_pt2_before_delayed_block_cleanout.trc
-rw-r----- 1 oracle oinstall 7416343 11月 14 10:38 /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_13005_pt2_after_delayed_block_cleanout.trc
[oracle@vm-oel7 ~]$ egrep "^ Itl|^0x0|^Block header dump" /u01/app/oracle/diag/rdbms/orcl19c/orcl19c/trace/orcl19c_ora_13005_pt2_after_delayed_block_cleanout.trc
Block header dump: 0x000021fb
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f64.0151.23 C--- 0 scn 0x0000000000297d7a
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x000021fc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f64.0151.25 C--- 0 scn 0x0000000000297d7a
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
（中略）
Block header dump: 0x0000228f
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f70.0151.2e C--- 0 scn 0x0000000000297d7a
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Block header dump: 0x00002296
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.01b.00000512 0x01015f70.0151.3a C--- 0 scn 0x0000000000297d7a
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

遅延ブロッククリーンアウトが発生したため、エントリの Flag は C---（コミット済み）になっていました。
また、Scn も値が入っており、トランザクションが終了した状態になっていました。

手順７．別セッションにて該当データブロックにアクセス（遅延ブロッククリーンアウトの解消確認）
遅延ブロッククリーンアウト発生後のデータブロックにアクセス（今回はSELECT）し、遅延ブロッククリーンアウトが解消されていることを確認します。
アクセスしたときの実行統計や待機イベントも確認してみます。

[oracle@vm-oel7 ~]$ sqlplus / as sysdba
SQL> set autotrace on
SQL> select count(*) from app_tbl;

COUNT(*)
----------
1000

Execution Plan
----------------------------------------------------------
Plan hash value: 1770987

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
154 consistent gets
0 physical reads
0 redo size
550 bytes sent via SQL*Net to client
390 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select
2 wait_class,
3 event,
4 total_waits,
5 total_waits_fg
6 from
7 v$system_event
8 order by
9 wait_class,
10 total_waits desc;

WAIT_CLASS EVENT TOTAL_WAITS TOTAL_WAITS_FG
-------------------- ---------------------------------------- ----------- --------------
Administrative index (re)build lock or pin object 102 102
Administrative OFS operation completion 1 1
Commit log file sync 6 6
（中略）
User I/O db file sequential read 11911 7917
User I/O db file scattered read 301 117
User I/O Disk file operations I/O 283 96

遅延ブロッククリーンアウトが解消されたため、REDOデータは生成されていないことが確認できました。
「db file sequential read」が多少増えていますが、今回は遅延ブロッククリーンアウトは発生していない想定のため、他起因によるものと思います。

対応案
遅延ブロッククリーンアウトの原因は「バッファ・キャッシュ領域の枯渇」ということを前提に対応案を考えてみました。

インフラ、アプリ両方でそれぞれ対応案がありますが、どちらもメリット・デメリットがありますね。

参考サイト
執筆にあたり主に下記Webサイトを参考にさせていただきました。ありがとうございました。
フルスキャンで何故シングルブロックリードが発生するのか？(2/2) - DBひとりでできるもん
遅延ブロッククリーンアウトの性能対策/JPOUG Tech Talk Night #8 - Speaker Deck
OracleDatabaseのトランザクションと一貫性 - Speaker Deck
遅延ブロッククリーンアウト - オラクル・Oracleをマスターするための基本と仕組み
遅延ブロッククリーンアウトを観測する① - SQL*Plusの使いにくさは異常

おわりに
今回調べたことを整理するため記事にしましたが、今でも理解できていない箇所は多々残っており、見落としもあると思います。
たとえばITLのFlagの「U」と「C」の具体的な違いが分かっていなかったり…
機会があればぜひ製品サポートに聞いてみたいと思います。

今年度はシステム更改が始まり、Enterprise Managerも構築することになりました。
来年はEnterprise Manager関連の検証が多くなりそうです。

最後までお読みいただきましてありがとうございました。
17日目の記事もお楽しみに！

2024-11-17

OracleのLifetime Support（ライフタイム・サポート・ポリシー）

Oracle Oracle-保守・運用

今回はOracleのライフタイム・サポート・ポリシーについて調査しました。

※本記事は個人の学習目的でまとめた内容となります。製品のサポート対象については必ずOracleサポートへ確認して正確な情報を入手してください。

ライフタイム・サポート・ポリシー（Lifetime Support）とは
2006年3月1日に日本オラクルが発表したオラクル社の製品サポートの提供方針（ポリシー）です。
オラクル社の製品のライセンスとサポート契約をユーザが継続している間は無期限にサポートを提供します。
サポート契約を結んでいる場合、製品のアップデート、パッチの提供、さまざまな技術サポートなどを受けることができます。

Premier Support
米国オラクル本社の製品出荷開始日から5年間メンテナンスとサポートを提供します。
Premier Support期間終了時点でExtended Supportを選択していない場合、Premier Support期間終了後、自動的にSustaining Supportに移行されます。
Premier Support期間中にもかかわらずOracle製品と連携して使用する製品（例えばOS）のサポートが終了した場合、Premier Supportは終了し、Sustaining Supportでの対応となります。

Extended Support
一部のサポート対象項目に対してさらに3年間延長してPremier Supportと同等のサービスを提供します。
Extended Supportは最終PSRに対してのみ提供されます（たとえばOracle Database 10gR2であれば10.2.0.5にのみ提供される）
Extended Support期間中にもかかわらずOracle製品と連携して使用する製品（例えばOS）のサポートが終了した場合、Extended Supportは終了し、Sustaining Supportでの対応となります。

Sustaining Support
Premier SupportやExtended Supportに比べサポート対象項目に制限がありますが、ユーザがライセンスやサポート計画を継続している間は無期限で一部のサポート対象項目に対してメンテナンスとサポートを提供します。
プログラム修正やパッチなども提供されますがPremier Support期間中（Extended Supportを選択した場合、Extended Support期間中）に作成されたものに限定されます。
Extended Support終了後はExtended Supportを選択しなかったユーザに対してもExtended Support期間中に作成されたプログラム修正、パッチなどを提供します。

■参考資料
Oracle Response Center: ソフトウェア | NEC
ソフトウェア | Lifetime Support Policy | Oracle 日本
新・門外不出のOracle現場ワザエキスパートが明かす運用・管理の極意

■おわりに
大抵のプロジェクトではサポート契約を結んでいるプロジェクトが多いと思いますが、製品アップグレードやパッチ提供などサポート契約の期間を意識した運用スケジュールの計画が重要です。
運用スケジュールの意識があまり無い状態でプロジェクトを進めてPremier SupportやExtended Supportの期間を過ぎてしまうと、障害発生時などユーザが期待するサービスが受けられないリスクもあります。

2024-11-17

Oracleのパッチ

Oracle Oracle-保守・運用

今回はOracleのパッチについて調査しました。

パッチとは
オラクル社が提供する製品のセキュリティホールや不具合（バグ）を修正するものです。
パッチは製品のリリース（バージョン）ごとに提供されていますが、12cR1までと12cR2以降で提供されるパッチの種類が変わりました。

なお、パッチはMOS（My Oracle Support）からダウンロードする必要があります（つまり、サポート契約しないと入手できません）

【補足】
Long Term Support
製品の特定のバージョンを長期間安定的にサポートする
サポート期間は開発元の方針や製品の種類によって異なるが、LTSは数年に設定することが多い
なお、通常のサポート期間（STS：Short Term Support）は数ヶ月から1年前後が多い

リリース（バージョン）番号
従来型リリースモデルと年次リリースモデルでリリース番号の読み方が変わりました。

パッチの種類
12cR1まで（従来型リリースモデル）と12cR2以降（年次リリースモデル）で提供されるパッチの種類が変わっています。

●12cR1まで（従来型リリースモデル）
PSR、PSU、SPU、個別パッチなどが提供されます。
パッチの修正量は大きいものから「PSR　＞　PSU　＞　SPU　＞　個別パッチ」となります。

PSR（Patch Set Release）
年次またはそれ以上（原則1～2年ごと）に提供される最も基本的な重要パッチ
主に下記の内容が含まれている
・セキュリティホール修正
・不具合（バグ）修正（バグフィックス）
・新機能
・オプティマイザ修正
・新規パラメータ
PSR適用後、リリース番号の4桁目のバージョン番号が変わる
（例）11.2.0.4.0の場合、4がバージョン番号

PSU（Patch Set Update）
毎年四半期ごと(1月/4月/7月/10月)に提供される累積パッチ
主に下記の内容が含まれている
・セキュリティホール修正
・不具合（バグ）修正（バグフィックス）
パッチ適用後、リリース番号の5桁目のバージョン番号が変わる
（例）11.2.0.4.0の場合、0がバージョン番号

SPU（Security Patch Update、旧Critical Patch Update）
毎年四半期ごと(1月/4月/7月/10月)に提供される累積パッチ
PSUからセキュリティホール修正のみ抽出したパッチ
主に下記の内容が含まれている
・セキュリティホール修正
SPU適用後、OPatch（opatch lsinventoryコマンド）で適用したSPU番号を確認できる
※SPUはPSUに含まれることが多いため、PSUを適用した場合、別途SPUを適用するか確認が必要
※12.1.0.1以降はSPUはPSUに含まれるため、SPU単体で提供されない

個別パッチ（Interim Patch、PSE）
不定期で提供されるパッチ
1つの不具合に対して特別に提供されるパッチ
PSRやPSUの修正に含まれていない不具合が発生しているが、次のパッチリリースまでデータベースを運用することが困難な場合、Oracleサポートから特別に提供される
個別パッチを適用した場合、他のパッチ適用時にコンフリクト（競合）が発生する可能性がある
たとえば個別パッチAが適用済みで個別パッチBを適用しようとしたが、パッチBで修正されるモジュールがパッチAと同じモジュールだったため、コンフリクトが発生するなど
パッチは同じモジュールを含むパッチを複数適用できない（適用済みパッチと同じモジュールを含むパッチを適用するとコンフリクトが発生する）
将来的に個別パッチはPSRやPSUなどに含まれる場合もあるため、緊急時を除き、積極的に適用しないでも良いと思います
個別パッチ適用後、OPatch（opatch lsinventoryコマンド）で適用した個別パッチ番号を確認できる

BP（Bundle Patch）
Oracle ExadataやOracle Database Appliance、Windowsなど特定のプラットフォーム向け提供される累積パッチ
Windows Bundle Patch（Patch X）
Windowsに提供される累積パッチ
WindowsではPSUやSPU、個別パッチは提供されていない
代わりに上記の修正が含まれているWindows Bundle Patchが提供されている
DBBP（Database Proactive Bundle Patch）
ExadataやDatabase Applianceなどに提供される累積パッチ
Grid Infrastructureとデータベースの両方に対する修正を含む

●12cR2以降（年次リリースモデル）
RU、RUR、個別パッチなどが提供されます。

RU（Release Updates）
毎年四半期ごと(1月/4月/7月/10月)に提供される累積パッチ
特定のリリースに対する追加の修正が含まれる
従来型リリースモデルのPSUと同様の位置づけとなっているが、RUはオプティマイザに対する修正も含まれる
主に下記の内容が含まれている
・セキュリティホール修正
・不具合（バグ）修正（バグフィックス）
・オプティマイザ修正

RUR（Release Update Revisions）
毎年四半期ごと(1月/4月/7月/10月)に提供される累積パッチ
特定のRUに対する追加の修正が含まれる
特定のRUに対して最大2つ提供される
主に下記の内容が含まれている
・セキュリティホール修正

Oracle 12cR2ファミリー
従来型リリースモデルは同一のリリースにおいて複数のリリース番号を持つパッチセットが提供されていた。
（例）
11gR2の場合
初期リリース：11.2.0.1
パッチセット：11.2.0.2　→　11.2.0.3　→　11.2.0.4

12cR2以降
12cR2（12.2.0.1）：従来型リリースモデルの初期リリース相当
18c：従来型リリースモデルのパッチセットの12.2.0.2相当
19c：従来型リリースモデルのパッチセットの12.2.0.3相当

12cR2以降は18c、19cと呼ばれているが、従来型リリースモデルと密接な関係があるため、Oracle12cR2ファミリーとも呼ばれる

パッチ提供期間
パッチはPremier Support期間中（リリースから5年間）、Extended Supportを契約している場合はExtended Support期間中（リリースから最長8年間）は提供されます
※パッチの提供可否については必ずOracleサポートへ確認して正確な情報を入手してください

パッチ適用方法
適用するパッチの種類で適用方法や適用ツールが異なります。
OpatchはOracle Database製品に含まれていますが、MOSから最新版をダウンロードすることを推奨されています（OPatchのバージョンが古いとパッチ適用に失敗する可能性もある）

【補足】
アウトオブプレース
新規に製品ホーム作成後、パッチをインストールする方法
インストール完了後、既存ホームで稼働しているサービスを停止し、製品ホームを切り替える
・インプレースに比べ、サービス停止時間が短い
・新規ホームを作成するため、新規パッチを格納する領域も必要

インプレース
既存ホームで稼働しているサービス停止後、既存の製品ホームにパッチをインストール（上書き）する方法
・アウトオブプレースに比べ、サービス停止時間が長い
・新規パッチ格納用の領域は不要

OPatch（Oracleパッチ適用ユーティリティ）
Oracle製品ホームにパッチを適用したり、適用済みパッチを管理するツールです。
主に下記の機能があります
・パッチの適用
・パッチのロールバック（適用済みパッチの取り消し）
・コンフリクト（競合）チェック

パッチ適用時は下記のコマンドを使用します。
opatchコマンド
Oracleホームに対してパッチを適用する
パッチ適用対象Oracleホームの所有者（たとえばoracleユーザ）で実行する

datapatchコマンド
opatchコマンド実行後、パッチ適用に伴う修正をデータベースに適用する
データベースの内部構造を変更するSQL スクリプトが実行される

opatchautoコマンド
複数の製品ホームに対して一括でパッチを適用する
たとえばRestart環境やRAC環境などGrid Infrastructure製品とOracle Database製品が導入されている場合、GridホームとOracleホームに対して一括でパッチを適用する（datapatchも自動で実行される）
rootユーザで実行する
ローリングアップグレード（デフォルト）で適用するため、RAC環境の場合、パッチ適用に伴う停止時間を短縮できる
※Windowsでは使用不可

【補足】ローリングアップグレード
複数ノードで構成されるクラスタ環境において1ノードずつパッチを適用する方法（シングル環境、Restart環境では使用不可）
パッチ適用に伴うサービス停止時間を最小限に抑えることができるため、可用性の観点で有効な方法
1ノードずつパッチを適用するため、一時的にノード間で新しいパッチと古いパッチが混在する状況になる
※ローリングアップグレードできるパッチとできないパッチがあるため、適用時はパッチのREADMEをよく読むこと（異なるバージョンが混在できるパッチのみ適用可能）

個人ではサポート契約していないため、パッチのダウンロード、パッチ適用の検証はできませんが、Oracle製品インストール直後のパッチバージョンだけ確認してみます。

■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■前提
・PSR 12.1.0.2.0のみ適用済み（フルインストール）
・上記以外のパッチ適用なし
※フルインストール：11.2.0.2以降のPSRの提供方法。Oracle製品のインストールと同時にPSRも自動適用するため、ベースリリースはインストール不要となる。
（例）12cR1をインストールする場合、予めベースリリース12.1.0.1.0にPSR 12.1.0.2.0が適用された状態でダウンロードできるため、ベースリリース12.1.0.1.0をダウンロード後、PSR 12.1.0.2.0を適用するという処理が省略できる。ただし、ベースリリースとPSRがセットになっているため、ファイルサイズも大きくなる

■検証パターン
①製品のパッチバージョン確認（Gridホーム）
②製品のパッチバージョン確認（Oracleホーム）

■検証
①製品のパッチバージョン確認（Gridホーム）
Grid Infrastructure製品（Gridホーム）に適用されているパッチバージョンを確認します

【検証手順】
1. パッチバージョン確認

【作業ログ】

1. パッチバージョン確認
[grid@node1 ~]$ opatch lsinventory

Oracle Interim Patch Installerバージョン12.1.0.1.3
Copyright (c) 2023, Oracle Corporation. All rights reserved。

Oracle Home : /u01/app/12.1.0/grid
Central Inventory : /u01/app/oraInventory
from : /u01/app/12.1.0/grid/oraInst.loc
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : /u01/app/12.1.0/grid/cfgtoollogs/opatch/opatch2023-04-21_11-41-25午前_1.log

Lsinventory Output file location : /u01/app/12.1.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2023-04-21_11-41-25午前.txt

--------------------------------------------------------------------------------
インストールされた最上位製品(1):

Oracle Grid Infrastructure 12c 12.1.0.2.0
このOracleホームには1の製品がインストールされています。

このOracleホームには仮パッチがインストールされていません。

--------------------------------------------------------------------------------
★PSR 12.1.0.2.0が適用されている。PSUなどは適用していないため、「このOracleホームには仮パッチがインストールされていません。」と出ている

②製品のパッチバージョン確認（Oracleホーム）
Database製品（Oracleホーム）に適用されているパッチバージョンを確認します

【検証手順】
1. パッチバージョン確認

【作業ログ】

1. パッチバージョン確認
[oracle@OTDCNA01 ~]$ opatch lsinventory

Oracle Interim Patch Installerバージョン12.1.0.1.3
Copyright (c) 2023, Oracle Corporation. All rights reserved。

Oracle Home : /opt/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /opt/app/oraInventory
from : /opt/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : /opt/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2023-04-21_10-52-15午前_1.log

Lsinventory Output file location : /opt/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2023-04-21_10-52-15午前.txt

--------------------------------------------------------------------------------
インストールされた最上位製品(1):

Oracle Database 12c 12.1.0.2.0
このOracleホームには1の製品がインストールされています。

このOracleホームには仮パッチがインストールされていません。

--------------------------------------------------------------------------------
★PSR12.1.0.2.0が適用されている。PSUなどは適用していないため、「このOracleホームには仮パッチがインストールされていません。」と出ている

※SQLでもバージョンは確認できますが、確認できるのはPSRのみでPSUなどのバージョンは確認できません
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

■参考資料
OPatchautoを使用したパッチ適用オーケストレーション
OPatchを使用したバイナリ・パッチ適用
Oracle Databaseのリリース番号の概要
用語集
Oracle Databaseのリリース番号について
https://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2014/B2-4.pdf（パッチ計画のベスト・プラクティスとパッチ適用時の性能トラブルを未然に防ぐ現場ワザ）
Oracle Database 最新情報と年次リリースモデルに関して - Speaker Deck
データベース・パッチセットの種類と特徴について | NTTデータ先端技術株式会社
オラクルマスター教科書 Gold DBA Oracle Database AdministrationⅡ
オラクルマスター教科書 Oracle Expert RAC 11g R2編
絵で見てわかるシステム構築のためのOracle設計

■おわりに
従来型リリースモデルはパッチの種類によって提供時期が不定期だったり、種類が多かったり分かりにくい印象でした。
年次リリースモデルはパッチの種類に関係なく提供時期が定期的となり、従来型リリースモデルに比べ種類も少なくなったため、分かりやすくなった印象です。
結果不正などのバグフィックスやオプティマイザ修正を伴うパッチを適用した場合、アプリケーション（SQL）の実行計画に影響が出る可能性があるため、アプリケーションの再テストが推奨されています。
なお、RURはRU19.16.0で廃止され、19.17.0以降はMRP（Monthly Recommended Patches）としてパッチ提供するスタイルに変わりました。
※現時点でMRPはLinuxプラットフォームのみ対応しており、他のプラットフォームでRUに含まれていない修正は個別パッチを適用する必要があります。

2024-01-13

Oracleの待機イベント

Oracle Oracle-パフォーマンス

今回はOracleの待機イベントについて調査、検証しました。

待機イベントとは
プロセス（セッション）が処理できず（CPUを使用できず）、他の処理が完了するのを待っているイベント（出来事、アクション）です。
たとえばプロセスA（セッションA）に対して「db file sequential read」という待機イベントが発生している場合、現在ディスクのランダムアクセスが発生しているため、プロセスA（セッションA）は「db file sequential read」が完了するのを待っているということになります。
プロセス（セッション）に待機が発生した場合、待機イベント情報として待機理由や待機時間、待機回数などが記録されます。

主な待機クラス
待機クラスは10gから導入された概念で、待機イベントを特性ごとにグルーピングしたものです。
AWRレポートなどを使用して性能調査する場合、一般的に先ず待機クラスを見た方が良いと言われています。
待機クラスの状況からシステムの傾向やボトルネックのあたりをつけて調査範囲を絞り込むことができるため、いきなり待機イベントを見るより効率的だと言われています。

主な待機イベント
待機イベントは1000種類以上あると言われていますが、その中でも私が今までの現場で良く見た待機イベントを載せます。

enq: TX - row lock contention

log file sync / log file parallel write

buffer busy waits

library cache lock

free buffer waits

log buffer space

SQL*Net message from client
本待機イベントは「SQL文が発行されていない」という状態のため、通常無視して問題ありません。

SQL*Net message to client

SQL*Net more data from client
「SQL*Net message from client」待機イベントとの違いは、クライアントからSQL文が送られてきている途中で送信が滞ってしまったなど。
Oracleはパケットを複数回やり取りすることもあるため、途中で送受信が滞ってしまった場合はこの待機イベントが発生します。

SQL*Net more data to client

db file parallel write

【補足】
データベース・バッファ・キャッシュのバッファ状態
バッファには4つの状態があります。
空き（未使用）
データが読み込まれていない空のバッファ
確保済み（PIN）
読込み中 or 書込み中のバッファ。このバッファにアクセスできるセッションは1つだけであり、別のセッションはアクセスできるまで待機
使用済み
データファイルに書き込まれていないバッファ。データの内容がデータファイルと一致していないバッファ。書き込まれたあとは「クリーン」に遷移
クリーン
データの内容がデータファイルと一致しているバッファ。読取り一貫性を提供するためのバッファ（CRバッファ）としても使用される。クリーンバッファは上書き可能
db file scattered read
データベース・バッファ・キャッシュ上に単一ブロックが分散的（不連続）に配置されるため、「scattered」となる

db file sequential read
データベース・バッファ・キャッシュ上に複数ブロックが連続に配置されるため、「sequential」となる

direct path read
大規模表（データベース・バッファ・キャッシュにキャッシュされないブロック）を直接PGAに配置することで下記メリットがある
・データベース・バッファ・キャッシュにブロックを配置する処理が省略できる
・大量のブロックがデータベース・バッファ・キャッシュに配置されることで既存のブロックがエージアウトされることを防ぐ

待機イベントを確認できる主なビュー
v$event_name
待機イベント一覧の情報
v$session_wait
セッションごとの現在待機している待機イベント（待機状態）
※10g以降はv$sessionにV$SESSION_WAITの列情報も包含されているため、個別でV$SESSION_WAITを見る必要はなくなった
v$system_event
インスタンス起動後の待機イベント（累積値）
v$session_event
セッションごとの待機イベント（累積値）

待機イベントを発生、確認する検証をしてみました。

■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)

■検証パターン
①待機イベント（enq: TX - row lock contention）発生

■検証
①待機イベント（enq: TX - row lock contention）発生
複数のセッションで同一の行をUPDATEする場合、一方のセッションがCOMMITするまでもう一方のセッションは待機イベント（enq: TX - row lock contention）が発生するか検証します。

【検証手順】
1. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
2. [セッション1 / アプリケーションユーザ] EMP表のEMPNO = 7369の行をUPDATE（COMMITしない）
3. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
4. [セッション2 / アプリケーションユーザ] EMP表のEMPNO = 7369の行をUPDATE（COMMITしない）
5. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
6. [セッション1 / アプリケーションユーザ] COMMIT
7. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認

【作業ログ】

1. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');

SID USERNAME EVENT STATE
---------- -------------------- ---------------------------------------- ---------------
15 HR SQL*Net message from client WAITING
275 SCOTT SQL*Net message from client WAITING
★クライアントからリクエスト待ち

2. [セッション1 / アプリケーションユーザ] EMP表のEMPNO=7369の表をUPDATE（COMMITしない）
SQL> update emp set sal = 9999 where empno = 7369;

1 row updated.

3. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');

4. [セッション2 / アプリケーションユーザ] EMP表のEMPNO=7369の表をUPDATE（COMMITしない）
SQL> update SCOTT.emp set sal = 9999 where empno = 7369;
★応答なし

5. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');

SID USERNAME EVENT STATE
---------- -------------------- ---------------------------------------- ---------------
15 HR enq: TX - row lock contention WAITING
275 SCOTT SQL*Net message from client WAITING
★HRが「enq: TX - row lock contention」イベントで行ロック取得を待機している

6. [セッション1 / アプリケーションユーザ] UPDATE後のコミット
SQL> commit;

Commit complete.

7. [セッション3 / 管理者ユーザ] 現在発生している待機イベントを確認
SQL> select sid, username, event, state from v$session where username in('SCOTT', 'HR');

■参考資料
Oracle待機イベント
待機イベントの説明
Oracleの現場を効率化する100の技
オラクルマスター教科書 Gold DBA Oracle Database AdministrationⅡ
オラクルマスター教科書　ORACLE MASTER Expert パフォーマンス・チューニング編
データベースの限界性能を引き出す技術
新・門外不出のOracle現場ワザエキスパートが明かす運用・管理の極意

■おわりに
待機イベントは「待機」というキーワードからネガティブなイメージが持たれやすいですが、SQL文を処理する都合上、必要な待機時間、無視できる待機時間もあります。（もちろん無駄な待機時間もありますが）
無駄な待機時間から待機時間（待機イベント）の原因を特定し、チューニング（SQL文のロジック修正やハードウェアのスケールアップなど）してくのが基本的な対処方法になります。

2023-12-16

OCJP Bronze SE / Silver SE11 受験記

資格試験

この記事は、JPOUG Advent Calendar 2023 16日目の記事です。
15日目は wmo6hash さんの記事「働き方の新しいスタイル」への仕事道具最適化'23 - wmo6hash::blog でした。

去年はOracle Databaseの従来型監査の検証について書きましたが、今年はOCJPを受験したため、受験した感想や学習で躓いたポイントを書こうと思います。
これから受験を考えている方の何かの参考になれば幸いです。

OCJPとは？
OCJP（Oracle Certified Java Programmer：Oracle認定Java プログラマ）は日本オラクル社が主催するJava言語に関する知識を問う試験です。
Bronze（未経験者向け）/ Silver（開発初心者向け）/ Gold（開発中上級者向け）の3段階の難易度の試験が用意されています。
各試験の試験時間や出題範囲、受験料など詳細は日本オラクル社の公式サイトをご参照ください。
（参考）Java SE 11 認定資格 | オラクル認定資格制度 | Oracle University
※参考リンクはSE11の情報です。本記事の執筆中に知ったのですが、2023年12月1日からSE17のSilverの試験がリリースされていました。
（参考）新着ニュース | Oracle University
　私が勉強を始めた当時はSE11が最新でした。
※元々はSCJP（Sun Certified Java Programmer：Sun認定Java プログラマ）としてサン・マイクロシステムズ社が主催する試験でしたが、2010年1月の買収に伴い、オラクル社が試験の運営を引き継いでいます。

プログラマー経歴
私は元々アプリケーション側のエンジニアで、プログラマーとしての経験は以下のとおりです。
・C言語（約4年）
・Java言語（約3年）
現在はOracle DBA業務が中心のため、もう6～7年はプログラムから離れています。（たまにVBAでツール作っている程度です）

なぜ受験したのか？
「Oracle DBA業務が中心なのになぜ今ごろJavaの試験を受けたのか？」とツッコミがありそうですが、私が受験した主な理由です。
・元プログラマーとして、資格試験を通じてプログラムの知識を体系的に復習・整理するため
・アプリケーション（Java）のエラーが発生した際、エラー内容が理解できればエラーの発生起因がアプリケーション側なのか基盤側なのか切り分けやすくなると思ったため
・（Goldの範囲ですが）スレッドプログラミングやJDBCの理解を深めるため（特にOLTPではこの辺の知識もあると強みになると思います）
・Goldは会社から報奨金が出るため（これが一番の理由かもしれません（笑））
　→SilverはGold受験の前提資格となるため、先ずSilverまで受験しました。

学習時間
纏まった時間はあまり取れないため、Bronze、Silverともに2ヶ月ぐらい掛かりました。
平日は通勤時間と夜に少々、休日は空いた時間で勉強する程度でした。

学習方法
Bronze、Silverともに紫本と黒本を使用しました。

紫本
・オラクル認定資格教科書 Java プログラマ Bronze SE（試験番号1Z0-818）
・オラクル認定資格教科書 Java プログラマ Silver SE11（試験番号1Z0-815）
黒本
・徹底攻略Java SE Bronze問題集［1Z0-818］対応
・徹底攻略Java SE 11 Silver問題集［1Z0-815］対応
現役の方は黒本だけでも十分と巷では言われていますが、私はJava（というかプログラム）から離れてブランクがあるため、紫本からやり直しました。

Bronze、Silverともに最初の1ヶ月間は紫本を1周しました。（Javaを思い出すことが目的のため、章末問題、模擬問題はやりませんでした）
残りの1ヶ月間で黒本（模擬試験含む）を3周しました。
3週目でもスムーズに解答できなかった問題には付箋を貼っておき、試験直前まで付箋が貼られた問題を繰り返し解いていました。
また、自宅の検証用PCにJavaをインストールし、実機検証しながら理解を深めていました。
実際の動きを確認しながら理解を深めることができるため、実機検証は有効な手段だと思います。
※JavaはSE21が最新（2023年12月16日時点）ですが、SE11対応の試験ということでSE11をインストールしました。
（参考）Oracle Java SE Supportロードマップ | Oracle 日本

試験結果
Bronzeは95%、Silverは85%で無事合格できました。（Bronzeは60%、Silverは63%が合格ライン）
試験問題については言及できませんが、黒本の類似問題が体感5割ぐらい出題されたと思います。

躓いたポイント
黒本の問題で個人的に躓いたポイントをいくつか紹介します。
全部を紹介するとキリがないため、特に覚えられなかったもの（思い出せなかったもの）だけ紹介します。

【Bronze編】
ポリモーフィズム（多態性、多相性）
Javaに限らず、オブジェクト指向プログラミングでは一度は目にするキーワードですね。
サブクラス型のオブジェクトをスーパークラス型で扱い、オブジェクトごとで異なる動作を実現する仕組みです。（スーパークラスの特定のメソッドをサブクラスでオーバーライドするため、is-a関係が成り立つことが前提）
…ということを忘れていました。
たとえば下記のようなコードがあったとします。

実行結果

FugaクラスもPiyoクラスもHogeインタフェースとis-a関係にあるため、ポリモーフィズムが実現可能です。
hoges[0]（Fugaインスタンス）のfunc()メソッドは Merry christmas! 2023 を出力します。
hoges[1]（Piyoインスタンス）のfunc()メソッドはFugaクラスのfunc()メソッドをオーバーライドしているため、Happy new year! 2024 を出力します。
クラス図は下記のようになります。表現が正しいか不明ですが個人の理解で書いたイメージ図も載せます。

配列の変数宣言
C言語の配列の変数宣言の記憶とごちゃ混ぜになり、なかなか覚えられませんでした。
たとえば下記のうち、配列の宣言、配列インスタンスの生成方法として正しいものはどれでしょうか？

答え
3、5、7

C言語は変数宣言時に要素数を指定しますが、Java言語は変数宣言時に要素数は指定できないなど結構混乱しました。

【Silver編】
switch文の条件式にnullが設定されているString型オブジェクトを指定
バージョン7からswitch文の条件式にString型オブジェクトも指定できるようになったそうです。
私が触っていた当時は確かバージョン5ぐらいで馴染みがないため、なかなか覚えられませんでした。
たとえば下記のようなコードがあったとします。

実行結果

msgはnullが入っているため、default句でmsgに"def"が設定されると予想していました。
switch文の条件式にString型オブジェクトを指定した場合、String型オブジェクトのhashCode()メソッドを実行し、ハッシュコードで分岐する動きになります。
つまり、ここではmsgはnullが入っているため、「null.hashCode()」となり、「NullPointerException」が発生します。
※msgはフィールド変数のため、値を指定しない場合、デフォルト値（null）で初期化されます。

1次元配列と2次元配列のclone()
同じclone()でも1次元配列と2次元配列で動きが異なります。（シャローコピーとディープコピー）
当時もここは躓いていたと思います。

◆1次元配列のclone()
たとえば下記のようなコードがあったとします。

実行結果

検証結果から1次元配列の要素（値）をコピーしていることが分かります。（arr2の1次元目の要素を変えてもコピー元の値は変わりません）
なお、検証では要素がプリミティブ型、参照型どちらも同じ動きをしました。

arr1、arr2で別々の参照を持つのか裏を取るため、配列インスタンスのハッシュコードも表示してみました。

arr1、arr2でそれぞれハッシュコードが異なる（別々の参照を持っている配列インスタンスである）ことが分かります。

◆2次元配列のclone()
たとえば下記のようなコードがあったとします。

実行結果

検証結果から1次元配列の要素（参照）をコピーしていることが分かります。（arr2の2次元目の要素を変えるとコピー元の値も変わります）
なお、検証では要素がプリミティブ型、参照型どちらも同じ動きをしました。

こちらも配列インスタンスのハッシュコードも表示してみました。

arr1、arr2でそれぞれハッシュコードが異なる（別々の参照を持っている2次元配列インスタンスである）ことが分かります。
しかし、arr1[0]とarr2[0]、arr1[1]とarr2[1]はそれぞれハッシュコードが同じ（同じ参照を持っている1次元配列インスタンスである）ことが分かります。
なお、arr3はarr1およびarr2の1次元配列インスタンスとハッシュコードが異なる（別々の参照を持っている1次元配列インスタンスである）ため、arr2の2次元目の要素を変えてもarr3の要素は変わりません。

具体例を書く時間が取れなかったのですが、他にもラムダ式やモジュール・システムなどの新機能も馴染みが無いため、なかなか覚えられず苦労しました。

引っ掛かったポイント
黒本の問題で個人的に引っ掛かったポイントをいくつか紹介します。
こちらも全部を紹介するとキリがないため、特に引っ掛かったものだけ紹介します。

【Bronze編】
if文に中括弧がない
たとえば下記のようなコードがあったとします。

実行結果

良く見るとif文に中括弧がありません。
msgがnullのため、if文には入りません。
if文の中括弧を省略した場合、true時の処理として最初の1文だけ実行されます。（true/falseに関わらず、7行目の"Hello, Java!"は出力されます）

switch文のcase句にbreakがない
たとえば下記のようなコードがあったとします。

実行結果

良く見ると「case 'b':」にbreakがありません。
breakがないと次のcase句の処理も実行されます。

【Silver編】
初期化されていない変数の使用
たとえば下記のようなコードがあったとします。

実行結果

5行目のif文がfalseだった場合、iが不定値の状態で11行目で出力されてしまうため、コンパイルでエラーになります。

上記のように注意して読まないと見落としてしまうようなものが他にもありました。

受験する際の注意事項
・試験予約は早めにした方が良い（特にSilver）
受験日の1か月前にSilverの試験予約をしようとしたのですが、最寄りのテストセンターは空いていませんでした。（最短で1.5か月先とか）
Silverは試験時間が180分あるため、180分の席を確保できる日があまり無かったのではと思います。
受験日を先延ばししたくないという方には、あらかじめ試験予約だけしておくことをお勧めします。
・トイレは済ませておく
試験中も退室できますが、試験時間は止まりません。
・計算用紙（ホワイトボード）はなるべく余白を残しながら使う
計算用紙としてホワイトボードを渡されますが、書いたら消せません。
ホワイトボードは交換してもらえますが、待っている間は手元に計算用紙がない状態になってしまいます。
・Bronzeは試験時間が鬼門
Bronzeは試験時間65分に対し、試験問題60問です。（1問につき約1分で解くペースになる）
・Silverも試験時間が鬼門
Silverは試験時間180分に対し、試験問題80問です。（1問につき約2分半で解くペースになる）
Bronzeに比べ、時間的に余裕ありそうと錯覚してしまいますが、見直しを含めると私は余裕が無かったです。
・分からない問題は消去法
試験時間には限りがあります。時間を掛け過ぎる前に分からない問題は消去法で選択肢を絞る方法も有効だと思います。

参考資料
徹底攻略Java SE Bronze問題集［1Z0-818］対応
徹底攻略Java SE 11 Silver問題集［1Z0-815］対応

さいごに
今年は業務ではAWRレポート（Oracle Databaseの性能レポート）を読んで性能分析する機会も多い年でした。
読めば読むほど知らないことが多いという現実を突きつけられるため、とても勉強になる年でもありました。
来年はOCJPのGold取得やOGG（Oracle GoldenGate）、RAT（Real Application Testing）にも手を出せたらいいなと思います。
最後までお読みいただきありがとうございました。
17日目の記事もお楽しみに！

忘れかけのIT備忘録

今まで学んできた知識や小技、なるほど！と思ったことをメモするブログです。

UNDO表領域の拡張エラー（ORA-30036）を検証する

Oracleのエクステント確保時の動作

SELECTなのに更新処理？！～遅延ブロッククリーンアウトの動きに迫る～

OracleのLifetime Support（ライフタイム・サポート・ポリシー）

Oracleのパッチ

Oracleの待機イベント

OCJP Bronze SE / Silver SE11 受験記