Re: performance impact of optimizer_search_limit

Steve,

I'm curious as the difference in execution plans that made such a drastic improvement.

My guess would be it went from a hash join to nested loops with an index.

Jared

On Monday 04 June 2001 04:55, Wilkes, Steve wrote:
> Hi,
>
> Oracle 8.0.5
> AIX 4.3.3
>
> I have improved the response time of one of my 8 table join queries by
> increasing optimizer_search_limit from the default 5 to 8. At the value of
> 8 the plan changes and has made a 10 min query run in under 5 seconds. The
> optimizer is set to CHOOSE with the statistics up to date and a sample size
> of 20%. This is a third party application and I don't have access to the
> source code to add hints. The value of optimizer_max_permutations has been
> left at 80000.
>
> I know that this makes the optimizer now check 8!=40320 permutations
> instead of 5!=120 permutations. I expect the parse time to increase but
> does anyone have any experience as to the performance impact that this may
> have? Any horror stories?
>
> Thanks in advance.
>
> Steve Wilkes
>
> _____________
> Oracle DBA
> npower
>
> =====================================================================
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> gpupower.co.uk or postmaster_at_npower.com
>
> This outgoing e-mail (and any attachments) has been checked
> (using Sophos Sweep 3.45 + patches) before leaving us (UK 08457 353637),
> and has been found to be clean from any virus infection.
>
> =====================================================================

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).