Index Creation Recommendations in Oracle

 

Index creation :

 

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 : 

1. Upon investigation we were able to capture below scenario where user U1000 and U2000 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,DAN,SEC,BST_YRA,DT_FILED
FROM SS_RECT_APL
WHERE BST_YRA=:1 AND PROC_STAT=:2
ORDER BY DAN,BST_YRA
 
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_BST_YR | 366K| | 1257 (5)| 00:00:02 |
 
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROC_STAT"=:2)
3 - access("BST_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 BST_YR,PROC_STAT columns of table BST.T_RECT_APL .

 

Create the below composite index to resolve the issue:

SQL> create index BST_COMP_IDX on BST.T_RECT_APL(BST_YR, PROC_STAT);

✅✅✅