I am using oracle 12.1.0.2. I have following update statement which is being used by more than 50 threads from job scheduler to pick min(doc_pk) to be processed next. I have DOC_GEN_ST_CD in WHERE clause as well as I am updating it. So , would it be helpful to create index on the column ?
Also , I am seeing a lot of enq:TX - row lock contention in the AWR. What would help to reduce it?
Top 10 Foreground Events by Total Wait Time
| Event | Waits | Total Wait Time (sec) | Wait Avg(ms) | % DB time | Wait Class |
|---|
| enq: TX - row lock contention | 67,678 | 124K | 1832.07 | 76.1 | Application |
| DB CPU | | 37.8K | | 23.2 | |
UPDATE PD_DOC
SET DOC_GEN_ST_CD = :B2, GEN_START_TS = LOCALTIMESTAMP
WHERE DOC_GEN_ST_CD = :B1
AND DOC_PK = (SELECT Min(DOC_PK)
FROM PD_DOC D
WHERE D.DOC_GEN_ST_CD = :B1
)
Plan hash value: 707891791
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | | 1 (100)| | | | 0 |00:00:00.89 | 44590 | 44283 |
| 1 | UPDATE | PD_DOC | 1 | | | | | | | 0 |00:00:00.89 | 44590 | 44283 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| PD_DOC | 1 | 1 | 15 | 1 (0)| 00:00:01 | ROWID | ROWID | 0 |00:00:00.89 | 44590 | 44283 |
|* 3 | INDEX UNIQUE SCAN | PK_PD_DOC | 1 | 1 | | 1 (0)| 00:00:01 | | | 0 |00:00:00.89 | 44590 | 44283 |
| 4 | SORT AGGREGATE | | 1 | 1 | 11 | | | | | 1 |00:00:00.89 | 44590 | 44283 |
| 5 | PARTITION RANGE ALL | | 1 | 1 | 11 | 6699 (1)| 00:00:01 | 1 |1048575| 0 |00:00:00.89 | 44590 | 44283 |
|* 6 | TABLE ACCESS FULL | PD_DOC | 53 | 1 | 11 | 6699 (1)| 00:00:01 | 1 |1048575| 0 |00:00:00.89 | 44590 | 44283 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Size | 709 MB |
| Number of Exents | 2,835 |
| Total size of LOBs | 21 GB |
What is update trying to do?
The update statement to trying to identify doc_pk to be processed next. It will generate PDF for the identified doc_pk from .net code.
What is doc_gen_st_cd?
doc_gen_st_cd is document generation status code. It can be available, processing, generated, error
The bind value are
1. In where condition 'AVAILABLE'
2. IN UPDATE SET 'PROCESSING'
It pick up next available doc_pk and set it to 'PROCESSING'. So, when the job are running with 50 thread number of 'PROCESSING' in the table will be 50. Number of 'AVAILABLE' records is 1.5 million to start with and will be around 30 thousand daily.
Why 50 thread? So that 50 thread could parallel generate PDF. They are eventually push to AWS S3 .
Why min value? We need to pick up in the order doc_pk was inserted.
Currently, it only has primary key index on doc_pk.
It is range partition on insrt_ts (insert timestamp)per day.
PARTITION BY RANGE (INSRT_TS)
INTERVAL( NUMTODSINTERVAL(1,'DAY')
)
This partition was created so that records older than 15 days could be dropped.