In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
On : 11.2.0.4 version, RDBMS, sessions hang on "row cache lock" (dc_objects) while creating & dropping a table concurrently
Session hang on "row cache lock" (dc_objects) in mode 3 (S mode) and the row cache objects shows "LOADING" status and complete flag is FALSE
Current Wait Stack:
0: waiting for 'row cache lock'
cache id=0x8, mode=0x0, request=0x3
row cache parent object: address=c000000bce63ab78 cid=8(dc_objects)
hash=ec2b1e45 typ=21 transaction=0000000000000000 flags=00088000
objectno=218843 ownerid=39 nsp=1
name=OPEN_RATE_HOUR_340_0923 own=c000000bce63ac40[c000000a05c798a8,c000000a05c798a8] wat=c000000bce63ac50[c000000bca6d3790,c000000bb70cb908] mode=S
status=-/-/-/-/-/-/-/-/LOADING <<<<<< loading
request=N release=FALSE flags=8
instance lock=QI ec2b1e45 f0c5baf5
set=0, complete=FALSE <<<<<< false
Current SQL: create table open_rate_hour_340_0923......
The call stacks indicate any (system) triggers are fired;
ksedsts <- ksdxfstk <- ksdxcb <- sspuser <- kernel
<- _pw_wait <- pw_wait <- sskgpwwait <- skgpwwait <- ksliwat
<- kslwaitctx <- kqrigt <- cold_kqrLockAndPinPo <- kqrpre1 <- kqrpre
<- kkttabid <- kktglmr <- kktexefrdevt <- kktfrddltrg <- opiexe
<- opiosq0 <- opiall0 <- opial7 <- opiodr <- ttcpip
<- opitsk <- opiino <- opiodr <- opidrv <- sou2o
<- opimai_real <- ssthrdmain <- main
The database parameter "recyclebin" is default value (on)
CHANGES
No changes made.
CAUSE
The cause is due to following bug which was closed as not a bug:
Bug 25641559- ROW CACHE LOCK WITHOUT HOLDER
"row cache lock" will happen if the application is constantly & concurrently executing CREATE/DROP DDL.
During the incident, lot of concurrent session attempt to execute drop a table that is already in recycle-bin with (most likely) original name.
The sessions do not hang forever, they move forward. Problem over here is - resource locked in S mode is not getting shared with concurrent S request .
This typically should not happen is happening because no session is able to LOAD the object during S request. This is indicated by the fact that object is
always seen to be marked with flag KQRFLOAD.
Table rename happens during Drop while moving it to recycle-bin. And during execution of rename, rnmobj() invokes kqrCreateNewVersion() that marks the
kqr object with flag KQRFOBSL (obsolete).
Combination of these 2 flags is leading to this problem apart from it being in recycle bin with original name.
SOLUTION
To implement the solution, please execute the following steps:
1. Disable recyclebin
alter system set recyclebin='off' scope=spfile sid='*';
OR
2. Disable system triggers
alter system set "_system_trig_enabled"=false;
