OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query tuning

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

Query tuning [message #404429]

Thu, 21 May 2009 15:35

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,

Attachment: Execution-plan.txt
(Size: 4.28KB, Downloaded 1413 times)

Report message to a moderator

Re: Query tuning [message #404435 is a reply to message #404429]

Thu, 21 May 2009 16:48

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

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

Report message to a moderator

Re: Query tuning [message #404437 is a reply to message #404429]

Thu, 21 May 2009 17:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Are all columns in the WHERE clause indexed?
Do all tables & indexes have current statistic?

The tables removed out of FROM clause get subordinated into WHERE clause by using either IN or EXISTS

Report message to a moderator

Re: Query tuning [message #404677 is a reply to message #404437]

Fri, 22 May 2009 14:50

mukundsahare
Messages: 21
Registered: February 2007

Junior Member

Hi,

I am not able to subordinate the removed tables from FROM clause.Please help me.

Regards,

Report message to a moderator

Re: Query tuning [message #404678 is a reply to message #404429]

Fri, 22 May 2009 14:59

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')

Report message to a moderator

Re: Query tuning [message #404685 is a reply to message #404678]

Fri, 22 May 2009 15:28

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,

Report message to a moderator

Re: Query tuning [message #404690 is a reply to message #404429]

Fri, 22 May 2009 16:47

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.

Report message to a moderator

Previous Topic:	Index issue
Next Topic:	More archivelogs are generating.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 13:57:07 CST 2024