oracle 11g下如何捕捉library cache物件執行時產生的lock、pin等資訊
我們知道使用10049 event可以跟蹤語句執行過程中在library cache物件上產生的lock和pin的動作，但此方法僅在Oracle 10g版本下有效，11g下另有他法。
先來回顧一下10g裡是怎麼做的
//////////////////////////
// ORACLE 10gR2 下的測試
//////////////////////////
###建立測試表，執行測試SQL語句
drop table t2;
create table system.t2 as select * from all_users;
select * from system.t2 where user_id<50;
###獲取語句hash value，轉成16進位制
select to_char(hash_value,'xxxxxxxxxx') from v$sql where sql_text like 'select * from system.t2 where user_id<%';
TO_CHAR(HAS
-----------
200eeb23
0xEB23|(0x2000+0x0010+0x0020)=0xEB232030=3944947760
注：
0xEB23是sql語句hash value轉換成16進位制以後的低4位數
0x2000表示"DUMP BY HASH VALUE"
0x0010表示"trace lock operations"
0x0020表示"trace pin operations"
###使用10049對語句進行trace
oradebug setmypid
oradebug event 10049 trace name context forever,level 3944947760;
Session altered.
select * from system.t2 where user_id<50;
oradebug tracefile_name
/oracle/app/oracle/admin/pboss/udump/pboss1_ora_2093188.trc
### .trc檔案輸出
cat /oracle/app/oracle/admin/pboss/udump/pboss1_ora_2093188.trc
*** 2016-04-26 10:53:00.346
*** ACTION NAME:() 2016-04-26 10:53:00.339
*** MODULE NAME:(sqlplus@qb550135 (TNS V1-V3)) 2016-04-26 10:53:00.339
*** SERVICE NAME:(SYS$USERS) 2016-04-26 10:53:00.339
*** SESSION ID:(1713.37581) 2016-04-26 10:53:00.339
KGLTRCLCK kglget hd = 0x7000001a3c2c860 KGL Lock addr = 0x7000001b2ad5800 mode = N <---因為本次是軟解析所以輸出較少我們再看一下硬解析的情況
###清空shared_pool後再次進行10049 trace
alter system flush shared_pool;
oradebug setmypid
oradebug event 10049 trace name context forever,level 3944947760;
Session altered.
select * from system.t2 where user_id<50;
oradebug tracefile_name
/oracle/app/oracle/admin/pboss/udump/pboss2_ora_2445438.trc
### .trc檔案輸出
cat /oracle/app/oracle/admin/pboss/udump/pboss2_ora_2445438.trc
*** SESSION ID:(2137.39198) 2016-04-26 11:12:04.699
KGLTRCLCK kglget hd = 0x7000001996e6eb8 KGL Lock addr = 0x7000001b00d2b38 mode = N
KGLTRCPIN kglpin hd = 0x7000001996e6eb8 KGL Pin addr = 0x7000001c0b81780 mode = X
KGLTRCPIN kglpndl hd = 0x7000001996e6eb8 KGL Pin addr = 0x7000001c0b81780 mode = X
KGLTRCLCK kglget hd = 0x700000153b8b5f8 KGL Lock addr = 0x7000001be110888 mode = N
KGLTRCPIN kglpin hd = 0x700000153b8b5f8 KGL Pin addr = 0x7000001b1204900 mode = X
KGLTRCPIN kglpndl hd = 0x700000153b8b5f8 KGL Pin addr = 0x7000001b1204900 mode = X
KGLTRCLCK kgllkdl hd = 0x700000153b8b5f8 KGL Lock addr = 0x7000001be110888 mode = N
KGLTRCLCK kgllkdl hd = 0x7000001996e6eb8 KGL Lock addr = 0x7000001b00d2b38 mode = N
###在x$kglob裡檢視上述handle address所代表的library cache裡的物件
col KGLNAOWN format a10
col KGLNAOBJ format a58
col KGLHDOBJ format a25
set linesize 160
select kglhdadr,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from x$kglob where kglhdadr=hextoraw(upper('7000001996e6eb8'));
KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------- ---------------------------------------------------------- ---------- -------------------------
07000001996E6EB8 07000001996E6EB8 select * from system.t2 where user_id<:"SYS_B_0" 537848611 07000001B16EE9C8
select kglhdadr,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from x$kglob where kglhdadr=hextoraw(upper('700000153b8b5f8'));
KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------- ---------------------------------------------------------- ---------- -------------------------
0700000153B8B5F8 07000001996E6EB8 select * from system.t2 where user_id<:"SYS_B_0" 537848611 07000001A2B80210
其中07000001996E6EB8是父遊標的handle address、0700000153B8B5F8是子游標的handle address
###對Trace得到的內容作一下總結
(1) 在父遊標上獲取Null模式的lock
(2) 在父遊標上獲取Exclusive模式的pin
(3) 釋放父遊標上Exclusive模式的pin
(4) 在子游標上獲取Null模式的lock
(5) 在子游標上獲取Exclusive模式的pin
(6) 釋放子游標上Exclusive模式的pin
(7) 釋放子游標上Null模式的lock
(8) 釋放父遊標上Null模式的lock
oracle 11g裡該如何實現跟蹤？
如果要在11g Trace出這些內容，使用10049 event時無效的，因為在11g裡功能更強大的隱含引數_kgl_debug替代了原先的10049 event。
_kgl_debug引數可以在系統或者會話級別靈活設定跟蹤物件，這個物件可以是table、index等object，也可以library cache裡的一條語句
跟蹤scott.t2這個物件：
alter session set "_kgl_debug"="name='T2' schema='SCOTT' namespace=1 debug=96"
其中namespace為1時表示：table/view/sequence/synonym等型別的object
其中namespace為2時表示：package body/type body等型別的object
debug=96表示僅針對lock和pin執行trace操作：
Trace Locks：0x20
Trace Pins：0x40
Trace Locks + Trace Pins=0x60=96
跟蹤full hash value為0c3fd8f8071f22064d99be791649a55f的語句：
alter session set "_kgl_debug"="hash='0c3fd8f8071f22064d99be791649a55f' debug=96";
注意這裡的full hash_value來自於X$KGLOB.KGLNAHSV，如何得到這個值，後面會有詳細介紹。
下面就來演示一下11g裡Trace lock/pin的過程
//////////////////////////
// ORACLE 11gR2 下的測試
//////////////////////////
###建立測試表
create table scott.t0517_2 as select * from all_users;
select * from scott.t0517_2;
###獲取SQL的hash_value
set linesize 150
select address,child_address,sql_id,hash_value from v$sql where sql_text like 'select * from scott.t0517_2';
ADDRESS CHILD_ADDRESS SQL_ID HASH_VALUE
---------------- ---------------- ------------- ----------
07000000BD3AEF00 07000000BD3AECB0 3ywd5md8ay2q0 1353648832
###根據hash_value找到KGLNAHSV
col KGLNAOWN format a30
col KGLNAOBJ format a30
set linesize 190
select kglhdadr,kglhdpar,kglnaown,kglnaobj,KGLNAHSH,KGLNAHSV from x$kglob where KGLNAHSH=1353648832;
KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLNAHSV
---------------- ---------------- ------------------------------ ------------------------------ ---------- --------------------------------
07000000BD3AECB0 07000000BD3AEF00 select * from scott.t0517_2 1353648832 45ecd74da55e32363f71a59b50af0ac0
07000000BD3AEF00 07000000BD3AEF00 select * from scott.t0517_2 1353648832 45ecd74da55e32363f71a59b50af0ac0
###session的Trace File名稱
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata06/tstdb1_diag/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_8193016.trc
###設定_kgl_debug同時跟蹤表物件和SQL語句
alter system flush shared_pool;
alter session set "_kgl_debug"="name='T0517_2' schema='SCOTT' namespace=1 debug=96,hash='45ecd74da55e32363f71a59b50af0ac0' debug=96"; <---中間以逗號分隔
###執行語句
select * from scott.t0517_2;
###最後關閉_kgl_debug(將debug值設為0)
alter session set "_kgl_debug"="name='T0517_2' schema='SCOTT' namespace=1 debug=0,hash='45ecd74da55e32363f71a59b50af0ac0' debug=0"; <---中間以逗號分隔
因為我們設定_kgl_debug引數時指定了Scott.t0517_2表和"select * from scott.t0517_2"語句的作為Trace的物件，中間以逗號分隔，所以生成的TraceFile裡既包含了Scott.t0517_2表上的
library cache lock/pin操作，也包含了"select * from scott.t0517_2"這條語句上的library cache lock/pin，Trace檔案是按照xml格式組織的，以下是摘錄的部分片段(來自於sql語句的跟蹤)：
<KGLTRACE>
<Timestamp>2016-05-18 12:50:01.066</Timestamp>
<SID>266</SID>
<Function>kgllkal</Function>
<Reason>TRACELOCK</Reason>
<Param1>7000000bda28ef8</Param1>
<Param2>0</Param2>
<LibraryHandle>
<Address>7000000bd9ef918</Address>
<Hash>50af0ac0</Hash>
<LockMode>N</LockMode>
<PinMode>0</PinMode>
<LoadLockMode>0</LoadLockMode>
<Status>VALD</Status>
<ObjectName>
<Name>select * from scott.t0517_2</Name>
<FullHashValue>45ecd74da55e32363f71a59b50af0ac0</FullHashValue>
<Namespace>SQL AREA(00)</Namespace> <---<Namespace>SQL AREA(00)</Namespace>表示對SQL的跟蹤，如果是<Type>TABLE(02)</Type>則表示對
表的跟蹤
<Type>CURSOR(00)</Type>
<Identifier>1353648832</Identifier>
<OwnerIdn>0</OwnerIdn>
</ObjectName>
</LibraryHandle>
<LibraryObjectLock>
<Address>7000000bda28ef8</Address>
<Handle>7000000bd9ef918</Handle>
<Mode>N</Mode>
</LibraryObjectLock>
</KGLTRACE>
針對"select * from scott.t0517_2"語句控制程式碼的lock/pin相關操作所呼叫到的核心函式，從trace結果中按照呼叫先後順序輸出如下：
kglLock
kglHandleInitialize
kgllkal
kglLock
kglpin
kglobld
kglHandleInitialize
kglPin
kglUnPin
kglpndl
按我個人的理解kglHandleInitialize是在硬解析的時候構造存放SQL的handle address、kgllkal應該是用於分配lock address，kglobld用於將object裝載進記憶體
與此類似對於表Scott.t0517_2也有這麼一組核心函式完成加鎖解鎖的過程。
無論是lock還是pin都有三個模式屬性LockMode、PinMode、LoadLockMode，每個模式屬性都有N，S，X，0四種取值的可能性。
完整的Trace檔案可以參考附件"tstdb1_ora_8193016.trc"
透過以上對比不難發現相比10g僅能輸出遊標的trace資訊，11g引入了_kgl_debug引數後所能提供的library cache lock/pin資訊跟蹤選項更為全面，能對遊標進行Trace也能對各object輸出Trace資訊，資訊量更為豐富，當然讀懂這些Trace需要具備更高的專業素養
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-2103539/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- library cache pin和library cache lock的診斷分析
- zt_如何平面解決library cache lock和library cache pin
- 定位Library Cache pin,Library Cache lock等待的解決方法
- enq：Library cache lock/pin等待事件ENQ事件
- Library cache lock/pin詳解(轉)
- library cache lock和library cache pin區別總結
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- library cache pin/lock的解決辦法
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- library cache lock\pin的查詢與處理
- Oracle 11g業務使用者更改密碼後產生大量library cache lock等待Oracle密碼
- zt_library cache pin和lock等待分析
- 11G資料庫之library cache lock及library cache pin模擬結合hanganalyze定位資料庫
- Shared pool的library cache lock/pin及硬解析
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 0317Library Cache Pin/Lock Wait EventsAI
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- 如果一個過程正在執行，這個時候編譯這個過程，會產生library cache pin編譯
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)4.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt
- library cache lock和cursor: pin S wait on X等待AI
- oracle異常:library cache lockOracle
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- 【ASK_ORACLE】Library Cache概念篇（二）之Library Cache Pin的定義Oracle
- 由最長SQL想到的Latch Free( Library Cache Pin/Lock)整理~~草稿SQL
- oracle11g之expdp產生library cache lock及tablespace autoextend off_alert logOracle
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin等待分析
- library cache pin 等待事件事件
- Oracle 11g 密碼延遲認證與 library cache lock 等待Oracle密碼
- 11g密碼錯誤延時造成大量"library cache lock"等待密碼