Re: This small query kills oracle 9.2.0.3 (nightmare)

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" <andkovacs_at_yahoo.com> wrote in message
news:412ebb69.0309091000.68f4cd2c_at_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 ....