IO Waits Kyle Hailey Kylehaileyhotmail com 2 Waits

IO Waits Kyle Hailey Kyle_hailey@hotmail. com

#. 2 Waits Covered in this Section Ø Ø Ø Ø Ø db file sequential read db file scattered read db file parallel read direct path write sort segment request direct path write (lob) file open file identify Copyright 2006 Kyle Hailey

IO Waits SGA Log Library Buffer Cache DBWR User 1 Data Files Copyright 2006 Kyle Hailey #. 3

#. 4 Table/Index IO Ø db file sequential read Ø db file scatted read Ø db file parallel read Copyright 2006 Kyle Hailey

#. 5 db file sequential read : Diag 1) Search Buffer Cache for the block Shadow Process 2) Fails 3) Reads of disk Buffer Cache Copyright 2006 Kyle Hailey • File • Block

#. 6 db file sequential read : Wait Ø #1 Top Wait Ø Single Block Read Ø Index Ø Data via and Index Ø Data via Rowid Ø Rollback Ø Also dumping headers, rebuilding controlfile Ø Sequential means Ø Sequence from an Index Ø Sequence from a rowid Copyright 2006 Kyle Hailey

db file sequential read : Args Ø P 1 = File # Ø P 2 = Block # Ø P 3 = 1 Ø Can be more than 1 for temporary segments select segment_name, segment_type from dba_extents where file_id = P 1 and P 2 between block_id and block_id + blocks – 1; Copyright 2006 Kyle Hailey #. 7

#. 8 db file scattered read : Diag 1) Search Buffer Cache for the blocks 2) Fails Shadow Process 3) Reads off disk Buffer Cache Copyright 2006 Kyle Hailey • File • Block • Multi Block Read Count

#. 9 db file scattered read : Wait Ø # 3 Top Wait Event Ø Multi Block Read Ø Full Table Scan Ø Index Fast Full Scans Ø Scattered Means Ø Blocks are read and scattered throughout buffer cache Copyright 2006 Kyle Hailey

#. 10 db file scattered read : Args Ø P 1 = File # Ø P 2 = Block # Ø P 3 = # of Blocks to Read select segment_name, segment_type from dba_extents where file_id = P 1 and P 2 between block_id and block_id + blocks – 1; Copyright 2006 Kyle Hailey

#. 11 db file parallel read : Diag 1) Search Buffer Cache for the blocks Shadow Process 2) Fails Buffer Cache 3) Reads that block off Disk Copyright 2006 Kyle Hailey

#. 12 db file parallel read : Wait Ø #18 Top Wait Ø Process issues multiple single block reads in parallel Ø Documentation says only for recovery Ø But seems to happen for normal read ops as well Ø Async Call – wait for all reads to complete Copyright 2006 Kyle Hailey

#. 13 db file parallel read: Args Ø P 1 = # of files to read from Ø P 2 = # of total blocks to read Ø P 3 = # of I/O requests Ø Same value as P 2 Copyright 2006 Kyle Hailey

#. 14 IO Solutions Ø If Ø Db scattered Read Ø Db file sequential Read Ø Db file parallel Read Ø Then Ø Check Ø Should be between 5 -15 ms Ø Data in Statspack under “File IO Stats” Ø Check Ø Ø average read times per file Cache buffer Hit ratio Check db_cache_advice 9 i and higher Data in Statspack under “Buffer Pool Advisory” Ø Tune High IO SQL Copyright 2006 Kyle Hailey

#. 15 IO Solutions : Ave Read Time File IO Stats DB/Inst: LABSF 03/labsf 03 Snaps: 1 -2 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename --------------------------------------Av Mx Av Av Rd Rd Av Av Buffer Buf. Wt Reads/s (ms) Bkt Blks/Rd Writes/s Waits (ms) ------- --- ------------ -SYSTEM /u 01/app/oracle/oradata/labsf 03/system 01. dbf 445 15 0. 4 16 1. 0 1, 157 39 2, 744 93. 3 USERS /u 01/app/oracle/oradata/labsf 03/users 01. dbf 223 7 0. 5 ### 1. 0 9, 725 324 4 100. 0 Copyright 2006 Kyle Hailey

IO Solutions : Buffer Pool Advisory Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads -------- -------------D 56. 1 6, 986 2. 3 58, 928 D 112. 2 13, 972 1. 6 42, 043 D 224. 4 27, 944 1. 0 25, 772 D 336. 6 41, 916 1. 0 25, 715 D 448. 8 55, 888 1. 0 25, 715 D 596 1. 0 74, 351 1. 0 25, 715 D 728 1. 2 90, 818 1. 0 25, 715 D 840 1. 4 104, 790 1. 0 25, 715 D 952 1. 6 118, 762 1. 0 25, 715 D 1, 064 1. 8 132, 734 1. 0 25, 715 Copyright 2006 Kyle Hailey #. 16

#. 17 IO Solutions Ø After Checking Ø File IO response times Ø Buffer Cache Hit Ratio Ø Then the only choice is to Ø Tune the SQL Copyright 2006 Kyle Hailey

#. 18 IO Throughput Copyright 2006 Kyle Hailey

#. 19 Direct I/O WAITS Direct I/O : This mechanism lets the client bypass the buffer cache for I/O intensive operations. The disk blocks are written into and read from process private memory. Ø Direct IO Clients : hash join Ø sqlldr Ø loading/reading LOBs Ø parallel DMLs Ø sorts Ø create table as select Ø create index Ø Copyright 2006 Kyle Hailey

#. 20 direct path read Ø # 7 Top Wait Ø Could signal sort problems Ø Sorts read into their PGA from datafiles via direct path read Ø Other Uses Ø PQO Ø Asyncronous read ahead Copyright 2006 Kyle Hailey

#. 21 direct path read : Diag Shadow Process PGA X Buffer Cache Copyright 2006 Kyle Hailey

#. 22 direct path write Ø # 8 Top Wait Ø Occurs when: Ø Could Ø signal sorting waits Sorts write to TEMP segment via direct path write Ø insert /*+ APPEND */ Ø sql*loader direct=y Copyright 2006 Kyle Hailey

#. 23 direct path write : Diag DBWR Shadow Process PGA TEMP Buffer Cache Copyright 2006 Kyle Hailey

#. 25 direct path write (lob) Ø #35 Ø Note: Direct path read (lob) Ø Happens for reads on LOBs that are stored NOCACHE Ø LOBS stored CACHE are read into the buffer cache Copyright 2006 Kyle Hailey

file open Ø #16 Ø Args P 1 fib Ø P 2 iov Ø P 3 0 Ø Ø Ø Wait on an open file The time it takes to open the file Times should be order of a block read To tune, try pre-opening files Leave sessoins connected Ø High reconnection rates can exacerbate Ø Can try holding log files open (see script from Steve Adams) Copyright 2006 Kyle Hailey #. 26

#. 27 file identify Ø #29 Ø The time it takes to identify a file so that it can be opened later. Ø Datafile should be identified just once Ø Logfiles are identified every checkpoint Ø Reduce checkpoints Ø Arguments p 1 fib Ø p 2 filename Ø p 3 opcode (1 test for create, 2 get details) Ø Copyright 2006 Kyle Hailey

#. 28 Summary I/O Ø direct path read : sorts or PQO Ø direct path write : direct path or temp io Ø sort segment request : SMON occupied? Ø direct path write (lob) : stored NOCACHE Ø file open : log files or session reconnects Ø file identify : log files or session reconnects Copyright 2006 Kyle Hailey

#. 29 Summary I/O Ø Buffer Cache IO Ø db file sequential read Ø db file scattered read Ø db file parallel read Ø Tune Ø I/O’s should be 5 -15 ms Ø Check Buffer Cache Advisory Ø Tune SQL Copyright 2006 Kyle Hailey