locks onDB

[Roshan]

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

[Roshan]

Please find below the table DDL.


  CREATE TABLE "T24PROD"."FBNK_STMT_PRINTED_X"
   ( "RECID" VARCHAR2(255 BYTE),
        "XMLRECORD" "SYS"."XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 48 MAXTRANS 255
 NOCOMPRESS  LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T24PRODTAB"
 XMLTYPE COLUMN "XMLRECORD" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "T24PRODLOB" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA
  PARTITION BY HASH ("RECID")
 (PARTITION "SYS_P5847" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5848" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5849" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5850" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5851" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5852" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5853" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5854" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5855" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5856" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5857" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5858" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5859" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5860" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5861" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5862" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5863" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5864" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5865" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5866" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5867" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5868" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5869" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5870" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5871" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5872" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5873" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5874" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5875" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5876" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5877" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5878" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5879" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5880" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5881" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5882" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5883" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5884" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5885" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5886" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5887" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5888" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5889" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5890" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5891" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5892" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5893" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5894" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5895" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5896" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5897" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5898" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5899" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5900" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5901" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5902" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5903" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5904" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5905" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5906" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5907" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5908" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5909" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ,
 PARTITION "SYS_P5910" SEGMENT CREATION IMMEDIATE
  TABLESPACE "T24PRODTAB"
 NOCOMPRESS ) ;
  CREATE UNIQUE INDEX "T24PROD"."FBNK_STMT_PRINTED_X_S_PK" ON "T24PROD"."FBNK_STMT_PRINTED_X" ("RECID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T24PRODINDEX"
  PARALLEL 20 ;
ALTER TABLE "T24PROD"."FBNK_STMT_PRINTED_X" ADD CONSTRAINT "FBNK_STMT_PRINTED_X_S_PK" PRIMARY KEY ("RECID")
  USING INDEX "T24PROD"."FBNK_STMT_PRINTED_X_S_PK"  ENABLE;

[ErmanArslansOracleBlog]

Is it insert operation?
What is the db wait of the session?

If it is an insert, constraints (unique index/unique constraint / primary key) may be causing this. Consider 2 or more inserts almost at the same time to the same table, session 1 inserted data, but didn't commit and there is a constraint, so the second session will wait even though they are all insert operations.. Because Oracle doesn't now , the first session will commit or rollback and that's why to ensure the constraint is not violated, it will make second session wait, till the first one issue a commit or rollback.. So keep that in mind and analyze your environment.