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

Top reported wait Single block read block accessed via index rowid 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

Multi Block Read Full Table Scan 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 Documentation says only for recovery seems to happen for normal read ops Async Call – wait for all reads to complete Examples Not contiguous multi block read Index full or range scan 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 Block not found in cache Read block from disk Found other session already reading block from disk Wait for the other session to finish IO