Re: parallel query

Prasath wrote:
> How do I avoid the sort being written to disk? Which parameter needs
> to be changed?
>
> I am using Oracle 9iR2. Since the workarea_size_policy is set to AUTO,
> all the _size parameters become redundant.
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 343188 consistent gets
> 331539 physical reads
> 0 redo size
> 20273 bytes sent via SQL*Net to client
> 859 bytes received via SQL*Net from client
> 86 SQL*Net roundtrips to/from client
> 5 sorts (memory)
> 2 sorts (disk)

You have 331,539 physical reads which is most likely contributing more to performance problems than 2 sorts to disk. Are you sure that you are looking at the correct location for your performance problem?

If you have WORKAREA_SIZE_POLICY set to auto and your PGA_AGGREGATE_TARGET defined, you can use the and V$PGA_TARGET_ADVICE views to tune your PGA_AGGREGATE_TARGET parameter.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown