Re: Optimizer help, get query to run as good as with RULE hint

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).