Re: Question on Oracle EBS R12
Date: Tue, 28 May 2013 18:42:12 +0530
Message-ID: <CAEWC_QBBcaC4AzN67vZkfEJEjefXVKaOXaFFDLrpo=J+PDg1sw_at_mail.gmail.com>
I hope "Manual Index" is Custom index. Yes , in many Ebusiness Suite implemtations , custom indexes are there. Only thing is if they are causing some other issue, at some other place Oracle support will not help in that case
You can bring back the index and along with that , can you check the 10053 trace , why it is not picking the new custom index
Regathering the stats for the table and its indexes can improve the performance.
Thanks and Regards,
Harman
On Mon, May 27, 2013 at 6:42 PM, Ankit Thakwani <ankit.thakwani_at_gmail.com>wrote:
> Hi,
>
> We have implemented a customized Oracle EBS R12 recently and faced a
> severe performance issue in first few weeks due to following query
>
> DELETE FROM XLA_DISTRIBUTION_LINKS
> WHERE APPLICATION_ID = :B1
> AND AE_HEADER_ID
> IN
> (SELECT AE_HEADER_ID FROM
> XLA_AE_HEADERS
> WHERE APPLICATION_ID = :B1 AND EVENT_ID = :B2 )
>
> This SQL was hogging up 100% CPU.
>
> Now when we analyze the plan, it was evident that the Table
> XLA_AE_HEADERS did not had an index on the columns used in the
> sub-query i.e. APPLICATION_ID and EVENT_ID, and after we created the
> index and disabled the other index (optmizer somehow chose a different
> index even after creating the new index) the CPU utilization was under
> control.
>
> We had to do it forcefully as Oracle SR was not helpful at all.
>
> So my question is, is it OK to create a manual index on Oracle EBS R12 ?
>
> Has anyone has had to do a similar thing to bring improve the performance?
>
> Regards,
> Ankit
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 28 2013 - 15:12:12 CEST