RE: Surprising parameters for direct path read in 100046 trace

From: <oracle-l-bounce_at_freelists.org>
Date: Wed, 8 Mar 2006 00:41:02 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF484B54@WIN02.hotsos.com>

Direct reads are a different animal then a normal read into the buffer cache. A direct read goes into the PGA for this type of operation. Therefore it's not like the buffer cache which can hold a copy of the buffer over time.

Ric Van Dyke
Hotsos Enterprises
Cell 248-705-0624

---

Hotsos Symposium, be there:
http://www.hotsos.com/portal/events/SYM06

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fedock, John (KAM.RHQ)
Sent: Tuesday, March 07, 2006 1:27 PM
To: chris_at_thedunscombes.f2s.com; oracle-l_at_freelists.org Subject: RE: Surprising parameters for direct path read in 100046 trace

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Chris Dunscombe Sent: Tuesday, March 07, 2006 5:15 AM
To: oracle-l_at_freelists.org
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

--

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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 08 2006 - 00:41:02 CST