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