Re: KEEP pool and disk reads
- From: Rajesh.Rao@xxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 27 Jun 2004 13:03:53 -0400
Thanks Jonathan, for your valuable insight.
When I altered the session to have the same 10Mb values for sort_area_size
and sort_area_retained_size, the disk_reads fell to 0.
Thanks once again.
Regards
Raj
"Jonathan Lewis"
<jonathan@xxxxxxxxx To: <oracle-l@xxxxxxxxxxxxx>
mon.co.uk> cc:
Sent by: Subject: Re: KEEP pool and
disk reads
oracle-l-bounce@fre
elists.org
06/27/2004 06:03 AM
Please respond to
oracle-l
It's perfectly feasible to do a sort in memory
and still record writes and reads on the temp
tablespace.
You've done roughly 5MB of sorting, after the
sort is complete, Oracle is at liberty to dump
most of the sorted data to disc to conserve
memory whilst returning the result set in chunks
dictated by your fetch array size.
(This was the most significant feature of the
old sort_area_retained_size - the excess of
sort_area_size over sort_area_retained_size
was dumped to disc, and Oracle held open
sort_area_retained_size for feeding the
data from disc to the front-end (or next
step in the execution path)).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: <Rajesh.Rao@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, June 26, 2004 5:56 PM
Subject: Re: KEEP pool and disk reads
Jonathan / Mladen,
I did perform a 10046 trace, and as expected saw no sequential or scattered
read waits. Instead saw some direct path read and direct path write waits
in the trace file, as under :
WAIT #3: nam='direct path write' ela= 13255 p1=201 p2=6793 p3=125
WAIT #3: nam='direct path write' ela= 12945 p1=201 p2=6665 p3=125
WAIT #3: nam='direct path write' ela= 14130 p1=201 p2=6537 p3=125
WAIT #3: nam='direct path write' ela= 15772 p1=201 p2=6409 p3=125
WAIT #3: nam='direct path write' ela= 12162 p1=201 p2=6281 p3=116
Given that db_files is set to 200, the p1 value of 201 points to the
datafile belonging to my TEMP tablespace. I was not able to correlate the
628 disk reads shown in the autotrace, with the p3 values in the trace
files.
What are these "direct path write" waits? For, autotrace shows me all sorts
are happening in memory. Then, what are these writes on the TEMP
tablespace?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
