Message-ID: <51B02DD4.20506@mgm-tp.com>
Date: Thu, 06 Jun 2013 08:36:04 +0200
From: Thomas Kellerer <thomas.kellerer@mgm-tp.com>
Organization: mgm technology partners GmbH
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
To: "oracle-l@freelists.org" <oracle-l@freelists.org>
Subject: Re: Very unstable execution plan
References: <51AEE7BF.2020709@mgm-tp.com> <CE70217733273F49A8A162EE074F64D90D48B1@exmbx06.thus.corp>
In-Reply-To: <CE70217733273F49A8A162EE074F64D90D48B1@exmbx06.thus.corp>
Content-type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: thomas.kellerer@mgm-tp.com

> The answer to the first part of your posting is just so cute I think
> I'll have to emulate it and write a blog about it (though it might be
> nice to see the plan, including predicate section, as pulled from
> memory by dbms_xplan). The plan is "the same" - but the later steps
> involve nested loop joins into tmp_vu_sparte by index idx_tmp_sparte
> - I'll bet the order of joining to those two copies have changed, so
> the predicates involved are different, which is why the numbers of
> rows (hence CR gets, hence CPU time) is so different.>

Yes your assumption is correct as it seems.

Now the question is: how can we convince the optimizer to do it always right?

> For the second part, check the predicate section of the plan for implicit conversions before you do anything else.

That's the first thing I checked. There are only comparisons on columns with the same datatype involved.
Not parameters (or literals) at all.

Regards
Thomas

--
http://www.freelists.org/webpage/oracle-l