Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.linkpendium.com!nntp-relay.ihug.net!ihug.co.nz!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: andkovacs@yahoo.com (Andras Kovacs)
Newsgroups: comp.databases.oracle.server
Subject: Re: This small query kills oracle 9.2.0.3 (nightmare)
Date: 9 Sep 2003 11:00:27 -0700
Organization: http://groups.google.com/
Lines: 11
Message-ID: <412ebb69.0309091000.68f4cd2c@posting.google.com>
References: <412ebb69.0309080550.66c6ac39@posting.google.com> <1efdad5b.0309080836.62cb12ab@posting.google.com> <412ebb69.0309081450.39d995f3@posting.google.com> <bjkgfm$1fp$1$8300dec7@news.demon.co.uk>
NNTP-Posting-Host: 199.22.61.2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1063130429 31443 127.0.0.1 (9 Sep 2003 18:00:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 9 Sep 2003 18:00:29 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242711

Jonathan, you are right that there are plenty of other staffs in
dbms_stats. However the trouble is caused by this "small query" part
of the big one.

pga_aggregate_target is the parameter that should be used. Oracle will
not even talk to you if you use sort_area_size. They advised me to
read 3 notes on PGA tuning in Oracle 9.2.

However after having read the 3 notes I still don't understand what
oracle messes up when using sort_area_size. This is one of the most
basic parameters ....