My production db is on oracle 10g(10.2.0.5) on windows 2003 32 bit.
Below query is taking too much time to execute.
Please see the execution plan following query.
- Code: Select all
SELECT -- san_10apr10 : comment below line and byfercate by count of soft limits and count of soft manual limits breach.
COUNT( DISTINCT CASE WHEN chk_lim_data.available_limit < 0 AND lim_sevr_mast.ignore_breach_for_evaluation = 'Y' THEN chk_lim.limit_syscode END) total_soft_breach
-- san_10apr10 : this will count the total soft limit breaches.
--Count( distinct case when chk_lim_data.available_limit < 0 and lim_sevr_mast.ignore_breach_for_evaluation = 'Y' AND lim_sevr_mast.limit_severity_syscode = 2 then chk_lim.limit_syscode end) total_soft_breach
-- san_10apr10 : this will count the total soft manual limit breaches.
--,Count( distinct case when chk_lim_data.available_limit < 0 and lim_sevr_mast.ignore_breach_for_evaluation = 'Y' AND lim_sevr_mast.limit_severity_syscode = 3 then chk_lim.limit_syscode end) total_soft_manual_breach
,COUNT( DISTINCT CASE WHEN chk_lim_data.available_limit < 0 AND lim_sevr_mast.ignore_breach_for_evaluation = 'N' THEN chk_lim.limit_syscode END) total_hard_breach
,COUNT( DISTINCT chk_lim.limit_syscode ) total_limit_checked
-- san_10apr10 : one more variable v_total_soft_manual_breach included here
INTO v_total_soft_breach,v_total_hard_breach, v_total_limit_checked
--into v_total_soft_breach,v_total_soft_manual_breach,v_total_hard_breach, v_total_limit_checked
FROM LMS_CHECKED_LIMITS chk_lim
inner join LMS_CHECKED_LIMITS_DATA chk_lim_data
ON chk_lim.checked_limit_syscode = chk_lim_data.checked_limit_syscode
--------nel_12mar08 added by nelson: user was hetting mesage as hard limit breached (which was pasive but no popup was shown.
-------- when data is inserted in lms_Response_Data only active breaches was considered but here when response was framed pasive was not excluded hence the issue.
-- the table has ben added because active_Pasive flag is maintained at tran level
inner join LMS_CHECKED_LIMITS_TRAN_LEVEL trn_chk_data ON
/* san_25sep09 :- commented below 7 lines and write without using Nvl() function for enhancing performance tuning.
Nvl(chk_lim_data.instrument_syscode,0) = Nvl(trn_chk_data.instrument_syscode,0) and
Nvl(chk_lim_data.account_syscode,0) = Nvl(trn_chk_data.account_syscode,0) and
Nvl(chk_lim_data.lov_data_syscode_group1,0) = Nvl(trn_chk_data.lov_data_syscode_group1,0) and
Nvl(chk_lim_data.lov_data_syscode_group2,0) = Nvl(trn_chk_data.lov_data_syscode_group2,0) and
Nvl(chk_lim_data.lov_data_syscode_group3,0) = Nvl(trn_chk_data.lov_data_syscode_group3,0) and
Nvl(chk_lim_data.lov_data_syscode_group4,0) = Nvl(trn_chk_data.lov_data_syscode_group4,0) and
Nvl(chk_lim_data.lov_data_syscode_group5,0) = Nvl(trn_chk_data.lov_data_syscode_group5,0) and */
chk_lim_data.instrument_syscode = trn_chk_data.instrument_syscode AND
chk_lim_data.account_syscode = trn_chk_data.account_syscode AND
chk_lim_data.lov_data_syscode_group1 = trn_chk_data.lov_data_syscode_group1 AND
chk_lim_data.lov_data_syscode_group2 = trn_chk_data.lov_data_syscode_group2 AND
chk_lim_data.lov_data_syscode_group3 = trn_chk_data.lov_data_syscode_group3 AND
chk_lim_data.lov_data_syscode_group4 = trn_chk_data.lov_data_syscode_group4 AND
chk_lim_data.lov_data_syscode_group5 = trn_chk_data.lov_data_syscode_group5 AND
NVL(chk_lim_data.transaction_syscode,trn_chk_data.transaction_syscode) = trn_chk_data.transaction_syscode AND
chk_lim.limit_syscode = trn_chk_data.limit_syscode AND
chk_lim.transaction_master_syscode = trn_chk_data.transaction_master_syscode AND
chk_lim.inflow_outflow = trn_chk_data.inflow_outflow AND
NVL(trn_chk_data.active_passive,'A') = 'A'
inner join LMS_LIMIT_MASTER lim_mast
ON chk_lim.limit_syscode = lim_mast.limit_syscode
inner join LMS_LIMIT_VALUES lim_values --deep020409 added
ON lim_values.level_code = chk_lim.level_code AND lim_values.limit_syscode = chk_lim.limit_syscode
inner join LMS_LIMIT_SEVERITY_MASTER lim_sevr_mast --deep020409 added
ON lim_values.limit_severity_syscode = lim_sevr_mast.limit_severity_syscode
inner join LMS_PACKET_DETAILS pack_detail --san_22may08
ON chk_lim.transaction_master_syscode = pack_detail.transaction_master_syscode
WHERE pack_detail.process_syscode = 50687;
--where chk_Lim.transaction_Master_Syscode = p_Tran_Master_Syscode_Curr;
- Code: Select all
=========================================================================================================================
TOTAL_SOFT_BREACH TOTAL_HARD_BREACH TOTAL_LIMIT_CHECKED
----------------- ----------------- -------------------
1 0 6
Elapsed: 00:02:10.48
Execution Plan
----------------------------------------------------------
Plan hash value: 4076069282
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 | | 1768 (2)| 00:00:22 |
| 1 | SORT GROUP BY | | 1 | 124 | | | |
| 2 | NESTED LOOPS | | 1 | 124 | | 1768 (2)| 00:00:22 |
|* 3 | HASH JOIN | | 1 | 119 | | 1767 (2)| 00:00:22 |
| 4 | NESTED LOOPS | | 1 | 109 | | 1764 (2)| 00:00:22 |
| 5 | NESTED LOOPS | | 1 | 99 | | 1763 (2)| 00:00:22 |
|* 6 | HASH JOIN | | 10 | 740 | 2616K| 1753 (2)| 00:00:22 |
| 7 | TABLE ACCESS FULL | LMS_CHECKED_LIMITS_DATA | 59485 | 1916K| | 153 (2)| 00:00:02 |
|* 8 | TABLE ACCESS FULL | LMS_CHECKED_LIMITS_TRAN_LEVEL | 73875 | 2957K| | 1284 (2)| 00:00:16 |
|* 9 | TABLE ACCESS BY INDEX ROWID| LMS_CHECKED_LIMITS | 1 | 25 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | XPKLMS_CHECKED_LIMITS | 1 | | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | LMS_PACKET_DETAILS | 1 | 10 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | XPKLMS_PACKET_DETAILS | 1 | | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | LMS_LIMIT_VALUES | 843 | 8430 | | 3 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | LMS_LIMIT_SEVERITY_MASTER | 1 | 5 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | XPKLMS_LIMIT_SEVERITY_MASTER | 1 | | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LIM_VALUES"."LEVEL_CODE"="CHK_LIM"."LEVEL_CODE" AND
"LIM_VALUES"."LIMIT_SYSCODE"="CHK_LIM"."LIMIT_SYSCODE")
6 - access("CHK_LIM_DATA"."INSTRUMENT_SYSCODE"="TRN_CHK_DATA"."INSTRUMENT_SYSCODE" AND
"CHK_LIM_DATA"."ACCOUNT_SYSCODE"="TRN_CHK_DATA"."ACCOUNT_SYSCODE" AND
"CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP1"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP1" AND
"CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP2"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP2" AND
"CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP3"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP3" AND
"CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP4"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP4" AND
"CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP5"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP5")
filter("TRN_CHK_DATA"."TRANSACTION_SYSCODE"=NVL("CHK_LIM_DATA"."TRANSACTION_SYSCODE","TRN_CHK_DATA"."TRANSA
CTION_SYSCODE"))
8 - filter(NVL("TRN_CHK_DATA"."ACTIVE_PASSIVE",'A')='A')
9 - filter("CHK_LIM"."LIMIT_SYSCODE" IS NOT NULL AND "CHK_LIM"."LIMIT_SYSCODE"="TRN_CHK_DATA"."LIMIT_SYSCODE"
AND "CHK_LIM"."TRANSACTION_MASTER_SYSCODE"="TRN_CHK_DATA"."TRANSACTION_MASTER_SYSCODE" AND
"CHK_LIM"."INFLOW_OUTFLOW"="TRN_CHK_DATA"."INFLOW_OUTFLOW")
10 - access("CHK_LIM_DATA"."CHECKED_LIMIT_SYSCODE"="CHK_LIM"."CHECKED_LIMIT_SYSCODE")
11 - filter("PACK_DETAIL"."PROCESS_SYSCODE"=50687)
12 - access("CHK_LIM"."TRANSACTION_MASTER_SYSCODE"="PACK_DETAIL"."TRANSACTION_MASTER_SYSCODE")
15 - access("LIM_VALUES"."LIMIT_SEVERITY_SYSCODE"="LIM_SEVR_MAST"."LIMIT_SEVERITY_SYSCODE")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
115263527 consistent gets
7062 physical reads
0 redo size
561 bytes sent via SQL*Net to client
2869 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
==========================================================================================================================
when i am taking awr report. it is showing high CPU consumption and enq: TX-row lock contention is high in top 5 wait events.
Please suggest.
Thanks
