Did the increase in SORT_AREA_SIZE change the execution plan, regardless of the use of hints (since certain hints can easily be ignored)? Larger SORT_AREA_SIZE might encourage the CBO to choose a SORT-MERGE join, for example...
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, February 16, 2003 1:38 PM > > Coincidentally, one of the points I mentioned at the > Hotsos Symposium was the increasing the sort_area_size > could affect execution paths for the worse. (Even when > there is no risk of excess memory usage causing swapping). > > I was going to post a simple example to demonstrate this - > and then cane across a really bizarre result in 8.1.7.4 > and 9.2.0.2 - > > Using EXACTLY the same script to generate and report > data, and hinting EXACTLY the same execution path, > and running the 10053 trace against it, I built an example > where the optimizer cost of sorting went UP when I increased > the sort_area_size from 1M to 5M for a particular query. > The 10053 trace showed: "cost / pass 18" when s_a_s > was 1M, and " cost / pass 35" when s_a_s was 5M - > when everything else was exactly the same. > > > BTW - your statistics would suggest to me that I > needed to find out what bits of code were doing so > much sorting - and see if I could address the problem > at source, rather than fiddling with database parameters. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Coming soon one-day tutorials: > Cost Based Optimisation > Trouble-shooting and Tuning > Indexing Strategies > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > ____UK_______March 19th > ____USA_(FL)_May 2nd > > > Next Seminar dates: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > > ____USA_(CA, TX)_August > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > -----Original Message----- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: 14 February 2003 17:54 > hint > > > >I changed my sort_area_size to 1M (down from 5M) and the query > completed in 18 seconds. > > > >We had set sort_area_size to 5M at the suggestion of Oracle or other > reasons. Looks like it's time to set it back. > > > >I ran the disk_sorts query and it returned this: > > > >DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT > >---------- ------------ --------------- > > 47073 23815K 826 > > > >Doesn't this suggest setting sort_area_size larger? > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
