RE: Very unstable execution plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 6 Jun 2013 09:54:46 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D4B2C_at_exmbx06.thus.corp>

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 Thu Jun 06 2013 - 11:54:46 CEST