Re: sql slow after db upgrade
Date: Wed, 22 Oct 2014 16:47:06 -0700
Message-ID: <CAHDOOG5dBKGe5JmTf5fmgReUAo7m0h4sSFvEfD0uGSq+vhnCPg_at_mail.gmail.com>
Thank you Carlos. I am doing that actually. I was actually going to add that infact setting _unnest_subquery was not recommeneded in the ebiz init.ora recomendation list. They suggest to remove this parameter if set.
I pointed it out because that is helping and the way I thought it might help is that the good plan was not merging the Corelated subqueries and the bad was doing that. I have an SR with Oracle with sqlxplain option (on the bad database) and sqltxtrxec option (on the good one). I will run with the SQLT XPLORE option as well now. Hi Niall:
I wanted to use option 2 but the problem with our developers is that once I tell them, they would put that and promise to look at this but never look at it. So I was trying to understand myself what is causing it and I could not find any bugs related to this as well (like John pointed).
- Kumar
On Wed, Oct 22, 2014 at 8:58 AM, Carlos Sierra <carlos.sierra.usa_at_gmail.com> wrote:
> Kumar,
>
> I suggest you do not deviate from EBS Development required list of
> parameters. With that said, then you may have a regression in one SQL, so
> you want to treat that as one isolated case, unless you see a pattern.
>
> If I were you, I would use SQLT to create an isolated test case using SQLT
> Test Case functionality, then once you can reproduce good and bad plan by
> using OFE on that SQLT TC, the use SQLT XPLORE to identify which particular
> fix of the CBO change the SQ un-nesting affecting you. SQLTXPLAIN (SQLT) is
> MOS 215187.1.
>
> If you take that path, I offer to guide you if needed.
>
> Cheers,
>
> Carlos Sierra
> carlos.sierra.usa_at_gmail.com
>
> Life is Good!
>
>
>
> > On Oct 22, 2014, at 06:58, Kumar Madduri <ksmadduri_at_gmail.com> wrote:
> >
> > Our ebusiness 12.1.2 apps database was upgraded from 11.2.0.2 to 11.2.0.4
> > As part of testing, one of the concurrent programs was running slow and
> the main difference was subquery unnesting was being done in the upgraded
> database (as seen from run time explain plan) and here the query runs for a
> long time and does not complete.
> > Workaround is to alter session set "_unnest_subquery"=false; in the
> 11.2.0.4 database and this helps.
> > I have seen several blogs where people soft installations had this as a
> prereq (setting _unnest_subquery = false) to avoid sql issues. But nothing
> related to ebusiness.
> > Plus I don't see this issue in a 11.2.0.2 database and I can
> additionally validate that by setting optimizer_features_Enable to 11.2.0.2
> in the upgraded database.
> > The query in question has correlated subqueries.
> >
> > Any suggestions?
> >
> > I am not pasting the query or explain plans because of length. But
> subquery unnesting seems to be the cause of the issue.
> >
> > thanks for your time
> >
> > kumar
> >
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 23 2014 - 01:47:06 CEST