RE: performance impact of optimizer_search_limit

DOOOODES!
This parameter is session modifiable.

To wit:

SVRMGR> alter session set optimizer_search_limit=400; ORA-00068: invalid value 400 for parameter optimizer_search_limit, must be between 3 and 255

So,

IF (you='heavily certified')

        THEN (wave OCP pigskin over dbms, wait for result) ELSIF (you='DIY type')

        THEN (well....DIY)
ENDIF;
|| -----Original Message-----
|| From: Jared Still [mailto:jkstill_at_cybcon.com]
|| Sent: Monday, June 04, 2001 2:23 PM
|| To: Multiple recipients of list ORACLE-L
|| Subject: 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).
||

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohan, Ross
  INET: MohanR_at_STARS-SMI.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).