OraFAQ Forum: Performance Tuning » Need to decrease the cost as much as possible

Home » RDBMS Server » Performance Tuning » Need to decrease the cost as much as possible (Version: Oracle 10.2.0.3.0 OS: Solaris)

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

Need to decrease the cost as much as possible [message #517893]

Fri, 29 July 2011 04:10

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

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #517894 is a reply to message #517893]

Fri, 29 July 2011 04:13

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #518127 is a reply to message #517894]

Mon, 01 August 2011 07:54

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 #518128 is a reply to message #517894]

Mon, 01 August 2011 08:06

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Michel did not say this for fun:

Michel Cadot wrote on Fri, 29 July 2011 10:13

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Read those links and follow them in future.
I've added the [code] tags this time. Next time do it yourself.

Vast majority of the table accesses there are full table scans.
Maybe you need some indexes.

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

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #518708 is a reply to message #518131]

Fri, 05 August 2011 09:57

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member

Hi Cookiemonster ,
Thanks for replying .

As per your questions.

1) Group by clause is the requirement so as to get needed output.
2) query is using bind variables .

As per the execution plan , i can see some full table scans , we have to use index hints , but i am not confirm about usage . if you need i can provide you the info about indexes . Please tell me the tablename.column name for which you need index list.

Thanks,
Saurabh

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #518738 is a reply to message #518708]

Fri, 05 August 2011 16:50

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #518756 is a reply to message #518738]

Sat, 06 August 2011 04:17

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member

Below are the details for indexes :

ACCOUNT

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
ACCOUNT_UK ACCOUNT_NM
ACCOUNT_PK ACCOUNT_ID

DEAL

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
DEAL_COEID_IDX COE_ID
DEAL_LOSTORIGINI_IDX LOST_ORIGIN_ID
DEAL_LOSTREASONI_IDX LOST_REASON_ID
DEAL_LOSTTYPEID_IDX LOST_TYPE_ID
DEAL_OBLIGORRATIN_IDX OBLIGOR_RATING_ID
DEAL_ACCOUNTID_IDX ACCOUNT_ID
DEAL_BORROWERID_IDX BORROWER_ID
DEAL_DEALCATEGORY_IDX DEAL_CATEGORY_ID
DEAL_DEALCLASSID_IDX DEAL_CLASS_ID
DEAL_DEALSTATUSI_IDX DEAL_STATUS_ID
DEAL_DEALSTATUSI_IDX TIMELINE_TYPE_ID
DEAL_CLOSINGMEMO_IDX CLOSING_MEMO_FINAL_ACK_BY_ID
DEAL_UK DEAL_CD
DEAL_PK DEAL_ID
DEAL_VERTICALID_IDX VERTICAL_ID
DEAL_CREATEDUSER_IDX CREATED_USER_ID
DEAL_PRIMARYINDEX_IDX PRIMARY_INDEX_ID
DEAL_REGIONID_IDX REGION_ID
DEAL_SEGMENTID_IDX SEGMENT_ID
DEAL_APPROVALLEVE_IDX APPROVAL_LEVEL_ID
DEAL_BANKID_IDX BANK_ID

BORROWER

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
BORROWER_UK BORROWER_NM
BORROWER_PK BORROWER_ID

DEAL_TEAM

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
DEAL_TEAM_PK DEAL_TEAM_ID
DEALTEAM_TRADEID_IDX TRADE_ID
DEALTEAM_MEMBERUSERI_IDX MEMBER_USER_ID
DEALTEAM_MEMBERTYPER_IDX MEMBER_TYPE_ROLE_ID
DEALTEAM_MEMBERROLEI_IDX MEMBER_ROLE_ID
DEALTEAM_DEALID_IDX DEAL_ID
DEALTEAM_MODIFICATION_IDX MODIFICATION_ID

WF_USER

INDEX_NAME COLUMN_NAME
------------------------- --------------------
WF_USER_SSO_UK SSO_ID
WF_USER_PK USER_ID
WFUSER_VERTICALID_IDX VERTICAL_ID
WFUSER_REGIONID_IDX REGION_ID
WFUSER_SEGMENTID_IDX SEGMENT_ID
WFUSER_BANKID_IDX BANK_ID
WFUSER_BUSINESSID_IDX BUSINESS_ID

MODIFICATION

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
MODIFICATION_PK MODIFICATION_ID
MODIFICATION_VERTICALID_IDX VERTICAL_ID
MODIFICATION_CREATEDUSER_IDX CREATED_USER_ID
MODIFICATION_RISKCLASSIFI_IDX RISK_CLASSIFICATION_ID
MODIFICATION_APPROVINGREG_IDX APPROVING_REGION_ID
MODIFICATION_APPROVINGSEG_IDX APPROVING_SEGMENT_ID
MODIFICATION_MODIFICATION_IDX MODIFICATION_REASON_ID
MODIFICATION_OBLIGORRATIN_IDX OBLIGOR_RATING_ID
MODIFICATION_ACCOUNTID_IDX ACCOUNT_ID
MODIFICATION_BORROWERID_IDX BORROWER_ID
MODIFICATION_DEALID_IDX DEAL_ID
MODIFICATION_DEALCATEGORY_IDX DEAL_CATEGORY_ID
MODIFICATION_DEALSTATUSI_IDX DEAL_STATUS_ID
MODIFICATION_TIMELINETYPE_IDX TIMELINE_TYPE_ID

Let me know if you need some more info .

I tried using hints in this manner .

Code

select
*
from
( select
/*+ INDEX(dealteams12_ DEALTEAM_MEMBERUSERI_IDX) INDEX(wfuser18_ WF_USER_PK) INDEX(modificati0_ MODIFICATION_APPROVINGSEG_IDX) INDEX(wfuser18_ WF_USER_SSO_UK) */ 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_,

Rest code is same , but not confirm whether its correct or not so request you post inputs .

Thanks,
saurabh

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #518768 is a reply to message #518756]

Sat, 06 August 2011 09:58

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you already forget what has been said about formatting?

Regards
Michel

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #518790 is a reply to message #518768]

Sat, 06 August 2011 18:02

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #519385 is a reply to message #518790]

Thu, 11 August 2011 11:04

alex2000316
Messages: 1
Registered: August 2011

Junior Member

I'm not an expert..but..it's necessary the select * at the beginning?..i belevied that you can make it without this..my guess is that with the inner select you can bring a lot of records that you don't need (you need the last 500). In other words..maybe the inner select brings..(ie. 3000 records) and after this you make a select * from ( select..) where rownums <500.
Ah..i saw a not in() included...do you know the other values? as i know..the NOT IN is slower that IN..maybe if you can evaluate to change this.
Ah..another thing..maybe experts can say if it going to work or not..but you can try to use the /*+ First_rows(500)*/ hint

Report message to a moderator

Re: Need to decrease the cost as much as possible [message #519396 is a reply to message #519385]

Thu, 11 August 2011 13:36

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

Report message to a moderator

Previous Topic:	Performance tuning for select statement
Next Topic:	start with and connect by

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:51:26 CDT 2024