RE: 11gr2 sql issue

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 9 Nov 2011 00:29:14 -0500
Message-ID: <03d901cc9ea0$85c5caf0$915160d0$_at_rsiz.com>

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 11.1.0.7 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_gmail.com> 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 11.1.0.7
> 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_at_structureddata.org> 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_at_gmail.com>
wrote:
>> > Hi
>> > As part of regression testing in our db upgrade from 11.1.0.7 to
>> > 11.2.0.2, 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
>> http://structureddata.org
>
>

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l


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

Received on Tue Nov 08 2011 - 23:29:14 CST