RE: sql slow after db upgrade
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "carlos.sierra.usa@xxxxxxxxx" <carlos.sierra.usa@xxxxxxxxx>, Kumar Madduri <ksmadduri@xxxxxxxxx>
- Date: Mon, 27 Oct 2014 19:09:37 -0700
Mauro,
This part was not completely clear to me:
At this point XPLORE will test all the fix_controls and CBO parameters
generating an execution plan for each of them (for some parameters, ie.
optimizer_index_cost_adj we test several values), packing the result in a HTML
report.
Would you provide a little more detail?
Kindest regards,
Iggy
Subject: Re: sql slow after db upgrade
From: carlos.sierra.usa@xxxxxxxxx
Date: Mon, 27 Oct 2014 20:35:15 -0400
CC: Oracle-L@xxxxxxxxxxxxx; mauro.pagano@xxxxxxxxx
To: ksmadduri@xxxxxxxxx
Here we go: A blog post on SQLT XPLORE
http://mauro-pagano.com/
Thanks Mauro!
Carlos Sierracarlos.sierra.usa@gmail.comLife is Good!
On Oct 27, 2014, at 07:08, Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
wrote: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 Sierracarlos.sierra.usa@gmail.comLife is Good!
On Oct 27, 2014, at 06:26, Kumar Madduri <ksmadduri@xxxxxxxxx> 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.4Fix 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@xxxxxxxxx>
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@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
>
Other related posts:
