Re: sql slow after db upgrade
- From: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Wed, 22 Oct 2014 11:58:25 -0400
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@xxxxxxxxx
Life is Good!
> On Oct 22, 2014, at 06:58, Kumar Madduri <ksmadduri@xxxxxxxxx> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
