| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: KEEP pool and disk reads
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_at_jlcomp.de To: <oracle-l_at_freelists.org>
mon.co.uk> cc:
Sent by: Subject: Re: KEEP pool and disk reads
oracle-l-bounce_at_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/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
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?
 |
 |