RE: Surprising parameters for direct path read in 100046 trace

  • From: "Fedock, John (KAM.RHQ)" <John.Fedock@xxxxxxxxxxxx>
  • To: <chris@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 7 Mar 2006 14:27:26 -0500

That is how I would read it as well.  Let me know what you find out.  Time for 
a TAR?


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Chris Dunscombe
Sent: Tuesday, March 07, 2006 5:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Surprising parameters for direct path read in 100046 trace


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

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


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


Other related posts: