Home » RDBMS Server » Performance Tuning » Query tuning
Query tuning [message #404429] |
Thu, 21 May 2009 15:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
Please help me in tuning the below query. I have attached the execution plan.
SELECT z.account_number cust_no,
z.party_name cust_name,
z.orig_system_reference addr_ref,
z.invoice_currency_code curr,
SUM(z.bal) bal
FROM
(
SELECT hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code,
SUM(aps.amount_due_remaining) bal
FROM AR_PAYMENT_SCHEDULES_V aps,
ra_customer_trx rct,
ra_cust_trx_types rctt,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_cust_accounts hca,
hz_parties hp
WHERE aps.customer_trx_id = rct.customer_trx_id
AND aps.invoice_currency_code= rct.invoice_currency_code
AND aps.customer_id = hcas.cust_account_id
AND aps.customer_site_use_id = hcsu.site_use_id
AND aps.cust_trx_type_id = rctt.cust_trx_type_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND SUBSTR(rctt.name,1,3) IN ( SELECT flex_value
FROM arc_entity_master
WHERE attribute1 = 'HTA' )
GROUP BY hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code
UNION ALL
SELECT hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code,
SUM(NVL(ad.amount_dr,0)) -
SUM(NVL(ad.amount_cr,0)) bal
FROM ar_distributions ad,
ar_receivable_applications ara,
ar_cash_receipts acr,
AR_PAYMENT_SCHEDULES_V aps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_cust_accounts hca,
hz_parties hp
WHERE source_table = 'RA'
AND ad.source_id = ara.receivable_application_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = aps.cash_receipt_id
AND aps.invoice_currency_code = acr.currency_code
AND acr.pay_from_customer = hca.cust_account_id
AND acr.customer_site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND ara.status IN ('UNAPP','ACC')
AND acr.attribute1 IN ( SELECT flex_value
FROM arc_entity_master
WHERE attribute1 = 'HTA' )
GROUP BY hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code
) z
GROUP BY z.account_number,
z.party_name,
z.orig_system_reference,
z.invoice_currency_code
Regards,
|
|
|
Re: Query tuning [message #404435 is a reply to message #404429] |
Thu, 21 May 2009 16:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
SELECT z.account_number cust_no,
z.party_name cust_name,
z.orig_system_reference addr_ref,
z.invoice_currency_code curr,
Sum(z.bal) bal
FROM (SELECT hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code,
Sum(aps.amount_due_remaining) bal
FROM ar_payment_schedules_v aps,
ra_customer_trx rct,
ra_cust_trx_types rctt,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_cust_accounts hca,
hz_parties hp
WHERE aps.customer_trx_id = rct.customer_trx_id
AND aps.invoice_currency_code = rct.invoice_currency_code
AND aps.customer_id = hcas.cust_account_id
AND aps.customer_site_use_id = hcsu.site_use_id
AND aps.cust_trx_type_id = rctt.cust_trx_type_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND Substr(rctt.NAME,1,3) IN (SELECT flex_value
FROM arc_entity_master
WHERE attribute1 = 'HTA')
GROUP BY hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code
UNION ALL
SELECT hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code,
Sum(Nvl(ad.amount_dr,0)) - Sum(Nvl(ad.amount_cr,0)) bal
FROM ar_distributions ad,
ar_receivable_applications ara,
ar_cash_receipts acr,
ar_payment_schedules_v aps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_cust_accounts hca,
hz_parties hp
WHERE source_table = 'RA'
AND ad.source_id = ara.receivable_application_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = aps.cash_receipt_id
AND aps.invoice_currency_code = acr.currency_code
AND acr.pay_from_customer = hca.cust_account_id
AND acr.customer_site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND ara.status IN ('UNAPP','ACC')
AND acr.attribute1 IN (SELECT flex_value
FROM arc_entity_master
WHERE attribute1 = 'HTA')
GROUP BY hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code) z
GROUP BY z.account_number,
z.party_name,
z.orig_system_reference,
z.invoice_currency_code
Eliminate out of the FROM clause ra_customer_trx rct, ra_cust_trx_types rctt, hz_cust_site_uses hcsu tables because they contribute no data to SELECT clause.
Are all columns in the WHERE clause indexed?
Do all tables & indexes have current statistic?
[Updated on: Thu, 21 May 2009 17:18] Report message to a moderator
|
|
|
Re: Query tuning [message #404436 is a reply to message #404435] |
Thu, 21 May 2009 17:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
Thanks for the reply.
I am sorry but I did't get how I can remove the tables suggested by you as based on the join condition for these tables we are fetching the data.
Regards,
Mukund
|
|
|
|
|
Re: Query tuning [message #404678 is a reply to message #404429] |
Fri, 22 May 2009 14:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
I'll do 1 of the 3 as an example
FROM (SELECT hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code,
Sum(aps.amount_due_remaining) bal
FROM ar_payment_schedules_v aps,
ra_cust_trx_types rctt,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_cust_accounts hca,
hz_parties hp
WHERE (aps.customer_trx_id,aps.invoice_currency_code) IN
(SELECT rct.customer_trx_id, rct.invoice_currency_code FROM ra_customer_trx rct)
AND aps.customer_id = hcas.cust_account_id
AND aps.customer_site_use_id = hcsu.site_use_id
AND aps.cust_trx_type_id = rctt.cust_trx_type_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND Substr(rctt.NAME,1,3) IN (SELECT flex_value
FROM arc_entity_master
WHERE attribute1 = 'HTA')
|
|
|
Re: Query tuning [message #404685 is a reply to message #404678] |
Fri, 22 May 2009 15:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
Thanks for your help.I have modified as suggested .Request you to please check whether I am correct or not.
FROM (
SELECT hca.account_number,
hp.party_name,
hcas.orig_system_reference,
aps.invoice_currency_code,
Sum(aps.amount_due_remaining) bal
FROM ar_payment_schedules aps,
hz_cust_acct_sites hcas,
hz_cust_accounts hca,
hz_parties hp
WHERE (aps.customer_trx_id,aps.invoice_currency_code) IN
(SELECT rct.customer_trx_id, rct.invoice_currency_code FROM ra_customer_trx rct)
AND aps.customer_id = hcas.cust_account_id
AND aps.customer_site_use_id IN (SELECT hcsu.site_use_id FROM hz_cust_site_uses hcsu)
AND aps.cust_trx_type_id IN (SELECT rctt.cust_trx_type_id FROM ra_cust_trx_types rctt
WHERE Substr(rctt.NAME,1,3) IN (SELECT flex_value
FROM arc_entity_master
WHERE attribute1 = 'HTA'))
AND hcas.cust_acct_site_id IN (SELECT hcsu.cust_acct_site_id FROM hz_cust_site_uses hcsu)
AND hcas.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
Regards,
|
|
|
Re: Query tuning [message #404690 is a reply to message #404429] |
Fri, 22 May 2009 16:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>Request you to please check whether I am correct or not.
That looks GOOD to me.
Similar changes can & should be made in the rest of the original SQL.
Afterwards, I'd like to see the new EXPLAIN PLAN & know how the performance changed between before & after changes.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 10:20:55 CDT 2024
|