深入理解shared pool共享池之library cache的library cache pin系列三
背景
關於library cache相關的LATCH非常多,名稱差不多,我相信一些人對這些概念還是有些暈,我之前也有些暈,希望此文可以對這些概念有個更為清晰的理解,本文主要學習library cache pin前期相關文章
http://blog.itpub.net/9240380/
結論
1,library cache pin是用於併發的一種特殊鎖,它不是LATCH2,library cache pin適用於正在執行儲存過程時,不能同時去編譯儲存過程,後者則會產生此等待事件,目的即防止併發修改儲存過程的定義
3,library cache pin的分析可採用如下指令碼:
整合下上述的SQL為一個SQL,便於運維使用,直接查出造成library cache pin的原兇
SQL> select sid,serial#,event,v$sql.sql_id,v$sql.sql_text from dba_kgllock,v$session,v$sql where kgllktype='Pin' and kgllkmod<>0 and dba_kgllock.kgllkuse=v$session.saddr and v$session.sql_id=v$sql.sql_id;
SID SERIAL# EVENT SQL_ID SQL_TEXT
---------- ---------- ------------------------------ ------------- --------------------------------------------------
116 153 SQL*Net message from client 6t5v50dgh7paq SELECT COUNT(1) FROM T_LOCK_TEST
4,如果產生了library cache pin,是基於儲存過程對應的library cache bucket,
同時會在BUCKET中產生PIN OWNER及PIN WAITER,否則會消失
透過pin waiter及pin owner結合v$session及dba_kgllock可以定位PIN的持有者及等待者
5,所以在業務高峰期間,一實不要執行編譯儲存過程的工作
6,dba_kgllock不止記錄pin也記錄lock,關於dba_kgllock含義請見官方手冊
7,library cache pin不會在v$lock及dba_dml_locks和dba_waiters,dba_blockers出現相關任何資訊
可見不同的字典適用於不同的場景,一定要深入理解這些字典的用途
測試
----oracle version
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
----與library cache相關的latch,概念極為容易迷惑人
SQL> select latch#,name from v$latch where lower(name) like '%library cache pin%';
LATCH# NAME
---------- ------------------------------
220 library cache pin allocation
219 library cache pin
---與library cache相關的鎖,有控制併發訪問lco,lco內容的,控制跨節點訪問lco的失效的
SQL> select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where lower(name) like '%library%';
TYPE NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
-------------------- ------------------------------ ------------------------------ -------------------- --- ----------------------------------------------------------------------
V Library Cache Lock 3 hash value hash value NO Synchronizes accesses to library cache objects
E Library Cache Lock 2 hash value hash value NO Synchronizes accesses to library cache objects
L Library Cache Lock 1 hash value hash value NO Synchronizes accesses to library cache objects
Y Library Cache Pin 3 hash value hash value NO Synchronizes accesses to the contents of library cache objects
G Library Cache Pin 2 hash value hash value NO Synchronizes accesses to the contents of library cache objects
N Library Cache Pin 1 hash value hash value NO Synchronizes accesses to the contents of library cache objects
IV Library Cache Invalidation object # time stamp NO Synchronizes library cache object invalidations across instances
7 rows selected.
---library cache lock模擬小記
http://blog.itpub.net/9240380/viewspace-759281/,可見是如果某物件正在執行時,會對其物件相引用的物件加library cache lock
SQL> select latch#,name from v$latch_children where lower(name) like '%library cache lock%';
LATCH# NAME
---------- ------------------------------
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
218 library cache lock
---library cache lock的含義,引自官方手冊 Oracle? Database Reference 11g Release 2 (11.2)之Descriptions of Wait Events
library cache lock含義
1,用於併發控制對於library cache的訪問,在library cache 的library cache handle加鎖,這個目的:
a,防止同一時間只能一個應用訪問相同的物件
b,確保在一個操作的既定時間內,確保相互信賴物件的一致性,即確保依賴物件不能被修改
c,同時會持一個鎖用於library cache object
library cache lock的引數
handle address 載入的物件地址
lock address 載入的鎖地址,它不是正常的哪個鎖或LATCH,它是一個狀態物件
mode 指定載入物件是以何種模式載入
namespace 名稱空間
namespace
The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view.
name and type
The name or "type" of the enqueue or global lock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always two characters. Use the following SQL statement to retrieve the lock name.
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1,16711680)/65535) "Lock"
from v$session_wait
where event = 'DFS enqueue lock acquisition';
---namespace的資訊源自v$db_object_cache,也可以直觀理解不同的物件型別,當然不完全一致
SQL> select distinct namespace from v$db_object_cache order by 1;
NAMESPACE
----------------------------
BODY
CLUSTER
CURSOR
INDEX
INVALID NAMESPACE
PUB_SUB
RSRC CONSUMER GROUP
RSRC PLAN
RULESET
STORED OUTLINE
SUBSCRIPTION
NAMESPACE
----------------------------
TABLE/PROCEDURE
TRIGGER
13 rows selected.
可見如下前者是latch地址,後者是library cache handle address,到底有何區別呢
SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where lower(name) like '%library cache lock%';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
latch: library cache lock address number tries Concurrency
library cache lock handle address lock address 100*mode+namespace Concurrency
---為了直觀分析,形成測試用例
SQL> select sid from v$mystat where rownum=1;
SID
----------
116
SQL> create table t_lock_test(a int,b int);
Table created.
SQL> insert into t_lock_test select level,level from dual connect by level<=1500000;
1500000 rows created.
SQL> commit;
Commit complete.
SQL> insert into t_lock_test select level,level from dual connect by level<=1500000;
1500000 rows created.
SQL> commit;
Commit complete.
SQL> insert into t_lock_test select level,level from dual connect by level<=1500000;
1500000 rows created.
SQL> commit;
Commit complete.
SQL> create or replace procedure proc_hard_parse
2 as
3 v_cnt pls_integer;
4 begin
for i in 1..100000000 loop
6 select count(1) into v_cnt from t_lock_test;
7 end loop;
8 end;
9 /
Procedure created.
SQL> exec proc_hard_parse;
SQL> select sid from v$mystat where rownum=1;
SID
----------
121
SQL> alter procedure proc_hard_parse compile;
hang住
--從這裡可見library cache pin的p1text可見它不是latch
SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where sid in (116,121);
SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------
116 SQL*Net message from client driver id #bytes Idle
121 library cache pin handle address pin address 100*mode+namespace Concurrency
SQL> select sid,event,p1,p2,p3 from v$session_wait where sid in (116,121);
SID EVENT P1 P2 P3
---------- ------------------------------ ---------- ---------- ----------
116 SQL*Net message from client 1650815232 1 0
121 library cache pin 2739958280 2663017760 301
可見library cache pin不是鎖
SQL> select sid,type,id1,id2,lmode,request from v$lock where sid in (121,116);
no rows selected
透過dba_ddl_locks可以查詢出相關的資訊,可見在測試的儲存過程上加了排它鎖,但是透過dba_ddl_locks查不出到底什麼原因引發了library cache pin
SQL> select session_id,name,type,mode_held,mode_requested from dba_ddl_locks where session_id in (116,121) and name='PROC_HARD_PARSE';
SESSION_ID NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ ---------------------------------------- --------- ---------
116 PROC_HARD_PARSE Table/Procedure/Type Null None
121 PROC_HARD_PARSE Table/Procedure/Type Exclusive None
SQL> select count(*) from dba_dml_locks;
COUNT(*)
----------
0
SQL> select count(*) from dba_waiters;
COUNT(*)
----------
0
SQL> select count(*) from dba_blockers;
COUNT(*)
----------
0
---檢視與LOCK相關的字典,想辦法找到產生這個library cache pin的源頭是什麼
SQL> select table_name from dict where lower(table_name) like '%lock%';
TABLE_NAME
------------------------------
DBA_KGLLOCK
DBA_LOCK
DBA_LOCK_INTERNAL
DBA_DML_LOCKS
DBA_DDL_LOCKS
SQL> desc dba_kgllock;
Name Null? Type
----------------- -------- ------------
KGLLKUSE RAW(8)
KGLLKHDL RAW(8)
KGLLKMOD NUMBER
KGLLKREQ NUMBER
KGLLKTYPE VARCHAR2(4)
SQL> select kgllkuse,kgllkhdl,kgllkmod,kgllkreq,kgllktype from dba_kgllock where rownum<=1;
KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL
---------------- ---------------- ---------- ---------- ----
00000000A4748578 00000000A3AE6128 1 0 Lock
---轉化上述的library cache pin的p1text,即handle address
SQL> select to_char('2739958280','xxxxxxxx') from dual;
TO_CHAR('
---------
a3507208
經查官方手冊可見kgllkuse即會話的地址
SQL> select kgllkuse,kgllkhdl,kgllkmod,kgllkreq,kgllktype from dba_kgllock where kgllkhdl='00000000A3507208';
KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL
---------------- ---------------- ---------- ---------- ----
00000000A4726CC0 00000000A3507208 1 0 Lock
00000000A472D818 00000000A3507208 3 0 Lock
00000000A472D818 00000000A3507208 0 3 Pin --等待pin
00000000A4726CC0 00000000A3507208 2 0 Pin --持有pin
SQL> select sid,saddr from v$session where sid in (116,121);
SID SADDR
---------- ----------------
116 00000000A4726CC0
121 00000000A472D818
查詢持pin的會話及SQL
SQL> select sid,serial#,sql_id from v$session where saddr='00000000A4726CC0';
SID SERIAL# SQL_ID
---------- ---------- -------------
116 153 6t5v50dgh7paq
SQL> col sql_text for a50
SQL> select sql_id,sql_text from v$sql where sql_id='6t5v50dgh7paq';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
6t5v50dgh7paq SELECT COUNT(1) FROM T_LOCK_TEST
整合下上述的SQL為一個SQL,便於運維使用,直接查出造成library cache pin的原兇
SQL> select sid,serial#,event,v$sql.sql_id,v$sql.sql_text from dba_kgllock,v$session,v$sql where kgllktype='Pin' and kgllkmod<>0 and dba_kgllock.kgllkuse=v$session.saddr and v$session.sql_id=v$sql.sql_id;
SID SERIAL# EVENT SQL_ID SQL_TEXT
---------- ---------- ------------------------------ ------------- --------------------------------------------------
116 153 SQL*Net message from client 6t5v50dgh7paq SELECT COUNT(1) FROM T_LOCK_TEST
然後與應用人員溝通後,可以執行如下命令,殺死原兇會話即可
alter system kill session '116,153'
--可見產生library cache pin後,是基於儲存過程,同時會在bucket的資料結構新增lock owner,pin owners,pin waiters
BUCKET 59215:
LIBRARY OBJECT HANDLE: handle=a3507208 mtx=0xa3507338(0) lct=22 pct=24 cdp=0
name=SCOTT.PROC_HARD_PARSE
hash=84bc90d61f9452e79e7fe2d54c82e74f timestamp=11-24-2015 02:02:36
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-001d-20bf lock=X pin=S latch#=9 hpc=0008 hlc=0008 --pin正以共享模式持有,卻有會話請求排它模式pin,見下面的pin wqiters
lwt=0xa35072b0[0xa35072b0,0xa35072b0] ltm=0xa35072c0[0xa35072c0,0xa35072c0]
pwt=0xa3507278[0x9eba6d50,0x9eba6d50] ptm=0xa3507288[0xa3507288,0xa3507288]
ref=0xa35072e0[0xa35072e0,0xa35072e0] lnd=0xa35072f8[0xa34de600,0xa34fed28]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
98c7ce28 2 DEP[01] whr=0 timestamp=11-24-2015 02:02:36
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9ec58960 a472d818 a472d818 1 X [00]
9eb1fd70 a4726cc0 a4726cc0 1 N PNC/[400]
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
9eae0c20 a4726cc0 a4726cc0 9eb1fd70 3 S 0011 --可見PIN正以共享模式持有,這個user對應v$session.saddr即會話的地址,也對應dba_kgllock的KGLLKUSE
PIN WAITERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
9eba6d20 a472d818 a472d818 0 0 X 0000 ---可見請求排它模式的PIN
LIBRARY OBJECT: object=9d949488
type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
DEPENDENCIES: count=3 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9d94b210 9d94af50 a3a1b6f0 0 DEP[01]
1 9d94b210 9d94afa8 a351ce38 33 DEP[01]
2 9d94b210 9d94b000 a3929db0 0 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency# table reference handle flags
----------- -------- --------- -------- -------------------
0 9d9288d0 9d9285e8 a34fa2c8 /ROD[20]
ACCESSES: count=1 size=16
dependency# types
----------- -----
1 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
a351ce38 a351ce38
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3507148 9d9495a0 I/P/A/-/- 0 NONE 00 0.37 1.09
2 9d9497a0 97745578 I/-/A/-/- 0 NONE 00 13.27 16.00
4 9d928540 97b67850 I/P/A/-/- 1 NONE 00 2.26 4.00
解決library cache pin後的儲存過程對應的library cache bucket內容
BUCKET 59215:
LIBRARY OBJECT HANDLE: handle=a3507208 mtx=0xa3507338(0) lct=22 pct=24 cdp=0
name=SCOTT.PROC_HARD_PARSE
hash=84bc90d61f9452e79e7fe2d54c82e74f timestamp=11-24-2015 02:02:36
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-001d-20bf lock=N pin=0 latch#=9 hpc=0008 hlc=0008 --可見pin=0,可見pin有種狀態,即pin=0,pin=s,pin=x(pin=0我估計是null模式的pin)
lwt=0xa35072b0[0xa35072b0,0xa35072b0] ltm=0xa35072c0[0xa35072c0,0xa35072c0]
pwt=0xa3507278[0xa3507278,0xa3507278] ptm=0xa3507288[0xa3507288,0xa3507288]
ref=0xa35072e0[0xa35072e0,0xa35072e0] lnd=0xa35072f8[0xa34de600,0xa34fed28]
DEPENDENCY REFERENCES:
reference latch flags
--------- ----- -------------------
98c7ce28 2 DEP[01] whr=0 timestamp=11-24-2015 02:02:36
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
9eb1fd70 a4726cc0 a4726cc0 1 N [00]
9ec58960 a472d818 a472d818 0 N [4044]
LIBRARY OBJECT: object=9d949488 --而且與出現等待事件library cache pin出現的pin owner及pin waiter也消失了
type=PRCD flags=EXS/LOC[0005] pflags=NST[0001] status=VALD load=0
DEPENDENCIES: count=3 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9d94b210 9d94af50 a3a1b6f0 0 DEP[01]
1 9d94b210 9d94afa8 a351ce38 33 DEP[01]
2 9d94b210 9d94b000 a3929db0 0 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency# table reference handle flags
----------- -------- --------- -------- -------------------
0 9d9288d0 9d9285e8 a34fa2c8 /ROD[20]
ACCESSES: count=1 size=16
dependency# types
----------- -----
1 0009
TRANSLATIONS: count=1 size=16
original final
-------- --------
a351ce38 a351ce38
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a3507148 9d9495a0 I/-/A/-/- 0 NONE 00 0.37 1.09
2 9d9497a0 97745578 I/-/A/-/- 0 NONE 00 13.27 16.00
4 9d928540 97b67850 I/-/A/-/- 0 NONE 00 2.26 4.00
BUCKET 59215 total object count=1
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1845911/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 深入理解shared pool共享池之library cache的library cache lock系列四
- 深入理解shared pool共享池之library cache系列一
- 深入理解shared pool共享池之library cache系列二
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- 深入理解shared pool共享池空間及library cache分配之ora-4031 系列一
- library cache lock和library cache pin理解
- Shared pool的library cache lock/pin及硬解析
- shared pool之三:library cache結構/library cache object的結構-dump LibraryHandleObject
- Library Cache Pin 及 Library Cache Lock分析
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- library cache pin和library cache lock的診斷分析
- 定位Library Cache pin,Library Cache lock等待的解決方法
- 【ASK_ORACLE】Library Cache概念篇(二)之Library Cache Pin的定義Oracle
- library cache lock和library cache pin區別總結
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- library cache pin 等待事件事件
- 等待模擬-library cache shared pool 硬解析
- zt_如何平面解決library cache lock和library cache pin
- 優化Shared Pool Latch與Library Cache Latch競爭優化
- shared pool library cache latch 競爭優化辦法優化
- 解決library cache pin等待事件事件
- enq:Library cache lock/pin等待事件ENQ事件
- library cache pin 阻塞程式查詢
- Library cache lock/pin詳解(轉)
- 查詢Library Cache Pin等待原因
- library cache pin等待事件的模擬事件
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOLObject
- Shared Pool優化和Library Cache Latch衝突優化優化
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- oracle調優之-共享池尺寸調優+library cache+dicitonary library 命中率Oracle
- DBA手記(學習)-library cache pin
- 分析解決因”library cache pin”等待
- library cache pin/lock的解決辦法
- 模擬cache buffers chains與library cache pin等待事件AI事件
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt