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

Last updated on MAY 23, 2017

Applies to:

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

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 hundreds of Community platforms