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: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Mon, 10 Jul 2006 16:02:57 GMT
Message-ID: <J27394.27L@igsrsparc2.er.usgs.gov>


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
Received on Mon Jul 10 2006 - 11:02:57 CDT

Original text of this message

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