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" <ORACLE-L_at_fatcity.com>
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 <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: Tim Gorman
INET: Tim_at_SageLogix.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).
Received on Sun Feb 16 2003 - 16:23:46 CST
