Sessions Hang on "row cache lock" (dc_objects) While Creating & Dropping a Table Concurrently (Doc ID 2319957.1)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

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;