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@xxxxxxxxxxxxxxxxxx>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@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 > > > -- Regards Sidney Chen -- //www.freelists.org/webpage/oracle-l