Its version 19.9.0.0.0 of oracle exadata. We are seeing below application query which used to run around ~200-300 times in an hour and was used to take ~.5 sec/execution. But then after it started executing ~1000 times/hour and the execution time went up to ~3 to ~4 seconds/execution flooding the whole database with "row lock contention' application waits.
It seems this statement is taking a lock and doing some processing at the same time waiting for 20 seconds if not getting a lock. And the row lock contention appears when lot of sessions submitted doing same functionality and fighting with each other. So wanted to know, how should we fix such issue? Should we make it as NOWAIT and let the session skip that process when one is already working? or should we minimize the WAIT from 20 to ~10?
SELECT ID FROM LOCK_TAB WHERE LOCK_NAME = 'XXXX' FOR UPDATE OF ID WAIT 20