Fwd: Question on Oracle EBS R12
Date: Tue, 28 May 2013 11:45:13 +0530
Message-ID: <CAKA=bnS_M=mkUfB2i0bZpaGqzJ7G=6hXW70pmNgTOvnovMB1rg_at_mail.gmail.com>
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_at_rsiz.com> 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_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
>
>
-- Thanks and Regards Ankit Thakwani -- Thanks and Regards Ankit Thakwani -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 28 2013 - 08:15:13 CEST