RE: sequential read on full-table scan?

  • From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
  • To: <sjaffarhussain@xxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 22 May 2005 12:50:30 +0200

please correct me if I am wrong -- but I think the quote below is not 100% true.
During a full table scan, *all* blocks below the HWM are read into the buffer
cache,
with multiblock I/O requests. the actual sizes of those I/O requests depend on
several
factors (as stated earlier in this thread) such as extent boundaries and blocks
already being present in the buffer cache. This obviously also includes blocks
containing overflow row pieces.

Oracle will not "chase" all the pointers to row pieces until you start fetching
rows;
therefore, chances are that you will only need a LIO to fetch a row piece.
And maybe you don't even need the LIO, depending on your actual SQL statement,
because the leading row piece could hold enough info to produce the result.

Moreover, if the LIO is needed, it leads to a PIO only 
(showing up in a trace file as a sequential read)
if the block didn't arrive yet in (or already aged out from) the buffer cache.

So, as usual, the right answer is "it depends" and the last sentence below is
too strong ...

kind regards,

Lex.
 
------------------------------------------------------------------
Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
------------------------------------------------------------------

-----Original Message (quote) -----

Chained or migrated rows It is a problem if you see many db file sequential read
waits against a table when the execution plan of the SQL statement calls for a
full table scan. This indicates the table has many chained or migrated rows.
Oracle goes after each chained or migrated row with the single-block I/O call.



--
//www.freelists.org/webpage/oracle-l

Other related posts: