WWV_FLOW_ACTIVITY_LOG Locks Up the Complete Database. Library Cache Latch Contention in Oracle DB 11.2.0.2

(Doc ID 1318444.1)

Last updated on NOVEMBER 14, 2016

Applies to:

Oracle Application Express (formerly HTML DB) - Version: 4.0 to 4.0.2.00.07 - Release: 4 to 4
Information in this document applies to any platform.

Symptoms

An 11.2.0.2 database with APEX 4.0.2.00.07 was running for a few days, and eventually "locked up". Library cache locks were found in the database.  Every second more and more APEX sessions were piling up with wait event 'library cache lock'.

The PL/SQL block in question in the activity logging PL/SQL package of APEX. This is the package that writes to the activity log of Application Express, which tracks information like the application, page, user, elapsed time, etc. One of the changes in Application Express 4.0 was a new attribute in the activity log called content length. It is populated via a block like:

BEGIN
execute immediate ('begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;');
exception
when others
then g_content_length := 0;
END;

Note that the following is an edited, actual excerpt of what is seen from a DB system state dump if this is issue:

application name: APEX:APPLICATION 418, hash value=2530913801
action name: PAGE 2, hash value=4119315140

==>There are 299 sessions blocked by this session.

LibraryObjectLock: Address=0x6b8e09de0 Handle=0x6cbc78998 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0
User=0x7354641b0 Session=0x7354641b0 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=4f05fc9f
LibraryHandle: Address=0x6cbc78998 Hash=11ad2a9c LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
==> ObjectName: Name=begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;
FullHashValue=1326d76fc65e0eabbdef39d811ad2a9c Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=296561308 OwnerIdn=592
Statistics: InvalidationCount=547418 ExecutionCount=0 LoadCount=547420 ActiveLocks=120 TotalLockCount=548429 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=23374 HandleInUse=23374 HandleReferenceCount=0


This indicates the dB is stuck in compiling "begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;."
Many sessions are waiting for this operation to complete.
Notice also the high number of invalidations for this package-> "InvalidationCount=547418 ExecutionCount=0 LoadCount=547420 ActiveLocks=120 TotalLockCount=548429"





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