Have not had the chance to research this but when I read your message I saw that the numbers might make sense if there is nothing else missing.
You are saying the "cost / pass 18" when it was 1M. Do not you think that '18' is the cost for one pass of memory sort in 1M of memory? If the answer is yes, then five passes of memory sort/1M each will cost 90 (5 * 18) while one pass of memory sort in 5M of memory will cost 35. What do you think? Regards, Waleed -----Original Message----- To: Multiple recipients of list ORACLE-L Sent: 2/16/03 3: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: Khedr, Waleed 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).
