RE: sequential read waits during insert
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Sep 2004 18:05:13 -0400
What is the chain count for this table?
Waleed
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]=20
Sent: Thursday, September 23, 2004 1:00 PM
To: oracle-l@xxxxxxxxxxxxx
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
----------------------------------------------------------------
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=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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
