2023-01-24

Oracleの統計情報

今回はOracleの統計情報について検証しました。

統計情報(オプティマイザ統計とも呼ばれます)は、SQLの処理(ワークロード)の見積もりに必要なデータ(サーバやOS、データベースの特性を数値化したもの)です。
オプティマイザが実行計画を作成/選択する際、アクセスパスのコストを見積もる元データ(判断材料)として使用されます。

統計情報は2種類あります。

オブジェクト統計
表統計(XXX_TAB_STATISTICS、XXX_TABLES)
・行数(NUM_ROWS)
・ブロック数(BLOCKS)
・行の平均の長さ(AVG_ROW_LEN)
※XXX_TABLESは統計情報の失効状態(STALE_STATS)は確認不可
※ブロック数はHWMまでのブロック数
列統計(XXX_TAB_COL_STATISTICS)
・列内の個別値(NDV)の数(NUM_DISTINCT)
・列内のNULL数(NUM_NULLS)
・ヒストグラム(HISTGRAM)
索引統計(XXX_IND_STATISTICS、XXX_INDEXES)
・索引の高さ(深さ)(BLEVEL)
・リーフブロック数(LEAF_BLOCKS)
・クラスタ化係数(CLUSTERING_FACTOR)
※XXX_INDEXESは統計情報の失効状態(STALE_STATS)は確認不可

【補足】
NDV(Number of Distinct Value)
列に存在する値の種類の数
たとえば性別列に「男性」、「女性」のデータが存在する場合、2(性別列に存在するデータは2種類)となる

ヒストグラム
表の列データの分布状況
セレクティビティ(行の選択率)のコストを見積もるために使用される統計情報
通常の統計情報に比べ、より最適な実行計画を作成/選択するのが目的
頻度分布ヒストグラム、高さ調整ヒストグラム、上位頻度ヒストグラム、ハイブリッドヒストグラムがある
詳細は別記事で書きます
※セレクティビティ：行が選択される割合。全表スキャンか索引スキャンかの判断指標になる
※上位頻度ヒストグラム、ハイブリッドヒストグラムは12c以降の機能

クラスタ化係数
索引の列データと表の列データの分布度(データの格納順がどれくらい異なるかの指標)
索引の列データに格納されているデータと実際の表の列データに格納されているデータの順番がどれくらい揃っているかを表す
データの格納順がだいたい揃っている(一致している)場合、クラスタ化係数は小さくなる
データの格納順がほとんど揃っていない場合、クラスタ化係数は大きくなる
クラスタ化係数の値によってデータアクセスのオペレーションが変わってくる(クラスタ化係数が小さい場合、TABLE ACCESS BY INDEX ROWIDが採用されやすくなる)
詳細は別記事で書きます

システム統計
ハードウェアやOSのリソースI/O、使用率など(AUX_STATS$)
・CPUSPEEDNW(CPU速度(MHz))
・IOSEEKTIM(ディスクのシーク時間・回転待ち時間・OSオーバーヘッドの合計(ミリ秒))
・IOTFRSPEED(1ブロックの平均転送速度)

統計情報の収集方法は2種類あります。

・自動(自動メンテナンスタスク、動的サンプリング)
・手動(DBMS_STATSパッケージ、Enterprise Manager)

【補足】
自動メンテナンスタスク
データベースのメンテナンスのため、定期的に自動で実行されるタスク(ジョブ)
データベース作成時に自動的に作成される
自動メンテナンスタスクのうち、自動オプティマイザ統計収集ジョブが統計情報を収集する
※システム統計は収集されないため、手動で収集する必要がある
※自動メンテナンスタスクを使用する場合、STATISTICS_LEVEL初期化パラメータをTYPICALまたはALLに設定する
※自動メンテナンスタスクには自動セグメントアドバイザや自動SQLチューニングアドバイザ、SQL計画管理(SPM)展開アドバイザ(12c～)もある

収集時間帯(デフォルト)
・月～金　22:00～02:00
・土、日　06:00～20時間後に終了

収集対象
・オブジェクト統計が存在しないオブジェクト
・更新(表全体に対し、10%以上の変更)があったオブジェクト
・オブジェクト統計が古いオブジェクト

動的サンプリング
12c以降は動的統計と呼ばれている
オプティマイザが実行計画を作成する際、統計情報の欠落など統計情報が最新化されていない場合、自動的に統計情報を収集する
SQLを実行した際、何かしらの理由で統計情報が取得されていない場合、統計情報を取得してSQLの処理を継続するために動的サンプリングは実装されている
動的サンプリングを使用する場合、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータを設定する(0だと無効)

今回は手動による統計情報の収集とリストアを検証してみます。
Enterprise Manager環境が無いため、DBMS_STATSパッケージを使用します。

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

■前提
・非CDB
・索引の統計情報も自動収集する
select DBMS_STATS.GET_PREFS(pname => 'CASCADE') as cascade from dual;

CASCADE
------------------------------
DBMS_STATS.AUTO_CASCADE

■検証パターン
①統計情報取得＆リストア(データベース単位)
②統計情報取得＆リストア(ディクショナリ単位)
③統計情報取得＆リストア(固定表(動的パフォーマンス表)単位)
④統計情報取得＆リストア(スキーマ単位)
⑤統計情報取得＆リストア(テーブル単位)
⑥統計情報取得＆リストア(インデックス単位)
⑦統計情報取得＆リストア(システム統計単位)

■検証
①統計情報取得＆リストア(データベース単位)
オブジェクト統計のうち、データベース単位で統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

1. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');

SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');

OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT
SCOTT PK_EMP EMP

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_DATABASE_STATS();
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

3. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 13:18:39 NO
SCOTT DEPT 4 5 20 2023-01-23 13:18:48 NO
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
SCOTT SALGRADE 5 5 10 2023-01-23 13:19:27 NO

4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 13:27:26 NO
SCOTT DEPT 4 5 20 2023-01-23 13:27:34 NO
SCOTT EMP 14 5 38 2023-01-23 13:27:36 NO
SCOTT SALGRADE 5 5 10 2023-01-23 13:28:11 NO

SQL> BEGIN
2 DBMS_STATS.RESTORE_DATABASE_STATS(as_of_timestamp => TO_DATE('2023-01-23 13:19:28', 'YYYY-MM-DD HH24:MI:SS'));
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。
★リストア日時は統計情報取得日時+1した値を指定します（取得時刻が2023-01-23 13:19:27の場合、2023-01-23 13:19:28にする）

5. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');

②統計情報取得＆リストア(ディクショナリ単位)
オブジェクト統計のうち、ディクショナリ単位で統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

1. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT') AND TABLE_NAME NOT LIKE 'X$%';

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
HISTGRM$ 47905 1374 34 2023-01-23 13:18:51 YES

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_DICTIONARY_STATS();
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

3. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT') AND TABLE_NAME NOT LIKE 'X$%';

4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT');

SQL> BEGIN
2 DBMS_STATS.RESTORE_DICTIONARY_STATS(as_of_timestamp => TO_DATE('2023-01-23 13:45:19', 'YYYY-MM-DD HH24:MI:SS'));
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

5. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT') AND TABLE_NAME NOT LIKE 'X$%';

③統計情報取得＆リストア(固定表(動的パフォーマンス表)単位)
オブジェクト統計のうち、固定表(動的パフォーマンス表)単位で統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

1. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
X$DIAG_INFO 11 63 2023-01-03 22:02:12

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

3. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';

4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';

SQL> BEGIN
2 DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(as_of_timestamp => TO_DATE('2023-01-23 14:30:33', 'YYYY-MM-DD HH24:MI:SS'));
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

5. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';

④統計情報取得＆リストア(スキーマ単位)
オブジェクト統計のうち、スキーマ単位で統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT');
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 14:50:17 NO
SCOTT DEPT 4 5 20 2023-01-23 14:50:17 NO
SCOTT EMP 14 5 38 2023-01-23 14:50:17 NO
SCOTT SALGRADE 5 5 10 2023-01-23 14:50:17 NO

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 14:58:28 NO
SCOTT DEPT 4 5 20 2023-01-23 14:58:28 NO
SCOTT EMP 14 5 38 2023-01-23 14:58:28 NO
SCOTT SALGRADE 5 5 10 2023-01-23 14:58:28 NO

SQL> BEGIN
2 DBMS_STATS.RESTORE_SCHEMA_STATS(ownname => 'SCOTT',
3 as_of_timestamp => TO_DATE('2023-01-23 14:50:18', 'YYYY-MM-DD HH24:MI:SS'));
4 END;
5 /

PL/SQLプロシージャが正常に完了しました。

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 14:50:17 NO
SCOTT DEPT 4 5 20 2023-01-23 14:50:17 NO
SCOTT EMP 14 5 38 2023-01-23 14:50:17 NO
SCOTT SALGRADE 5 5 10 2023-01-23 14:50:17 NO

⑤統計情報取得＆リストア(テーブル単位)
オブジェクト統計のうち、テーブル単位で統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

1. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP');
4 END;
5 /

PL/SQLプロシージャが正常に完了しました。

3. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 15:12:51 NO
★統計情報が取得された

SQL> BEGIN
2 DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP',
4 as_of_timestamp => TO_DATE('2023-01-23 15:12:52', 'YYYY-MM-DD HH24:MI:SS'));
5 END;
6 /

PL/SQLプロシージャが正常に完了しました。

5. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 15:12:51 NO
★統計情報がリストアされた

⑥統計情報取得＆リストア(インデックス単位)
オブジェクト統計のうち、インデックス単位で統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

1. 統計情報取得状態確認
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
3 indname => 'PK_EMP');
4 END;
5 /

PL/SQLプロシージャが正常に完了しました。

3. 統計情報取得状態確認
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

4. 統計情報リストア
★インデックスの統計情報のリストア関数が無いため確認不可

⑦統計情報取得＆リストア(システム統計単位)
システム統計の統計情報を取得＆リストアできるか検証します

【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認

【作業ログ】

1. 統計情報取得状態確認
SQL> SELECT * FROM AUX_STATS$;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 07-07-2014 06:53
SYSSTATS_INFO DSTOP 07-07-2014 06:53
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 3308.9701
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_SYSTEM_STATS();
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

3. 統計情報取得状態確認
SQL> SELECT * FROM AUX_STATS$;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-23-2023 15:42
SYSSTATS_INFO DSTOP 01-23-2023 15:42
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1477
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
★統計情報が取得された

4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT * FROM AUX_STATS$;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-23-2023 15:46
SYSSTATS_INFO DSTOP 01-23-2023 15:46
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1477
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR

SQL> BEGIN
2 DBMS_STATS.RESTORE_SYSTEM_STATS(as_of_timestamp => TO_DATE('23-01-23 15:42:55', 'YY-MM-DD HH24:MI:SS'));
3 END;
4 /

PL/SQLプロシージャが正常に完了しました。

5. 統計情報取得状態確認
SQL> SELECT * FROM AUX_STATS$;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-23-2023 15:51
SYSSTATS_INFO DSTOP 01-23-2023 15:51
SYSSTATS_INFO FLAGS 128
SYSSTATS_MAIN CPUSPEEDNW 1477
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
★リストアは正常終了したが、統計情報取得日時は指定した時刻に戻らなかった(リストアを実施した日時になった)

■参考資料
DBMS_STATS
自動データベース・メンテナンス・タスクの管理
https://www.oracle.com/technetwork/jp/ondemand/branch/120328-cbo-1593994-ja.pdf
パフォーマンス改善と事前対策に役立つ Oracle SQLチューニング本気で学ぶ実践的な考え方とテクニック

■おわりに
RESTORE_XXX_STATS()関数に指定するリストア日時に「統計情報を取得した日時」を指定した場合、NUM_ROWS列などの統計情報がNULLになってしまいました。
リストア日時には統計情報を取得した日時より、1大きい値を指定する必要がありそうでした(秒なら1秒進める、分なら1分進める、時間なら1時間進める)

SQL> --統計情報取得
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

SQL> --リストア日時に「統計情報を取得した日時」を指定してリストア
SQL> BEGIN
2 DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP',
4 as_of_timestamp => TO_DATE('2023-01-23 13:18:50', 'YYYY-MM-DD HH24:MI:SS'));
5 END;
6 /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

SQL> --リストア日時に「統計情報を取得した日時+1」を指定してリストア
SQL> BEGIN
2 DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP',
4 as_of_timestamp => TO_DATE('2023-01-23 13:18:51', 'YYYY-MM-DD HH24:MI:SS'));
5 END;
6 /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';

OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
★「統計情報を取得した日時」の統計情報が入った(リストアされた)

なお、リストアに使用する統計情報はデフォルトで31日間保存されます。
統計情報は他にも保留(取得した統計情報をオプティマイザに公開しない)、エクスポート/インポート(EXPORT_XXX_STATS/IMPORT_XXX_STATS)、ロック(統計情報を変更させない)などができます。

2023-01-07

sqlplusの/nologオプション

Oracle Oracle-ユーティリティ

今さらですがsqlplusで/nologオプションを指定するメリットを知ったので軽く検証しました。

/nologオプション
SQL*Plusを起動する際、データベースに接続しない

メリット
一例として、データベースに接続せずにSQL*Plusを起動するため、UNIX系OSでpsやhistoryを実行した場合、DBユーザ名やパスワードが表示されない
製品バージョンによるかもしれませんが、SQL*Plus起動時にDBユーザ名やパスワードを指定した状態でダイレクトにDB接続すると、DBユーザやパスワードがpsで見れてしまうケースがあるそうです。(sqlplusプロセスの引数の情報もそのまま表示してしまうため)
historyにもコマンド履歴として残ります。
セキュリティ観点からパスワードが見れる(残ってしまう)のは好ましくありません。
SQL*Plus起動時に/nologオプションを指定して(sqlplus /nolog)、SQL*Plus起動後にDB接続(connect)すれば、psやhistoryでも「sqlplus /nolog」という情報しか残らなくなるため、パスワードが見れる(残る)という事象を防げます。

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

■前提
・非CDB

■検証パターン
①/nologオプションなしでDB接続
②/nologオプションありでDB接続

■検証
①/nologオプションなしでDB接続
/nologオプションを指定せず、SQL*Plusを起動してDB接続します
DB接続後、別セッションでpsコマンドでsqlplusプロセスの状態も確認します

【検証手順】
1. DBユーザおよびパスワードを指定してSQL*Plus起動(DB接続)
2. sqlplusプロセス確認

【作業ログ】

1. DBユーザおよびパスワードを指定してSQL*Plus起動(DB接続)
[oracle@node1 ~]$ sqlplus system/password
SQL> show user
ユーザーは"SYSTEM"です。
SQL> set lines 200 pages 3000
SQL> set tab off
SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
orcl1 OPEN

2. sqlplusプロセス状態確認
SQL> !ps -ef | grep sqlplus | grep -v grep
oracle 4514 4425 0 12:44 pts/0 00:00:00 sqlplus
★psではDBユーザ名とパスワードは見えなかった
　予想だがsqlplusプロセス名の右に15文字分の半角スペースがあるので、「system/password」という文字列をマスキングしているのかもしれない

[oracle@node1 ~]$ history | grep sqlplus | grep -v grep
1002 sqlplus system/password
★historyにはDBユーザ名とパスワードが残っている

②/nologオプションありでDB接続
/nologオプションを指定して、SQL*Plusを起動してDB接続します
SQL*Plus起動後・DB接続後、別セッションでpsコマンドでsqlplusプロセスの状態も確認します

【検証手順】
1. /nologを指定してSQL*Plus起動
2. sqlplusプロセス状態確認
3. DBユーザおよびパスワードを指定してDB接続
4. sqlplusプロセス状態確認

【作業ログ】

1. /nologを指定してSQL*Plus起動
[oracle@node1 ~]$ sqlplus /nolog
SQL> show user
ユーザーは""です。
SQL> select instance_name, status from v$instance;
SP2-0640: 接続されていません。

2. sqlplusプロセス状態確認
SQL> !ps -ef | grep sqlplus | grep -v grep
oracle 6857 4425 0 12:50 pts/0 00:00:00 sqlplus
★今度はsqlplusプロセス名の右に6文字分の半角スペースがあるので、「/nolog」という文字列をマスキングしているのかもしれない

[oracle@node1 ~]$ history | grep sqlplus | grep -v grep
1008 sqlplus /nolog
★historyには「sqlplus /nolog」という文字列だけ残っている

3. DBユーザおよびパスワードを指定してDB接続
[oracle@node1 ~]$ sqlplus /nolog
SQL> connect system/password
接続されました。
SQL> show user
ユーザーは"SYSTEM"です。
SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
orcl1 OPEN

4. sqlplusプロセス状態確認
SQL> !ps -ef | grep sqlplus | grep -v grep
oracle 7680 4425 0 12:53 pts/0 00:00:00 sqlplus
★psではDBユーザ名とパスワードは見えなかった
　今回もsqlplusプロセス名の右に6文字分の半角スペースがある

[oracle@node1 ~]$ history | grep sqlplus | grep -v grep
1011 sqlplus /nolog
★historyには「sqlplus /nolog」という文字列だけ残っている

■参考資料
プログラム引数のセキュリティについて

■おわりに
検証した製品のバージョンによるかもしれませんが、12cR1ではsqlplusでDBユーザ名とパスワードを指定しても、psではDBユーザ名とパスワードは表示されませんでした。(スペースに置き換わってるように見えました)
特定の製品バージョンからsqlplusの引数をマスキングするようになったのかもしれません。
ただ、historyには残ってしまうのでセキュリティのことを考慮するなら/nologオプションでSQL*Plus起動後、sqlplus内でDB接続した方が安全だと思います。

なお、rootユーザからoracleユーザへスイッチと同時にDB接続したら、その情報がpsでも見えました。

su - oracle -c "sqlplus system/password"

[root@node1 ~]# su - oracle -c "sqlplus system/password"
SQL> show user
ユーザーは"SYSTEM"です。
SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
orcl1 OPEN

SQL> !ps -ef | grep sqlplus | grep -v grep
root 8758 11549 0 12:55 pts/0 00:00:00 su - oracle -c sqlplus system/password
oracle 8759 8758 0 12:55 ? 00:00:00 sqlplus

[root@node1 ~]# history | grep sqlplus | grep -v grep
1025 su - oracle -c "sqlplus system/password"

2023-01-02

Oracleの読取り一貫性

Oracle Oracle-同時実行性

今回はOracleの読取り一貫性について調査しました。

読取り一貫性とは、特定のトランザクションがSELECT文(データ読込み)を開始した時点で確定しているデータを読み込ませる機能です。
いったんOracleのSELECT文(データ読込み)の動きを復習します。

SELECT中に別トランザクションでデータ更新された場合、このUNDOセグメントのデータを使用して、読取り一貫性を実現しています。

では、SELECT中にUNDOセグメントがいっぱいになって読込み開始時点のデータが上書きされてしまった場合、どうなってしまうのでしょうか？
結論から言うと「ORA-01555: スナップショットが古すぎます」が発生します。
読込み開始時点のUNDOデータが上書きされて、読取り一貫性を実現できなくなってしまったためです。

■参考資料
ORACLE MASTER Silver［Silver DBA11g］（試験番号：1Z0-052）完全詳解＋精選問題集

■おわりに
個人の感想ですが、データベースの運用担当をしていた頃は「ORA-01555」のエラーをよく見ました。
UNDO_RETENTION値を増やせばその分「ORA-01555」エラーが減るケースが多いと思いますが、UNDO表領域が物理的にサイズ不足になると今度は「ORA-30036: string(UNDO表領域'string'内)でセグメントを拡張できません」エラーが発生してしまいます。

2023-01-02

トランザクションのACID特性と分離レベルの概要

Oracle Oracle-同時実行性

今回はトランザクションのACID特性と分離レベルについて調査しました。

ACID特性
ACID特性とは、トランザクションで処理するデータの整合性を保つ(データの信頼性、整合性を保証する)ために必要な4つの特性です。
Atomicity(原子性)、Consistency(一貫性)、Isolation(分離性、独立性)、Durability(持続性、耐久性)の頭文字を取ってACID特性と呼ばれています。

Atomicity(原子性)
トランザクションは、そのすべてを反映させるか、そのすべてを取り消すかの状態であること（オールオアナッシング）
SQLではCOMMIT/ROLLBACKで実現

Consistency(一貫性)
トランザクションは、その前後でデータの整合性が保たれ、矛盾のない状態になっていること
OracleではロックやUNDOセグメントで実現（想定）

Isolation(分離性、独立性)
トランザクションでは、同時並行処理結果(Parallel)も逐次処理結果(Serial)も同じであること
トランザクション処理が外部から隠蔽されており、別のトランザクションの処理から影響を受けない／影響を与えない状態になっていること
OracleではロックやUNDOセグメントで実現（想定）

Durability(持続性、耐久性)
トランザクションは、処理が完了したら、その結果が障害などで影響を受けず、不変に保たれること
OracleではREDOログ（変更履歴）やアーカイブログで実現（想定）

トランザクション分離レベル（ANSI/ISO SQL規格）
複数のトランザクションの処理を同時に実行した際、各トランザクションの処理の整合性をどこまで保証するかトランザクション同士の影響度合を4段階で定義したものです。
ACIT特性のうち、Isolation(分離性、独立性)に該当します。
一般的に複数の処理を実行する際、並列度が高いほど処理は速くなります。
ただし、データベースのように共通のデータを複数のトランザクションが処理する場合、並列度を高くして処理を速めたからと言って処理対象のデータが必ずしも整合性がある(正しいデータの状態が保証されている)とは限りません。
・処理性能を上げる代わりにデータの整合性を下げるか
・データの整合性を上げる代わりに処理性能を下げるか
というようにトレードオフになっています。
トレードオフの関係(分離レベル)には4種類あります。（トランザクションの同時実行性を保証する）

READ UNCOMMITTED (非コミット読取り)
確定していないデータまで読み取る
他のトランザクションの処理による更新途中のデータまで読み取る
データの一貫性は低いが処理性能は高い

READ COMMITTED (コミット読取り)
確定した最新データを常に読み取る
他のトランザクションの処理は常にコミット済みのデータのみ読み取る

REPEATABLE READ (リピータブル・リード)
読み取り対象のデータを常に読み取る
特定のトランザクションの処理が実行中の間、読み取り対象のデータは読み取り途中で他のトランザクションによって変更されない
同じトランザクション中では同じデータは毎回同じ値を読むことができる

SERIALIZABLE (シリアライズ可能)
直列化可能
並列で実行している複数のトランザクションの処理の結果が、逐次実行した場合と同じ結果となる
データの処理性能は低いが整合性は高い

データリード現象
データの一貫性を下げる振る舞いとして、3種類のデータリード現象が定義されています。

ダーティリード (Dirty Read)
特定のトランザクションで変更したデータをCOMMITしていないにもかかわらず、別のトランザクションが読込めてしまう現象

ノンリピータブルリード (Non-repeatable Read) / ファジーリード (Fuzzy Read)
特定のトランザクション実行中に読み込んだデータが、別のトランザクションでデータ変更・COMMITしたことにより、1回目の読込みデータと2回目の読込みデータに差異が発生する現象

ファントムリード (Phantom Read)
特定のトランザクション実行中に読み込んだデータが、別のトランザクションでデータ挿入/削除・COMMITしたことにより、1回目の読込みデータと2回目の読込みデータに差異が発生する現象

分離レベルとリード現象の関係性をまとめました。

※Oracleの場合、READ COMMITTEDとSERIALIZABLEを提供しています

■分離レベルの設定
SET TRANSACTION句で設定できます

■参考資料
データの同時実行性と整合性
トランザクション分離レベル - Wikipedia
SET TRANSACTION

2023-01-01

Oracleのロック検証（デッドロック編）

Oracle Oracle-同時実行性

今回はOracleのデッドロックについて検証しました。

デッドロックとは、2つ以上のセッションのトランザクションがお互いにロック解除待ち状態に陥る状態です。
お互いのセッションのトランザクションが相手のロック解除を待っているため、いずれのセッションもトランザクションを終了できず、ロック競合を解消できなくなります。

デッドロックが発生した場合、デッドロックが検出されたセッション(ORA-00060が発生したセッション)のトランザクションが自動的にロールバックされます。
ロールバック後、トランザクション制御できるようになるため、そのトランザクション全体をコミットまたはロールバックしてトランザクションを終了し、ロック競合を解除します。

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

■前提
・非CDB

■検証パターン
①別々のセッションから同じリソースに対して更新

■検証
①別々のセッションから同じリソースに対して更新
セッション1、セッション2で同じ表の別々のレコードに対してロック取得後、セッション1からセッション2がロック取得中のレコードに対して更新、セッション2からセッション1がロック取得中のレコードに対して更新しようとしたときデッドロック検出されるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のレコードに対してロック取得
2. セッション2(HR)で同じ表の別レコードに対してロック取得
3. セッション1(SCOTT)からセッション2(HR)がロック取得中のレコードに対して更新
4. セッション2(HR)からセッション1(SCOTT)がロック取得中のレコードに対して更新

【作業ログ】

1. セッション1(SCOTT)で特定のレコードに対してロック取得
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10

14行が選択されました。

SQL> update emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

2. セッション2(HR)で同じ表の別レコードに対してロック取得
SQL> select * from scott.emp;

14行が選択されました。

SQL> update scott.emp set SAL = 2000 where EMPNO = 7521;

1行が更新されました。

3. セッション1(SCOTT)からセッション2(HR)がロック取得中のレコードに対して更新
SQL> update emp set SAL = 5000 where EMPNO = 7521;
★応答なし

update emp set SAL = 5000 where EMPNO = 7521
*
行1でエラーが発生しました。:
ORA-00060: リソース待機の間にデッドロックが検出されました。
★「4」実行後、数秒経ってからORA-00060発生

4. セッション2(HR)からセッション1(SCOTT)がロック取得中のレコードに対して更新
SQL> update scott.emp set SAL = 6000 where EMPNO = 7369;
★応答なし
セッション1(SCOTT)でトランザクション全体をコミットまたはロールバック後、制御が戻った(更新できた)

※デッドロック発生後のロック状態
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
16 31780 HR 372
372 65257 SCOTT

SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
372 TM RX/SX(行排他ロック) 2
16 TM RX/SX(行排他ロック) 2
16 TX X(排他ロック) 2
372 TX X(排他ロック) 1
16 TX NONE(ロックなし) 0

SQL> select o.owner, o.object_name, o.object_type, lo.SESSION_ID, decode(lo.LOCKED_MODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode from v$locked_object lo, dba_objects o where lo.OBJECT_ID = o.OBJECT_ID;

OWNER OBJECT_NAME OBJECT_TYPE SESSION_ID LCK_MODE
--------------- --------------- --------------- ---------- ------------------------------
SCOTT EMP TABLE 372 RX/SX(行排他ロック)
SCOTT EMP TABLE 16 RX/SX(行排他ロック)

【参考】

●DBアラートログ
Sun Jan 01 10:47:40 2023
Global Enqueue Services Deadlock detected (DID = 3_0_1). More information in file
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lmd0_5951.trc.
★デッドロックが検出されたノード(本検証ではノード1)のDBアラートログに出力された。ノード2のDBアラートログには何も出力されなかった

●トレースファイル
*** 2023-01-01 10:47:40.171
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1d0020][0x133],[TX][ext 0x0,0x2]
----------resource 0xa6636938----------------------
resname : [0x1d0020][0x133],[TX][ext 0x0,0x2]
lmdid : 0
rht group : 0
rht ptr : 0xa62830d8
rht bucket idx: 1860
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 39
hv last r.inc : 3
current inc : 3
hv status : 0
hv master inst: 1
open options : deadlock detection=Y, cached=N, varvblk=N, slock=N
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x0300000000000000419bbd6000000000 .A`
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xa6636938
entry : DIR=Y, MASTER=Y
On Scan_q? : N
On Cache? : N
On Remote_q? : N
history : x11
frozen : 0
Total accesses: 29
Imm. accesses: 16
Granted_locks : 1
Cvting_locks : 1
value_block: 03 00 00 00 00 00 00 00 41 9b bd 60 00 00 00 00
GRANTED_Q:
lp 0xa52578c8 gl KJUSEREX rp 0xa6636938 [0x1d0020][0x133],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9c0e6e8 possible pid 19700 xid 38000-0001-0000001D rseq 1 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
open opt KJUSERDEADLOCK sec since grant 10
CONVERT_Q:
lp 0xa5257e98 gl KJUSERNL rl KJUSEREX rp 0xa6636938 [0x1d0020][0x133],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9ee04e8 possible pid 19535 xid 33000-0001-7FF300000025 rseq 1 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
----------enqueue 0xa52578c8------------------------
lock version : 2073
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6636938
procp : 0xa76d49a8
pid : 19535
proc version : 19
oprocp : (nil)
opid : 19535
group lock owner : 0xa9c0e6e8
possible pid : 19700
xid : 38000-0001-0000001D
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since grant : 10
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 1
valblk : 0x0008000000000000a0cd8c56f37f0000 .V
user session for deadlock lock 0xa52578c8
sid: 16 ser: 31780 audsid: 2930374 user: 102/HR
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 56 O/S info: user: oracle, term: UNKNOWN, ospid: 19700
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/1, ospid: 19699
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update scott.emp set SAL = 6000 where EMPNO = 7369
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[56.19700] on resource TX-001D0020-00000133-00000000-00000002
2023-01-01 10:47:40.183766 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xa5257e98------------------------
lock version : 1337
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6636938
procp : 0xa76d49a8
pid : 19535
proc version : 19
oprocp : (nil)
opid : 19535
group lock owner : 0xa9ee04e8
possible pid : 19535
xid : 33000-0001-00000025
dd_time : 10.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
sec since grant : N/A
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
Msg_Seq : 0x0
res_seq : 1
valblk : 0x17000000000000000000000000000000 .
user session for deadlock lock 0xa5257e98
sid: 372 ser: 65257 audsid: 2930373 user: 110/SCOTT
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 51 O/S info: user: oracle, term: UNKNOWN, ospid: 19535
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/0, ospid: 19534
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update emp set SAL = 5000 where EMPNO = 7521　★デッドロックの原因となったSQL
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[51.19535] on resource TX-001D0020-00000133-00000000-00000002
2023-01-01 10:47:40.185572 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1b0012][0x147],[TX][ext 0x0,0x2]
----------resource 0xa6a6e638----------------------
resname : [0x1b0012][0x147],[TX][ext 0x0,0x2]
lmdid : 1
rht group : 0
rht ptr : 0xa62832b0
rht bucket idx: 717
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 45
hv last r.inc : 3
current inc : 3
hv status : 0
hv master inst: 1
open options : deadlock detection=Y, cached=N, varvblk=N, slock=N
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x01000000000000006f8025a500000000 .o%
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xa6a6e638
entry : DIR=Y, MASTER=Y
On Scan_q? : N
On Cache? : N
On Remote_q? : N
history : x11
frozen : 0
Total accesses: 44
Imm. accesses: 31
Granted_locks : 1
Cvting_locks : 1
value_block: 01 00 00 00 00 00 00 00 6f 80 25 a5 00 00 00 00
GRANTED_Q:
lp 0xa5095dd8 gl KJUSEREX rp 0xa6a6e638 [0x1b0012][0x147],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9ee04e8 possible pid 19535 xid 33000-0001-00000025 rseq 5 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
open opt KJUSERDEADLOCK sec since grant 6
CONVERT_Q:
lp 0xa50963a8 gl KJUSERNL rl KJUSEREX rp 0xa6a6e638 [0x1b0012][0x147],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9c0e6e8 possible pid 19700 xid 38000-0001-7FF30000001D rseq 5 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
----------enqueue 0xa5095dd8------------------------
lock version : 991
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6a6e638
procp : 0xa76dad80
pid : 19700
proc version : 11
oprocp : (nil)
opid : 19700
group lock owner : 0xa9ee04e8
possible pid : 19535
xid : 33000-0001-00000025
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since grant : 6
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 5
valblk : 0x01000000000000009c91260000000000 .&
user session for deadlock lock 0xa5095dd8
sid: 372 ser: 65257 audsid: 2930373 user: 110/SCOTT
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 51 O/S info: user: oracle, term: UNKNOWN, ospid: 19535
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/0, ospid: 19534
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update emp set SAL = 5000 where EMPNO = 7521　★デッドロックの原因となったSQL
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[51.19535] on resource TX-001B0012-00000147-00000000-00000002
2023-01-01 10:47:40.186533 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xa50963a8------------------------
lock version : 645
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6a6e638
procp : 0xa76dad80
pid : 19700
proc version : 11
oprocp : (nil)
opid : 19700
group lock owner : 0xa9c0e6e8
possible pid : 19700
xid : 38000-0001-0000001D
dd_time : 4.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
sec since grant : N/A
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
Msg_Seq : 0x0
res_seq : 5
valblk : 0x17000000000000000000000000000000 .
user session for deadlock lock 0xa50963a8
sid: 16 ser: 31780 audsid: 2930374 user: 102/HR
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 56 O/S info: user: oracle, term: UNKNOWN, ospid: 19700
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/1, ospid: 19699
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update scott.emp set SAL = 6000 where EMPNO = 7369
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[56.19700] on resource TX-001B0012-00000147-00000000-00000002
2023-01-01 10:47:40.186987 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------

========================================================================
Global Wait-For-Graph(WFG) for GES Deadlock ID=[3_0_1]
------------------------------------------------------------------------
Victim : (instance=1, lock=0xa5257e98)
Start (master) Instance : 1
Number of Locks involved : 4
Number of Sessions involved : 2

User session identified by:
{
User Name : oracle
User Machine : node1.oracle12c.jp
OS Terminal Name : pts/0
OS Process ID : 19534
OS Program Name : sqlplus@node1.oracle12c.jp (TNS V1-V3)
Application Name : SQL*Plusnode1.oracle12c.jp (TNS V1-V3)
Action Name : AQ LB CoordinatorCLeaNupholdst
Current SQL : update emp set SAL = 5000 where EMPNO = 7521　★デッドロックの原因となったSQL
Session Number : 372
Session Serial Number : 65257
Server Process ORAPID : 51
Server Process OSPID : 19535
Instance : 1
}
waiting for Lock 0xa5257e98 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1d0020.0x133(ext 0x0,0x2)
GES Transaction ID : 33000-0001-00000025
}
which is blocked by Lock 0xa52578c8 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1d0020.0x133(ext 0x0,0x2)
GES Transaction ID : 38000-0001-0000001D
}
owned by the
User session identified by:
{
User Name : oracle
User Machine : node1.oracle12c.jp
OS Terminal Name : pts/1
OS Process ID : 19699
OS Program Name : sqlplus@node1.oracle12c.jp (TNS V1-V3)
Application Name : SQL*Plusnode1.oracle12c.jp (TNS V1-V3)
Action Name : KDILM background CLeaNupholds
Current SQL : update scott.emp set SAL = 6000 where EMPNO = 7369
Session Number : 16
Session Serial Number : 31780
Server Process ORAPID : 56
Server Process OSPID : 19700
Instance : 1
}
waiting for Lock 0xa50963a8 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1b0012.0x147(ext 0x0,0x2)
GES Transaction ID : 38000-0001-0000001D
}
which is blocked by Lock 0xa5095dd8 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1b0012.0x147(ext 0x0,0x2)
GES Transaction ID : 33000-0001-00000025
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
End of Global WFG for GES Deadlock ID=[3_0_1]
========================================================================

kjddprg: Transaction Deadlock added to the fixed table.

*** 2023-01-01 10:47:40.976
* Cancel deadlock victim lockp 0xa5257e98
Global blockers dump start:---------------------------------

■ロック解除方法
トランザクション制御を戻すためのロック解除方法は主に2種類あります

①トランザクションのコミットまたはロールバック
②セッション切断

①トランザクションのコミットまたはロールバック
--1.セッション1(SCOTT)でデータ更新
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

--2.セッション2(HR)でデータ更新
SQL> update scott.emp set SAL = 2000 where EMPNO = 7369;
★応答なし
セッション1(SCOTT)でトランザクション全体をコミットまたはロールバック後、制御が戻った(更新できた)

②セッション切断
--1.セッション1(SCOTT)でデータ更新
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。
★「3」でセッション切断後、exitでDBログアウト時に下記メッセージが出力されました
SQL> exit
ERROR:
ORA-00028: セッションは強制終了されました。

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options (障害を含んでいます。)との接続が切断されました。

--2.セッション2(HR)でデータ更新
SQL> update scott.emp set SAL = 2000 where EMPNO = 7369;
★応答なし

--3.別セッションでセッション切断
--セッションID、シリアルナンバー確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
24 16459 HR 251
251 9114 SCOTT

--ロック解除待ちの原因となっているセッションを切断
SQL> alter system kill session '251, 9114';

システムが変更されました。

SQL> --セッション切断後
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
24 16459 HR
251 9114 SCOTT

2022-12-31

Oracleのロック検証（TXロック、TMロック編）

Oracle Oracle-同時実行性

今回はOracleのロックについて検証しました。

ロックとは、複数のセッションでデータを同時に変更できないようにする仕組み(排他制御)です。
Oracleに限らず、たいていのRDBMSはロックを使用してデータの整合性を確保します。
セッションが1つしか存在しないシステムならあまりロックを意識しなくても良いかもしれませんが、セッションが複数存在するシステムならロックを意識してアプリ設計しないとロック取得待ちやデッドロックの原因になります。

ロックモード
DBリソースへのアクセスを制御するロックの種類
共有ロック(読取りロック)
同じリソースを複数のセッションで同時にアクセスできるようにするためのロック
共有ロック取得後も別セッションから対象のリソースへアクセスできる
ただし、別セッションは表定義やデータを変更できない(共有ロックは取得可能、排他ロックは取得不可)
※PostgreSQLなどはSELECT時に共有ロックを取得しますが、Oracleは読取り一貫性(CR)を採用しているため、SELECT時に共有ロックは取得しません
排他ロック(占有ロック)
同じリソースを複数のセッションで同時にアクセスできない(共有できない)ようにするためのロック
排他ロック取得後は別セッションはリソースへアクセスできない(共有ロック・排他ロックともに取得不可)

ロック範囲
リソースをロックする範囲
表レベル
特定の表(テーブル)全体をロックする
行レベル
表(テーブル)の特定の行(レコード)をロックする

エンキュー(Oracleエンキュー)
データの変更をシリアライズするため、共有メモリ構造体(ロック)を提供するサービス
ロックはキューで管理される(FIFO型のロック)

自動ロック
OracleはDDL文やDML文でトランザクションを発行すると自動的にロックを取得する
DMLロック(データ・ロック)
DML文を実行した場合、自動的に取得されるロック
行レベルと表レベルの2種類のロックが自動的に取得される
データ保護が目的

・行レベルのロック(行ロック、TXロック、トランザクション)
表の特定の行に対するロック
INSERT/UPDATE/DELETE/MERGE/SELECT FOR UPDATE文を実行した場合、自動的に取得される
ロック取得タイミング：トランザクション開始時
ロック解放タイミング：トランザクションのコミット/ロールバック
※特定の行に対する表の表ロックも取得する(データ更新中のDDL操作(表定義変更など)を防ぐため)
・表レベルのロック(表ロック、TMロック、DMLエンキュー)
特定の表に対するロック
INSERT/UPDATE/DELETE/MERGE/SELECT FOR UPDATE文を実行した場合、自動的に取得される
ロック取得タイミング：トランザクション開始時
ロック解放タイミング：トランザクションのコミット/ロールバック
なお、表ロックには下記のモードがある

[ロックモード2]行共有ロック(RS/SS：ROW SHARE、SSは何の略か分かりません)
表のロックを保持しているトランザクションが該当行をロックしており、該当行を更新する予定であることを示すモード
[ロックモード3]行排他ロック(RX/SX：ROW EXCLUSIVE、SXは何の略か分かりません)
表のロックを保持しているトランザクションが該当行を更新/SELECT FOR UPDATEしたことを示すモード
[ロックモード4]共有表ロック(S：SHARE)
表のロックを保持しているトランザクションがいても別のトランザクションも同じ表へ問合せできることを示すモード
1トランザクションが共有表ロックを保持する場合、表更新できるが、複数トランザクションが共有表ロックを保持する場合、表更新できない
[ロックモード5]共有行排他表ロック(SRX：SHARE ROW EXCLUSIVE)
表のロックを保持しているトランザクションがいても別のトランザクションも同じ表へ問合せできることを示すモード
共有表ロックを保持するトランザクションは表更新できるが、別トランザクションは表更新できない
[ロックモード6]排他表ロック(X：EXCLUSIVE)
表のロックを保持しているトランザクション以外、表への問合せ・更新できないことを示すモード

※LOCK TABLE文で手動で明示的に表ロックを取得できる

DDLロック(データディクショナリ・ロック)
DDL文を実行した場合、自動的に取得されるロック
変更対象のオブジェクトに対するDMLロックも自動的に取得される
スキーマ・オブジェクトの構造(表定義やビュー定義など)保護が目的
※明示的にDDLロックを取得できない
※DDL文のロック解除待ち時間(秒)はDDL_LOCK_TIMEOUT初期化パラメータで設定できる(デフォルト(0)は待機しない＝NOWAIT)
・排他DDLロック
別トランザクションがDDLロック/DMLロックを取得できないようにするロック
別トランザクションのスキーマ・オブジェクト定義に対するDDL操作(ALTER TABLE、DROP TABLEなど)による変更(破壊)を防ぐ
・共有DDLロック
別トランザクションが同じスキーマ・オブジェクト定義を変更(破壊)しない場合、同じスキーマ・オブジェクトにアクセスできるようにするロック
スキーマ・オブジェクト定義を変更(破壊)しないDDL文としてCREATE PROCEDUREなどがある
・ブレーク可能解析ロック
ライブラリキャッシュ(共有SQL領域)のSQL文がスキーマ・オブジェクトを参照する場合、対応するSQL文に対して取得されるロック
参照しているスキーマ・オブジェクト定義が変更/削除された場合、対応する共有SQL領域のSQL文を無効化する
SQL文の解析フェーズ中に共有プール内で取得される

システムロック
データファイルなどの内部構造の保護が目的
・ラッチ
SGA内の共有データの保護(排他制御)に使用される
共有メモリ領域へのアクセス開始～終了まで保持されるロック
マイクロ秒～ミリ秒レベルで獲得と解放を繰り返すが獲得に失敗した場合、スピンして獲得できるのを待機する
※スピン後でも獲得できずスリープする場合、待機イベント「latch free」などで待機する
・共有プール上でのメモリの割り当てや解放
・バッファキャッシュ上でのバッファの探索
・ライブラリキャッシュ上でのオブジェクトの探索
・セッションの開始
・チェックポイントの開始
・ログバッファの割り当て
など
・mutex
ラッチに似ているが、ラッチは複数オブジェクトをグルーピングして保護するが、mutexは単一オブジェクトを保護する
・内部ロック
ディクショナリキャッシュ内のデータ保護
制御ファイル、REDOログファイル、アーカイブログファイル、データファイルなどのファイル保護
表領域とUNDOセグメントの保護

今回は特定のデータに対してロックモード1～6のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証しました。
結果は下記のようになりました。

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

■前提
・非CDB

■設定内容
DDLロックタイムアウト時間
SQL> select inst_id, name, value from gv$parameter where name = 'ddl_lock_timeout';

INST_ID NAME VALUE
------- ------------------------------ ----------------------------------------
1 ddl_lock_timeout 0
2 ddl_lock_timeout 0

■検証パターン
①ロックモード1に対するロック取得検証
②ロックモード2に対するロック取得検証
③ロックモード3に対するロック取得検証
④ロックモード4に対するロック取得検証
⑤ロックモード5に対するロック取得検証
⑥ロックモード6に対するロック取得検証

■検証　※作業ログは全パターンを載せると膨大な量になるため、セッション2(HR)から一部DML(insert・update)、一部DDL(DROP TABLE)を実行したログのみ載せます
①ロックモード1に対するロック取得検証
特定のデータに対してロックモード1のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード1のロック取得
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】
別セッションからロックモード1～6のロックが取得できたため作業ログは割愛します

②ロックモード2に対するロック取得検証
特定のデータに対してロックモード2のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(select for update)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
3. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(LOCK TABLE)
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得
SQL> select * from emp where EMPNO = 7369 for update;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20

2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
134 63853 HR
141 35627 SCOTT

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
134 TX X(排他ロック) 2
134 TM RX/SX(行排他ロック) 2
141 TX X(排他ロック) 2
141 TM RX/SX(行排他ロック) 2

※下記SQLでオブジェクトレベルでロック取得状況を確認できます
SQL> select o.owner, o.object_name, o.object_type, lo.SESSION_ID, decode(lo.LOCKED_MODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode from v$locked_object lo, dba_objects o where lo.OBJECT_ID = o.OBJECT_ID;

OWNER OBJECT_NAME OBJECT_TYPE SESSION_ID LCK_MODE
--------------- --------------- --------------- ---------- ------------------------------
SCOTT EMP TABLE 141 RX/SX(行排他ロック)
SCOTT EMP TABLE 134 RX/SX(行排他ロック)

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
134 63853 HR 141
141 35627 SCOTT

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
134 TM RX/SX(行排他ロック) 2
134 TX NONE(ロックなし) 0
141 TX X(排他ロック) 1
141 TM RX/SX(行排他ロック) 2

--DDL(DROP TABLE)
SQL> drop table scott.emp;
drop table scott.emp
*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
134 63853 HR
141 35627 SCOTT

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
141 TX X(排他ロック) 2
141 TM RX/SX(行排他ロック) 2

3. セッション1(SCOTT)で特定のデータに対してロックモード2のロック取得(LOCK TABLE)
SQL> LOCK TABLE emp IN ROW SHARE MODE nowait;

表がロックされました。

4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 3652 HR
128 9275 SCOTT

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
12 TX X(排他ロック) 2
12 TM RX/SX(行排他ロック) 2
128 TM RS/SS(行共有ロック) 2

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 3652 HR
128 9275 SCOTT

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
12 TX X(排他ロック) 2
12 TM RX/SX(行排他ロック) 2
128 TM RS/SS(行共有ロック) 2

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
12 3652 HR
128 9275 SCOTT

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
128 TM RS/SS(行共有ロック) 2

③ロックモード3に対するロック取得検証
特定のデータに対してロックモード3のロックを取得し、同じデータに対して別セッションからロックモード1～6のロックが取得できるか検証します

【検証手順】
1. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(insert)
2. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
3. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(update)
4. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
5. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(delete)
6. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
7. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(LOCK TABLE)
8. セッション2(HR)で同じデータに対してロックモード1～6のロック取得

【作業ログ】

1. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR 257

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 1
257 TM RX/SX(行排他ロック) 2
372 TX X(排他ロック) 2
372 TM RX/SX(行排他ロック) 2
372 TX NONE(ロックなし) 0

3. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;

1行が更新されました。

1行が作成されました。

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 2
257 TM RX/SX(行排他ロック) 2
372 TX X(排他ロック) 2
372 TM RX/SX(行排他ロック) 2

--ロックモード3ロック取得(update)
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
★応答なし

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 1
257 TM RX/SX(行排他ロック) 2
372 TM RX/SX(行排他ロック) 2
372 TX NONE(ロックなし) 0

※別セッションでロック状況確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');

SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
257 48116 SCOTT
372 65105 HR

SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
257 TX X(排他ロック) 2
257 TM RX/SX(行排他ロック) 2

5. セッション1(SCOTT)で特定のデータに対してロックモード3のロック取得(delete)
SQL> delete from scott.emp where EMPNO = 7369;

1行が削除されました。

6. セッション2(HR)で同じデータに対してロックモード1～6のロック取得
--ロックモード3ロック取得(insert)
SQL> insert into scott.emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values(9999, 'TEST', 'DBA', 7369, sysdate, 2000, 500, 10);