Performance Analysis - DB

Our initial analysis of RCC DB for the problems listed in trail mail reveals following facts :
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 U130 and U131 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,PST_YR,DT_FILED
FROM SS_RECT_APL
WHERE PST_YR=:1 AND PROC_STAT=:2
ORDER BY PAN,PST_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_IPL | 7233 | 339K| 37916 (2)| 00:00:46 |
|*3 | INDEX RANGE SCAN | INDX_T_RECT_IPL_PST_YR | 366K| | 1257 (5)| 00:00:02 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROC_STAT"=:2)
3 - access("PST_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 PST_YR,PROC_STAT columns of table BST.T_RECT_IPL .