Charles Hooper wrote:
> DA Morgan wrote:
>> Charles Hooper wrote:
>>
>>> I noticed a similar trend in 10g R2 when the workarea_size_policy is
>>> set to auto, and the pga_aggregate_target is set to 2GB. These are
>>> likely single pass sorts to disk are are being recorded. I was able to
>>> virtually eliminate the sorts to disk on my system by setting
>>> sort_area_size to 20M
>> How many simultaneous users on the system?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> When I was seeing the above, there was only one user in the database at
> the time. I was in the process of setting the database server up, and
> testing various parameters to obtain the best performance from the
> server - got to love the efficiency of database platform independent
> applications. Even if it was not significantly hurting performance, I
> don't care to see disk sorts.
>
> Jonathan Lewis mentioned in his Cost-Based Oracle Fundamentals book
> that Oracle places limits on a single work area to 5% of the
> pga_aggregate_target (100MB is 5% of 2GB), however cost calculations
> are based on sort_area_size, which defaults to _smm_min_size, which
> defaults to 1MB.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Thanks. My main concern was considering how much RAM it would take if
Oracle reserved that much sort space for every connected user.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jul 10 2006 - 17:06:32 CDT
