Re: sequential read waits during insert

Hi!

> The scattered reads are for table_2. So far so good. But the sequential
> reads are for blocks of the table to be inserted into, which is not part
of
> the from clause, i.e. none of table_1, _2, or _3. It looks like the blocks
> are read from highest one-by-one to lowest, seemingly unrelated to the
> scattered reads.

My hypothesis would be that your table has been consisting of logically full blocks and you've deleted lot's of rows from it using full table scan or perhaps dropped a column, that space in every block has dropped below PCTUSED, thus blocks have been put into freelist in sequential order (the latter blocks in table being first in freelist).

Now when your inserts starts inserting into the table (hash join can return rows as soon the build partitions have been put together and first match is found from probe table), the insert will start reading in insert candidate blocks from from the end of the table using freelist, thus causing single block reads.

Now, if you have too little free space in those blocks, you might end into situation where every row requires a separate block for accommodation, or even worse, some rows might have to read in several insert candidate blocks before can be accommodated.

This results in excessive sequential reads for your inserted table.

Potential solutions:

* Reorganize table
* Insert append
* ASSM
* ...

Or - I might be totally wrong ;)

Tanel.

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