Re: parallel query

Prasath wrote:
> thank you very much for the reply.
> i added 'parallel' hint on the SELECT query and I see that the timing
> has reduced by 40% and the cost has reduced by half.
>
> but the worrying thing is the 2 sorts in the disk. i was wondering how
> this can be eliminated? if i am right, this might happen due to the
> lack of sufficient memory. if i had set workarea-size_policy to AUTO
> and pga_aggregate-size to 2GB, then oracle allocates only 5% of the
> 2GB, that is 100MB to the session for sorting. is there any way of
> improving this by reconfiguring?

The cost that you are seeing is just an estimated cost made by Oracle (extensive detail in Jonathan's book). The real cost, or more correctly the performance penalty, can be determined by examining a 10046 trace file and/or a 10053 trace file. These trace files will tell you if what you are looking at is worth your time to tune.

You can adjust the SORT_AREA_SIZE parameter at the session level for testing purposes to determine the memory required to avoid a disk sort (it is likely far less than 100MB). The SORT_AREA_SIZE parameter determines the minimum for the maximum amount of memory to use before sorting to disk when WORKAREA_SIZE_POLICY is set to auto. Jonathan's book recommends that SORT_AREA_RETAINED_SIZE should be the same value as SORT_AREA_SIZE - this does not agree with the information in other performance tuning books that suggest that as SORT_AREA_SIZE is increased, SORT_AREA_RETAINED_SIZE should be decreased, which helps reduce memory consumption. Jonathan provides very sound evidence for his advice.

As has been hinted in this tread, be careful about setting SORT_AREA_SIZE to too large of a value.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jul 11 2006 - 07:36:45 CDT