Search

Top 60 Oracle Blogs

Recent comments

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