Re: Dynamic Memory in 9i and Sort Area Size, why is it small

"Niko" <nick_wakefield_at_hotmail.com> wrote in message news:9da94cd1.0205161100.271d7c62_at_posting.google.com...
> I previously worked with 8i and set all the parameters by hand. Now I
> am considering running oracle 9i for our data warehouse but when I set
> the dynamic memory allocation up I get a very small sort_area_size,
> which is not good for the type of queries I would run.
>
> I though I had to just set the following parameters
>
> SGA_MAX_SIZE
> WORKAREA_SIZE_POLICY=auto
> PGA_AGGREGATE_TARGET
>
> but the sort_area_size gets reported as being only 64k.

What it gets reported as is irrelevant (assuming you have made sure NOT to set sort_area_size itself, which over-rides the automatic handling feature). The point is that everyone starts out with zero sort_area_size anyway, regardless of what the SORT_AREA_SIZE parameter is set to, and that was true in 8 and 8i too. It's what happens as sorts start that's important: and in your case, with workarea_size_policy set to AUTO, and with a decent PGA_aggregate_target, the sort size will grow as load is placed on it by the activities of the user.

There are a bunch of new statistics available in views such as v$sesstat, v$mystat and v$sysstat to help you see what is really happening with auto-managed PGA. In particular, have a look at 'workarea memory allocated'. Far more reliable.

>I am wrong in
> thinking that if I set the pga target this will allocate X amount of
> memory per user process and the sga will allocate x amount to be
> shared and oracle will then managing block buffers, sort area and all
> that itself.
>

You are indeed wrong. Setting PGA auto-management on has not the slightest bearing on what happens to your SGA, since the PGA is not a component of the SGA. You are also wrong in thinking that setting PGA auto-management on means that any fixed amount of memory is allocated per user process. Firstly because user processes don't get allocated PGA memory at all (server processes do). Secondly because the whole point of auto-PGA is that no fixed allocations are made, but dynamic allocations are made and reclaimed as Oracle deems appropriate.

Just make sure you haven't set your own values for sort_area_size (or indeed any of the other _area_size parameters, such as bitmap_merge_area_size), and all will be well.

Regards
HJR
> Also any though on how I should set the parameters for a 2gb box with
> only 3 concurrent users at a time for a 50gb warehouse on 9i.
>
> TIA
Received on Fri May 17 2002 - 19:40:35 CDT