Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Surprising parameters for direct path read in 100046 trace

Surprising parameters for direct path read in 100046 trace

From: Chris Dunscombe <chris_at_thedunscombes.f2s.com>
Date: Tue, 07 Mar 2006 10:14:35 +0000
Message-ID: <20060307101435.bjpfludeokwgo8wc@webmail.christallize.com>


Hi,

I've traced a long running piece of SQL and observed the following in the trace file (only a small sample):

WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 5 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641041 p3=1
WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
.................

My understanding is that p1 is the filenum#, p2 the block# and p3 the block count. So my question is why is Oracle continually re-reading the same 2 blocks?

Oracle version 9.2.0.4 64 bit on Solaris. Filenum# 202 is an Oracle temp file. The SQL was in the middle of a sort merge at the time.

Thanks,

Chris Dunscombe

www.christallize.com

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 07 2006 - 04:14:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US