© 2024 NTT DATA Corporation Performance measures for delayed block cleanout JPOUG Tech Talk Night #8 Jan. 23th 2024 NTT Data Corporation Kazuhiro Takahashi

© 2024 NTT DATA Corporation 2 Introduction • Database specialist • Oracle, Exadata, OCI • Community activities as an Oracle ACE MOSC (My Oracle Support Community) Community activities

© 2024 NTT DATA Corporation 3 What we would like to tell today • What is delayed block cleanout of Oracle? • The problem, its analysis, and how to address it • Design Considerations

© 2024 NTT DATA Corporation 4 Case study of an incident • Incident: • SQL delays occurred during nighttime batch ETL processing in a DWH performance test • Situation: • a job that normally takes about 35 minutes takes more than 1 hour. • The delay occurs when the multiplicity of the ETL server is increased • Process Overview: • The processing that caused the delay is a job to output csv with full scanning of the table. • This table is rewritten (TRUNCATE/INSERT) in the job before this job. There is no other update to the table in question XXXTBL (500m+) XXXTBL (500m+) csv 0:00~ 2:00~ TRUNCATE/ INSERT SELECT DB(Exadata) ETL server delay

© 2024 NTT DATA Corporation 5 Case study of an incident - analysis Suspect delayed block cleanout 通常 遅延発生時 SQL SELECT ・・・ FROM XXXTBL WHERE XXXFLG <> '1' OR XXXFLG IS NULL; Data 5,000,000+ rows Execution plan ------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5353K| 255M| |* 1 | TABLE ACCESS STORAGE FULL| XXXTBL | 5353K| 255M| ------------------------------------------------------------- Elapased time Less than 30 sec 50 min (less than 30 sec for daytime manual execution ) IO Smart scan No smart scan excessive single block reads against UNDO

© 2024 NTT DATA Corporation 6 Analysis of SQL report • SQL report of delayed SQL for the time period of the event 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 | ------------------------------------------------------------------------------------------------- Execution Plan Statistics Significant IO waits. 98% of processing time is IO waiting Smart scan not working. 22GB of IO is occurring (segment size of the table is about 1GB) Full scan of a single table

© 2024 NTT DATA Corporation 7 Analysis of ASH report • Run the job (net) in question during the daytime, reproduce the event, and obtain an ASH report. 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 Large number of accesses to UNDO during the same time period Single block reads significantly occur in the SQL in question

© 2024 NTT DATA Corporation 8 What is a delayed block cleanout? • Delayed block cleanout occurs when uncommitted blocks are written from the buffer cache to storage in an uncommitted state in large transactions that cannot fit in the buffer cache. • Such a block remains uncommitted on disk after the transaction is committed, and the next referencing transaction references the UNDO and changes it to committed. • Tends to be more apparent in batch processing that handles large amounts of data Normal (Cleanout) Delayed block cleanout キャッシュ ストレージ INSERT／COMMIT SELECT (1) Change block (ITL) to committed on commit (cleanout) (2) Reflected into storage by DBWR (asynchronous) (3) Read committed blocks INSERT／COMMIT SELECT (1) Blocks are reflected in storage before commit (3) Read uncommitte d blocks (4) Return the results (2) Block (ITL) cannot be marked as committed at commit time (4) Clean out and return results if already committed using UNDO UNDO (5) Reflected to storage by DBWR (asynchronous) C C C C U U U C

© 2024 NTT DATA Corporation 9 Flush AFTER commit Flush BEFORE commit 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 Simple verification • When blocks on buffer cache are cleared before commit, SELECT increases logical block reads and redo and delays performance * Oracle 19.11, direct path read is disabled intentionally by alter session set “_serial_direct_read”=never; Flushing after commit reflects blocks in committed state into storage Flushing before commit reflects blocks in uncommitted state into storage SELECT changes uncommitted blocks to committed, which takes processing time Increased logical block reads and redo generation

© 2024 NTT DATA Corporation 10 Links between an ITL entries and UNDO segment • The block's ITL has a link to the most recent transaction that updated that block (XID) and a link to the UNDO block (UBA) • Each row of the table block has a link to the ITL (LB: lock byte, indicating which transaction holds the lock) • If LB=0, then committed row. Otherwise, you can check if it is committed from the ITL and the transaction table 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 Block Segment Header … DBA:0x00803c75 … UNDO segment 5 Rec4 From UBA From XID Slot 2 To UNDO segment 3 Transaction table UNDO block Record Directory Record Heap Table Transaction table records the status of the most recent transaction (active or not, committed SCN, etc.) [1] pp.41, Figure 3-3. Links between an ITL entry and the associated undo segment Have a link to the most recent transaction information (ITL) in the LB of each row ITL has a link to the transaction table

© 2024 NTT DATA Corporation 11 Case study of an incident - Mechanism (hypothesis) • Increased multiplicity of INSERTs increases buffer cache utilization and the amount of uncommitted state blocks on disk • Delayed block cleanout on SELECT prevents smart scan and delays SQL due to large number of UNDO reads (single block reads) • No delay in SELECT because it is already cleaned out in the next day's mid-day hours (1)TRUNCATE XXXTBL (2)INSERT /COMMIT Tablespaces As buffer cache utilization increases, the number of blocks flushed from the buffer cache in uncommitted state increases Buffer cache (3)INSERT/COMMIT (n multiplicity) Both tables and indexes are written on buffer cache and DBWR is reflected asynchronously to disk As the number of cores in the batch server increases, the multiplicity of simultaneous INSERTs increases and buffer cache utilization increases (4) DBWR writes (asynchronous) Other tables (5)SELECT (smart scan) XXXTBL Tablespaces When there are large number of blocks in uncommitted status, the amount of access to UNDO increases and causes delays by single block reads Buffer cache (7) Access to UNDO to check for uncommitted Storage servers Storage servers UNDO (8) Change to committed, generate redo (6)Returns single blocks due to uncommited state SmartScan is blocked by uncommitted blocks, although Exadata's commit cache on the storage server allows some cleanout on the storage server side and return with a smart scan REDO (9) DBWR writes (asynchronous)

© 2024 NTT DATA Corporation 12 How to analysis • Characteristics of delayed block cleanout • Does SQL wait for single block reads? • Does the single block read UNDO? • Does it reproduce when SQL is executed by itself? • Is the table massively updated in advance before the SQL • The relatively high cleanouts-related statistics of AWR during the time of the delay are also useful for making decisions If all yes, Suspect delayed block cleanout When delayed block cleanout occurs 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 Normal time 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 Example

© 2024 NTT DATA Corporation 13 How to deal with it • Increase buffer cache, if DB server resources allow (minimum application impact) • Reviewing the AP processing method can reduce delays • The point is to address the process that caused the uncommitted block, not the delayed SQL itself # 概要 Explanation Advantages/Disadvantages Note 1 Buffer cache increase Reduce uncommitted blocks by reducing buffer cache contention • Almost no side effects to APs, little risk of changeDB server memory resources must be sufficient (considering multiplicity peaks) In this case, SQL improved to 8 seconds by doubling the SGA. 2 Reduce batch multiplexing Reduce uncommitted blocks by reducing buffer cache contention • No changes to DB server required • Requires configuration changes and testing with ETL tools or job manager 3 Increase commit frequency Increase commit frequency to reduce blocks in uncommitted status • No changes to DB server required • Requires configuration changes and testing with ETL tools • processing times may increase 4 Utilize direct path insert Change to INSERT method that does not depend on buffer cache • No changes to DB server required • Need to modify and test AP, such as adding APPEND hints to INSERT • Additional consideration on lock and storage usage (HWM) see next slide

© 2024 NTT DATA Corporation 14 Flush AFTER commit Flush BEFORE commit 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 Simple verification2 -Direct path insert • Use direct path insert to avoid delayed block cleanout The same processing time No change in the amount of logical block reads or redo generation * Oracle 19.11, direct path read is disabled intentionally by alter session set “_serial_direct_read”=never;

© 2024 NTT DATA Corporation 15 Design Considerations • Utilize direct path inserts for ETL processes • Avoid batch commit of excessive number of lines • Buffer cache sizing to account for increased application multiplicity • Check for buffer cache shortages during load testing and long-term stability testing • Check free buffer waits in the Buffer Pool Statistics section of the AWR report Conventional inserts Direct path inserts Purpose OLTP Row level parallel update TAT DWH Massive data load Throughput Concurrency control Row lock Table lock Storage efficiency Stored in empty block Stored above HWM (High Water Mark) (Existing free space is not reused) Cache Will be cached Will not be cached Functionality No restrictions Direct operation from remote sites is not allowed Trigger, referential integrity, 2pc are not allowed Performance Slower due to buffer cache Fast because buffer cache is bypassed Comparison of conventional inserts and direct path inserts

© 2024 NTT DATA Corporation 16 Summary • We discussed the performance issue of delayed block cleanout, the mechanism of occurrence, and how to deal with it • Understanding the characteristics will be helpful to identify the issue • For a better understanding, the following books on “Transactions and Consistency” will be helpful Reference: [1] Jonathan Lewis; “Oracle Core: Essential Internals for DBAs and Developers”; Apress, 2011. pp.25-43 “Transactions and Consistency”