Query Performance Using PO and AP tables

[grantie]

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

[Sadik]

Hello Grantie...

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

[grantie]

Hi Sadik. Thank you.

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

[Sadik]

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:

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.

AP.AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE  >=  '01-10-2009 00:00:00'

Check it with like,

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.

[grantie]

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?

[Sadik]

can you post that simple date query?

[mkandula]

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...

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

[grantie]

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:

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.

[grantie]

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 :-((

[Sadik]

Try your query with TO_DATE

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...