locks onDB

Posted by Roshan on May 24, 2023; 4:15am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/locks-onDB-tp11912.html

Oracle DB 19.18.0
Oracle Linux 8

Hello Erman,

we have a banking batch which runs around 7pm. It normally takes 4h30m to complete. around 10-11pm the batch hangs with error below

dExecutorFactory.java:122) [artemis-commons-1.5.5.014-redhat-00001.jar!/:1.5.5.014-redhat-00001]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1160) [?:1.8.0]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:1.8.0]
        at java.lang.Thread.run(Thread.java:825) [?:2.9 (01-04-2022)]
[ERROR] 2023-05-23 23:10:02,698 [tSA 109_238475521] DATABASE {COMO-NAME=tSA_109_20230523_20-22-30, command=tSA 109, hostname=****, sessionId=****} - Failed to write recid/table : 000446793515-20230630/FBNK_STMT_PRINTED_X Length=0
java.sql.SQLException: IJ031013: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@efa38593
        at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.tryLock(BaseWrapperManagedConnection.java:402) ~[?:?]
        at org.jboss.jca.adapters.jdbc.WrappedConnection.lock(WrappedConnection.java:161) ~[?:?]
        at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:482) ~[?:?]

I am attaching the AWR report. I am not able to share for the timeframe 10-11 pm because db was restarted.

Could you please advise if tuning needs to done on app code? Why are those blocking sessions created?

awrrpr23May23_09_10.html

Thanks,
R