Home » RDBMS Server » Performance Tuning » query tuning
query tuning [message #255164] Mon, 30 July 2007 21:38 Go to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

hi ,

i need help to tune one query . actually its taking almost 5 hrs to referesh.
SELECT /*+ LEADING(LDTB_CONTRACT_BALANCE) index(LDTB_CONTRACT_BALANCE IND01_LDTB_CON_BALANCE) index(LDTB_CONTRACT_MASTER)*/
LDTB_CONTRACT_MASTER.BRANCH
     , LDTB_CONTRACT_MASTER.PRODUCT PROD_ID
     , MITM_CUSTOMER_DEFAULT.CUST_MIS_1 OUC
     , LDTB_CONTRACT_MASTER.CURRENCY CCY
     , SUM(LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL) PRINCIPAL_DUE
     ,SUM (NVL(FN_GET_LD_ACCRUAL(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'IS',STTM_DATES.NEXT_WORKING_DAY),0) - NVL(FN_GET_LD_AMT(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'IS','PAID',LDTB_CONTRACT_MASTER.VALUE_DATE, LDTB_CONTRACT_MASTER.MATURITY_DATE),0))        INTEREST_DUE 
     ,SUM (NVL(FN_GET_LD_ACCRUAL(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'N',STTM_DATES.NEXT_WORKING_DAY) ,0)     - NVL(FN_GET_LD_AMT(LDTB_CONTRACT_MASTER.CONTRACT_REF_NO,'N','PAID',LDTB_CONTRACT_MASTER.VALUE_DATE, LDTB_CONTRACT_MASTER.MATURITY_DATE),0))  OVERDUE_INTEREST
FROM LDTB_CONTRACT_MASTER     
   , LDTB_CONTRACT_PREFERENCE     
   , MITM_CUSTOMER_DEFAULT     
   , STTM_CUSTOMER         
   , CFTB_CONTRACT_INTEREST     
   , LDTB_CONTRACT_BALANCE    
   , STTM_BRANCH         
   , LDTM_PRODUCT_MASTER    
   , CSTB_CONTRACT        
   , STTM_DATES         
WHERE 
LDTB_CONTRACT_MASTER.CONTRACT_REF_NO         = CSTB_CONTRACT.CONTRACT_REF_NO
AND CSTB_CONTRACT.MODULE_CODE='LD'
AND LDTB_CONTRACT_MASTER.COUNTERPARTY         = MITM_CUSTOMER_DEFAULT.CUSTOMER
AND LDTB_CONTRACT_MASTER.COUNTERPARTY         = STTM_CUSTOMER.CUSTOMER_NO
AND LDTB_CONTRACT_PREFERENCE.CONTRACT_REF_NO= LDTB_CONTRACT_MASTER.CONTRACT_REF_NO
AND LDTB_CONTRACT_PREFERENCE.VERSION_NO     = LDTB_CONTRACT_MASTER.VERSION_NO
AND CFTB_CONTRACT_INTEREST.CONTRACT_REFERENCE_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO
AND CFTB_CONTRACT_INTEREST.COMPONENT         = LDTB_CONTRACT_MASTER.MAIN_COMP
AND CFTB_CONTRACT_INTEREST.EVENT_SEQUENCE_NO    = (SELECT MAX(Y.EVENT_SEQUENCE_NO) FROM CFTB_CONTRACT_INTEREST Y WHERE Y.CONTRACT_REFERENCE_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO)
AND LDTB_CONTRACT_BALANCE.CONTRACT_REF_NO         = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO    
--AND NVL(LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL,0) <> 0
AND (PRINCIPAL_OUTSTANDING_BAL>0 or PRINCIPAL_OUTSTANDING_BAL<0)
AND LDTM_PRODUCT_MASTER.PRODUCT         = LDTB_CONTRACT_MASTER.PRODUCT
AND STTM_BRANCH.BRANCH_CODE     = LDTB_CONTRACT_MASTER.BRANCH
AND STTM_DATES.BRANCH_CODE     = STTM_BRANCH.BRANCH_CODE 
AND LDTB_CONTRACT_MASTER.VERSION_NO     = (SELECT MAX(Z.VERSION_NO) FROM LDTBS_CONTRACT_MASTER Z WHERE Z.CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO)
--AND (LDTB_CONTRACT_BALANCE.PRINCIPAL_OUTSTANDING_BAL <> 0
AND ((PRINCIPAL_OUTSTANDING_BAL>0 or PRINCIPAL_OUTSTANDING_BAL<0)
OR ( EXISTS ( SELECT 1 FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = LDTB_CONTRACT_MASTER.CONTRACT_REF_NO AND AMOUNT_DUE > (NVL(AMOUNT_SETTLED,0) + NVL(ADJUSTED_AMOUNT,0)) AND DUE_DATE <= STTM_DATES.TODAY )))
GROUP BY LDTB_CONTRACT_MASTER.BRANCH, LDTB_CONTRACT_MASTER.PRODUCT, MITM_CUSTOMER_DEFAULT.CUST_MIS_1, LDTB_CONTRACT_MASTER.CURRENCY


actually main prolem lies in the sum function used . its taking time there . IF SUM function is removed it wrks fine

[Updated on: Mon, 30 July 2007 22:57] by Moderator

Report message to a moderator

Re: query tuning [message #255168 is a reply to message #255164] Mon, 30 July 2007 23:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Rather than the SUM, I suspect it is more likely that the problem is caused by the FN_GET_LD_ACCRUAL() function embedded within the SUM.

I bet you my left ___ that FN_GET_LD_ACCRUAL() is calling more SQL.

How many rows does it return?
How many rows does it return when you take out the GROUP BY?

Ross Leishman
Re: query tuning [message #255173 is a reply to message #255168] Mon, 30 July 2007 23:31 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member


yes u are correct the function also uses the select statement inside.

actually its a BO report which has two links .one give the daily data and the other gives the summarize data and both the data give the out put in same report.

the daily report takes around 50 min whereas the summarized report takes around 5 hrs to refesh. the daily report gives around 70,000 to 100,000 record daily.

there is almost no change in the report apart from sum function and the group by clause.
Re: query tuning [message #255175 is a reply to message #255164] Mon, 30 July 2007 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please post EXPLAIN_PLAN for both daily & summary reports
Re: query tuning [message #255218 is a reply to message #255175] Tue, 31 July 2007 01:01 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

for the summery report

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 10706 |
| 1 | SORT GROUP BY NOSORT | | 1 | 72 | 10706 |
| 2 | VIEW | | 1 | 72 | 10705 |
| 3 | FILTER | | | | |
| 4 | SORT GROUP BY | | 1 | 468 | |
| 5 | CONCATENATION | | | | |
| 6 | FILTER | | | | |
| 7 | NESTED LOOPS | | 4 | 1872 | 126 |
| 8 | NESTED LOOPS | | 3 | 1290 | 120 |
| 9 | NESTED LOOPS | | 3 | 954 | 114 |
| 10 | NESTED LOOPS | | 3 | 825 | 108 |
| 11 | NESTED LOOPS | | 3 | 756 | 105 |
| 12 | NESTED LOOPS | | 3 | 660 | 102 |
| 13 | NESTED LOOPS | | 3 | 597 | 102 |
| 14 | NESTED LOOPS | | 3 | 546 | 102 |
| 15 | NESTED LOOPS | | 3 | 450 | 99 |
| 16 | NESTED LOOPS | | 3 | 402 | 99 |
| 17 | INDEX RANGE SCAN | IND01_LDTB_CON_BALANCE | 32 | 640 | 3 |
| 18 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_MASTER | 1 | 114 | 3 |
| 19 | INDEX RANGE SCAN | CM_PK | 1 | | 2 |
| 20 | SORT AGGREGATE | | 1 | 20 | |
| 21 | FIRST ROW | | 1 | 20 | 3 |
| 22 | INDEX RANGE SCAN (MIN/MAX)| CM_PK | 1 | 20 | 3 |
| 23 | INDEX UNIQUE SCAN | SYS_C00123682 | 1 | 16 | 0 |
| 24 | TABLE ACCESS BY INDEX ROWID | STTM_DATES | 1 | 32 | 1 |
| 25 | INDEX UNIQUE SCAN | SYS_C00123757 | 1 | | 0 |
| 26 | INDEX UNIQUE SCAN | LNPROD_PK | 1 | 17 | 0 |
| 27 | INDEX UNIQUE SCAN | SYS_C00123717 | 1 | 21 | 0 |
| 28 | INDEX UNIQUE SCAN | CP1_PK | 1 | 32 | 1 |
| 29 | TABLE ACCESS BY INDEX ROWID | MITM_CUSTOMER_DEFAULT | 1 | 23 | 1 |
| 30 | INDEX UNIQUE SCAN | PK_CUSTOMER_DEFAULT | 1 | | 0 |
| 31 | INDEX RANGE SCAN | IND_INT_CRN_COMP_ESN | 1 | 43 | 2 |
| 32 | TABLE ACCESS BY INDEX ROWID | CSTB_CONTRACT | 1 | 112 | 2 |
| 33 | INDEX UNIQUE SCAN | CC1_PK | 1 | | 1 |
| 34 | INDEX RANGE SCAN | IND_INT_CRN_COMP_ESN | 1 | 38 | 2 |
| 35 | TABLE ACCESS BY INDEX ROWID | CSTB_AMOUNT_DUE | 1 | 37 | 4 |
| 36 | INDEX RANGE SCAN | DUE_PK1 | 1 | | 3 |
| 37 | FILTER | | | | |
| 38 | NESTED LOOPS | | 187 | 87516 | 7124 |
| 39 | HASH JOIN | | 145 | 62350 | 6833 |
| 40 | NESTED LOOPS | | 145 | 59015 | 6726 |
| 41 | NESTED LOOPS | | 145 | 55970 | 6726 |
| 42 | HASH JOIN | | 145 | 39730 | 6435 |
| 43 | NESTED LOOPS | | 147 | 37338 | 5933 |
| 44 | HASH JOIN | | 147 | 32634 | 5785 |
| 45 | HASH JOIN | | 10475 | 1831K| 4908 |
| 46 | INDEX FULL SCAN | LNPROD_PK | 93 | 1581 | 1 |
| 47 | HASH JOIN | | 10475 | 1657K| 4906 |
| 48 | NESTED LOOPS | | 38 | 1824 | 3 |
| 49 | TABLE ACCESS FULL | STTM_DATES | 38 | 1216 | 3 |
| 50 | INDEX UNIQUE SCAN | SYS_C00123682 | 1 | 16 | 0 |
| 51 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_MASTER | 10475 | 1166K| 4902 |
| 52 | INDEX FULL SCAN | CM_PK | 10475 | | 887 |
| 53 | SORT AGGREGATE | | 1 | 20 | |
| 54 | FIRST ROW | | 1 | 20 | 3 |
| 55 | INDEX RANGE SCAN (MIN/MAX) | CM_PK | 1 | 20 | 3 |
| 56 | INDEX FAST FULL SCAN | IND_INT_CRN_COMP_ESN | 147K| 6204K| 382 |
| 57 | INDEX UNIQUE SCAN | CP1_PK | 1 | 32 | 1 |
| 58 | INDEX RANGE SCAN | IND01_LDTB_CON_BALANCE | 112K| 2194K| 496 |
| 59 | TABLE ACCESS BY INDEX ROWID | CSTB_CONTRACT | 1 | 112 | 2 |
| 60 | INDEX UNIQUE SCAN | CC1_PK | 1 | | 1 |
| 61 | INDEX UNIQUE SCAN | SYS_C00123717 | 1 | 21 | 0 |
| 62 | TABLE ACCESS FULL | MITM_CUSTOMER_DEFAULT | 31465 | 706K| 106 |
| 63 | INDEX RANGE SCAN | IND_INT_CRN_COMP_ESN | 1 | 38 | 2 |
| 64 | TABLE ACCESS BY INDEX ROWID | CSTB_AMOUNT_DUE | 1 | 37 | 4 |
| 65 | INDEX RANGE SCAN | DUE_PK1 | 1 | | 3 |
---------------------------------------------------------------------------------------------------------

for the daily report


-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 533 | 3292 |
| 1 | FILTER | | | | |
| 2 | SORT GROUP BY | | 1 | 533 | 3292 |
| 3 | FILTER | | | | |
| 4 | PX COORDINATOR | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10006 | 1 | 533 | 3587 |
| 6 | NESTED LOOPS | | 1 | 533 | 3587 |
| 7 | NESTED LOOPS | | 1 | 496 | 3584 |
| 8 | NESTED LOOPS | | 1 | 451 | 3583 |
| 9 | NESTED LOOPS | | 1 | 428 | 3582 |
| 10 | NESTED LOOPS | | 1 | 392 | 3580 |
| 11 | NESTED LOOPS | | 1 | 358 | 3578 |
| 12 | NESTED LOOPS | | 1 | 315 | 3576 |
| 13 | HASH JOIN | | 31 | 7998 | 3483 |
| 14 | HASH JOIN | | 31 | 7409 | 3478 |
| 15 | HASH JOIN | | 31 | 6417 | 3473 |
| 16 | HASH JOIN | | 31 | 5177 | 3468 |
| 17 | BUFFER SORT | | | | |
| 18 | PX RECEIVE | | 125K| 10M| 1691 |
| 19 | PX SEND BROADCAST | :TQ10000 | 125K| 10M| 1691 |
| 20 | TABLE ACCESS FULL | LDTB_CONTRACT_MASTER | 125K| 10M| 1691 |
| 21 | HASH JOIN | | 3223K| 248M| 1630 |
| 22 | PX RECEIVE | | 114K| 2229K| 144 |
| 23 | PX SEND HASH | :TQ10005 | 114K| 2229K| 144 |
| 24 | PX BLOCK ITERATOR | | 114K| 2229K| 144 |
| 25 | INDEX FAST FULL SCAN| IND01_LDTB_CON_BALANCE | 114K| 2229K| 144 |
| 26 | BUFFER SORT | | | | |
| 27 | PX RECEIVE | | 3223K| 187M| 1341 |
| 28 | PX SEND HASH | :TQ10001 | 3223K| 187M| 1341 |
| 29 | HASH JOIN | | 3223K| 187M| 1341 |
| 30 | TABLE ACCESS FULL | TATM_SLAB | 88 | 2024 | 3 |
| 31 | TABLE ACCESS FULL | TATB_TXNRULE | 219K| 8155K| 1200 |
| 32 | BUFFER SORT | | | | |
| 33 | PX RECEIVE | | 38 | 1520 | 3 |
| 34 | PX SEND BROADCAST | :TQ10002 | 38 | 1520 | 3 |
| 35 | TABLE ACCESS FULL | STTM_BRANCH | 38 | 1520 | 3 |
| 36 | BUFFER SORT | | | | |
| 37 | PX RECEIVE | | 38 | 1216 | 3 |
| 38 | PX SEND BROADCAST | :TQ10003 | 38 | 1216 | 3 |
| 39 | TABLE ACCESS FULL | STTM_DATES | 38 | 1216 | 3 |
| 40 | BUFFER SORT | | | | |
| 41 | PX RECEIVE | | 93 | 1767 | 3 |
| 42 | PX SEND BROADCAST | :TQ10004 | 93 | 1767 | 3 |
| 43 | TABLE ACCESS FULL | LDTM_PRODUCT_MASTER | 93 | 1767 | 3 |
| 44 | TABLE ACCESS BY INDEX ROWID | CFTB_CONTRACT_INTEREST | 1 | 57 | 3 |
| 45 | INDEX RANGE SCAN | IND_INT_CRN_COMP_ESN | 1 | | 2 |
| 46 | TABLE ACCESS BY INDEX ROWID | CSTB_CONTRACT | 1 | 43 | 2 |
| 47 | INDEX UNIQUE SCAN | CC1_PK | 1 | | 1 |
| 48 | SORT AGGREGATE | | 1 | 21 | |
| 49 | INDEX RANGE SCAN | IND_INT_CRN_COMP_ESN | 1 | 21 | 3 |
| 50 | TABLE ACCESS BY INDEX ROWID | LDTB_CONTRACT_PREFERENCE | 1 | 34 | 2 |
| 51 | INDEX UNIQUE SCAN | CP1_PK | 1 | | 1 |
| 52 | INDEX RANGE SCAN | PK_TATB_TXNRULE_DETAIL | 1 | 36 | 2 |
| 53 | TABLE ACCESS BY INDEX ROWID | MITM_CUSTOMER_DEFAULT | 1 | 23 | 1 |
| 54 | INDEX UNIQUE SCAN | PK_CUSTOMER_DEFAULT | 1 | | 0 |
| 55 | TABLE ACCESS BY INDEX ROWID | STTM_CUSTOMER | 1 | 45 | 1 |
| 56 | INDEX UNIQUE SCAN | SYS_C00123717 | 1 | | 0 |
| 57 | INDEX RANGE SCAN | CM_PK | 1 | 37 | 2 |
| 58 | TABLE ACCESS BY INDEX ROWID | CSTB_AMOUNT_DUE | 1 | 37 | 4 |
| 59 | INDEX RANGE SCAN | DUE_PK1 | 1 | | 3 |
-----------------------------------------------------------------------------------------------------

Re: query tuning [message #255219 is a reply to message #255218] Tue, 31 July 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and the sticky at the top of the forum.
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: query tuning [message #255222 is a reply to message #255219] Tue, 31 July 2007 01:06 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

hi pla find the explain plan in attachedment
  • Attachment: explan.txt
    (Size: 13.96KB, Downloaded 278 times)
Re: query tuning [message #255437 is a reply to message #255168] Tue, 31 July 2007 22:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Tue, 31 July 2007 14:01
How many rows does it return?
How many rows does it return when you take out the GROUP BY?


I don't see an answer to these questions. I also don't see any reference to daily or summary in the SQL.
Re: query tuning [message #255441 is a reply to message #255437] Tue, 31 July 2007 22:56 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

the number of rows return with the group by is something around

100 rows (reason there is some 100 branches). without group by it give 70,000 to 100000.

Re: query tuning [message #255446 is a reply to message #255441] Tue, 31 July 2007 23:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So that means it is calling the functions about 100,000 times. Since there are 4 functions in the SELECT, thats 400,000 calls.

Try running

SET TIMING ON

SELECT FN_GET_LD_ACCRUAL(
    LDTB_CONTRACT_MASTER.CONTRACT_REF_NO
,   'N', trunc(sysdate))
FROM   LDTB_CONTRACT_MASTER
WHERE  ROWNUM <= 1000


How long does that take? This is 1000 calls of the 1st function. Multiply by 2000 to get an indication of the total time required by the func.

Do the same with the other function.

How much time in total is spent on the function calls. If it is a lot, there is no point tuning the query - you need to work out another way to achieve the function call.

Ross Leishman
Re: query tuning [message #255449 is a reply to message #255446] Tue, 31 July 2007 23:32 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

it takes 11 sec for 1000 function . times is almost same for the other function.
its a Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi.

earlier the query was running in oracle 9i then it was fine . after conversion the problem started.
Re: query tuning [message #255544 is a reply to message #255449] Wed, 01 August 2007 03:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK. So forget about tuning the SQL, you need to tune the function.

What SQLs are in the function? Post them plus the explain plans.

Ross Leishman
Re: query tuning [message #255843 is a reply to message #255544] Thu, 02 August 2007 01:17 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

sorry for a delayed response .pls find deatils regarding the query in the attached file. Pls tell me how to get the explain plan for the function .
  • Attachment: Document1.txt
    (Size: 22.36KB, Downloaded 301 times)
Re: query tuning [message #255903 is a reply to message #255843] Thu, 02 August 2007 05:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Looks like you should probably focus on this one:
SELECT SUM(A.TILL_DATE_ACCRUAL) 
FROM
 LDTBS_CONTRACT_ACCRUAL_HISTORY A WHERE A.CONTRACT_REF_NO = :B2 AND 
  A.VALUE_DATE <= :B1 AND A.ACC_ENTRY_PASSED = 'Y' AND A.COMPONENT IN ( 
  SELECT DISTINCT COMPONENT FROM LDTB_AMOUNT_DUE WHERE CONTRACT_REF_NO = 
  A.CONTRACT_REF_NO AND COMPONENT_TYPE IN (SUBSTR(:B3 ,1,1),SUBSTR(:B3 ,2,1)))
   AND A.EVENT_SEQ_NO = ( SELECT MAX(EVENT_SEQ_NO) FROM 
  LDTB_CONTRACT_ACCRUAL_HISTORY WHERE CONTRACT_REF_NO = A.CONTRACT_REF_NO AND 
  COMPONENT = A.COMPONENT AND ACC_ENTRY_PASSED = 'Y' AND VALUE_DATE <= :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute   1469      0.09       0.10          0          0          0           0
Fetch     1469   1025.08    1114.75      19847  176061253          0        1469
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2938   1025.17    1114.85      19847  176061253          0        1469

Which I think is this one in the first function:
	Select sum(a.TILL_DATE_ACCRUAL) into l_amount
	from ldtbs_contract_accrual_history a
	where a.contract_ref_no = p_contract_ref_no
	and a.VALUE_DATE <= l_accrual_till
	and a.acc_entry_passed = 'Y'
	and a.component in ( select distinct component
		from ldtb_amount_due
		where contract_ref_no = a.contract_ref_no
		and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1)))
	and a.event_seq_no = ( select max(event_seq_no)
		from ldtb_contract_accrual_history
		where contract_ref_no = a.contract_ref_no
		and component = a.component
		and acc_entry_passed = 'Y'
		and VALUE_DATE <= l_accrual_till );

This is interesting because it is almost identical to:
	Select sum(a.TILL_DATE_ACCRUAL) into l_amount
	from ldtbs_contract_accrual_history a
	where a.contract_ref_no = p_contract_ref_no
	and a.VALUE_DATE <= l_accrual_till
	and a.acc_entry_passed = 'Y'
	and a.component in ( select distinct component
		from ldtb_amount_due
		where contract_ref_no = a.contract_ref_no
		and component_type in (substr(p_component_type,1,1),substr(p_component_type,2,1),substr(p_component_type,3,1)))
	and a.event_seq_no = ( select max(event_seq_no)
		from ldtb_contract_accrual_history
		where contract_ref_no = a.contract_ref_no
		and component = a.component
		and acc_entry_passed = 'Y'
		and VALUE_DATE <= l_accrual_till );


Post Explain Plans on both these queries. Cut the SQL out of the TK-Prof output and DO NOT REMOVE THE BIND VARIABLES - otherwise it will change the plan.

Ross Leishman
Re: query tuning [message #255923 is a reply to message #255903] Thu, 02 August 2007 07:11 Go to previous messageGo to next message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

pls find the explain plan for the query in the tkproof

  • Attachment: putty2.txt
    (Size: 13.78KB, Downloaded 313 times)
Re: query tuning [message #256046 is a reply to message #255923] Thu, 02 August 2007 18:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The plans are subtly different, but not materially different.

I suspect you have a problem with skewed data. ie. The SQL runs 1000 times and 999 of them are really fast, but one is really slow because of an uneven distribution of data.

Look for contracts in LDTBS_CONTRACT_ACCRUAL_HISTORY with hundreds (or thousands) of rows.
SELECT CONTRACT_REF_NO
FROM   LDTBS_CONTRACT_ACCRUAL_HISTORY
GROUP BY CONTRACT_REF_NO
HAVING COUNT(*) > 1000


Do the same for LDTB_AMOUNT_DUE
SELECT CONTRACT_REF_NO, COUNT(*)
FROM LDTB_AMOUNT_DUE 
GROUP BY CONTRACT_REF_NO
HAVING COUNT(*) > 1000


I am assuming that LDTBS_CONTRACT_ACCRUAL_HISTORY is a synonym for LDTB_CONTRACT_ACCRUAL_HISTORY - ie. they are the same table.

If these SQLs don't return anything, reduce the number to 500 and try again. Tell me the results.
Re: query tuning [message #256070 is a reply to message #256046] Thu, 02 August 2007 21:47 Go to previous message
vimal.negi
Messages: 9
Registered: July 2007
Location: DWQDQWDQW
Junior Member

yes u r correct LDTBS_CONTRACT_ACCRUAL_HISTORY is synonym for LDTB_CONTRACT_ACCRUAL_HISTORY . pls find the result of the query's with the attachment.


thanks ,
vimal
  • Attachment: Document1.txt
    (Size: 23.72KB, Downloaded 282 times)
Previous Topic: CAN WE USE HINT IN VIEW QUERY STATEMENT
Next Topic: Temp Datafiles Size
Goto Forum:
  


Current Time: Thu Dec 08 22:29:27 CST 2016

Total time taken to generate the page: 0.05952 seconds