Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!nntp.flash.net!news.tele.dk!news.tele.dk!small.news.tele.dk!npeer.de.kpn-eurorings.net!rz.uni-karlsruhe.de!feed.news.schlund.de!schlund.de!news.online.de!not-for-mail From: Harald Maier Newsgroups: comp.databases.oracle.server Subject: Re: This small query kills oracle 9.2.0.3 (nightmare) Date: Tue, 09 Sep 2003 07:55:58 +0200 Organization: 1&1 Internet AG Lines: 18 Message-ID: References: <412ebb69.0309080550.66c6ac39@posting.google.com> <1efdad5b.0309080836.62cb12ab@posting.google.com> <412ebb69.0309081450.39d995f3@posting.google.com> Reply-To: Harald Maier NNTP-Posting-Host: p5088e5e0.dip0.t-ipconnect.de Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: online.de 1063086958 693 80.136.229.224 (9 Sep 2003 05:55:58 GMT) X-Complaints-To: abuse@einsundeins.com NNTP-Posting-Date: Tue, 9 Sep 2003 05:55:58 +0000 (UTC) User-Agent: Gnus/5.1003 (Gnus v5.10.3) Emacs/21.3 (gnu/linux) Cancel-Lock: sha1:bbfNLQkk408H5YviRaV+KCXy4Nw= Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242667 andkovacs@yahoo.com (Andras Kovacs) writes: > Actually this small query is used by dbms_stats.gather_table_stats(). > I agree otherwise it doesn't have sense. I have forgotten to remove > the 1980 partition. That's only a small detail. > > Finally we managed to isolate the problem. > The problem is with sort_area_size and sort_area_retained_size. > They are large 80M and 40M. Some queries retrieve 500M data. At > Oracle nobody paid attention to them until this morning. On Oracle > 9 these parameters (on our system) don't work very well. We had to > set pga_aggregate_target instead. I read an Oracle note saying that > parameters like "_area_size" should not be used from version 9. I do not understand. Is the problem solved with the pga_aggregate_target and the workarea_size_policy parameters? Harald