Oracle Standard IO waits

from http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-io-waits

Standard I/O

---

db file sequential – read Single block read db file scattered – read Multi block read db file parallel – read Non-contiguous multi block read read by other session – wait for another session to do the io

select parameter1, parameter2, parameter3 from v$event_name where …

NAME P1 P2 P3 ----------------------- ----------- --------- -------- db file sequential read file# block# blocks db file scattered read file# block# blocks db file parallel read files blocks requests read by other session file# block# class#

db file sequential read

1. Top reported wait

2. Single block read

3. block accessed via

   1. index

   2. rowid

   3. rollback

Example

select * from emp where empno=99; where there is an index on emp(empno)

algorythm search buffer cache for block by rowid, if fail, read block off disk via file# and block#

Note: “sequential” means a sequence as in rowid

db file scattered read

1. Multi Block Read

   1. Full Table Scan

   2. Index Fast Full Scan

Example

select * from emp;

algorythm search buffer cache for block by rowid, if fail, read block off disk via file# and block# and # of blocks

Note: “scattered” means blocks are scattered throughout the buffer cache (even though they are laid out sequential on disk)

db_file_multiblock_read_count set the target block read size

db file parallel read

Process issues multiple single block reads in parallel

1. Documentation says only for recovery

2. seems to happen for normal read ops

3. Async Call – wait for all reads to complete

Examples

1. Not contiguous multi block read

2. Index full or range scan

3. Where values in

Example

Select * from emp where empno in (1,2,3,4,5,6);

a

read by other session

Multiple sessions reading the same data that requires IO

Example

two users doing a full table scan at the same time

algorythm

1. Block not found in cache

2. Read block from disk

3. Found other session already reading block from disk

4. Wait for the other session to finish IO