Re: sql slow after db upgrade
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: John Piwowar <jpiwowar@xxxxxxxxx>
- Date: Wed, 22 Oct 2014 17:31:08 +0100
In addition, if it really is a single query, and you can manage this with
alter session, then you can either
1) alter the session (doh!)
2) alter the query to use the OPT_PARAM hint (assuming access to the query)
3) run the hinted statement as per 2 and then associate the resulting plan
with the unhinted statement using sql plan management
https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex
On Wed, Oct 22, 2014 at 5:13 PM, John Piwowar <jpiwowar@xxxxxxxxx> wrote:
> 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@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
>>
>>
>
>
> --
> 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.
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
