RE: sql slow after db upgrade

Date: Tue, 28 Oct 2014 12:09:07 -0400
Subject: Re: sql slow after db upgrade

From: mauro.pagano_at_gmail.com
To: iggy_fernandez_at_hotmail.com
CC: carlos.sierra.usa_at_gmail.com; ksmadduri_at_gmail.com; oracle-l_at_freelists.org

Hi Iggy,
Thanks for letting me know!I just added more details in a second post, http://mauro-pagano.com/2014/10/28/sqlt-xplore-mechanics/If it's still not clear just let me know :-) Thanks,Mauro
On Mon, Oct 27, 2014 at 10:09 PM, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote:

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_at_gmail.com
Date: Mon, 27 Oct 2014 20:35:15 -0400
CC: Oracle-L_at_freelists.org; mauro.pagano_at_gmail.com To: ksmadduri_at_gmail.com

Here we go: A blog post on SQLT XPLORE
http://mauro-pagano.com/
Thanks Mauro!
Carlos Sierracarlos.sierra.usa_at_gmail.comLife is Good!

On Oct 27, 2014, at 07:08, Carlos Sierra <carlos.sierra.usa_at_gmail.com> 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_at_gmail.comLife 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.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_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://about.me/mauro.pagano
 		 	   		  
--
http://www.freelists.org/webpage/oracle-l