Re: 10gR2: Why TEMP TS & disk sorts if pga_aggregate_target = 700mb?

On Jan 23, 8:59 pm, "vlegaspi" <vlega..._at_gmail.com> wrote:
> > If the sorts to disk bother you, set SORT_AREA_SIZE to a value to
> > specify the smallest maximum amount of memory that can be used during a
> > single sort - Oracle can automatically use more memory than what is
> > specified. Care must be taken if you chose to specify SORT_AREA_SIZE,
> > if there are many sessions that will be connected to the database at
> > the same time.- Hide quoted text -- Show quoted text -
> I thought if I set workarea_size_policy = AUTO, Oracle will simply
> ignore all my SORT_* parameters?
>

The writing in the Oracle documentation leaves a bit to be desired when discussing the WORKAREA_SIZE_POLICY and the impact of SORT_AREA_SIZE. Worse yet, some people have published their opinions of how these parameters work together without testing the actual behavior. If you set the SORT_AREA_SIZE to 20MB, when WORKAREA_SIZE_POLICY is set to AUTO, Oracle will provide at least 20MB for individual sorting operations (I am not suggesting 20MB, just using that number as an example). Evidence of the SORT_AREA_SIZE can be seen in a 10053 trace file.

See this article for additional information: http://www.jlcomp.demon.co.uk/untested.html

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jan 23 2007 - 20:24:09 CST