Oracle DB blocks for cartentries table(UPDATE cartentries)

[riazmohamed]

We are seeing db blocks coming from cart randomly.

I have seen main blocker coming from Update Cartentries always:

UPDATE cartentries SET hjmpTS = :1 ,modifiedTS=:2 ,p_calculated=:3 ,p_taxvaluesinternal=:4 WHERE PK = :5

This db blocks affects delete or other cart operations.

This db block affects the whole website and its very slow and once we kill the db blocks then site would perform normally.

We have around 10M records in cartentries table.

anyone know how to fix this issue or reduce the number of db blocks.

thanks in advance.

[JamesZ]

So it is 37 sessions are blocked? What is the wait event in v$session, v$active_session_history, dba_hist_active_sess_history. Is it "enq: row lock contention", if so, then it is pure application lock (very high chance), sometime it is due to database performance (less chance).

To dig further, if you can export 5 minutes data for the time when the issue was happening from dba_hist_active_sess_history. Then we can know more information from database level.

[riazmohamed]

All are enq: TX - row lock contention in the v$session table

PFA the imagevsession-result.png

dba_hist_active_sess_history table return 0 record

thank you