Monday, December 1, 2008

Sequential Read vs Scattered Read






DB file sequential read waits:
  • single block read into one SGA buffer
  • contention on index reads
  • V$SESSION_WAIT parameter P3 (The number of blocks) should be 1

  • DB file scattered read waits:
  • multiblock read into many discontinuous SGA buffers
  • contention on full table scans
  • V$SESSION_WAIT parameter P3 (The number of blocks) should be greater than 1


  • Direct read:
  • single or multiblock read into the PGA, bypassing the SGA

  • Parameter:
    db_file_multiblock_read_count which might affect the chose of read method, however, starting at Oralce 10g, it is auto tuned parameter if it is not set explicitly.

    No comments: