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.
”
The wait parameters of library cache lock & pin waits
are
p1 The address in the memory of the libraray cache handle
p2 The memory address of the lock and pin structure
p3 is encoded as 10*mode+namespace
mode = 3 shared, 5 exclusive
The namespaces are
0 cursor
1 Table, procedure & others
2 package body
3 trigger
4 index
5 cluster
6 object
7 pipe
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
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’.
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.