Re: Full table scan: what order?

Jaap W. van Dijk wrote:

> I've written a script (or at least tried) that shows me how far a full
> table scan has progressed for a session. I can determine the block the
> session is currently reading by retrieving p1 (file_id) en p2
> (blocknumber) from v$session_wait in case of event 'db file scattered
> read'. But in which order does Oracle read the blocks?
> - file_id,extent_id?
> - extent_id?
> Is there a difference between Oracle 7 and Oracle 8?
> I've tried both assumptions but the result my script is giving is
> never a steady increasing value.
>

First there might not be waits recorded in v$session_wait for every block, some blocks can reside in the file cache ( and/or disk read ahead) and the timing that short, that it is not recorded. Second, blocks already residing in the buffer cache are not read again. Check the value of p3, the number of blocks asked for. If it is less than db_file_multiblock_read_count, u have hit a portion of the table that is cached.

/svend jensen Received on Mon Jan 14 2002 - 13:58:40 CST