RE: 11gr2 sql issue

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <greg@xxxxxxxxxxxxxxxxxx>, "'Kumar Madduri'" <ksmadduri@xxxxxxxxx>
  • Date: Wed, 9 Nov 2011 00:29:14 -0500

I tend to agree with Greg's lack of clarity.

Is that event interesting to you because you're wondering if underestimating
the cost of repeatedly sucking data through a straw across a dblink is a
common plan regression from to 11.2?

If that is indeed common, I could see it being interesting if there is also
a consistent way to eliminate the plan regression for some class of queries.
That might help Oracle figure out a fix, or help you preemptively fix the
similar queries en masse instead of waiting for them to be a problem one at
a time (some probably cruising below the radar but burning more resources
than previously.)

Or something else I'm missing?

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Greg Rahn
Sent: Tuesday, November 08, 2011 10:54 PM
To: Kumar Madduri
Cc: oracle Freelists
Subject: Re: 11gr2 sql issue

I'm unclear of what question you seek the answer to.  Are you trying to find
out why there was a plan regression and how to resolve it or are you just
curious about a bunch of wait events (symptoms) from the bad plan - which
seems a lot less interesting and useful to me given the root cause is really
what I'd be looking for/at.

On Tue, Nov 8, 2011 at 7:37 PM, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
> Greg
> I did explain plans too and there were changes  (i thought i mentioned 
> this..). We just took a 10053 trace to see if we can get any other 
> useful information as originally when the developer ran this from TOAD 
> the sql never completed. We did not even have a sql id at that time.
> But what striked me was this event. The same query runs fine when I 
> use the ordered hint or set the optimizer_features_enabled to 
> and the event no longer appears I was not mentioning about 10053 trace 
> being useful though but se.sql .
> Explain plan does show differences obviously.
> On Tue, Nov 8, 2011 at 7:30 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
>> I'd disagree that this is useful -- the most useful information with 
>> plan changes is simply the full plans (dbms_xplan) and you dont even 
>> need a 10053 trace for that.
>> On Tue, Nov 8, 2011 at 7:05 PM, Kumar Madduri <ksmadduri@xxxxxxxxx>
>> > Hi
>> > As part of regression testing in our db upgrade from to 
>> >, one of the developers reported a slow sql (attached 
>> > slow_sql.sql) We did a 10053 trace and noticed one of the things 
>> > was wip_entities was not doing a Index scan in 11gR2 vs 11gR1.
>> > But what is most useful till this point is Tanel's se.sql script 
>> > which reports this
>> --
>> Regards,
>> Greg Rahn

Greg Rahn


Other related posts: