Re: Question on Oracle EBS R12
- From: Mark Burgess <mark@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: ankit.thakwani@xxxxxxxxx
- Date: Tue, 28 May 2013 21:41:58 +1000
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:
