Tuesday, April 10, 2012

Record Print Status Too Slow

At my client site, this program was not performing well. We did a trace and found several sql statements were written poorly as if Oracle development did not understand the data and just joined the table without any regards to how query will perform. At my client site, client pays tens of thousands of invoices everyday and volume is higher at the end of every week, every bi-week and every month end. We knew we can not pay invoices in timely manner if we did not do anything. so first thing we did was to log a service request with Oracle and Oracle response was not satisfactory. I thought about it and decided to use sql plan management for these problem sqls. The details of bad sql and desired plan is as given below

  1. Bad Sql#1
  2. Bad Sql#2
  3. Bad Sql#3
  4. Bad Sql#4
  5. Bad Sql#5


Please leave some comments if you are experiencing slow performance of Record Print Status (IBY_FD_RECORD_PRINT_STATUS) program

Posted by Sanjai Misra at 10:35 PM
Labels:

6 comments:

  1. AnonymousFriday, April 20, 2012 at 8:34:00 PM CDT

    WE are experiencing severe degradation when there are more than 60k of invoices in a single PPR.

    INSERT INTO AP_INVOICE_PAYMENTS_ALL (INVOICE_PAYMENT_ID,
    INVOICE_ID,
    PAYMENT_NUM,
    CHECK_ID,
    AMOUNT,

    ....
    SELECT /*+ Leading(xeg) index(ac ap_checks_u1) */
    SI.INVOICE_PAYMENT_ID,
    SI.INVOICE_ID,
    SI.PAYMENT_NUM,
    AC.CHECK_ID,
    IBYDOCS.PAYMENT_AMOUNT,

    ReplyDelete
    Replies
    1. Sanjai MisraFriday, April 20, 2012 at 9:11:00 PM CDT

      We too have faced problem with PPR few times but for last 2 months it is working ok. We faced problem with other sql statement for selecting invoices for payment. It was in auto select package. We logged a service request with Oracle. Today only it has been closed. We kept on providing all sort of traces but no resolution was suggested. If you send me list of sql statements that are causing performance at your site, i can look into it and might suggest something.

      Delete
  2. AnonymousFriday, July 13, 2012 at 8:47:00 AM CDT

    For the autoselect - switch on the PPR: Gather stats profile. It tends to help.

    ReplyDelete
  3. AnonymousThursday, July 26, 2012 at 11:53:00 AM CDT

    A patch has been released following our SR; This resolves this issue by changing the hint.
    The other thing you need to do is to run gather stats @ 99% for AP_SELECTED_INVOICES_ALL and AP_UNSELECTED_INVOICES_ALL tables before running Record Print Status assuming you are submitting that via a concurrent program.

    This issue will surface when you have a very high volume.

    ReplyDelete
    Replies
    1. Sanjai MisraThursday, July 26, 2012 at 12:00:00 PM CDT

      Thanks for your update. Could you please provide the patch number?

      Delete
  4. AnonymousThursday, October 11, 2012 at 10:53:00 AM CDT

    The patch is 14009966:R12.AP.B.

    Also ensure that AP_SELECTED_INVOICES_ALL and AP_UNSELECTED_INVOICES_ALL have stats gathered before running record print status.


    ReplyDelete

Subscribe to: Post Comments (Atom)