Re: Oracle 9.2 PGA_Aggregate_target and a statistics question

I have a note from somewhere (which might be the manual) that no process is allowed more than 5% of the total PGA_AGGREGATE_TARGET.

I believe that Oracle also has an algorithm for being parsimonious with memory if the P_A_T is under pressure.

Apart from that, it's still on my todo list.

Possibly oracle works out for each join the minimum memory required for 100% in-memory execution, the minimum memory required for a one-pass disc execution and the minimum for a two-pass execution and decides which it can afford. But that begs the question of handling a complex multi-layer hash, or a hash feeding a sort (group by) or sort (order by).

The biggest problem I find with the 10053 is that the more features Oracle gets, the less useful the 10053 becomes.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK                  July / Sept
        Australia         August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Telemachus wrote in message ...


>PGA_AGGREGATE_TARGET : wonderful ! at last !

>

>

>... however I have a question ...

>

>Correct me if I'm wrong

>

>In the following PGA_AGGREGATE_TARGET = PAT

>1. premise :

>SORT_AREA_SIZE and HASH_AREA_SIZE are base inputs to the query optimization

>process so it can figure out how many passes etc etc, which table can fit

>and so on .

>

>2. premise :

>if these are not now directly specified , then it must use some fraction or

>the whole of PAT(minus of course whatever else is hogging PAT from everyone

>else's queries - for simplicity let's call the available bytes  delta-PAT)

>

>questions :

>Is all of delta-PAT considered as the base input to both of these

parameters


>or does anyone know what the fraction is. ?

>My ev10053 reading skills have never been very good.   The reason I ask is

>it's an interesting subtle change to the query optimization process, since

>now sort joins and hash joins have the potential to get a hell of a lot

more


>memory and we may come 'right down to the wire' finally for the final

>decision.