Re: Question on Oracle EBS R12
Date: Fri, 31 May 2013 17:50:06 +0530
Message-ID: <CAKA=bnSA+fAkNOqAwj1McBYh_q5w21mcPdQZ7PX4JOPBvBoynw_at_mail.gmail.com>
Thanks a TON Mark !, your suggestions worked wonders for us, we applied the Patch mentioned in the DOC ID:1465689.1 and applied the RPC. After doing this we removed the Custom Index and the performance is back to normal.
P.S. I am Sorry for not updating on this earlier, I had a DB Outage to attend to.
Regards,
Ankit
On Tue, May 28, 2013 at 5:11 PM, Mark Burgess
<mark_at_burgess-consulting.com.au> wrote:
> Hi Ankit,
>
> by chance is this problem happening when you run the Create Accounting Sub Ledger accounting jobs?
>
> If you are a new implementation of EBS depending on your transaction volume you may find that the Sub Ledger Accounting tables (and underlying partitions) grow fairly quickly - and depending on the nature of the business transactions you will notice that the rows in the partitioned XLA tables may vary largely depending on the nature of the transactions in the various subledgers (AP, AR, INV etc etc).
>
> To get around this I would suggest a couple of things:
>
> 1. Keep an eye out for the XLA rollup patches. The most recent financials rollups were released around March. There were a number of fixes post 12.1.3 around performance in Subledger Accounting.
> 2. Maintain current statistics on the XLA segments. If you are a new implementation then you may need to look at running a gather stats on a regular basis if the XLA tables are growing rapidly (this also applies to an upgraded R12 environment from 11i etc as the XLA tables are populated as part of the upgrade driver).
> 3. Investigate the option of maintaining a longer period of AWR information. We have had a couple of occurrences on customer sites where performance problems in the create accounting jobs only popped up at month end due to the nature of the business process cycle. The easiest way to resolve these issues was to compare the current plan to the good plan from the previous month then lock in the previous months execution plan with a SQL baseline.
>
> You might also want to check if MOS Note # 1465689.1 is relevant to your environment as well.
>
> Regards,
>
> Mark
>
> On 27/05/2013, at 11:12 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 Fri May 31 2013 - 14:20:06 CEST