Steve,
You might also want to test setting
optimizer_max_permutations < 80000.
By design, the CBO considers multiple starting tables
and restricts permutations during OR expansion when
optimizer_max_permutations < 80000. This can have a
significant impact on queries that join a large number
of tables or have a large number of OR expansions.
I believe apps 11i (not sure about 11.0), for example,
recommends setting this to 79999.
HTH,
- Anita
- "Wilkes, Steve" <steve.wilkes_at_npower.com> 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: Wilkes, Steve
> INET: steve.wilkes_at_npower.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).
Do You Yahoo!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.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).
Received on Sat Jun 16 2001 - 15:45:25 CDT
