My Oracle Support Banner

Apply server expiriencing high number of waits on Library cache Load lock after upgrade to (Doc ID 1153513.1)

Last updated on AUGUST 25, 2022

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


After the staging database on a Change Data Capture distributed hotlog environment was upgraded to, the apply server at the staging database started experiencing high waits on the 'Library Cache Load Lock' event.

An AWR report shows the following SQL statement as the one with the highest
number of parses:

select o.obj#, (o.spare2 + ((o.mtime - o.ctime)*86400)) version,,
t.trigflag from obj$ o, user$ u, tab$ t where = :1 and = :2 and
o.owner# = u.user# and o.obj# = t.obj#

The 10046 trace of the apply server shows the object being referenced is the
source table of the change table that does not exist in the staging database:

PARSING IN CURSOR #2 len=199 dep=1 uid=0 oct=3 lid=0 tim=3319761754296
hv=3222789043 ad='c00000015cade108' sqlid='4jh47dz01gqxm'
select o.obj#, (o.spare2 + ((o.mtime - o.ctime)*86400)) version,,
t.trigflag from obj$ o, user$ u, tab$ t where = :1   and = :2  
and o.owner# = u.user#   and o.obj# = t.obj#

The problem will also cause high PGA memory consumption for the apply server.  In that case, the following query will show a large number of 'opened cursors cummulative' for the session associated to the apply server process:

select vses.username||':'||vsst.sid||','||vses.serial# username,, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, order by vses.username, vsst.sid, vses.serial#,;




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

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

In this Document

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