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 -----------------------------------------------------------------