True - If work_area_policy = auto in 9.2, then the pga_aggregate_target is used. Since this defaults to 25M (better check that) and the limit for a process is 5% of total, then the v9 run is probably trying to do its sort with 1.25M, rather than the 90M used by 8i. This would tend to explain a dramatic increase in the run time. (But it's a little surprising that the estimated cost doesn't really indicate this). The session statistics for the v9 run should show the 'workarea usage - multipass' count go up each time this query runs. According to the Steve Adams' formula, the required memory for a one-pass sort would be about 3M, rather than 1.25M (Based on the assumption that the BYTES reported by the execution plan is close to correct, and the configuration is 8K blocks and default sort_multiblock_read_count). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland June 2004 UK - Optimising Oracle Seminar ----- Original Message ----- From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 02, 2004 9:08 AM Subject: RE: query slow in 9i, but not slow in 8i I wondered about pga_aggregate_target as well? My impression was that if you set the new parameters and the old ones the new ones took precedence (ie sas etc were ignored). I haven't tested this. Niall Litchfield Oracle DBA Audit Commission +44 117 975 7805 ---------------------------------------------------------------- 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 -----------------------------------------------------------------