RE: Very unstable execution plan

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Jun 2013 18:18:50 +0000

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@xxxxxxxxx; thomas.kellerer@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Sidney Chen [huanshengchen@xxxxxxxxx]
Sent: 06 June 2013 09:09
To: thomas.kellerer@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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).--
//www.freelists.org/webpage/oracle-l


Other related posts: