sequential read waits during insert

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 23 Sep 2004 10:59:49 -0600
Message-Id: <6.1.0.6.2.20040923105750.03494798@pop.centrexcc.com>

Hi all
my apologies if that shows up twice. I forgot to put a subject on the first post.

Can anyone offer an explanation for the following. I am trying to improve the performance of an insert - select from. The plan I am looking at is

operation

INSERT STATEMENT
FILTER

      NESTED LOOPS
        HASH JOIN
          TABLE ACCESS FULL TABLE_1
          TABLE ACCESS FULL TABLE_2
        TABLE ACCESS BY INDEX ROWID TABLE_3
          INDEX UNIQUE SCAN TABLE_3_PK
    SORT AGGREGATE
      NESTED LOOPS
        TABLE ACCESS BY INDEX ROWID TABLE_1
          INDEX RANGE SCAN TABLE_1_C
        TABLE ACCESS BY INDEX ROWID TABLE_3
          INDEX UNIQUE SCAN TABLE_3_PK

(I changed the table and index names)

The statement is taking an awful long time in the hash join. It is reading table_2 with a full scan (at dfmrc=128) and it is taking over 30 minutes to scan a measly 50,000 blocks. I turned on sqltrace at level 12 and I see the following:

WAIT #1: nam='db file sequential read' ela= 95 p1=136 p2=348745 p3=1
WAIT #1: nam='db file sequential read' ela= 68 p1=136 p2=348744 p3=1
WAIT #1: nam='db file sequential read' ela= 171 p1=136 p2=348743 p3=1
WAIT #1: nam='db file sequential read' ela= 52 p1=136 p2=348742 p3=1
WAIT #1: nam='db file scattered read' ela= 23034 p1=109 p2=23181 p3=128
WAIT #1: nam='db file sequential read' ela= 114 p1=136 p2=348741 p3=1
WAIT #1: nam='db file sequential read' ela= 70 p1=136 p2=348740 p3=1
WAIT #1: nam='db file sequential read' ela= 155 p1=136 p2=348739 p3=1
WAIT #1: nam='db file sequential read' ela= 160 p1=136 p2=348738 p3=1
WAIT #1: nam='db file sequential read' ela= 560 p1=136 p2=348737 p3=1

...

WAIT #1: nam='db file sequential read' ela= 42 p1=136 p2=348614 p3=1
WAIT #1: nam='db file sequential read' ela= 145 p1=136 p2=348613 p3=1
WAIT #1: nam='db file sequential read' ela= 58 p1=136 p2=348612 p3=1
WAIT #1: nam='db file sequential read' ela= 104 p1=136 p2=348611 p3=1
WAIT #1: nam='db file scattered read' ela= 9135 p1=109 p2=23309 p3=128
WAIT #1: nam='db file sequential read' ela= 148 p1=136 p2=348610 p3=1
WAIT #1: nam='db file sequential read' ela= 658 p1=136 p2=348609 p3=1
WAIT #1: nam='db file sequential read' ela= 121 p1=136 p2=348608 p3=1
WAIT #1: nam='db file sequential read' ela= 138 p1=136 p2=348607 p3=1

...

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.

Can anyone offer an explanation why the table to be inserted into would be read in this fashion while the hash join is going on?

PS the database is on 9.2.0.5 on Solaris (8 I believe), so the times are in microseconds.
Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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

Received on Thu Sep 23 2004 - 11:55:31 CDT