RE: Very unstable execution plan
Date: Fri, 7 Jun 2013 18:18:50 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D4F15_at_exmbx06.thus.corp>
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
Received on Fri Jun 07 2013 - 20:18:50 CEST