Need your help in tuning the query [message #598635] |
Wed, 16 October 2013 08:23 |
|
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 |
Lalit Kumar B
Messages: 3174 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 #598639 is a reply to message #598638] |
Wed, 16 October 2013 08:55 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
BlackSwan wrote on Wed, 16 October 2013 19:20how/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 #598645 is a reply to message #598642] |
Wed, 16 October 2013 09:21 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
nishantranjan87 wrote on Wed, 16 October 2013 19:35There 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 #598679 is a reply to message #598672] |
Wed, 16 October 2013 14:56 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
nishantranjan87 wrote on Thu, 17 October 2013 01:02in 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 #598724 is a reply to message #598653] |
Thu, 17 October 2013 02:56 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Wed, 16 October 2013 16:30in 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 #598728 is a reply to message #598720] |
Thu, 17 October 2013 03:03 |
John Watson
Messages: 8938 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 |
cookiemonster
Messages: 13925 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 #598841 is a reply to message #598840] |
Fri, 18 October 2013 02:48 |
cookiemonster
Messages: 13925 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 #598847 is a reply to message #598846] |
Fri, 18 October 2013 05:15 |
cookiemonster
Messages: 13925 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 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
nishantranjan87 wrote on Fri, 18 October 2013 15:29You 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.
|
|
|