Re: Question on Oracle EBS R12
- From: Ankit Thakwani <ankit.thakwani@xxxxxxxxx>
- To: Mark Burgess <mark@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 31 May 2013 17:50:06 +0530
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@xxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx> 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
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
