Partition table get locked for concurrent DML operations in Oracle

Question

I have created one partition table in Oracle 11g and tried to insert records using Multithreading from Java batch job. Let's say, if I have 10 threads, & each wants to insert/update 1 million records concurrently or bulk insert some time without threads, in both the cases our table got locked by oracle for same session. If we have 10 threads then we got 20 records for 10 sessions for 1 same table. This is happening for only partition or partition with subpartition tables.

Below is create table query

CREATE TABLE "SEG_TEST_SINGLE_PARTITION"
(
"POR_CD"    CHAR(2 CHAR) NOT NULL ENABLE,
"PROD_MNTH" CHAR(6 CHAR) NOT NULL ENABLE,
"INS_DTTM" TIMESTAMP (6)
 )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
  (
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "a" PARTITION BY LIST
  (
  "POR_CD"
  )
  (
PARTITION "PWKLY_ORDR_POR01" VALUES
(
  '01'
)
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a" ,
PARTITION "PWKLY_ORDR_POR02" VALUES
(
  '02'
)
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a" ,
PARTITION "PWKLY_ORDR_POR03" VALUES
(
  '03'
)
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a" ,
PARTITION "PWKLY_ORDR_POR06" VALUES
(
  '06'
)
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a" ,
PARTITION "PWKLY_ORDR_POR15" VALUES
(
  '15'
)
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a" ,
PARTITION "PWKLY_ORDR_POR20" VALUES
(
  '20'
)
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a" ,
PARTITION "PWKLY_ORDR_PORDF" VALUES
(
  DEFAULT
)
SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "a"

) ;

By below query, I have checked table lock

select    a.INST_ID,a.XIDUSN,a.XIDSLOT,a.XIDSQN,a.OBJECT_ID,b.OBJECT_NAME,a.SESSION_ID,a.ORACLE_USERNAME,a.OS_USER_NAME,a.PROCESS,LOCKED_MODE from
GV$LOCKED_OBJECT a,dba_objects b where a.object_id=b.object_id and     ORACLE_USERNAME like '%ASP01%' order by 8;  


    "INST_ID"   "XIDUSN"    "XIDSLOT"   "XIDSQN"    "OBJECT_ID" "OBJECT_NAME"   "SESSION_ID"    "ORACLE_USERNAME"   "OS_USER_NAME"  "PROCESS"   "LOCKED_MODE"
        3         58          16         424487       3235818   "PLAN_TABLE$"      2447             "ASP1"             "z013"         "15140"        3

so as of now I dont have lock for above created table,Now I will run Insert query without commit and will check lock agian from sql developer( or from anywhere )

Insert into SEG_TEST_SINGLE_PARTITION (POR_CD,PROD_MNTH,INS_DTTM) values ('02','201607',to_timestamp('15-DEC-16 11.15.09.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
1 row inserted

Lock Query

select a.INST_ID,a.XIDUSN,a.XIDSLOT,a.XIDSQN,a.OBJECT_ID,b.OBJECT_NAME,a.SESSION_ID,a.ORACLE_USERNAME,a.OS_USER_NAME,a.PROCESS,LOCKED_MODE from
GV$LOCKED_OBJECT a,dba_objects b where a.object_id=b.object_id and ORACLE_USERNAME like '%ASP01%' order by 8;  

result with partition table(SEG_TEST_SINGLE_PARTITON) as object name

 "INST_ID"  "XIDUSN"    "XIDSLOT"   "XIDSQN"    "OBJECT_ID"     "OBJECT_NAME"                   "SESSION_ID"    "ORACLE_USERNAME"   "OS_USER_NAME"  "PROCESS"   "LOCKED_MODE"
    3       58          16       424487         3235818     "PLAN_TABLE$"                       2447        "ASP01"                 "z013"       "15140"            3
    3       139         5        56366          3929061     "SEG_TEST_SINGLE_PARTITION"         3339        "ASP01"                 "z013"       "31624"            3
    3       139         5        56366          3929063     "SEG_TEST_SINGLE_PARTITION"         3339        "ASP01"                 "z013"       "31624"            3

Now, we would like to know why are we getting tablelock for single table for two times with same session_id(3339) which is causing long delay while inserting through java multithreading or Bulk insert and some times got hang based on data size?

Answer 1

We had a similar problem in our project. We increased INITRANS. I'm not pretty sure, but a Senior Tech Arch told me there's a bug with oracle 12c. Also, MAXTRANS is deprecated. https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses007.htm

So we manually increased INITRANS value for all partitions, and then moved the partitions so that the setting is applicable.

The issue that we understood was that the concurrent updates on a data block happening were more than what our INITRANS setting was allowing.

I'm not sure of the accurate syntax, but it's something like:

ALTER TABLE SEG_TEST_SINGLE_PARTITION MOVE PARTITION PWKLY_ORDR_POR02 INITRANS 10;

ALTER TABLE SEG_TEST_SINGLE_PARTITION MODIFY PARTITION PWKLY_ORDR_POR02 REBUILD INDEX;