From: Jonathan Lewis <jonathan@jlcomp.demon.co.uk>
To: "huanshengchen@gmail.com" <huanshengchen@gmail.com>,
 "thomas.kellerer@mgm-tp.com" <thomas.kellerer@mgm-tp.com>
CC: "oracle-l@freelists.org" <oracle-l@freelists.org>
Subject: RE: Very unstable execution plan
Thread-Topic: Very unstable execution plan
Date: Thu, 6 Jun 2013 09:54:46 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D4B2C@exmbx06.thus.corp>
References: <51AEE7BF.2020709@mgm-tp.com>
 <CE70217733273F49A8A162EE074F64D90D48B1@exmbx06.thus.corp>
 <51B02DD4.20506@mgm-tp.com>,<CAM_ddu_wEOZ8xP7GVC9UEtnuXJiHrnieuJ=3aYKKstZ_7r4=SA@mail.gmail.com>
In-Reply-To: <CAM_ddu_wEOZ8xP7GVC9UEtnuXJiHrnieuJ=3aYKKstZ_7r4=SA@mail.gmail.com>
Accept-Language: en-GB, en-US
Content-Language: en-GB
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jonathan@jlcomp.demon.co.uk


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@freelists.org [oracle-l-bounce@freelists.org] on behalf of Sidney Chen [huanshengchen@gmail.com]
Sent: 06 June 2013 09:09
To: thomas.kellerer@mgm-tp.com
Cc: oracle-l@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