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

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 <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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

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).