Home » RDBMS Server » Performance Tuning » Need to decrease the cost as much as possible (Version: Oracle 10.2.0.3.0 OS: Solaris)
Need to decrease the cost as much as possible [message #517893] |
Fri, 29 July 2011 04:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/87818e70fa556dddb44b85b980f4e7e8?s=64&d=mm&r=g) |
SSharma
Messages: 17 Registered: July 2011 Location: INDIA
|
Junior Member |
|
|
Hi All Experts ,
I am bit new to tuning , and neeed to tune this query . At present it is taking 10 seconds to execute(actually it loads a webpage). And application team wants is this can be done in lesser time .
SQL Statement:
select
*
from
( select
segment2_.SEGMENT_NM as col_0_0_,
account4_.ACCOUNT_NM as col_1_0_,
borrower6_.BORROWER_NM as col_2_0_,
deal1_.DEAL_NAME as col_3_0_,
dealcatego7_.DEAL_CATEGORY_NM as col_4_0_,
modificati0_.COMMITMENT_AMT as col_5_0_,
modificati0_.MODIFICATION_SETUP_DT as col_6_0_,
region3_.REGION_NM as col_7_0_,
deal1_.DEAL_ID as col_8_0_,
refdealsta11_.DEAL_STATUS_NM as col_9_0_,
dealclass8_.DEAL_CLASS_NM as col_10_0_,
dealtype9_.DEAL_TYPE_NM as col_11_0_,
deal1_.CAPITAL_MARKETS_FL as col_12_0_,
modificati0_.EQUITY_FL as col_13_0_,
deal1_.PARTICIPATION_TRADE_OPT_FLAG as col_14_0_,
'' as col_15_0_,
modificati0_.MODIFICATION_ID as col_16_0_,
modificati0_.MODIFICATION_TYPE_FLAG as col_17_0_,
deal1_.DEAL_CD as col_18_0_,
refdealsta11_.DEAL_STATUS_CD as col_19_0_,
modificati0_.PITCH_APPROVAL_COMPLETED_FL as col_20_0_,
modificati0_.FUNDING_APPROVAL_COMPLETED_FL as col_21_0_,
modificati0_.ALL_APPROVALS_COMPLETED_FL as col_22_0_,
sponsor5_.SPONSOR_NM as col_23_0_
from
MODIFICATION modificati0_
inner join
DEAL deal1_
on modificati0_.DEAL_ID=deal1_.DEAL_ID
left outer join
DEAL_CLASS dealclass8_
on deal1_.DEAL_CLASS_ID=dealclass8_.DEAL_CLASS_ID
left outer join
DEAL_TYPE dealtype9_
on deal1_.DEAL_TYPE_ID=dealtype9_.DEAL_TYPE_ID
inner join
SEGMENT segment2_
on modificati0_.APPROVING_SEGMENT_ID=segment2_.SEGMENT_ID
inner join
REGION region3_
on modificati0_.APPROVING_REGION_ID=region3_.REGION_ID
inner join
ACCOUNT account4_
on modificati0_.ACCOUNT_ID=account4_.ACCOUNT_ID
inner join
SPONSOR sponsor5_
on modificati0_.SPONSOR_ID=sponsor5_.SPONSOR_ID
inner join
BORROWER borrower6_
on modificati0_.BORROWER_ID=borrower6_.BORROWER_ID
inner join
DEAL_CATEGORY dealcatego7_
on modificati0_.DEAL_CATEGORY_ID=dealcatego7_.DEAL_CATEGORY_ID
inner join
DEAL_STATUS_TIMELINE dealstatus10_
on modificati0_.DEAL_STATUS_ID=dealstatus10_.DEAL_STATUS_ID
and modificati0_.TIMELINE_TYPE_ID=dealstatus10_.TIMELINE_TYPE_ID
inner join
REF_DEAL_STATUS refdealsta11_
on dealstatus10_.DEAL_STATUS_ID=refdealsta11_.DEAL_STATUS_ID
inner join
DEAL_TEAM dealteams12_
on modificati0_.MODIFICATION_ID=dealteams12_.MODIFICATION_ID,
WF_USER wfuser18_
where
dealteams12_.MEMBER_USER_ID=wfuser18_.USER_ID
and modificati0_.DRAFT_FL='N'
and dealstatus10_.DEAL_STATUS_ID=1
and (
(
modificati0_.APPROVING_SEGMENT_ID=3
or segment2_.BUSINESS_ID=3
or modificati0_.APPROVING_SEGMENT_ID=45
or segment2_.BUSINESS_ID=2
or segment2_.BUSINESS_ID=1
or modificati0_.APPROVING_SEGMENT_ID=5
or segment2_.BUSINESS_ID not in (
2 , 4
)
or modificati0_.APPROVING_SEGMENT_ID=12
or modificati0_.APPROVING_SEGMENT_ID=45
or modificati0_.APPROVING_SEGMENT_ID=44
or modificati0_.APPROVING_SEGMENT_ID=12
or modificati0_.APPROVING_SEGMENT_ID=5
)
and (
modificati0_.CONFIDENTIALITY_FL='N'
or modificati0_.CONFIDENTIALITY_FL is null
)
or wfuser18_.SSO_ID=991123420
)
group by
segment2_.SEGMENT_NM ,
account4_.ACCOUNT_NM ,
borrower6_.BORROWER_NM ,
deal1_.DEAL_NAME ,
dealcatego7_.DEAL_CATEGORY_NM ,
modificati0_.COMMITMENT_AMT ,
modificati0_.MODIFICATION_SETUP_DT ,
region3_.REGION_NM ,
deal1_.DEAL_ID ,
refdealsta11_.DEAL_STATUS_NM ,
dealclass8_.DEAL_CLASS_NM ,
dealtype9_.DEAL_TYPE_NM ,
deal1_.CAPITAL_MARKETS_FL ,
modificati0_.EQUITY_FL ,
deal1_.PARTICIPATION_TRADE_OPT_FLAG ,
modificati0_.MODIFICATION_ID ,
modificati0_.MODIFICATION_TYPE_FLAG ,
deal1_.DEAL_CD ,
refdealsta11_.DEAL_STATUS_CD ,
modificati0_.PITCH_APPROVAL_COMPLETED_FL ,
modificati0_.FUNDING_APPROVAL_COMPLETED_FL ,
modificati0_.ALL_APPROVALS_COMPLETED_FL ,
sponsor5_.SPONSOR_NM )
where
rownum <= 500
Below is the execution plan
Execution Plan
----------------------------------------------------------
Plan hash value: 4036384643
--------------------------------------------------------------------------------
-----------------------------------------------
| Id | Operation | Name |
Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------------------------------
| 0 | SELECT STATEMENT | |
500 | 324K| | 4424 (2)| 00:00:54 |
|* 1 | COUNT STOPKEY | |
| | | | |
| 2 | VIEW | |
25099 | 15M| | 4424 (2)| 00:00:54 |
|* 3 | SORT GROUP BY STOPKEY | |
25099 | 8284K| 17M| 4424 (2)| 00:00:54 |
|* 4 | HASH JOIN RIGHT OUTER | |
25099 | 8284K| | 2605 (2)| 00:00:32 |
| 5 | TABLE ACCESS FULL | DEAL_TYPE |
670 | 16080 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | |
25099 | 7696K| | 2600 (2)| 00:00:32 |
| 7 | TABLE ACCESS FULL | DEAL_CLASS |
2020 | 40400 | | 8 (0)| 00:00:01 |
|* 8 | HASH JOIN | |
25099 | 7206K| 2072K| 2591 (2)| 00:00:32 |
| 9 | TABLE ACCESS FULL | WF_USER |
78440 | 1149K| | 335 (1)| 00:00:05 |
|* 10 | HASH JOIN | |
25611 | 6977K| | 1799 (3)| 00:00:22 |
|* 11 | HASH JOIN | |
2116 | 559K| | 897 (3)| 00:00:11 |
| 12 | TABLE ACCESS FULL | REGION |
301 | 5117 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | |
2116 | 524K| | 893 (3)| 00:00:11 |
|* 14 | HASH JOIN | |
2076 | 460K| | 793 (3)| 00:00:10 |
|* 15 | HASH JOIN | |
2071 | 404K| | 721 (2)| 00:00:09 |
|* 16 | HASH JOIN | |
2071 | 311K| | 133 (5)| 00:00:02 |
| 17 | TABLE ACCESS FULL | DEAL_CATEGORY |
1669 | 35049 | | 8 (0)| 00:00:01 |
|* 18 | HASH JOIN | |
2071 | 268K| | 125 (5)| 00:00:02 |
| 19 | TABLE ACCESS FULL | SPONSOR |
123 | 2706 | | 3 (0)| 00:00:01 |
|* 20 | HASH JOIN | |
2071 | 224K| | 121 (5)| 00:00:02 |
| 21 | TABLE ACCESS FULL | SEGMENT |
44 | 880 | | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS | |
2071 | 184K| | 118 (5)| 00:00:02 |
| 23 | NESTED LOOPS | |
1942 | 161K| | 118 (5)| 00:00:02 |
| 24 | TABLE ACCESS BY INDEX ROWID| REF_DEAL_STATUS |
1 | 19 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | REF_DEAL_STATUS_PK |
1 | | | 0 (0)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | MODIFICATION |
1942 | 125K| | 117 (5)| 00:00:02 |
|* 27 | INDEX UNIQUE SCAN | DEAL_STATUS_TIMELINE_PK |
1 | 6 | | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | DEAL |
57920 | 2601K| | 586 (2)| 00:00:08 |
| 29 | TABLE ACCESS FULL | BORROWER |
33694 | 888K| | 71 (2)| 00:00:01 |
| 30 | TABLE ACCESS FULL | ACCOUNT |
36542 | 963K| | 99 (2)| 00:00:02 |
|* 31 | TABLE ACCESS FULL | DEAL_TEAM |
146K| 1145K| | 900 (2)| 00:00:11 |
--------------------------------------------------------------------------------
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
3 - filter(ROWNUM<=500)
4 - access("DEAL1_"."DEAL_TYPE_ID"="DEALTYPE9_"."DEAL_TYPE_ID"(+))
6 - access("DEAL1_"."DEAL_CLASS_ID"="DEALCLASS8_"."DEAL_CLASS_ID"(+))
8 - access("DEALTEAMS12_"."MEMBER_USER_ID"="WFUSER18_"."USER_ID")
filter((("MODIFICATI0_"."APPROVING_SEGMENT_ID"=3 OR "MODIFICATI0_"."APPRO
VING_SEGMENT_ID"=5 OR
"MODIFICATI0_"."APPROVING_SEGMENT_ID"=12 OR "MODIFICATI0_"."APPROV
ING_SEGMENT_ID"=44 OR
"MODIFICATI0_"."APPROVING_SEGMENT_ID"=45) OR ("SEGMENT2_"."BUSINES
S_ID"=1 OR "SEGMENT2_"."BUSINESS_ID"=2 OR
"SEGMENT2_"."BUSINESS_ID"=3) OR "SEGMENT2_"."BUSINESS_ID"<>2 AND "
SEGMENT2_"."BUSINESS_ID"<>4) AND
("MODIFICATI0_"."CONFIDENTIALITY_FL" IS NULL OR "MODIFICATI0_"."CO
NFIDENTIALITY_FL"='N') OR
TO_NUMBER("WFUSER18_"."SSO_ID")=991123420)
10 - access("MODIFICATI0_"."MODIFICATION_ID"="DEALTEAMS12_"."MODIFICATION_ID")
11 - access("MODIFICATI0_"."APPROVING_REGION_ID"="REGION3_"."REGION_ID")
13 - access("MODIFICATI0_"."ACCOUNT_ID"="ACCOUNT4_"."ACCOUNT_ID")
14 - access("MODIFICATI0_"."BORROWER_ID"="BORROWER6_"."BORROWER_ID")
15 - access("MODIFICATI0_"."DEAL_ID"="DEAL1_"."DEAL_ID")
16 - access("MODIFICATI0_"."DEAL_CATEGORY_ID"="DEALCATEGO7_"."DEAL_CATEGORY_ID
")
18 - access("MODIFICATI0_"."SPONSOR_ID"="SPONSOR5_"."SPONSOR_ID")
20 - access("MODIFICATI0_"."APPROVING_SEGMENT_ID"="SEGMENT2_"."SEGMENT_ID")
25 - access("REFDEALSTA11_"."DEAL_STATUS_ID"=1)
26 - filter("MODIFICATI0_"."DEAL_STATUS_ID"=1 AND "MODIFICATI0_"."DRAFT_FL"='N
')
27 - access("DEALSTATUS10_"."DEAL_STATUS_ID"=1 AND "MODIFICATI0_"."TIMELINE_TY
PE_ID"="DEALSTATUS10_"."TIMELINE_TYPE_I
D")
31 - filter("DEALTEAMS12_"."MODIFICATION_ID" IS NOT NULL)
592 rows selected.
Thanks to all in advance
|
|
|
|
Re: Need to decrease the cost as much as possible [message #518127 is a reply to message #517894] |
Mon, 01 August 2011 07:54 ![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.gravatar.com/avatar/87818e70fa556dddb44b85b980f4e7e8?s=64&d=mm&r=g) |
SSharma
Messages: 17 Registered: July 2011 Location: INDIA
|
Junior Member |
|
|
Hi Michel ,
Apologise for reproducing the problem in wrong manner .
The correct execution plan is like this :
Plan hash value: 2838218337
-----------------------------------------------------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 500 | 324K| | 2728 (2)| 00:00:33
|
|* 1 | COUNT STOPKEY | | | | | |
|
| 2 | VIEW | | 5485 | 3562K| | 2728 (2)| 00:00:33
|
|* 3 | SORT GROUP BY STOPKEY | | 5485 | 1805K| 3832K| 2728 (2)| 00:00:33
|
|* 4 | HASH JOIN RIGHT OUTER | | 5485 | 1805K| | 2330 (2)| 00:00:28
|
| 5 | TABLE ACCESS FULL | DEAL_CLASS | 2020 | 40400 | | 8 (0)| 00:00:01
|
|* 6 | HASH JOIN RIGHT OUTER | | 5485 | 1697K| | 2322 (2)| 00:00:28
|
| 7 | TABLE ACCESS FULL | DEAL_TYPE | 670 | 16080 | | 5 (0)| 00:00:01
|
|* 8 | HASH JOIN | | 5485 | 1569K| 1584K| 2316 (2)| 00:00:28
|
|* 9 | HASH JOIN | | 5592 | 1518K| | 1799 (3)| 00:00:22
|
|* 10 | HASH JOIN | | 968 | 255K| | 897 (3)| 00:00:11
|
| 11 | TABLE ACCESS FULL | DEAL_CATEGORY | 1669 | 35049 | | 8 (0)| 00:00:01
|
|* 12 | HASH JOIN | | 968 | 235K| | 888 (3)| 00:00:11
|
| 13 | TABLE ACCESS FULL | REGION | 301 | 5117 | | 3 (0)| 00:00:01
|
|* 14 | HASH JOIN | | 970 | 219K| | 884 (2)| 00:00:11
|
|* 15 | HASH JOIN | | 957 | 191K| | 812 (2)| 00:00:10
|
|* 16 | HASH JOIN | | 957 | 149K| | 225 (4)| 00:00:03
|
|* 17 | HASH JOIN | | 995 | 129K| | 125 (5)| 00:00:02
|
| 18 | TABLE ACCESS FULL | SPONSOR | 123 | 2706 | | 3 (0)| 00:00:01
|
|* 19 | HASH JOIN | | 995 | 107K| | 121 (5)| 00:00:02
|
| 20 | TABLE ACCESS FULL | SEGMENT | 44 | 880 | | 3 (0)| 00:00:01
|
| 21 | NESTED LOOPS | | 1991 | 176K| | 118 (5)| 00:00:02
|
| 22 | NESTED LOOPS | | 1867 | 154K| | 118 (5)| 00:00:02
|
| 23 | TABLE ACCESS BY INDEX ROWID| REF_DEAL_STATUS | 1 | 19 | | 1 (0)| 00:00:01
|
|* 24 | INDEX UNIQUE SCAN | REF_DEAL_STATUS_PK | 1 | | | 0 (0)| 00:00:01
|
|* 25 | TABLE ACCESS FULL | MODIFICATION | 1867 | 120K| | 117 (5)| 00:00:02
|
|* 26 | INDEX UNIQUE SCAN | DEAL_STATUS_TIMELINE_PK | 1 | 6 | | 0 (0)| 00:00:01
|
| 27 | TABLE ACCESS FULL | ACCOUNT | 36780 | 969K| | 99 (2)| 00:00:02
|
| 28 | TABLE ACCESS FULL | DEAL | 57304 | 2518K| | 586 (2)| 00:00:08
|
| 29 | TABLE ACCESS FULL | BORROWER | 33959 | 895K| | 71 (2)| 00:00:01
|
|* 30 | TABLE ACCESS FULL | DEAL_TEAM | 144K| 1129K| | 900 (2)| 00:00:11
|
| 31 | TABLE ACCESS FULL | WF_USER | 79494 | 1164K| | 335 (1)| 00:00:05
|
-----------------------------------------------------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
3 - filter(ROWNUM<=500)
4 - access("DEAL1_"."DEAL_CLASS_ID"="DEALCLASS8_"."DEAL_CLASS_ID"(+))
6 - access("DEAL1_"."DEAL_TYPE_ID"="DEALTYPE9_"."DEAL_TYPE_ID"(+))
8 - access("DEALTEAMS12_"."MEMBER_USER_ID"="WFUSER18_"."USER_ID")
filter((("MODIFICATI0_"."APPROVING_SEGMENT_ID"=3 OR "MODIFICATI0_"."APPROVING_SEGMENT_ID"=5 OR
"MODIFICATI0_"."APPROVING_SEGMENT_ID"=12 OR "MODIFICATI0_"."APPROVING_SEGMENT_ID"=44 OR
"MODIFICATI0_"."APPROVING_SEGMENT_ID"=45) OR ("SEGMENT2_"."BUSINESS_ID"=1 OR "SEGMENT2_"."BUSINESS_ID"=2 OR
"SEGMENT2_"."BUSINESS_ID"=3) OR "SEGMENT2_"."BUSINESS_ID"<>2 AND "SEGMENT2_"."BUSINESS_ID"<>4) AND
("MODIFICATI0_"."CONFIDENTIALITY_FL" IS NULL OR "MODIFICATI0_"."CONFIDENTIALITY_FL"='N') OR
TO_NUMBER("WFUSER18_"."SSO_ID")=991123420)
9 - access("MODIFICATI0_"."MODIFICATION_ID"="DEALTEAMS12_"."MODIFICATION_ID")
10 - access("MODIFICATI0_"."DEAL_CATEGORY_ID"="DEALCATEGO7_"."DEAL_CATEGORY_ID")
12 - access("MODIFICATI0_"."APPROVING_REGION_ID"="REGION3_"."REGION_ID")
14 - access("MODIFICATI0_"."BORROWER_ID"="BORROWER6_"."BORROWER_ID")
15 - access("MODIFICATI0_"."DEAL_ID"="DEAL1_"."DEAL_ID")
16 - access("MODIFICATI0_"."ACCOUNT_ID"="ACCOUNT4_"."ACCOUNT_ID")
17 - access("MODIFICATI0_"."SPONSOR_ID"="SPONSOR5_"."SPONSOR_ID")
19 - access("MODIFICATI0_"."APPROVING_SEGMENT_ID"="SEGMENT2_"."SEGMENT_ID")
24 - access("REFDEALSTA11_"."DEAL_STATUS_ID"=1)
25 - filter("MODIFICATI0_"."DEAL_STATUS_ID"=1 AND "MODIFICATI0_"."DRAFT_FL"='N')
26 - access("DEALSTATUS10_"."DEAL_STATUS_ID"=1 AND "MODIFICATI0_"."TIMELINE_TYPE_ID"="DEALSTATUS10_"."TIMELINE_TYPE_I
D")
30 - filter("DEALTEAMS12_"."MODIFICATION_ID" IS NOT NULL)
CM: added [code] tags
[Updated on: Mon, 01 August 2011 08:02] by Moderator Report message to a moderator
|
|
|
|
Re: Need to decrease the cost as much as possible [message #518131 is a reply to message #518128] |
Mon, 01 August 2011 08: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) |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Looking at the query with all it's OR clauses and outer-joins you may struggle to improve it without a serious rewrite.
Couple of questions:
1) Why is the group by there?
2) Does the query use bind variables in reality rather than hard-coded values in the where clause?
|
|
|
|
Re: Need to decrease the cost as much as possible [message #518738 is a reply to message #518708] |
Fri, 05 August 2011 16:50 ![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) |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) Group by should be used with aggregate functions, you don't have any. If you need distinct rows, use the distinct keyword.
2) Then use binds to generate the plan - hard-coded values can change the plan.
Start with the index lists for ACCOUNT, DEAL, BORROWER, DEAL_TEAM and WF_USER.
|
|
|
|
|
Re: Need to decrease the cost as much as possible [message #518790 is a reply to message #518768] |
Sat, 06 August 2011 18:02 ![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) |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You need some more composite indexes.
eg:
DEAL_TEAM - MEMBER_USER_ID, MODIFICATION_ID
DEAL: deal_id, deal_type_id, deal_class_id
An index that includes all the columns for given table in referenced in the where clause is far more likely to be used.
|
|
|
|
Re: Need to decrease the cost as much as possible [message #519396 is a reply to message #519385] |
Thu, 11 August 2011 13:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/87818e70fa556dddb44b85b980f4e7e8?s=64&d=mm&r=g) |
SSharma
Messages: 17 Registered: July 2011 Location: INDIA
|
Junior Member |
|
|
Thanks Cookiemonster and Alex for your precious time ,but the modified query which i posted above (using index hints) is working fine and now taking about 6 seconds as against 10 secs it was taking prior . User is happy with it . I will surely try the First_rows hint and make some composite indexes as advised above if it needs to be tuned further .
Cheers,
Saurabh
|
|
|
Goto Forum:
Current Time: Sun Jul 07 15:46:06 CDT 2024
|