Saturday, January 30, 2010

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

No comments:

Post a Comment

Post a Comment