Oracle 9.2 PGA_Aggregate_target and a statistics question

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.

Thinking about it a bit more, then it wouldn't take all of delta-PAT at run-time, but only whatever was selected by the optimizer as the cheapest plan....

there seem to be a fair few metalinks on the issue....

Other things I like :
storage mapping
All the sampling and gather stats improvements (although the docs on the DYNAMIC SAMPLE hint are awful - and it would be a bad day if SAMPLE(10) level had to be used) , and auto/skewonly histograms are a top notch idea , Remember, gang, ANALYZE is being deprecated .... DBMS_STATS is the way to go
.

T

T. Received on Thu Jul 11 2002 - 10:06:15 CDT