RE: 11gr2 sql issue
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "ksmadduri@xxxxxxxxx" <ksmadduri@xxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Wed, 9 Nov 2011 14:18:23 -0500
As Greg and Mark said, your question is very general and thus hard to answer.
It's also been said that attachments doesn't make it onto the list email so
attaching stuff that is too big and won't fit a cut&paste and hoping for the
best won't change anything either.
Short of begin able to see what the plan is doing (and probably have time to
interpret anyway if it's so big you can't cut&paste it) it sounded like in 11.1
the query was doing a hash join to the remote table and therefore probably only
going across the db link once. In 11.2 it sounded like that had been changed to
run later in the plan and via a Nested Loop so now it has to hit the db link
once for every row returned by the driving table/result set. That sounds like
the culprit to me. Going across the db link many times adds up in the wait
event you were pasting.
Thanks,
Finn
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kumar Madduri
Sent: Wednesday, November 09, 2011 8:43 AM
To: Mark W. Farnham
Cc: greg@xxxxxxxxxxxxxxxxxx; oracle Freelists
Subject: Re: 11gr2 sql issue
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@xxxxxxxx> 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@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
> > 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@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>
> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
>>> This e-mail and any attachments are confidential, may contain legal,
>>> professional or other privileged information, and are intended solely for
>>> the addressee. If you are not the intended recipient, do not use the
>>> information in this e-mail in any way, delete this e-mail and notify the
>>> sender. CEG-IP1
--
//www.freelists.org/webpage/oracle-l
Other related posts:
