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 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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 #557634 is a reply to message #557632] Thu, 14 June 2012 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or better:
AND (to_date('12-06-2012', 'DD-MM-YYYY') BETWEEN cfd.VALID_FROM_DT AND cfd.VALID_TO_DT)

Wink

Regards
Michel
Re: Performance issue [message #557637 is a reply to message #557634] Thu, 14 June 2012 08:17 Go to previous messageGo to next message
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 #559109 is a reply to message #557637] Thu, 28 June 2012 14:36 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Sometimes rebuilding indexes and running new stats modifies the optimizer to select a different plan that will run faster.

alter index SCOTT.PK_EMP rebuild nologging online noparallel;
execute dbms_stats.gather_index_stats('SCOTT','PK_EMP',estimate_percent=>100)
alter index SCOTT.PK_EMP logging;
Re: Performance issue [message #559122 is a reply to message #559109] Thu, 28 June 2012 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sometimes rebuilding indexes and running new stats modifies the optimizer to select a different plan that will run faster


And will turn other DML slower.
So?

Regards
Michel
Re: Performance issue [message #559140 is a reply to message #557637] Fri, 29 June 2012 02:22 Go to previous message
John Watson
Messages: 4863
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.
Previous Topic: how to reduce dml locks?
Next Topic: tools for monitoring the load of the db
Goto Forum:
  


Current Time: Sun Dec 21 17:29:51 CST 2014

Total time taken to generate the page: 0.08838 seconds