Observations
1. Heavy TX-Row Lock contention issues has been observed in the system .
2. Large execution time/high io has been experienced for few queries .
Reasons & Facts
1. TX-Row lock contention happens when multiple users tries to manipulate the record which is hold by another
user for modification .
Fact : Upon investigation we were able to capture below scenario where user A130796 and A124826 are
waiting for the same record.
2. Large execution time has been experienced for the below query
Sql Statement
SELECT ROWID,PROC_STAT,SEQ_NO,PAN,SEC,ATT_YR,DT_FILED
FROM SS_RECT_APL
WHERE ATT_YR=:1 AND PROC_STAT=:2
ORDER BY PAN,ATT_YR
Execution Statistics
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3781133068
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7233 | 339K | 37920 (2)| 00:00:46 |
| 1 | SORT ORDER BY | | 7233 | 339K | 37920 (2)| 00:00:46 |
|* 2| TABLE ACCESS BY INDEX ROWID| T_RECT_APL | 7233 | 339K | 37916 (2)| 00:00:46 |
|* 3| INDEX RANGE SCAN | INDX_T_RECT_APL_ATT_YR | 366K| | 1257 (5)| 00:00:02 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROC_STAT"=:2)
3 - access("ATT_YR"=TO_NUMBER(:1))
Fact : Upon investigation it has been noticed that above query is taking too much I/O 37920 and hence large execution time for the same .
Recommendations
1. For resolving TX-row lock issues make sure that no two users were working on the same record.
2. For increasing the efficiency and decreasing the execution time of the query listed above
a composite index need to be made on ATT_YR,PROC_STAT columns of table ATT.T_RECT_APL .
