遅延ブロッククリーンアウトの性能対策/JPOUG Tech Talk Night #8

遅延ブロッククリーンアウトとは、Oracle Databaseにおいて、未コミット状態のブロックを参照時にコミット済みに更新する処理です。バッチ処理等において過度に発生すると、再現性のないSQLの性能遅延が発生することがあります。本セッションでは、遅延ブロッククリーンアウトによる性能遅延事象と発生メカニズム、その対処方法についてお話しします。

Transcript

1. © 2024 NTT DATA Corporation 遅延ブロッククリーンアウトの性能対策 JPOUG Tech Talk Night #8 2024年1月23日 株式会社NTTデータ 高橋 一裕
2. © 2024 NTT DATA Corporation 2 自己紹介 • 公共系SIプロジェクトのDBスペシャリスト • 技術領域はOracle、Exadata、OCI • 現場の経験を共有してDBAの生活の質を向上したい • OracleACEとしてコミュニティ活動 MOSC (My Oracle Support Community) コミュニティ活動
3. © 2024 NTT DATA Corporation 3 本日お伝えしたいこと • Oracleの遅延ブロッククリーンアウトとは • 問題とその解析、対処方法 • 設計における留意点
4. © 2024 NTT DATA Corporation 4 発生事例 ～事象 ▪事象： • DWHの性能試験で、夜間バッチのETL処理においてSQLの遅延が発生 ▪状況 • 正常時は35分程度のジョブが1時間以上かかってしまう • ETLのサーバの多重度を増やすと遅延が発生する ▪処理概要 • 遅延の発生した処理は、表をフルスキャンしてcsv出力するジョブ • この表はこの前段のジョブにて、洗い替え(TRUNCATE/INSERT)をする • 当該表への更新は他に存在しない XXXTBL (500万件) XXXTBL (500万件) csv 0時～ 2時～ TRUNCATE/ INSERT SELECT DB（Exadata） ETLのサーバ 遅延
5. © 2024 NTT DATA Corporation 5 発生事例 ～解析 遅延ブロッククリーンアウトを疑う 通常 遅延発生時 SQL 単一テーブルのSELECT SELECT ・・・ FROM XXXTBL WHERE XXXFLG <> '1' OR XXXFLG IS NULL; データ 500万件+ 実行計画 単純なフルスキャン ------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5353K| 255M| |* 1 | TABLE ACCESS STORAGE FULL| XXXTBL | 5353K| 255M| ------------------------------------------------------------- 処理時間 ３０秒以下 ５０分 （日中帯手動実行では３０秒以下★） IO スマートスキャン スマートスキャンなし シングルブロックリード多発（UNDO）★
6. © 2024 NTT DATA Corporation 6 【参考】SQLレポートの解析 • 事象発生時間帯の遅延SQLのSQLレポートを取得 Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------- Elapsed Time (ms) 3,003,205 3,003,205.2 17.0 CPU Time (ms) 181,891 181,890.7 1.9 Executions 1 1.0 0.0 Buffer Gets 3,464,444 3,464,444.0 0.2 Disk Reads 2,910,047 2,910,047.0 1.8 Parse Calls 1 1.0 0.0 Rows 5,353,657 5,353,657.0 N/A User I/O Wait Time (ms) 2,936,823 2,936,823.1 35.9 ・・・ IO Interconnect (GB) 22 21.5 0.8 Uncompressed (GB) 1 1.0 0.1 IO Interconnect Ret for Smart Scan (GB) 0 N/A N/A SELECT ・・・ FROM XXXTBL WHERE XXXFLG <> '1' OR XXXFLG IS NULL; ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5353K| 255M| 1593 (8)| 00:00:01 | |* 1 | TABLE ACCESS STORAGE FULL| XXXTBL | 5353K| 255M| 1593 (8)| 00:00:01 | ------------------------------------------------------------------------------------------------- 実行計画 統計 顕著なIO待ちが発生。処理時間 の98%はIO待ち スマートスキャンが効いていない 22GBのIOが発生（表のセグメン トサイズは1GB程度） 単一表のフルスキャン
7. © 2024 NTT DATA Corporation 7 【参考】ASHレポートの解析 • 日中帯に当該ジョブ（ネット）を走行させ、事象再現。ASHレポートを取得 SQL ID FullPlan hash Plan hash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text Containe r Name XXX 1478445147 2335235626 1 96.45 cell single block physical read 93.87 TABLE ACCESS - STORAGE FULL 93.87SELECT ・ ・・ XXXDB File ID % Activity Event % Event File Name Tablespace 217 16.45 cell single block physical read 16.45 +DATA/XXX/…/DATAFILE/undo_1.753.979149483 UNDO_1 220 14.84 cell single block physical read 14.84 +DATA/XXX/…/DATAFILE/undo_1.749.979149571 UNDO_1 215 14.52 cell single block physical read 14.52 +DATA/XXX/…/DATAFILE/undo_1.755.979149469 UNDO_1 219 14.52 cell single block physical read 14.52 +DATA/XXX/…/DATAFILE/undo_1.751.979149557 UNDO_1 216 10.65 cell single block physical read 10.65 +DATA/XXX/…/DATAFILE/undo_1.754.979149483 UNDO_1 Top SQL with Top Events Top DB Files 同時間帯にUNDOへのアクセスが 大量に発生 当該SQLでシングルブロックリード （cell single block physical read）顕著に発生
8. © 2024 NTT DATA Corporation 8 遅延ブロッククリーンアウトとは • 遅延ブロッククリーンアウトはバッファキャッシュに収まりきらない大きなトランザクションにおいて、未コミットのブロックがバッファ キャッシュからストレージに未コミット状態で書き込まれるために発生する • このようなブロックはそのトランザクションがコミットされてもディスク上に未コミットのまま残り、次に参照するトランザクションが UNDOを参照しコミット済みに変更する • 大量データを扱うバッチ処理で顕在化する傾向がある（OLTPではあまり問題にならない） 通常（クリーンアウト） 遅延ブロッククリーンアウト キャッシュ ストレージ INSERT／COMMIT SELECT ①コミット時にブロック (ITL)をコミット済みに 変更(クリーンアウト) ②DBWRによりスト レージへ反映(非同期) ③コミット済みブ ロックを読込み INSERT／COMMIT SELECT ①コミット前にブロック がストレージへ反映さ れる ③未コミットブ ロックを読込み ④結果を返却 ②コミット時にブロック (ITL)をコミット済みに できない ④UNDOを利用してコ ミット済みならクリーンア ウトして結果を返却 UNDO ⑤DBWRによりスト レージへ反映(非同期) 済 済 済 済 未 未 未 済
9. © 2024 NTT DATA Corporation 9 コミット後にフラッシュ コミット前にフラッシュ SQL create table t1 as select * from emp where 1=0; insert into t1 select * from emp; commit; alter system flush buffer_cache; set autotrace on select count(*) from t1; create table t1 as select * from emp where 1=0; insert into t1 select * from emp; alter system flush buffer_cache; commit; set autotrace on select count(*) from t1; 実行結果 COUNT(*) ---------- 1000000 Elapsed: 00:00:00.11 Statistics ---------------------------------------------------------- 2 recursive calls 1 db block gets 9428 consistent gets 8314 physical reads 97776 redo size 550 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed COUNT(*) ---------- 1000000 Elapsed: 00:00:00.19 Statistics ---------------------------------------------------------- 2 recursive calls 1 db block gets 16580 consistent gets 8445 physical reads 715536 redo size 550 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 【参考】簡単な検証 • コミット前にバッファキャッシュ上のブロックがクリアされると、SELECTで論理ブロック読み込みとREDOが増加し性能が遅延 ※Oracle 19.11で検証。alter session set “_serial_direct_read”=never;でダイレクトパスリードを意図的に抑止 コミット後にフラッシュすると、コミッ ト済み状態のブロックがストレージ に反映される コミット前にフラッシュすると、 未コミット状態のブロックが ストレージに反映される SELECTで未コミットブロッ クをコミット済みに変更する ため、処理時間がかかる 論理ブロック読込みや REDO生成量が増加
10. © 2024 NTT DATA Corporation 10 【参考】ITLエントリとUNDOセグメントのリンク • ブロックのITLにはそのブロックを更新した直近のトランザクションへのリンク(XID)と、UNDOブロックへのリンク(UBA)を持つ • 表ブロックの各行はITLへのリンクを持っている(LB：ロックバイト、どのトランザクションがロックを保持しているかを示す) • LB=0ならコミット済み行。そうでなければ、ITLおよびトランザクション表からコミット済みかを確認できる ITL List #1: XID 3.1843.9 UBA 0x008011b2.0aba.16 #2: XID 5.1843.2 UBA 0x00803c75.0a0d.4 Row directory Free Space Rows xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 2 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 2 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 1 xxxxxxxxxxxxxxxxxxxxxxxxxxx LB = 0 表ブロック Segment Header … DBA:0x00803c75 … UNDOセグメント5 Rec4 From UBA From XID Slot 2 UNDOセグメント３へ トランザクション表 UNDOブロック Record Directory Record Heap 表 トランザクション表には直近のトラン ザクションの状態（アクティブかどうか、 コミットSCN等）が記録されている 参考文献１：pp.41, Figure 3-3. Links between an ITL entry and the associated undo segmentより抜粋 各行のLBに直近のトランザクション 情報（ITL）へのリンクを持つ ITLにはトラン ザクション表へ のリンクを持つ
11. © 2024 NTT DATA Corporation 11 発生事例 ～遅延発生メカニズム（仮説） • INSERTの多重度増加でバッファキャッシュの利用率が増加し、ディスク上の未コミット状態のブロック量が増加 • SELECTの遅延ブロッククリーンアウトでスマートスキャンが阻害され、大量のUNDO読み込み（シングルブロックリード）によりSQLが遅延 • 翌日日中帯ではクリーンアウト済みなので、SELECTで遅延は発生しない ①TRUNCATE XXXTBL ②INSERT /COMMIT 業務表領域 バッファキャッシュの利用率が上がると、 未コミットの状態でバッファキャッシュ上 からフラッシュされるブロック数が増加 バッファ キャッシュ ③INSERT /COMMIT (n多重） 表・索引ともにバッファキャッシュ上に書き込み、 DBWRがディスクに非同期反映 バッチサーバのコア数が増えると、 同時にINSERTする多重度が 増え、バッファキャッシュの利用 率が上昇 ④DBWRによる ブロック書き込み 他の表 ⑤SELECT （スマートスキャン） XXXTBL 業務表領域 未コミットの状態のブロック 数が多いとUNDOへのア クセス量が増え、遅延が 発生する（シングルブロッ クリード） バッファ キャッシュ ⑦未コミットかチェックする ためUNDOへアクセス ストレージサーバ ストレージサーバ UNDO ⑧コミット済みに変更、 REDOを生成 ⑥未コミットブロックのため シングルブロックを返却 未コミットブロックだとスマートスキャン は不可（Exadataの仕様※） REDO ⑨DBWRが書き 込み（非同期） ※Exadataのストレージサーバ上の コミットキャッシュにより、ある程度ス トレージサーバ側でクリーンアウトして スマートスキャンで返却可能
12. © 2024 NTT DATA Corporation 12 解析方法 • 遅延ブロッククリーンアウトによる遅延事象の特徴 • SQLの待機はシングルブロックリードか？ • シングルブロックリードはUNDOか？ • SQL単体で実行すると再現しない？ • 事前にSQLの前に洗い替えなど、表が大量に更新されているか？ • 遅延発生時間帯のAWRのcleanouts関連の統計値が比較的高くなることも判断材料として有効 全てYESなら、 遅延ブロッククリーンアウトを疑う ◆遅延ブロッククリーンアウト発生時 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- cleanout - number of ktugct call 72,399,097★ 6,696.7 4.1 cleanouts and rollbacks - consis 16,460 1.5 0.0 cleanouts only - consistent read 65,909,546★ 6,096.4 3.7 ◆通常時 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- cleanout - number of ktugct call 589,352★ 40.9 786.9 cleanouts and rollbacks - consis 48 0.0 0.1 cleanouts only - consistent read 584,439★ 40.6 780.3 ktugct: Kernel Transaction Undo Get Commit Time 例
13. © 2024 NTT DATA Corporation 13 対処方法 • DBサーバのリソースが許せば、バッファキャッシュの増加（AP影響がない現実解） • リソース制約が厳しい場合は、洗い替えのAP処理方式を見直すことで遅延を軽減できる • 遅延したSQLそのものでなく、未コミット状態のブロックを発生させた処理に対処する点がポイント 概要 詳細 メリット・デメリット 備考 案１ バッファキャッシュ増加 洗い替え時のバッファキャッシュの競 合を減らすことで未コミットブロック を減らす • APへの副作用がほぼなく、変更のリスクは少ない • DBサーバのメモリリソースに余裕が必要（多重度の ピークを考慮） 当該事例ではSGAを2 倍確保することでSQL が8秒に改善 案２ 洗い替えのバッチ多重 を減らす 洗い替え時のバッファキャッシュの競 合を減らすことで未コミットブロック を減らす • DBサーバへの変更は不要 • ETLツールまたはJP1による設定変更・試験が必要 案３ 洗い替えのコミット頻 度を上げる 洗い替え時のコミット頻度を上げる ことで、未コミット状態のブロックを 減らす • DBサーバへの変更は不要 • ETLツールの変更・試験が必要 • 洗い替え処理が伸びる懸念がある 案４ 洗い替えをダイレクト パスインサートにする 洗い替え時のバッファキャッシュを経 由しないINSERT方式に変更す る • DBサーバへの変更は不要 • INSERTにAPPENDヒントを付与するなど業務処理 の改修・試験が必要 • ロックとHWM上に格納することになる点に注意 次頁参照
14. © 2024 NTT DATA Corporation 14 コミット後にフラッシュ コミット前にフラッシュ SQL create table t1 as select * from emp where 1=0; insert /*+ append */ into t1 select * from emp; commit; alter system flush buffer_cache; set autotrace on select count(*) from t1; create table t1 as select * from emp where 1=0; insert /*+ append */ into t1 select * from emp; alter system flush buffer_cache; commit; set autotrace on select count(*) from t1; 実行結果 COUNT(*) ---------- 1000000 Elapsed: 00:00:00.06 Statistics ---------------------------------------------------------- 1 recursive calls 1 db block gets 8186 consistent gets 8174 physical reads 192 redo size 550 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed COUNT(*) ---------- 1000000 Elapsed: 00:00:00.06 Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 8184 consistent gets 8173 physical reads 132 redo size 550 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 【参考】簡単な検証２（ダイレクトパスインサート） • 洗い替えをダイレクトパスインサートにすると遅延ブロッククリーンアウトを回避できる ※Oracle 19.11で検証。alter session set “_serial_direct_read”=never;でダイレクトパスリードを意図的に抑止 処理時間は同じ 論理ブロック読込みや REDO生成量は変化なし
15. © 2024 NTT DATA Corporation 15 設計における留意点 • ETL等の表の洗い替え処理はダイレクトパスインサートを活用する • 過度な行数の一括コミットを避ける • バッファキャッシュのサイズはAP多重度の増加も考慮 • 負荷試験や長期安定試験では、バッファキャッシュの不足がないかを確認 • AWRレポートのBuffer Pool StatisticsのFree buffer waits 観点 従来型インサート ダイレクトパスインサート 用途 OLTP向き 行レベル並列更新、TAT重視 DWH向き 大量データロード、スループット重視 排他制御 行ロック 表ロック 格納効率 空きブロックに格納 HWM（ハイウォーターマーク）上に格納 （既存空き領域は再利用されない） キャッシュ バッファキャッシュに乗る バッファキャッシュに乗らない 機能 制約なし リモートサイトからのダイレクト操作は不可 トリガー、参照整合性制約、2pcは使えない 性能 バッファキャッシュ経由のため低速 バッファキャッシュをバイパスするため高速 従来型インサートとダイレクトパスインサートの比較
16. © 2024 NTT DATA Corporation 16 まとめ • 遅延ブロッククリーンアウトの性能遅延と発生メカニズム、その対処方法についてお話しました • 解析に癖があるので、事象の特徴を理解しておくと、現場での解決に役立つ • 理解を深めるには、以下の書籍の「トランザクションと一貫性」について読むとよいと思います 【参考文献１】 Jonathan Lewis; “Oracle Core: Essential Internals for DBAs and Developers”; Apress, 2011. pp.25-43 “Transactions and Consistency”