Re: Very unstable execution plan

  • From: Rajiv Iyer <raju.rgi@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 6 Jun 2013 15:55:30 +0530

I think the table tmp_vu_sparte holds records for each day and different
timings for that day. You are probably looking for each day data where the
timings are the earliest. I guess probably using a GROUP BY in the
underlying view might avoid the need to self join.


On Thu, Jun 6, 2013 at 12:06 PM, Thomas Kellerer <thomas.kellerer@xxxxxxxxxx
> wrote:

> > 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
> --
> //


Other related posts: