暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Performance tuning library cache lock & single-task message

原创 Anbob 2015-01-28
914
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.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论