12.2 DB HANG DUE TO ROW CACHE LOCK AND ITL ENQUEUE WHEN MORE THAN 250 SESSIONS CONCURRENTLY CREATE TABLES

APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
A pressure test is implemented in 12.2 and DB hang when we try to create more than 250 sessions and each session will create 50 tables with constraints. But in 10.2.0.4 DB can handle this workload.

The issue can both happen in RAC and non-RAC environment.

In AWR, Wait event ‘row cache lock’, ‘enq: TX - allocate ITL entry’ and ‘row cache mutex’ was significant.

Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class
row cache lock 2,360 1.5M 626.88 s 85.1 Concurrency
enq: TX - allocate ITL entry 8,342 256.9K 30.80 s 14.8 Configuration
row cache mutex 23,155 1453.7 62.78ms .1 Concurrency

cache# in wait event ‘row cache lock’ is 0xc. The value is related to ‘dc_constraints’.

 hanganalyze show a hang:

------------------------------------------------------------------------------
-
Chain 1:
------------------------------------------------------------------------------
-
Oracle session identified by:
{
instance: 1 (XXXXXXXXX)
os id: 25428732
process id: 344, oracle@XXXXXXXXX
session id: 1764
session serial #: 54127
}
is waiting for 'row cache lock' with wait info:
{
    p1: 'cache id'=0xc
    p2: 'mode'=0x0
    p3: 'request'=0x5
    time in wait: 1 min 3 sec (last interval)
    time in wait: 12 min 54 sec (total)
    timeout after: never
    wait id: 374
    blocking: 240 sessions
    current sql: create table t3_test_274_15(c1 number not null, c2 number not null, c3 number not null)
short stack: ksedsts <- ksdxfstk <- ksdxcb <- sspuser <-__sighandler
             <- thread_wait <- sskgpwwait <- skgpwwait <- ksliwat <- kslwaitctx
              <- kqrget <- kqrLockAndPinPo <- kqrpre1 <- kkdcnco <- kkdcico
               <- ctcdrv <- opiexe <- opiosq0 <- kpooprx <- kpoal8
                <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr
                 <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main
   wait history:
   * time between current wait and wait #1: 0.000000 sec
    1. event: 'row cache mutex'
    time waited: 0.030116 sec
    wait id: 387 p1: 'cache id'=0xc
                 p2: 'where requested'=0xb
}
and is blocked by
=> Oracle session identified by:
{
     instance: 1 (XXXXXXXXX)
     os id: 23003978
     process id: 318, oracle@XXXXXXXXX
     session id: 2201
     session serial #: 43917
}
which is waiting for 'enq: TX - allocate ITL entry' with wait info:
{
     p1: 'name|mode'=0x54580004
     p2: 'usn<<16 | slot'=0xdd0001
     p3: 'sequence'=0x47f
     time in wait: 0.604539 sec
     timeout after: 3.395461 sec
     wait id: 397
     blocking: 240 sessions
current sql: create table t3_test_115_24(c1 number not null, c2 number not null, c3 number not null)
short stack: ksedsts <- ksdxfstk <- ksdxcb <- sspuser <-__sighandler
             <- thread_wait <- sskgpwwait <- skgpwwait <- ksliwat <- kslwaitctx
              <- ksqcmi <- ksqgtlctx <- ksqgelctx <- ktuGetTxForXid <- ktcwit1
               <- ktbgfi <- kdimod0 <- kauupd <- updrow <- qerupFetch
                <- updaul <- updThreePhaseExe <- updexe <- opiexe <- opiall0
                 <- opikpr <- opiodr <- rpidrus <- skgmstack <- rpidru
                  <- rpiswu2
      wait history:
        * time between current wait and wait #1: 0.000022 sec
        1. event: 'enq: TX - allocate ITL entry'
        time waited: 3.999057 sec
        wait id: 396 p1: 'name|mode'=0x54580004
        p2: 'usn<<16 | slot'=0xfb0006
        p3: 'sequence'=0x6

CAUSE
The issue was caused by Bug 30571768 - 12.2 DB HANG DUE TO ROW CACHE LOCK AND ITL ENQUEUE WHEN MORE THAN 25。
Base Bug 30603433 - IMPLEMENT CACHING OF _NEXT_CONSTRAINT DC_CONSTRAINTS OBJECT

SOLUTION

1. making use of the no-constraint table and the global temporary table approach as a workaround.

or

2. To reduce using constraint contention, the Dev is considering a change to improve the performance in bug 30603433.

If the bug fix is ready, you can request a patch to improve the performance.

REFERENCES

NOTE:2319957.1 - Sessions Hang on “row cache lock” (dc_objects) While Creating & Dropping a Table Concurrently
NOTE:2402166.1 - “Row cache lock” and “Enq: TX - allocate ITL entry” While Creating Tables Concurrently