Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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" - UnknownReceived on Mon Jul 10 2006 - 11:02:57 CDT
![]() |
![]() |