Re: sql slow after db upgrade
Date: Wed, 22 Oct 2014 09:13:33 -0700
Message-ID: <CAJgcjABhMAF-SOty9zt3aeZ0zMqAJgVfeehkjXEX5CvhSjggEw_at_mail.gmail.com>
Sounds like you has an easily reproduced test case for an Oracle SR. It's not unusual for there to be performance issues with individual queries in EBS after an upgrade, and it can be hard to find results by searching for known bugs yourself.
Your best bet in this case is to open an SR with the appropriate product team for the concurrent request, show then the difference in execution plans between 11.2.0.2 and 11.2.0.4, and ask (politely of course), "Hey guys, WTF?" ;-)
I 100% do NOT recommend setting a hidden parameter in your EBS database without explicit approval from Support. Disabling a feature system-wide to fix a problem with a single query could cause undesirable results elsewhere, and then you'd just have a different set of users yelling at you.
On Wednesday, October 22, 2014, 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
>
>
-- Sent from a mobile device, because leaving the couch to find a real keyboard would unnecessarily delay this vital communication. So would proofreading, so don't be surprised by typos. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 22 2014 - 18:13:33 CEST