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

Last updated on AUGUST 25, 2022

Applies to:

Symptoms

After the staging database on a Change Data Capture distributed hotlog environment was upgraded to 11.1.0.7, 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.property,
t.trigflag from obj$ o, user$ u, tab$ t where u.name = :1 and o.name = :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.property,
t.trigflag from obj$ o, user$ u, tab$ t where u.name = :1   and o.name = :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, vstt.name, 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 vstt.name 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#, vstt.name order by vses.username, vsst.sid, vses.serial#, vstt.name;

Changes

Cause

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