RE: sequential read waits during insert

What is the chain count for this table?

Waleed

-----Original Message-----

From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]=20 Sent: Thursday, September 23, 2004 1:00 PM To: oracle-l_at_freelists.org
Subject: sequential read waits during insert

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

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

  operation

      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=20
table_2 with a full scan (at dfmrc=3D128) and it is taking over 30 = minutes
to=20
scan a measly 50,000 blocks. I turned on sqltrace at level 12 and I see the=20
following:

WAIT #1: nam=3D'db file sequential read' ela=3D 95 p1=3D136 p2=3D348745 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 68 p1=3D136 p2=3D348744 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 171 p1=3D136 p2=3D348743 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 52 p1=3D136 p2=3D348742 = p3=3D1
WAIT #1: nam=3D'db file scattered read' ela=3D 23034 p1=3D109 p2=3D23181 = p3=3D128
WAIT #1: nam=3D'db file sequential read' ela=3D 114 p1=3D136 p2=3D348741 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 70 p1=3D136 p2=3D348740 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 155 p1=3D136 p2=3D348739 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 160 p1=3D136 p2=3D348738 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 560 p1=3D136 p2=3D348737 = p3=3D1
...
WAIT #1: nam=3D'db file sequential read' ela=3D 42 p1=3D136 p2=3D348614 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 145 p1=3D136 p2=3D348613 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 58 p1=3D136 p2=3D348612 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 104 p1=3D136 p2=3D348611 = p3=3D1
WAIT #1: nam=3D'db file scattered read' ela=3D 9135 p1=3D109 p2=3D23309 = p3=3D128
WAIT #1: nam=3D'db file sequential read' ela=3D 148 p1=3D136 p2=3D348610 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 658 p1=3D136 p2=3D348609 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 121 p1=3D136 p2=3D348608 = p3=3D1
WAIT #1: nam=3D'db file sequential read' ela=3D 138 p1=3D136 p2=3D348607 = p3=3D1
...

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

Can anyone offer an explanation why the table to be inserted into would be=20
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=20
microseconds.
Regards

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

Regards

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

--

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

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 23 2004 - 18:51:58 CDT