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.
@?/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
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_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.
