| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sequential read on full-table scan?
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.
-----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.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 24 2005 - 04:44:02 CDT
 |
 |