Home » RDBMS Server » Performance Tuning » Need your help in tuning the query (11.5.10.2)
Need your help in tuning the query [message #598635] Wed, 16 October 2013 08:23 Go to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
UPDATE rcv_receiving_sub_ledger RCV_SUB 
SET    RCV_SUB.pa_addition_flag = Decode(RCV_SUB.pa_addition_flag, 'N', 'O', 
                                                                   'I', 'J'), 
       RCV_SUB.request_id = :B4, 
       RCV_SUB.last_update_date = SYSDATE, 
       RCV_SUB.last_updated_by = :B3, 
       RCV_SUB.last_update_login = :B3, 
       RCV_SUB.program_id = :B2, 
       RCV_SUB.program_application_id = :B1, 
       RCV_SUB.program_update_date = SYSDATE 
WHERE  EXISTS (SELECT 1 
               FROM   rcv_transactions RCV_TXN, 
                      po_distributions PO_DIST, 
                      po_lines_all POL 
               WHERE  ( ( RCV_TXN.destination_type_code = 'EXPENSE' ) 
                         OR ( RCV_TXN.destination_type_code = 'RECEIVING' 
                              AND ( RCV_TXN.transaction_type IN ( 
                                    'RETURN TO VENDOR', 'RETURN TO RECEIVING' 
                                                                ) ) ) ) 
                      AND Trunc(RCV_TXN.transaction_date) <= Trunc( 
                          Nvl(:B6, RCV_TXN.transaction_date)) 
                      AND RCV_TXN.po_distribution_id = 
                          PO_DIST.po_distribution_id 
                      AND RCV_TXN.po_line_id = POL.po_line_id 
                      AND RCV_SUB.code_combination_id = 
                          PO_DIST.code_combination_id 
                      AND RCV_SUB.rcv_transaction_id = RCV_TXN.transaction_id 
                      AND Trunc(PO_DIST.expenditure_item_date) <= Trunc( 
                          Nvl(:B5, PO_DIST.expenditure_item_date)) 
                      AND PO_DIST.project_id > 0 
                      AND PO_DIST.accrue_on_receipt_flag = 'Y' 
                      AND (( RCV_TXN.destination_type_code = 'EXPENSE' 
                             AND ( ( RCV_TXN.transaction_type = 'DELIVER' 
                                     AND RCV_SUB.entered_dr IS NOT NULL ) 
                                    OR ( RCV_TXN.transaction_type = 
                                         'RETURN TO RECEIVING' 
                                         AND RCV_SUB.entered_cr IS NOT NULL ) 
                                    OR ( RCV_TXN.transaction_type = 'CORRECT' 
                                         AND ( RCV_SUB.entered_cr IS NOT NULL 
                                                OR RCV_SUB.entered_dr IS NOT 
                                                   NULL ) 
                                         AND 
                                   RCV_SUB.accounting_line_type = 'Charge' 
                                         AND Decode(POL.order_type_lookup_code, 
                                             'RATE', 
                                             RCV_TXN.amount, 
                                             'FIXED PRICE', 
                                             RCV_TXN.amount, 
                      RCV_TXN.primary_quantity) 
                      < 
                      0 
                      ) 
                      OR ( RCV_TXN.transaction_type = 'CORRECT' 
                      AND RCV_SUB.entered_dr IS NOT NULL 
                      AND Decode(POL.order_type_lookup_code, 'RATE', 
                          RCV_TXN.amount, 
                      'FIXED PRICE', 
                      RCV_TXN.amount, 
                      RCV_TXN.primary_quantity) 
                      >= 
                      0 ) ) ))) 
       AND RCV_SUB.pa_addition_flag IN ( 'N', 'I' ) 
       AND RCV_SUB.actual_flag = 'A' 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1    339.81    4366.60    1346305   17820982          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    339.81    4366.60    1346305   17820982          4           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 173     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  RCV_RECEIVING_SUB_LEDGER (cr=17820982 pr=1346305 pw=0 time=71622890 us)
         0          0          0   FILTER  (cr=17820982 pr=1346305 pw=0 time=71622882 us)
   6079467    6079467    6079467    TABLE ACCESS FULL RCV_RECEIVING_SUB_LEDGER (cr=732433 pr=732265 pw=0 time=583123543 uscost=203825 size=2164234360 card=6079310)
         0          0          0    NESTED LOOPS  (cr=17088549 pr=614040 pw=0 time=3754070112 us cost=7 size=96 card=1)
    259606     259606     259606     NESTED LOOPS  (cr=16085723 pr=554142 pw=0 time=3036824111 us cost=5 size=74 card=1)
    606954     606954     606954      TABLE ACCESS BY INDEX ROWID RCV_TRANSACTIONS (cr=13845417 pr=464870 pw=0 time=2030829699 us cost=3 size=59 card=1)
   4596404    4596404    4596404       INDEX UNIQUE SCAN RCV_TRANSACTIONS_U1 (cr=9249013 pr=26408 pw=0 time=118469143 us cost=2 size=0 card=1)(object id 45286)
    259606     259606     259606      TABLE ACCESS BY INDEX ROWID PO_LINES_ALL (cr=2240306 pr=89272 pw=0 time=999588896 us cost=2 size=89725950 card=5981730)
    606954     606954     606954       INDEX UNIQUE SCAN PO_LINES_U1 (cr=1633350 pr=6553 pw=0 time=51025411 us cost=1 size=0 card=1)(object id 24505518)
         0          0          0     TABLE ACCESS BY INDEX ROWID PO_DISTRIBUTIONS_ALL (cr=1002826 pr=59898 pw=0 time=710223086 us cost=2 size=22 card=1)
    259604     259604     259604      INDEX UNIQUE SCAN PO_DISTRIBUTIONS_U1 (cr=743222 pr=8531 pw=0 time=72882793 us cost=1 size=0 card=1)(object id 45005)


Attached are the trace file.How can we tune it.
My idea is to create an index on RCV_TRANSACTIONS and RCV_RECEIVING_SUB_LEDGER.

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Wed, 16 October 2013 08:35] by Moderator

Report message to a moderator

Re: Need your help in tuning the query [message #598636 is a reply to message #598635] Wed, 16 October 2013 08:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
At first place, your post is difficult to read.

Quote:
My idea is to create an index on RCV_TRANSACTIONS and RCV_RECEIVING_SUB_LEDGER.


1. There is a FTS : TABLE ACCESS FULL RCV_RECEIVING_SUB_LEDGER.
2. Is there an index created on code_combination_id, rcv_transaction_id?
3. Is a NOT NULL constraint defined on entered_cr?
4. What does the column accounting_line_type, pa_addition_flag and actual_flag signify? How about the uniqueness of the data?
5. Table access of RCV_TRANSACTIONS seems fine : TABLE ACCESS BY INDEX ROWID RCV_TRANSACTIONS
Re: Need your help in tuning the query [message #598638 is a reply to message #598636] Wed, 16 October 2013 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
how/why does RCV_RECEIVING_SUB_LEDGER appear in the posted EXPLAIN PLAN, but not in the posted SQL?
Re: Need your help in tuning the query [message #598639 is a reply to message #598638] Wed, 16 October 2013 08:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
BlackSwan wrote on Wed, 16 October 2013 19:20
how/why does RCV_RECEIVING_SUB_LEDGER appear in the posted EXPLAIN PLAN, but not in the posted SQL?


I guess you missed it. That's the table which is being updated.
Re: Need your help in tuning the query [message #598640 is a reply to message #598639] Wed, 16 October 2013 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
>I guess you missed it. That's the table which is being updated.
I CERTAINLY did miss it.
I need another cup of tea to get me more awake.
Re: Need your help in tuning the query [message #598642 is a reply to message #598640] Wed, 16 October 2013 09:05 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
There is index on
CREATE INDEX RCV_RECEIVING_SUB_LEDGER_N4 ON RCV_RECEIVING_SUB_LEDGER
(CODE_COMBINATION_ID, TRANSACTION_DATE)

and on rcv_transaction_id one another index has been created.but i would like to make composite index

CODE_COMBINATION_ID','RCV_TRANSACTION_ID','ENTERED_CR','ENTERED_DR','ACCOUNTING_LINE_TYPE','PA_ADDITION_FLAG')
Re: Need your help in tuning the query [message #598643 is a reply to message #598640] Wed, 16 October 2013 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
please post results from following SQL

SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB ;

SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB
WHERE RCV_SUB.pa_addition_flag IN ( 'N', 'I' ) ;

SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB
WHERE RCV_SUB.actual_flag = 'A' ;

SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB
WHERE RCV_SUB.pa_addition_flag IN ( 'N', 'I' )
AND RCV_SUB.actual_flag = 'A' ;
Re: Need your help in tuning the query [message #598645 is a reply to message #598642] Wed, 16 October 2013 09:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
nishantranjan87 wrote on Wed, 16 October 2013 19:35
There is index on
CREATE INDEX RCV_RECEIVING_SUB_LEDGER_N4 ON RCV_RECEIVING_SUB_LEDGER
(CODE_COMBINATION_ID, TRANSACTION_DATE)


What is the value for :B6?

AND Trunc(RCV_TXN.transaction_date) <= Trunc(Nvl(:B6, RCV_TXN.transaction_date))


Quote:

and on rcv_transaction_id one another index has been created.but i would like to make composite index

CODE_COMBINATION_ID','RCV_TRANSACTION_ID','ENTERED_CR','ENTERED_DR','ACCOUNTING_LINE_TYPE','PA_ADDITION_FLAG')


Not all columns that you see in predicate needs to be indexed. It depends on the cardinality, how many records you want to retrieve from the table. It is documented as around less than 15% for retriving rows using index. If it is significantly larger than that, FTS is better.

Those counts which Blackswan has posted, are required to go ahead.
Re: Need your help in tuning the query [message #598650 is a reply to message #598645] Wed, 16 October 2013 09:46 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB ; 12980094

Ans>>12980094

SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB
WHERE RCV_SUB.pa_addition_flag IN ( 'N', 'I' ) ;

Ans>>6079467

SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB
WHERE RCV_SUB.actual_flag = 'A' ;

Ans>>12980094


SELECT COUNT(*) FROM rcv_receiving_sub_ledger RCV_SUB
WHERE RCV_SUB.pa_addition_flag IN ( 'N', 'I' )
AND RCV_SUB.actual_flag = 'A' ;

Ans>>6079467
Re: Need your help in tuning the query [message #598653 is a reply to message #598650] Wed, 16 October 2013 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
in round numbers about half the rows are being changed; therefore a FTS is a better choice than using any INDEX.
After is certain point, the SQL is as fast as it can be.
Re: Need your help in tuning the query [message #598669 is a reply to message #598653] Wed, 16 October 2013 14:28 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
what need to be changed and what is FTS?
Re: Need your help in tuning the query [message #598672 is a reply to message #598669] Wed, 16 October 2013 14:32 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
in RCV_RECEIVING_SUB_LEDGER on which coloum do we need to create an index?
Re: Need your help in tuning the query [message #598676 is a reply to message #598669] Wed, 16 October 2013 14:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
nishantranjan87 wrote on Thu, 17 October 2013 00:58
what is FTS?


FTS means Full Table Scan.
Re: Need your help in tuning the query [message #598678 is a reply to message #598676] Wed, 16 October 2013 14:52 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
So there is no scope of creating any index and to improve the performance
Re: Need your help in tuning the query [message #598679 is a reply to message #598672] Wed, 16 October 2013 14:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
nishantranjan87 wrote on Thu, 17 October 2013 01:02
in RCV_RECEIVING_SUB_LEDGER on which coloum do we need to create an index?


As already been suggested, index is needed only when you retrieve some percentage(<15% say) of rows from the table. In your case, the query is retrieving almost 50% of the rows, so FTS is a better choice, which the optimizer has already chosen. An index might not necessarily be used, search this forum for "Why my index is not being used". Ross Leishman has a pretty good explanation about it in the blogs. It is the way Oracle works.
Re: Need your help in tuning the query [message #598680 is a reply to message #598678] Wed, 16 October 2013 14:59 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
nishantranjan87 wrote on Thu, 17 October 2013 01:22
So there is no scope of creating any index and to improve the performance

Did you try doing that?

Go through these links :
http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used
http://www.orafaq.com/tuningguide/not%20using%20index.html
https://communities.bmc.com/community/historical/dbazine?cmp=marketing-domain_dbazine-com

These are the links from the performance tuning forum sticky at the top of the forum.a
Re: Need your help in tuning the query [message #598720 is a reply to message #598680] Thu, 17 October 2013 02:15 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
Is there any other way to tune it or current one is the best one?
Re: Need your help in tuning the query [message #598724 is a reply to message #598653] Thu, 17 October 2013 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Wed, 16 October 2013 16:30
in round numbers about half the rows are being changed;

Really? the row source says zero rows are being updated. You've completely ignored the effect of the exists clause.

An index on rcv_receiving_sub_ledger(rcv_transaction_id, code_combination_id) may help.
Re: Need your help in tuning the query [message #598725 is a reply to message #598724] Thu, 17 October 2013 02:59 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
And how about the index creation on RCV_TRANSACTIONS.
Re: Need your help in tuning the query [message #598726 is a reply to message #598725] Thu, 17 October 2013 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
what indexes are currently there?
Re: Need your help in tuning the query [message #598728 is a reply to message #598720] Thu, 17 October 2013 03:03 Go to previous messageGo to next message
John Watson
Messages: 4861
Registered: January 2010
Location: Global Village
Senior Member
Well, I'll have go. Unlike many people on this forum, I do know about the horror that is EBS.

Your query is passing through all 12 million rows of RCV_RECEIVING_SUB_LEDGER and applying a filter on pa_addition_flag and action_flag, which results in about 6 million rows. For each of these 6 million rows, you are executing that horrible correlated subquery SELECT 1 FROM RCV_TRANSACTIONS AND SO ON. The end result is that you update no rows at all. So a flippant answer to tuning the statement is to say "don't run it all: it doesn't do anything".

The reason it does nothing is that your filter against po_distributions (which is in fact a synonym for the po_distributions_all table) is throwing away every row. I would try to factor out the query against po_distributions_all. Begin by running this:
select count(*) from po_distributions_all po_dist where
Trunc(PO_DIST.expenditure_item_date) <= Trunc( 
                          Nvl(:B5, PO_DIST.expenditure_item_date)) 
                      AND PO_DIST.project_id > 0 
                      AND PO_DIST.accrue_on_receipt_flag = 'Y' ;
How many rows does this return? Make sure you use the same value for :B5 that you used when you ran the statement before.
Re: Need your help in tuning the query [message #598729 is a reply to message #598726] Thu, 17 October 2013 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your where clause is wrong by the way.
this bit:
               WHERE  ( ( RCV_TXN.destination_type_code = 'EXPENSE' ) 
                         OR ( RCV_TXN.destination_type_code = 'RECEIVING' 
                              AND ( RCV_TXN.transaction_type IN ( 
                                    'RETURN TO VENDOR', 'RETURN TO RECEIVING' 
                                                                ) ) ) ) 


Is overridden by this bit:
                      AND (( RCV_TXN.destination_type_code = 'EXPENSE' 


That 2nd check of RCV_TXN.destination_type_code isn't OR'd to anything so must always be true.
So the OR in the first code snippet is a waste of time, destination_type_code can never be 'RECEIVING'.

Re: Need your help in tuning the query [message #598733 is a reply to message #598729] Thu, 17 October 2013 04:12 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
As PO_Distributions scan 0 rows and index po_distributions_U1 scan 259604.So i am going to create index on po_distributions on below coloum

PO_DISTRIBUTION_ID,CODE_COMBINATION_ID ,TRUNC(PO_DIST.EXPENDITURE_ITEM_DATE),PROJECT_ID,ACCRUE_ON_RECEIPT_FLAG
Re: Need your help in tuning the query [message #598839 is a reply to message #598733] Fri, 18 October 2013 02:18 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
As PO_Distributions scan 0 rows and index po_distributions_U1 scan 259604.So i am going to create index on po_distributions on below coloum

PO_DISTRIBUTION_ID,CODE_COMBINATION_ID ,TRUNC(PO_DIST.EXPENDITURE_ITEM_DATE),PROJECT_ID,ACCRUE_ON_RECEIPT_FLAG?

Is it feasible?
Re: Need your help in tuning the query [message #598840 is a reply to message #598839] Fri, 18 October 2013 02:22 Go to previous messageGo to next message
John Watson
Messages: 4861
Registered: January 2010
Location: Global Village
Senior Member
There is little point in asking questions if you ignore answers already given.
Re: Need your help in tuning the query [message #598841 is a reply to message #598840] Fri, 18 October 2013 02:48 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
And before you do anything else to fix the performance you should fix the where clause. It's either doing pointless work or the is giving the wrong results. There's zero point tuning something that's doing the wrong thing.
Re: Need your help in tuning the query [message #598846 is a reply to message #598841] Fri, 18 October 2013 04:59 Go to previous messageGo to next message
nishantranjan87
Messages: 16
Registered: October 2013
Location: india
Junior Member
You mean to say we need to remove the OR condition
OR ( RCV_TXN.destination_type_code = 'RECEIVING'
Re: Need your help in tuning the query [message #598847 is a reply to message #598846] Fri, 18 October 2013 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
I have no idea what you need to do to it since I have no idea what the query is actually supposed to do.
All I know is that two different bits of the where clause contradict each other and so it can't be correct as it is.
Is the query supposed to pick up records where destination_type_code = 'RECEIVING'?
Re: Need your help in tuning the query [message #598848 is a reply to message #598846] Fri, 18 October 2013 05:21 Go to previous message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
nishantranjan87 wrote on Fri, 18 October 2013 15:29
You mean to say we need to remove the OR condition
OR ( RCV_TXN.destination_type_code = 'RECEIVING'


Nishant,

Cookiemonster is pointing to the contradicting conditions mentioned in the predicate.


 WHERE ((RCV_TXN.DESTINATION_TYPE_CODE = 'EXPENSE') OR
       (RCV_TXN.DESTINATION_TYPE_CODE = 'RECEIVING' AND
       (RCV_TXN.TRANSACTION_TYPE IN
       ('RETURN TO VENDOR', 'RETURN TO RECEIVING'))))
...
AND ((RCV_TXN.DESTINATION_TYPE_CODE = 'EXPENSE'
...


In of the cases, it would mean DESTINATION_TYPE_CODE = 'RECEIVING' and DESTINATION_TYPE_CODE = 'EXPENSE'
Think.
Previous Topic: Help with 12c vs 10.2g
Next Topic: Digging out the Wait events.
Goto Forum:
  


Current Time: Sat Dec 20 05:46:54 CST 2014

Total time taken to generate the page: 0.06614 seconds