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.

[stefanweis]

we're having the exact same issue

[former_member618524]

riazmohamed did you find any solution for this ? can you please let me know

[JamesZ]

<All are enq: TX - row lock contention in the v$session table>
=> Then this is more like an application lock wait issue, let us see input from application person.
Also there is little change for database issue, but you don't have dba_hist_active_sess_history, then it is hard to track from database.

[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

[JamesZ]

Can you share more about the "Oracle DB blocks" you meant? Did you see oracle event "enq: row lock contention"?

Best regards,
James (I am mainly focusing on SAP NetWeaver on Oracle topic).

[riazmohamed]

Sorry for the late reply , yes , please find attached the screenshot for the same db-blocks.png