| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: parallel query
DA Morgan wrote:
> 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.
>
Yes, a very valid concern, especially if this were a 32 bit install on Windows. A large sort_area_size will quickly drive the Oracle process to the 2GB maximum per process on 32 bit Windows. This is not something that one would want to learn the hard way :-) Oracle running on 64 bit operating systems with a lot of RAM fixes this problem.
For those who may be reading this post, it is important to realize that sort_area_size defines the maximum memory per sort - a single user could easily require several times the sort_area_size for sorting operations. This becomes problematic when there are 150+ sessions, several of which have been logged in for weeks.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Jul 10 2006 - 17:48:16 CDT
 |
 |