Question on Oracle EBS R12

From: Ankit Thakwani <ankit.thakwani_at_gmail.com>
Date: Mon, 27 May 2013 18:42:20 +0530
Message-ID: <CAKA=bnSPVt5zstNQWK98Oc+G70e0ZE07QZUQCiDDEnAg1L9o1g_at_mail.gmail.com>



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
Received on Mon May 27 2013 - 15:12:20 CEST

Original text of this message