Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!logbridge.uoregon.edu!newsfeed.vmunix.org!news1.optus.net.au!optus!spool01.syd.optusnet.com.au!spool.optusnet.com.au!not-for-mail From: "Noons" Newsgroups: comp.databases.oracle.server References: <412ebb69.0309080550.66c6ac39@posting.google.com> <1efdad5b.0309080836.62cb12ab@posting.google.com> <412ebb69.0309081450.39d995f3@posting.google.com> Subject: Re: This small query kills oracle 9.2.0.3 (nightmare) Date: Tue, 9 Sep 2003 21:30:01 +1000 Organization: nunovyabizness Pty Ltd X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Lines: 27 Message-ID: <3f5db9fb$0$14558$afc38c87@news.optusnet.com.au> NNTP-Posting-Host: 211.29.173.224 X-Trace: 1063107067 14558 211.29.173.224 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242683 "Andras Kovacs" wrote in message news:412ebb69.0309081450.39d995f3@posting.google.com... > 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. That's baaaad news in a way... Imagine someone just plonking their 9i on top of 8 and not going through the motions of getting rid of any superfluous parameters! I'm reminded of quite a few 3rd-party apps that turn on the weirdest stuff in init.ora! All care is needed. Not a good idea, these major "changes" in parameter requirements. If they are not really needed, then it's much better to just "disable" them. Rather than having them mess up perfectly working systems. -- Cheers Nuno Souto wizofoz2k@yahoo.com.au.nospam