1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query Performance Using PO and AP tables

Discussion in 'Oracle Apps Technical' started by grantie, Jan 13, 2010.

  1. grantie

    grantie Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hello. I'm new to this forum (and to Oracle Apps) so please be gentle.

    I'm writing a query agains PO_Headers, PO_Lines, PO_Line_Locations, PO_Line_Distributions, AP_Invoice_Distributions and AP_Invoices. I am putting criteria in against Accounting_Date (say for a month). I think my query is correct with respect to joins and the output I'm getting looks good. However, performance is terrible. It has taken more than an hour. Does anyone have any tips or clues as to what I should look for to try and improve this?

    I won't post the SQL here unless I get a kind soul who is able and willing to help me ;).

    Thanks in advance
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hello Grantie... :)

    I would like to help, can I see the code please...
     
  3. grantie

    grantie Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi Sadik. Thank you.

    Code (SQL):
    SELECT
      PO.PO_HEADERS_ALL.ATTRIBUTE1,
      PO.PO_HEADERS_ALL.ATTRIBUTE2,
      PO.PO_VENDORS.VENDOR_NAME,
      PO.PO_VENDOR_SITES_ALL.ZIP,
      PO.PO_HEADERS_ALL.SEGMENT1,
      PO.PO_LINES_ALL.LINE_NUM,
      PO.PO_LINES_ALL.ATTRIBUTE13,
      PO.PO_LINE_LOCATIONS_ALL.CANCEL_FLAG,
      PO.PO_LINE_LOCATIONS_ALL.CLOSED_CODE,
      PO.PO_LINES_ALL.ITEM_DESCRIPTION,
      PO.PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE,
      PO.PO_HEADERS_ALL.CURRENCY_CODE,
      nvl(PO.PO_HEADERS_ALL.RATE,1),
      nvl(AP.AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE,1),
      PO.PO_LINES_ALL.UNIT_PRICE,
      AP.AP_INVOICE_DISTRIBUTIONS_ALL.UNIT_PRICE,
      SUM(AP.AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED),
      SUM(AP.AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_VARIANCE),
      SUM(nvl(AP.AP_INVOICE_DISTRIBUTIONS_ALL.BASE_QUANTITY_VARIANCE,0)),
      SUM(AP.AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE),
      SUM(nvl(AP.AP_INVOICE_DISTRIBUTIONS_ALL.BASE_INVOICE_PRICE_VARIANCE,0))
    FROM
      PO.PO_LINES_ALL,
      PO.PO_HEADERS_ALL,
      PO.PO_VENDORS,
      PO.PO_VENDOR_SITES_ALL,
      PO.PO_LINE_LOCATIONS_ALL,
      AP.AP_INVOICE_DISTRIBUTIONS_ALL,
      PO.PO_DISTRIBUTIONS_ALL
    WHERE
      ( PO.PO_VENDORS.VENDOR_ID=PO.PO_HEADERS_ALL.VENDOR_ID  )
      AND  ( PO.PO_LINES_ALL.PO_LINE_ID=PO.PO_LINE_LOCATIONS_ALL.PO_LINE_ID  )
      AND  ( PO.PO_HEADERS_ALL.PO_HEADER_ID= PO.PO_LINES_ALL.PO_HEADER_ID  )
      AND  ( PO.PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID=PO.PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID  )
      AND  ( PO.PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID=AP.AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID  )
      AND  ( PO.PO_VENDOR_SITES_ALL.VENDOR_SITE_ID=PO.PO_HEADERS_ALL.VENDOR_SITE_ID  )
      AND  ( PO.PO_LINES_ALL.ORG_ID= 132  )  AND  ( PO.PO_HEADERS_ALL.ORG_ID = 132  )  AND  ( PO.PO_VENDOR_SITES_ALL.ORG_ID= 132  )  AND  ( PO.PO_LINE_LOCATIONS_ALL.ORG_ID= 132  )  AND  ( AP.AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID= 132  )  AND  ( PO.PO_DISTRIBUTIONS_ALL.ORG_ID= 132  )
      AND  
      (
       PO.PO_HEADERS_ALL.SEGMENT1  LIKE  'K%'
       AND
       AP.AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE  >=  '01-10-2009 00:00:00'
      )
    GROUP BY
      PO.PO_HEADERS_ALL.ATTRIBUTE1,
      PO.PO_HEADERS_ALL.ATTRIBUTE2,
      PO.PO_VENDORS.VENDOR_NAME,
      PO.PO_VENDOR_SITES_ALL.ZIP,
      PO.PO_HEADERS_ALL.SEGMENT1,
      PO.PO_LINES_ALL.LINE_NUM,
      PO.PO_LINES_ALL.ATTRIBUTE13,
      PO.PO_LINE_LOCATIONS_ALL.CANCEL_FLAG,
      PO.PO_LINE_LOCATIONS_ALL.CLOSED_CODE,
      PO.PO_LINES_ALL.ITEM_DESCRIPTION,
      PO.PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE,
      PO.PO_HEADERS_ALL.CURRENCY_CODE,
      nvl(PO.PO_HEADERS_ALL.RATE,1),
      nvl(AP.AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE,1),
      PO.PO_LINES_ALL.UNIT_PRICE,
      AP.AP_INVOICE_DISTRIBUTIONS_ALL.UNIT_PRICE
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi

    Firstly, you don't need to use the Schema name in all your references to columns when you are accessing the APPS database as the APPS user. So you don't need to add the SCHEMA (PO, AP etc) prefix always.

    Secondly, you should use an alias after tablenames in your FROM statement. That is, for example, you should use, something like this:

    Code (SQL):
    SELECT   pha.segment1
      FROM   PO_HEADERS_ALL pha, PO_LINES_ALL pla
     WHERE   pla.po_header_id = pha.po_header_id
    Thirdly since you are already joining all tables on different conditions you don't need to check for ORG_ID's on all tables. Checking it on any one is sufficient and then you don't need to check on those tables which you joined with this one.

    Finally make sure your statement below, is being executed correctly.

    Code (SQL):
    AP.AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE  >=  '01-10-2009 00:00:00'
    Check it with like,
    Code (SQL):

    SELECT   *
      FROM   AP_INVOICE_DISTRIBUTIONS_ALL aia
     WHERE   aia.ACCOUNTING_DATE >= '01-10-2009 00:00:00'
    Just to make sure that the condition, your date format is correct.
     
  5. grantie

    grantie Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Sadik. I will try the aliases as you suggest.

    I did try a simple query that just hit invoice distributions for the same date range and that took for ever as well. What should I be looking at? A different date format?
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    can you post that simple date query?
     
  7. mkandula

    mkandula Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello Grantie...

    Also for the date column if you are looking for just a date >= '01-10-2009' i guess u can better use this one...

    Code (SQL):
    SELECT   *
      FROM   AP_INVOICE_DISTRIBUTIONS_ALL aia
     WHERE   trunc(aia.ACCOUNTING_DATE) >= '10-JAN-2009';
    This query returned me results in less than 2 seconds. so do try this out too.

    Thanks
    Murali
     
  8. grantie

    grantie Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hello Sadik and Murali.

    First when I removed the owner name, the table name wasn't recognised. I did manage to create aliases which at least makes the SQL more readable.

    Second, if I trunc the date, then performance is worse (I can see we have an index on accounting_date so using trunc would not make use of this index).

    But having made the following change I can < 10 second results for:

    Code (SQL):
    SELECT
      ID.ACCOUNTING_DATE
    FROM
      AP.AP_INVOICE_DISTRIBUTIONS_ALL  ID
    WHERE
      ID.ACCOUNTING_DATE  >=  '01-11-2009 00:00:00'
    So I am going to get my DBA to run an explain plan to see if that sheds any light.
     
  9. grantie

    grantie Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Also, on a related note, I am pulling out Qty_Received from PO Line Locations (amongst other things). I am then joining to PO Distributions and Invoice Distributions for other data. Now I have examples of where one PO Distribution can be invoiced via 2 (or more Invoice Distibrutions). So I had 3 PO Lines to start off with and 3 PO Distributions. But the PO was paid via 2 invoices which went into 4 distributions lines. In this case when I join across the tables my 3 rows are "inflated" into 3 rows and if I sum the Qty Received column, then I get the wrong number.

    Do you know of a clever way around this?

    Does it become easier over time :-((
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Try your query with TO_DATE

    Code (SQL):
    SELECT
      ID.ACCOUNTING_DATE
    FROM
      AP.AP_INVOICE_DISTRIBUTIONS_ALL  ID
    WHERE
      ID.ACCOUNTING_DATE  >=  to_date( '01-11-2009 00:00:00', 'MM/DD/RRRR HH24:MI:SS')
    Regarding your second points. try and first join PO lines with the invoice(s) and then from the invoice to the invoice distribution.

    And yes, with practice, these things won't bother you at all... btw i am actually a functional guy so bad at technical side of things. What i am saying to you is just from experience...
     
  11. hi

    If you are using the TOAD then do Ctl-E, you will the detail EXPLAIN PLAN of the query and check the cost of each condition.

    Check the Indexes on the tables.
    Once you reduce the cost by applying either RULE based or Cost based indexes, then it will be much faster.
     
    Sadik likes this.