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@(protected) >
To: <oracle-l@(protected) >
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.
