RE: Question on Oracle EBS R12

First, you have stated already that your implementation is "customized," so you already own responsibility for performance. Remember Sheldon Gregory's Rule: Customizations are like herpes; Once you have them you cannot get rid of them and they are entirely your responsibility.

Second, if you add a custom index, it is entirely appropriate for support to minimally help you on other performance issues that crop up where the custom index could possibly be disrupting what the normal plan choice would be.

That said, if the additional of an index that does not create a new constraint (that is, for example, a primary key on a combination of columns one of which was formerly nullable) then if it changes your results that would be a bug. Notice I wrote results as opposed to performance.

Please elaborate more on exactly what you did to "disable" the existing index. Unless it was supporting a constraint or the way you've disabled it prevents it from being used somewhere it is important to some other access path and plan, it probably won't hurt.

You might want to trace the plan creation to see why the optimizer was choosing the "wrong" index (from your perspective.)

So in your context, and with those caveats, it probably is OK.

Good luck,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ankit Thakwani
Sent: Monday, May 27, 2013 9:12 AM
To: oracle-l-freelists
Subject: Question on Oracle EBS R12

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-l Received on Mon May 27 2013 - 17:08:13 CEST