Re: Very unstable execution plan

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Sat, 8 Jun 2013 22:44:56 +0800
Message-ID: <CAM_ddu8euCdj4skY4vYjQJuRxhR2cu0FQVoNHBTu_bpHSbO9SQ_at_mail.gmail.com>

Kerry Osborne have a post on such a case recently. To seek for plan stability, consider using sql profile/sql plan baseline to force the sql to use dynamic sampling, so you dont need to change the original sql. http://kerryosborne.oracle-guy.com/2013/06/sql-gone-bad-but-plan-not-changed/

On Sat, Jun 8, 2013 at 2:18 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:

>
> Thomas,
>
> Any progress ?
> I'm still curious about the german index problem.
>
> How much change are you allowed to make to the problem query to stabilise
> it ? If nothing else, putting in a couple of hints in the outer select
> addressing the content of the view and telling Oracle to do dynamic
> sampling (level 2 may be sufficient) on the two copies of TMP_VU_SPARTE may
> be enough to sort the problem. You'll probably want to get the outline
> dumped from a call to dbms_xplan.display_cursor() to get the correct query
> block names and aliases first.
>
> I've written up a blog note emulating the problem:
> http://jonathanlewis.wordpress.com/2013/06/07/same-plan/
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: Jonathan Lewis
> Sent: 06 June 2013 10:54
> To: huanshengchen_at_gmail.com; thomas.kellerer_at_mgm-tp.com
> Cc: oracle-l_at_freelists.org
> Subject: RE: Very unstable execution plan
>
> Sidney,
> Column group statistics won't help in this case - they're only effective
> for equalities, and the joins involving two of the columns are range-based
> (>, <, etc).
>
> Thomas,
>
> I didn't see the attachment with the plans and predicates that you
> mentioned in an earlier post. Can you post them in-line. (All three - with
> stats, without stats, and with "german" index). The complete definition of
> the view would also be helpful - the section you sent didn't give us any
> clues about how pop_info was joined.
>
> As far as stats go - I've often seen Oracle do better without than with;
> but more specifically I've seen it to better if you get rid of histograms.
> How are you collecing stats on the critical table, and what do the stats
> look like ?
>
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Sidney Chen [huanshengchen_at_gmail.com]
> Sent: 06 June 2013 09:09
> To: thomas.kellerer_at_mgm-tp.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: Very unstable execution plan
>
> The cardinality for both plan is not correct, look at the card field for
> the IDX_TMP_VU_SPARTE and TMP_VU_SPARTE. it's card = 1. Seems it's due to
> the combination of 3 column in the predicate(VERTRAG_EXT_REF,
> DEKLARATION_GUELTIG_BIS, DEKLARATION_GUELTIG_VON). The default statistics
> can not handle such cardinality well for combined columns predicate.
> Dynamic sampling is good in such case, otherwise, you may want to create
> extended statistics on (VERTRAG_EXT_REF, DEKLARATION_GUELTIG_BIS,
> DEKLARATION_GUELTIG_VON) and (VERTRAG_EXT_REF, DEKLARATION_GUELTIG_VON,
> DEKLARATION_GUELTIG_BIS).--
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards
Sidney Chen


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jun 08 2013 - 16:44:56 CEST