Re: sequential read waits during insert
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 20:56:08 +0300
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
