OraFAQ Forum: Oracle Fusion Apps & E-Business Suite

Home » Applications » Oracle Fusion Apps & E-Business Suite » How to Tune the query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Tune the query [message #332973]

Thu, 10 July 2008 03:59

sreenu80
Messages: 50
Registered: July 2006
Location: bangalore

Member

Hi friends,

Could you please help on how to tune the below query,

This is one report.
If i pass all three parameters(p_date_from,p_date_to,
batch_name )this report is picking the data with in 2 mins if i am not pass any batch name it is taking long time more than 20 min.

I need this lessthan 10 mins.

select
ab.rowid
, ab.batch_name
, ab.batch_date
, nvl(ab.pay_group_lookup_code,'***NO PAY GROUP***') PAYGROUP
, gcc.segment1||'.'||gcc.segment3||'.'||gcc.segment2 LIABILITY_ACCT
, nvl(ab.control_invoice_total,0) BATCH_CONTROL_TOTAL
, fu.user_name CREATED_BY
, pv.segment1 VENDOR_NUMBER
, pv.vendor_name
, pvs.address_line1 ADDRESS1
, pvs.address_line2 ADDRESS2
, pvs.address_line3 ADDRESS3
, pvs.city||', '||pvs.state||' '||pvs.zip CITY_STATE_ZIP
, ai.invoice_id
, ai.invoice_num INVOICE_NUMBER
, ai.invoice_date
, ai.description
, nvl(ai.invoice_amount,0) INVOICE_AMOUNT
, nvl(ai.attribute1,get_image_num(ai.invoice_id)) AP_IMAGE_INDEX
, ai.attribute2 TAX_INCL
, NULL DUE_DATE
, NULL GROSS_AMOUNT
FROM ap_batches ab
, gl_code_combinations gcc
, ap_invoices ai
, po_vendors pv
, po_vendor_sites pvs
, fnd_user fu
, ap_payment_schedules ps
WHERE ab.batch_date between trunc(:p_date_from) and trunc(:p_date_to)
AND ab.batch_code_combination_id = gcc.code_combination_id(+)
AND ab.batch_id = ai.batch_id
AND ai.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND ab.created_by = fu.user_id
AND ai.invoice_id = ps.invoice_id
AND 1 >= (SELECT COUNT(1)
FROM ap_payment_schedules ps1
WHERE ps1.invoice_id = ai.invoice_id)
ab.batch_name = :BATCH_NAME
UNION ALL
SELECT
ab.rowid
, ab.batch_name
, ab.batch_date
, nvl(ab.pay_group_lookup_code,'***NO PAY GROUP***') PAYGROUP
, gcc.segment1||'.'||gcc.segment3||'.'||gcc.segment2 LIABILITY_ACCT
, nvl(ab.control_invoice_total,0) BATCH_CONTROL_TOTAL
, fu.user_name CREATED_BY
, pv.segment1 VENDOR_NUMBER
, pv.vendor_name
, pvs.address_line1 ADDRESS1
, pvs.address_line2 ADDRESS2
, pvs.address_line3 ADDRESS3
, pvs.city||', '||pvs.state||' '||pvs.zip CITY_STATE_ZIP
, ai.invoice_id
, ai.invoice_num INVOICE_NUMBER
, ai.invoice_date
, ai.description
, nvl(ai.invoice_amount,0) INVOICE_AMOUNT
, nvl(ai.attribute1,get_image_num(ai.invoice_id)) AP_IMAGE_INDEX
, ai.attribute2 TAX_INCL
, ps.due_date
, ps.gross_amount
FROM ap_batches ab
, gl_code_combinations gcc
, ap_invoices ai
, po_vendors pv
, po_vendor_sites pvs
, fnd_user fu
, ap_payment_schedules ps
WHERE ab.batch_date between trunc(:p_date_from) and trunc(:p_date_to)
AND ab.batch_code_combination_id = gcc.code_combination_id(+)
AND ab.batch_id = ai.batch_id
AND ai.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND ab.created_by = fu.user_id
AND ai.invoice_id = ps.invoice_id
AND 1 < (SELECT COUNT(1)
FROM ap_payment_schedules ps1
WHERE ps1.invoice_id = ai.invoice_id)
ab.batch_name = :BATCH_NAME
ORDER BY 1
, 19
, 8
, 15

Thanks
sreeni

Report message to a moderator

Previous Topic:	Is there a report to search for Invoices by Customer Profile Classes?
Next Topic:	spaces in report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 17 05:04:12 CDT 2024