Re: Very unstable execution plan
- From: Sidney Chen <huanshengchen@xxxxxxxxx>
- To: thomas.kellerer@xxxxxxxxxx
- Date: Thu, 6 Jun 2013 16:09:37 +0800
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).
On Thu, Jun 6, 2013 at 2:36 PM, Thomas Kellerer
<thomas.kellerer@xxxxxxxxxx>wrote:
> > The answer to the first part of your posting is just so cute I think
> > I'll have to emulate it and write a blog about it (though it might be
> > nice to see the plan, including predicate section, as pulled from
> > memory by dbms_xplan). The plan is "the same" - but the later steps
> > involve nested loop joins into tmp_vu_sparte by index idx_tmp_sparte
> > - I'll bet the order of joining to those two copies have changed, so
> > the predicates involved are different, which is why the numbers of
> > rows (hence CR gets, hence CPU time) is so different.>
>
> Yes your assumption is correct as it seems.
>
> Now the question is: how can we convince the optimizer to do it always
> right?
>
> > For the second part, check the predicate section of the plan for
> implicit conversions before you do anything else.
>
> That's the first thing I checked. There are only comparisons on columns
> with the same datatype involved.
> Not parameters (or literals) at all.
>
> Regards
> Thomas
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards
Sidney Chen
--
//www.freelists.org/webpage/oracle-l
Other related posts:
