First of all ' Thanks a lot ! ' for providing inputs. Let me explain the table structure of XLA_AE_HEADERS, it is list partitioned on column APPLICATION_ID with 19 partitions. The initial explain plan was using the composite partitioned index XLA_AE_HEADERS_N4 (APPLICATION_ID, REQUEST_ID), we then 'disabled' this index by setting this index to 'unusable' and created a new composite partitioned index on (APPLICATION_ID, EVENT_ID) The results/performance was much much better, the execution time earlier was 4-5 seconds and was the top consumer and now we do not even notice this call. Till now, we have not noticed any issues by disabling the index XLA_AE_HEADERS_N4. On Mon, May 27, 2013 at 8:38 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote: > It depends on what you mean by "OKAY." > > 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > 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 > -- > //www.freelists.org/webpage/oracle-l > > -- Thanks and Regards Ankit Thakwani -- Thanks and Regards Ankit Thakwani -- //www.freelists.org/webpage/oracle-l