Re: 11gr2 sql issue

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Wed, 9 Nov 2011 05:43:14 -0800
Message-ID: <CAHDOOG6syY=dokCuEamAX7jJnEhh-hSOCx1PTytqsaudtF-utA_at_mail.gmail.com>

Hi
Hope the attachments with the explain plans makes it (it was too big to be pasted).
With OFE set to 11.1.0.7 REMOTE step is step 22 and with the OFE set to 11.2.0.2 REMOTE step is 39.d
If I use the ordered hint the remote step is moved further above. This (ordered hint sql) seems to be the fastest. I am trying to understand these differences
Thank you
Kumar

On Tue, Nov 8, 2011 at 9:29 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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 therae 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 Wed Nov 09 2011 - 07:43:14 CST