ANBOB™

My colleague suddenly encountered a problem today,a Database becomes very slow , and the a lot of session wait library cache lock event, Let me help him to look. DB env is 10.2.0.4 rac.

at first ,to generate a AWR manually.

exec dbms_workload_repository.create_snapshot;
@?/rdbms/admin/awrrpt

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
library cache lock	 293,463	 146,216	 498	 91.5	Concurrency
CPU time	 	 10,861	 	 6.8	 
db file sequential read	 189,358	 1,042	 6	 .7	User I/O
db file scattered read	 79,436	 421	 5	 .3	User I/O
log file sync	 222,715	 396	 2	 .2	Commit

Library Cache Activity
"Pct Misses" should be very low
Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	740	0.14	540,869	0.00	0	0
CLUSTER	5	0.00	9	0.00	0	0
INDEX	54	0.00	95	0.00	0	0
SQL AREA	32,041	2.45	24,489,123	-0.01	63	4
TABLE/PROCEDURE	855	5.15	750,579	0.01	34	0
TRIGGER	34	0.00	168,673	0.00	0	0
Back to Library Cache Statistics 
Back to Top

Library Cache Activity (RAC)
Namespace	GES Lock Requests	GES Pin Requests	GES Pin Releases	GES Inval Requests	GES Invali- dations
CLUSTER	9	0	0	0	0
INDEX	95	4	0	1	0
TABLE/PROCEDURE	7,791	38	0	7	0

Riyaj Shamsudeen wrote in his blog that
”
Library cache locks aka parse locks are needed to maintain dependency mechanism between objects and their dependent objects like SQL etc. For example, if an object definition need to be modified or if parse locks are to be broken, then dependent objects objects must be invalidated. This dependency is maintained using library cache locks. For example, if a column is dropped from a table then all SQLs dependent upon the table must be invalidated and reparsed during next access to that object. Library cache locks are designed to implement this tracking mechanism.

”
In order to access an object in library cache, a process must first lock the library cache object handle, and then pin the object data heap itself. Requests for both library cache lock and library cache pin will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.
By acquiring a library cache lock on the library cache object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can even maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a library cache lock is also the only way to locate an object in cache—a process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a library cache pin on the object data heap itself (after acquiring a library cache lock on the library cache object handle). Pinning the object causes information about the object and its data heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released. Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.

Library cache lock have 3 lock mode，there are share、exclusive and null。A process acquires a share library cache lock if it intends only to read the object. For example, it wants to reference the object during compilation. A process acquires an exclusive library cache lock if it intends to create or modify the object. For example, it wants to drop the object from the database. Null library cache locks are a special case. They are acquired on objects that are to be executed like child cursor, procedure, function, package, or type body. You can use them to maintain an interest on an object for a long period of time (session persistency), and to detect if the object becomes invalid. You can break null library cache lock at any time. This is used as a mechanism to notify a session that an executable object is no longer valid. If a null library cache lock is broken, and thus the object is invalidated, then it is an indication to the user who was holding the null library cache lock that the object needs to be recompiled. A null library cache lock is acquired during the parse phase of SQL statement execution and is held as long as the shared SQL area for that statement remains in the shared pool. A null library cache lock does not prevent any DDL operation, and can be broken to allow conflicting DDL operations, hence the term “breakable parse lock.” A null library cache lock on an object is broken when there is an exclusive library cache pin on the object.

Library cache pin have 2 lock mode，there asr share、exclusive。 When a process pins an object data heap that is not in memory, the process can determine whether the data heap is to be loaded in the PGA or SGA. An object must be pinned in Exclusive mode if it is to be modified. However, the process first will always pin the object in Share mode, examine it for errors and security checks, and then, if necessary, (such as needing modification) pin it in Exclusive mode. An object is never pinned in Exclusive mode if only read access is required. This is because all dependent transient objects (cursors) are invalidated (null locks broken) when an object is unpinned from Exclusive mode. The effect would be unnecessary recompilation and reparsing of all dependent packages, procedures, and functions.

Find blocker sessions holding the lib cache in RAC

select a.sid Holder ,a.SERIAL#,b.INST_ID,b.KGLPNUSE Sesion , b.KGLPNMOD Held, b.KGLPNREQ Req
from x$kglpn b , v$session a
where b.KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache%')
and b.KGLPNMOD <> 0
and a.saddr=b.kglpnuse ;

   HOLDER    SERIAL#    INST_ID SESION                 HELD        REQ
---------- ---------- ---------- ---------------- ---------- ----------
      6515      10005          2 C0000020F0122A20          2          0

or

-- x$kglob where kglhdadr=P1   get lob name
-- DBA_KGLLOCK or x$kgllk.KGLLKHDL

select KGLLKSNM,KGLLKMOD,KGLLKREQ from x$kgllk where KGLLKHDL= p1 and KGLLKMOD>0 order by KGLLKMOD;

or 
oradebug  hanganalyze 

What are the holders waiting for?

SQL> select username,program,machine,sql_id,status,wait_time,event ,p1,p2 from v$session where sid=6515;

USERNAME    PROGRAM                MACHINE       SQL_ID      STATUS   WAIT_TIME   EVENT               P1       P2
----------- ---------------     ------------- ------------- -------- ----------   ------------------ -------   ----------
REPORT      task@kybb1 (TNS V1-V3) kybb1       9u5jnnk50k3h7 KILLED     661        single-task message  0         0	  

Notice the session status was ‘KILLED‘ and event was ‘single-task message’.

‘library cache lock’ Waits: Causes and Solutions (Doc ID 1952395.1)

what is event ‘single-task message’?

Oracle’s definition of the event:
When running single task, this event indicates that the session waits for the client side of the executable. Wait Time: Total elapsed time that this session spent in the user application.

where is the “the client side of the executable” came from? the I check the sql text.

SQL> select sql_text from v$sqlarea where sql_id='9u5jnnk50k3h7';

SQL_TEXT
--------------------------------------------------------------------------------
INSERT INTO STATQ_ZDKBYHXX_DAY 
SELECT :B1 , :B4 , C.RES_TYPE_ID BRANDID1, N.RES_TYPE_NAME, SUBSTR(E.RECORGID, 1, 8) ORGID, 
O.ORGNAME, COUNT(*) FROM TBCS.SUBS_REWARD A, TBCS.SUBSCRIBER B, IM.IM_INV_MOBTEL C, TBCS.REC_PRESENT D, 
TBCS.REC_CHANGE F, TBCS.RECEPTION E, IM.IM_RES_TYPE@IM N, TBCS.ORGANIZATION O WHERE A.REGION
 = :B1 AND B.ACTIVE = 1 AND A.REGION = B.REGION AND (EXISTS (SELECT 1 FROM TBCS.ORGANIZATION_CHILD T WHERE
 ... -- had truncated
 AND A.STATUS = 1 AND A.SERIAL

I found the SQL call a dblink ,so “the client side ” is clear.

SQL> select sysdate from dual@IM;
SYSDATE
---------
28-JAN-15

the Db link is worked fine. but blocker session status was ‘KILLED’, it is still here . then try to kill the session again, when the blocker session is gone, the Waiting for the event ‘library cache lock’ has disappeared.

some latch about library cache lock

The library cache latches serialize access to the objects in the library cache. Access to library cache objects always occurs through library cache locks. Because locking an object is not an atomic instruction, a library cache latch is acquired before the library cache lock request and is released after it. For most operations, the library cache latches are used, and therefore they can become a point of contention.

If an object is not in memory, then a library cache lock cannot be acquired on it. In order to prevent multiple processes to request the load of the same object simultaneously, another latch must be acquired before the load request. This is the library cache load lock latch. The library cache load lock latch is taken and held until a library cache load lock is allocated, then the latch is released. Loading of the object is performed under the library cache load lock and not under the library cache load lock latch as it may take quite a long time.

The session tries to find the library cache load lock for the database object so that it can load the object. The library cache load lock is always obtained in Exclusive mode, so that no other process can load the same object. If the library cache load lock is busy the session will wait on this event until the lock becomes available.

打赏

微信扫一扫，打赏作者吧～