Home » RDBMS Server » Performance Tuning » Performance issue (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 ,Windows XP)
| Performance issue [message #557630] |
Thu, 14 June 2012 07:10  |
 |
a_oracle
Messages: 98
Registered: November 2010
|
Member |
|
|
Hi All,
I have been facing performance issues with the below query which is taking nearly 6 minutes to retrive the data.
Please find the relevant details and the explain plan for the query. The query retrives mere 1450 records but seems from the explain plan that the joins
are ineffective.
Any help for the below issue will be highly appreciated.
Query:
select
'12-jun-2012',
rip.Invest_Vehicle_Id,
cfd.Client_Entity_Id,
sum(dla_vp.Contract_Amt * er.Exchange_Rt),
NVL(ratio_to_report(sum(dla_vp.Contract_Amt * er.Exchange_Rt))
OVER(partition by rip.Invest_Vehicle_Id),
0)
FROM loan_alloc la,
-- daily_loan_alloc dla,
Client_Fund_Det cfd,
ref_invest_pool rip,
Exchange_Rate er,
LOAN_ALLOC_VP la_vp,
DAILY_LOAN_ALLOC_VP dla_vp
WHERE dla_vp.Calendar_Dt = '12-jun-2012'
-- AND la.Loan_Alloc_Sq = dla.Loan_Alloc_Sq
AND la.Fund_Id = cfd.Client_Fund_Id
-- AND la.Invest_Pool_Id = rip.Invest_Pool_Id
AND la.Coll_Ccy_Id = rip.Coll_Ccy_Id
AND er.Calendar_Dt = '12-jun-2012'
AND er.From_Ccy_Id = la.Coll_Ccy_Id
AND er.To_Ccy_Id = 'USD'
AND ('12-jun-2012' BETWEEN cfd.VALID_FROM_DT AND cfd.VALID_TO_DT)
AND cfd.Active_Flg = 'Y'
AND ('12-jun-2012' BETWEEN rip.VALID_FROM_DT AND rip.VALID_TO_DT)
AND rip.Active_Flg = 'Y'
AND la_vp.Loan_Alloc_Sq = la.Loan_Alloc_Sq
AND la_vp.Loan_Alloc_Sq = dla_vp.Loan_Alloc_Sq
AND la_vp.loan_alloc_vp_id = dla_vp.loan_alloc_vp_id
AND la_vp.Invest_Pool_Id = rip.Invest_Pool_Id
group by '12-jun-2012', rip.Invest_Vehicle_Id, cfd.Client_Entity_Id;
Count of records in the above tables:
select count(*) from exchange_rate where calendar_dt = '12-jun-2012'; -- 9801
select count(*) from DAILY_LOAN_ALLOC_VP where calendar_dt = '12-jun-2012'; -- 875468
select count(*) from REF_INVEST_POOL ; -- 6147
select count(*) from CLIENT_FUND_DET ; -- 30357
select count(*) from LOAN_ALLOC; -- 77452802
select count(*) from LOAN_ALLOC_VP; -- 79722461
Index Information:
-- >> EXCHANGE_RATE which is also range (quarterly) partition
EXCHANGE_RATE_PK -- CALENDAR_DT, FROM_CCY_ID, TO_CCY_ID (primary key)
EXCHANGE_RATE_NU1 -- SOURCE_SQ
-- >> DAILY_LOAN_ALLOC_VP which is also range (monthly) partition
DAILY_LOAN_ALLOC_VP_PK -- CALENDAR_DT, LOAN_ALLOC_SQ, LOAN_ALLOC_VP_ID (primary key)
-- >> REF_INVEST_POOL
REF_INVEST_POOL_PK -- INVEST_POOL_ID, COLL_CCY_ID, CREATE_DATE_TIME (primary key)
REF_INVEST_POOL_NU1 -- INVEST_POOL_ID, COLL_CCY_ID, CURR_REC_FLG
REF_INVEST_POOL_NU2 -- INVEST_VEHICLE_ID
REF_INVEST_POOL_U1 -- INVEST_POOL_ID, COLL_CCY_ID, VALID_FROM_DT, VALID_TO_DT, ACTIVE_FLG
-- >> CLIENT_FUND_DET
CLIENT_FUND_DET_PK -- CLIENT_FUND_ID, CREATE_DATE_TIME
CLIENT_FUND_DET_NU1 -- CLIENT_FUND_ID, CURR_REC_FLG
CLIENT_FUND_DET_NU2 -- CLIENT_FUND_ID
CLIENT_FUND_DET_NU3 -- CLIENT_ENTITY_ID
CLIENT_FUND_DET_U1 -- CLIENT_FUND_ID, VALID_FROM_DT, VALID_TO_DT, ACTIVE_FLG
-- >> LOAN_ALLOC
LOAN_ALLOC_PK -- LOAN_ALLOC_SQ (primary key)
LOAN_ALLOC_NU1 -- LOAN_ID, ALLOC_ID, SOURCE_SQ, SETTLE_DT
LOAN_ALLOC_NU2 -- BUS_LINE_CD, SETTLE_CCY_ID
LOAN_ALLOC_NU3 -- FUND_ID, BUS_LINE_CD, COLL_CCY_ID, SETTLE_CCY_ID
LOAN_ALLOC_NU4 -- BORROWER_FUND_SQ
LOAN_ALLOC_NU5 -- FUND_ID, BROKER_ID, COLL_CCY_ID, COLL_TYPE_SQ, SETTLE_CCY_ID, BUS_LINE_CD, SEC_TYPE_ID, TERM_INDICATOR_CD, LOAN_ALLOC_SQ, FINANCE_TRADE_FLG
LOAN_ALLOC_TEMP -- COLL_TYPE_SQ
LOAN_ALLOC_TEMP1 -- LOAN_ALLOC_SQ, COLL_CCY_ID, BROKER_ID, FUND_ID, COLL_TYPE_SQ, INVEST_POOL_ID, LOAN_TYPE_ID (unique key)
-- >> LOAN_ALLOC_VP
LOAN_ALLOC_VP_PK -- LOAN_ALLOC_SQ, LOAN_ALLOC_VP_ID (primary key)
LOAN_ALLOC_VP_NU1 -- LOAN_ALLOC_SQ, LOAN_ALLOC_VP_ID, INVEST_POOL_ID
Explain Plan:
Plan hash value: 2301651982
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 144K| 18M| | 175K (26)| 00:05:45 | | |
| 1 | SORT GROUP BY | | 144K| 18M| 40M| 175K (26)| 00:05:45 | | |
|* 2 | HASH JOIN | | 144K| 18M| | 171K (27)| 00:05:38 | | |
| 3 | PARTITION RANGE SINGLE | | 101 | 1616 | | 52 (4)| 00:00:01 | 95 | 95 |
|* 4 | INDEX RANGE SCAN | EXCHANGE_RATE_PK | 101 | 1616 | | 52 (4)| 00:00:01 | 95 | 95 |
|* 5 | HASH JOIN | | 91472 | 10M| | 171K (27)| 00:05:38 | | |
|* 6 | TABLE ACCESS FULL | CLIENT_FUND_DET | 15131 | 531K| | 106 (23)| 00:00:01 | | |
|* 7 | HASH JOIN | | 74422 | 6250K| | 171K (27)| 00:05:37 | | |
|* 8 | TABLE ACCESS FULL | REF_INVEST_POOL | 3074 | 95294 | | 9 (34)| 00:00:01 | | |
|* 9 | HASH JOIN | | 878K| 46M| 36M| 171K (26)| 00:05:37 | | |
|* 10 | HASH JOIN | | 878K| 26M| 24M| 75692 (30)| 00:02:29 | | |
| 11 | PARTITION RANGE SINGLE| | 865K| 14M| | 4790 (4)| 00:00:10 | 31 | 31 |
|* 12 | INDEX RANGE SCAN | DAILY_LOAN_ALLOC_VP_PK | 865K| 14M| | 4790 (4)| 00:00:10 | 31 | 31 |
| 13 | INDEX FAST FULL SCAN | LOAN_ALLOC_VP_NU1 | 79M| 1064M| | 30610 (31)| 00:01:01 | | |
| 14 | INDEX FAST FULL SCAN | LOAN_ALLOC_TEMP1 | 77M| 1698M| | 47122 (21)| 00:01:33 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ER"."FROM_CCY_ID"="LA"."COLL_CCY_ID")
4 - access("ER"."CALENDAR_DT"=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ER"."TO_CCY_ID"='USD')
filter("ER"."TO_CCY_ID"='USD')
5 - access("LA"."FUND_ID"="CFD"."CLIENT_FUND_ID")
6 - filter("CFD"."ACTIVE_FLG"='Y' AND "CFD"."VALID_TO_DT">=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CFD"."VALID_FROM_DT"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - access("LA"."COLL_CCY_ID"="RIP"."COLL_CCY_ID" AND "LA_VP"."INVEST_POOL_ID"="RIP"."INVEST_POOL_ID")
8 - filter("RIP"."ACTIVE_FLG"='Y' AND "RIP"."VALID_FROM_DT"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "RIP"."VALID_TO_DT">=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("LA_VP"."LOAN_ALLOC_SQ"="LA"."LOAN_ALLOC_SQ")
10 - access("LA_VP"."LOAN_ALLOC_SQ"="DLA_VP"."LOAN_ALLOC_SQ" AND
"LA_VP"."LOAN_ALLOC_VP_ID"="DLA_VP"."LOAN_ALLOC_VP_ID")
12 - access("DLA_VP"."CALENDAR_DT"=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
|
|
|
|
| Re: Performance issue [message #557632 is a reply to message #557630] |
Thu, 14 June 2012 07:26  |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
to_date your date and it may stop doing those full table scans.
This:
AND ('12-jun-2012' BETWEEN cfd.VALID_FROM_DT AND cfd.VALID_TO_DT)
should be
AND (to_date('12-jun-2012', 'DD-MON-YYYY' BETWEEN cfd.VALID_FROM_DT AND cfd.VALID_TO_DT)
|
|
|
|
|
|
| Re: Performance issue [message #557637 is a reply to message #557634] |
Thu, 14 June 2012 08:17 |
|
a_oracle
Messages: 98
Registered: November 2010
|
Member |
|
|
Hi,
Thanks for the feedback, have changed the format accordingly but still the explain plan remains same:
explain plan for
select
to_date('12-06-2012', 'DD-MM-YYYY'),
rip.Invest_Vehicle_Id,
cfd.Client_Entity_Id,
sum(dla_vp.Contract_Amt * er.Exchange_Rt),
NVL(ratio_to_report(sum(dla_vp.Contract_Amt * er.Exchange_Rt))
OVER(partition by rip.Invest_Vehicle_Id),
0)
FROM loan_alloc la,
-- daily_loan_alloc dla,
Client_Fund_Det cfd,
ref_invest_pool rip,
Exchange_Rate er,
LOAN_ALLOC_VP la_vp,
DAILY_LOAN_ALLOC_VP dla_vp
WHERE dla_vp.Calendar_Dt = to_date('12-06-2012', 'DD-MM-YYYY')
-- AND la.Loan_Alloc_Sq = dla.Loan_Alloc_Sq
AND la.Fund_Id = cfd.Client_Fund_Id
-- AND la.Invest_Pool_Id = rip.Invest_Pool_Id
AND la.Coll_Ccy_Id = rip.Coll_Ccy_Id
AND er.Calendar_Dt = to_date('12-06-2012', 'DD-MM-YYYY')
AND er.From_Ccy_Id = la.Coll_Ccy_Id
AND er.To_Ccy_Id = 'USD'
AND (to_date('12-06-2012', 'DD-MM-YYYY') BETWEEN cfd.VALID_FROM_DT AND cfd.VALID_TO_DT)
AND cfd.Active_Flg = 'Y'
AND (to_date('12-06-2012', 'DD-MM-YYYY') BETWEEN rip.VALID_FROM_DT AND rip.VALID_TO_DT)
AND rip.Active_Flg = 'Y'
AND la_vp.Loan_Alloc_Sq = la.Loan_Alloc_Sq
AND la_vp.Loan_Alloc_Sq = dla_vp.Loan_Alloc_Sq
AND la_vp.loan_alloc_vp_id = dla_vp.loan_alloc_vp_id
AND la_vp.Invest_Pool_Id = rip.Invest_Pool_Id
group by to_date('12-06-2012', 'DD-MM-YYYY'), rip.Invest_Vehicle_Id, cfd.Client_Entity_Id;
select * from table(dbms_xplan.display):
Plan hash value: 1708927099
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 140K| 21M| | 179K (26)| 00:05:52 | | |
| 1 | WINDOW BUFFER | | 140K| 21M| | 179K (26)| 00:05:52 | | |
| 2 | SORT GROUP BY | | 140K| 21M| 47M| 179K (26)| 00:05:52 | | |
|* 3 | HASH JOIN | | 140K| 21M| | 174K (27)| 00:05:44 | | |
| 4 | PARTITION RANGE SINGLE | | 101 | 2929 | | 101 (37)| 00:00:01 | 95 | 95 |
|* 5 | TABLE ACCESS FULL | EXCHANGE_RATE | 101 | 2929 | | 101 (37)| 00:00:01 | 95 | 95 |
|* 6 | HASH JOIN | | 89250 | 11M| | 174K (27)| 00:05:44 | | |
|* 7 | TABLE ACCESS FULL | CLIENT_FUND_DET | 15131 | 531K| | 106 (23)| 00:00:01 | | |
|* 8 | HASH JOIN | | 72614 | 7020K| | 174K (27)| 00:05:43 | | |
|* 9 | TABLE ACCESS FULL | REF_INVEST_POOL | 3074 | 95294 | | 9 (34)| 00:00:01 | | |
|* 10 | HASH JOIN | | 857K| 55M| 46M| 174K (27)| 00:05:43 | | |
|* 11 | HASH JOIN | | 857K| 36M| 35M| 78652 (31)| 00:02:35 | | |
| 12 | PARTITION RANGE SINGLE| | 864K| 25M| | 10203 (26)| 00:00:21 | 31 | 31 |
|* 13 | TABLE ACCESS FULL | DAILY_LOAN_ALLOC_VP | 864K| 25M| | 10203 (26)| 00:00:21 | 31 | 31 |
| 14 | INDEX FAST FULL SCAN | LOAN_ALLOC_VP_NU1 | 79M| 1064M| | 27985 (31)| 00:00:55 | | |
| 15 | INDEX FAST FULL SCAN | LOAN_ALLOC_TEMP1 | 77M| 1698M| | 47122 (21)| 00:01:33 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ER"."FROM_CCY_ID"="LA"."COLL_CCY_ID")
5 - filter("ER"."CALENDAR_DT"=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"ER"."TO_CCY_ID"='USD')
6 - access("LA"."FUND_ID"="CFD"."CLIENT_FUND_ID")
7 - filter("CFD"."ACTIVE_FLG"='Y' AND "CFD"."VALID_TO_DT">=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CFD"."VALID_FROM_DT"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("LA"."COLL_CCY_ID"="RIP"."COLL_CCY_ID" AND "LA_VP"."INVEST_POOL_ID"="RIP"."INVEST_POOL_ID")
9 - filter("RIP"."ACTIVE_FLG"='Y' AND "RIP"."VALID_FROM_DT"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "RIP"."VALID_TO_DT">=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access("LA_VP"."LOAN_ALLOC_SQ"="LA"."LOAN_ALLOC_SQ")
11 - access("LA_VP"."LOAN_ALLOC_SQ"="DLA_VP"."LOAN_ALLOC_SQ" AND
"LA_VP"."LOAN_ALLOC_VP_ID"="DLA_VP"."LOAN_ALLOC_VP_ID")
13 - filter("DLA_VP"."CALENDAR_DT"=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
|
|
|
|
|
|
|
|
| Re: Performance issue [message #559140 is a reply to message #557637] |
Fri, 29 June 2012 02:22 |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I wonder about the hash joins at steps 10 and 11. The join at 10 scans 79m index keys, and keeps just 864000 of them, which is slightly over 1%. Then the join at 11 scans 77m index keys and keeps 857000. You could try nested loop joins instead, though 1% is on the edge: the hash joins are being implemented with index fast full scans, which is a pretty good access method. I would test by hinting it, either by hinting lested loops, or perhaps by using LEADING to change the driving table.
No promises - just an idea.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 13:47:29 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
