Newsgroups: comp.databases.oracle.misc Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!elnk-pas-nf1!elnk-nf2-pas!newsfeed.earthlink.net!newshub.sdsu.edu!newsfeed.news2me.com!newsfeed2.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed3.dallas1.level3.net!news.level3.com!news.sanjose1.Level3.net!Level3.net!news.indiana.edu!news.er.usgs.gov!news From: Brian Peasland Subject: Re: parallel query In-Reply-To: <1152538755.802448.285950@p79g2000cwp.googlegroups.com> X-Nntp-Posting-Host: edc-cv-160-53.cr.usgs.gov Content-Type: text/plain; charset=ISO-8859-1; format=flowed Message-ID: User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) Sender: news@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739) Content-Transfer-Encoding: 7bit Organization: U.S. Geological Survey, Reston VA References: <1152538755.802448.285950@p79g2000cwp.googlegroups.com> Mime-Version: 1.0 Date: Mon, 10 Jul 2006 16:02:57 GMT Lines: 43 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:128401 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@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