Re: sql slow after db upgrade
Date: Mon, 27 Oct 2014 07:08:04 -0400
Message-Id: <A304C920-D420-4CA3-A07A-2339C6668218_at_gmail.com>
For those interested, Mauro Pagano will be blogging soon about SQLT XPLORE (215187.1). XPLORE provides an easy way to identify regressions caused by some fix to the CBO.
Carlos Sierra
carlos.sierra.usa_at_gmail.com
Life is Good!
> On Oct 27, 2014, at 06:26, Kumar Madduri <ksmadduri_at_gmail.com> wrote:
>
> Update:
> Ran xplore tool from sqlt and shared the zip file with Carlos. He found out quickly and I found out in an non-standard way that this issue is caused by a regression of bug 13396096 that was fixed in 11.2.0.4
> Fix was ALTER system SET "_fix_control" = '13396096:0' scope=both;
> xplore has made it easy to troubleshoot this. There were several workarounds but this seems to be the narrowest in terms on scope (least damage I hope ).
> I have updated the SR giving this information.
>
> Thanks Carlos for your time and help. xplore seems to be really cool tool to debug sql issues that are caused after db upgrades.
>
> - Kumar
>
>
>
> On Wed, Oct 22, 2014 at 8:58 AM, Carlos Sierra <carlos.sierra.usa_at_gmail.com <mailto: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 <mailto:carlos.sierra.usa_at_gmail.com>
>
> Life is Good!
>
>
>
> > On Oct 22, 2014, at 06:58, Kumar Madduri <ksmadduri_at_gmail.com <mailto: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 Mon Oct 27 2014 - 12:08:04 CET