| 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?
sorry folks -- I was just reading my own post, and I see the text is confusing.
"This obviously also includes blocks containing overflow row pieces"
refers to all blocks below the HWM being read into the buffer cache by a full
table scan.
kind regards,
Lex.
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 Mon May 23 2005 - 14:17:13 CDT
 |
 |