Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: parallel query

Re: parallel query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 10 Jul 2006 13:50:06 -0700
Message-ID: <1152564606.544441.106320@m79g2000cwm.googlegroups.com>


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. Received on Mon Jul 10 2006 - 15:50:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US