Resolving “library cache pin” Waits

The old traditional export (exp) utility is hanging indefinitely at “exporting system procedural objects and actions” stage of the export.

 
[oracle@node1 ~]$ /orabackup/exp/prtdb/exp_prtdb.sh

Export: Release 11.2.0.1.0 - Production on Sun Aug 14 21:39:03 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit                                          Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,                                          OLAP,
Data Mining and Real Application Tes
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions

Following are the details from V$SESSION view of this session:

SID:				190
SERIAL#:			379
USERNAME:			EXPIMP
STATUS:				ACTIVE
PROGRAM:			exp@node1 (TNS V1-V3)
LAST_CALL_ET:			1363
BLOCKING SESSION STATUS:	NOT IN WAIT
EVENT#:				280
EVENT:				library cache pin
WAIT CLASS:			Concurrency
STATE:				WAITED SHORT TIME
BLOCKING SESSION:						
  

As you can see the session is waiting on “library cache pin” wait event. This means there is at least one other session holding a lock on some stored procedure which “exp” is trying to pin. The “BLOCKSING SESSION” column does not report which session is holding a lock (or for that matter blocking).

I used “oradebug” utility to analyze the situation. Following is an excerpt from the log file generated by “oradebug”:

    Oracle session identified by:
    {
                instance: 1 (prtdb.prtdb1)
                   os id: 24885
              process id: 49, oracle@node1
              session id: 13
        session serial #: 839
    }
    is waiting for 'Streams AQ: waiting for messages in the queue' with wait info:

Below is the information about session “13” from V$SESSION view:

SID:				13
SERIAL#:			841
USERNAME:			DBSNMP
STATUS:				ACTIVE
PROGRAM:			emagent@node1 (TNS V1-V3)
LAST_CALL_ET:			5
BLOCKING SESSION STATUS:	UNKNOWN
EVENT#:				364
EVENT:			Streams AQ: waiting for messages in the queue
WAIT CLASS:		Idle
STATE:			WAITING
BLOCKING SESSION:						

The culprit session belongs to “DBSNMP” user and it is waiting on an “Idle” wait event.

It was easy to kill this session as it did not belong to any application. After killing the session (13), export happily started working and things were all normal.

. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL
:
:
:

References:

Note:215858.1 - Interpreting HANGANALYZE trace files to diagnose hanging and performance problems

Note: 175006.1 Steps to generate HANGANALYZE trace files

Note: 402983.1 Master Note: How to diagnose Database Performance

Diagnosing Library Cache Latch Contention: A Real Case Study By Arup Nanda