Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news2.euro.net!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: This small query kills oracle 9.2.0.3 (nightmare) Date: Tue, 9 Sep 2003 20:07:36 +0100 Lines: 92 Message-ID: References: <412ebb69.0309080550.66c6ac39@posting.google.com> <1efdad5b.0309080836.62cb12ab@posting.google.com> <412ebb69.0309081450.39d995f3@posting.google.com> <412ebb69.0309091000.68f4cd2c@posting.google.com> NNTP-Posting-Host: jlcomp.demon.co.uk X-Trace: news.demon.co.uk 1063134428 4916 158.152.75.41 (9 Sep 2003 19:07:08 GMT) X-Complaints-To: abuse@demon.net NNTP-Posting-Date: Tue, 9 Sep 2003 19:07:08 +0000 (UTC) X-Priority: 3 x-mimeole: Produced By Microsoft MimeOLE V5.50.4522.1200 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242716 Do you mean that you have run exactly this query on the table and crashed the session or system ? How about the equivalent distinct() counts on the other columns. Have you tried running the query one partition at a time (purely as a test, not for a useful result) to find out roughly what the result may be compared to the number of rows in the partitions/table. The thing about PGA_AGGREGATE_TARGET is that the code that uses it seems to have a higher level view of events. In a query that requires several sorts to run concurrently, every single sort in the query could acquire the sort_area_size, and even if the sorts were sequential (as in a simple sort-merge join) each sort will still hold a sort_area_retained_size. Under the automatic work_area_policy (using pga_aggregate_target) the code seems to be a lot smarter about recognising that there will be multiple sort (or hash) events going on in a single query, and therefore __effectively__ scales back if necessary to ensure that each individual sort (hash) does not grab an excessive amount of memory. In any case, any one session is limited to a maximum of 5% of the pga_aggregate_target, and a parallel query is limited to 30% of the target. This is, perhaps, what is protecting you from exhausting memory and crashing the session/system. On top of all that, it is perfectly feasible that in the change from 8 to 9 you hit a 'little' bug in the analytic code, or sort code, or partitioning code, or parallel code, that was really the key factor in causing your crash. BTW - what crashed, and what was the error message, you didn't say in the original post. -- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____Finland__September 22nd - 24th ____Norway___September 25th - 26th ____UK_______December (UKOUG conference) Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____USA__October ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Andras Kovacs" wrote in message news:412ebb69.0309091000.68f4cd2c@posting.google.com... > 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 ....