My Oracle Support Banner

Abnormally High Library Cache Locks/Waits Due to High Parsing Time (Doc ID 1993568.1)

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1]
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

Parse time for some SQL(s) will be extremely long (1 minute) with an interval partitioned table in place but be much quicker if the partition is replaced by another type. The Call stack will be similar to the following

 

ksedsts <- ksdxfstk <- ksdxcb <- sspuser <- __sighandler <- kkpamRDEqual <- kkpamDEqual <- kkpamDAEqual <- kkpapDAFkk  <-kkofmp <-kkotap <- kkojnp <- kkopts <- kkocnp <- kkooqb <- kkoqbc <- apakkoqb <- apaqbdDescendents <- apaqbdList    <-apaqbd <- kkqctCostTransfQB <- kkqctdrvJPPD <- kkqjpdctr <- qksqbApplyToQbc <- kkqctdrvTD <- kkqjpddrv <- kkqdrv  <- kkqctdrvIT <- apadrv <- opitca <- __PGOSF640_kksFullTypeCheck <- rpiswu2 <- kksLoadChild  <- kxsGetRuntimeLock <-kksfbc  <-kkspsc0 <- kksParseCursor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main <- __libc_start_main <- _start

 

 

 

 

 

 

System state dump should show the SQL is in the parsing stage and a trace of the parsing and execution will have lines like these

PARSE #139998140894088:c=123206270,e=123834020,p=1,cr=54542,cu=0,mis=1,r=0,dep=0,og=1,plh=1639863147,tim=1416335947658736

The database will show long waits for "library cache lock" waits. 

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.