設定事件10049跟蹤遊標上的library cache lock/pin獲取過程
#define KGLTRCHSH 0x2000 /* trace by hash */
#define KGLTRCNSP 0x1000 /* trace by namespace */
#define KGLTRCLCK 0x0010 /* trace lock operations */
#define KGLTRCPIN 0x0020 /* trace pin operations */
#define KGLTRCOBF 0x0040 /* trace object freeing */
#define KGLTRCINV 0x0080 /* trace invalidations */
#define KGLDMPSTK 0x0100 /* dump the stack trace */
Event 10049事件可以用來跟蹤cursor上library cache lock/pin的獲取過程。
實驗過程中我們需要透過cursor的hash value來跟蹤 pin 和lock操作。
此事件的用法如下:
alter session set events '10049 trace name context forever,level N';
其中N值需要根據你的實驗環境不同去替換:N由CURSOR HASH VALUE轉換為16進位制後的後四位+跟蹤級別組成,N需要是10進位制。
我們的例子裡需要跟蹤:0x2000 /* trace by hash */+0x0010 /* trace lock operations */+0x0020 /* trace pin operations */=2030
SQL> select count(*) from wxh_tbd where object_id=1;
COUNT(*)
----------
0
SQL> select hash_value from v$sql where sql_text like 'select count(*) from wxh_tbd%';
HASH_VALUE
----------
1748072644
SQL> select to_char(1748072644,'xxxxxxxxxxx') from dual;
TO_CHAR(1748072644,'XXXXXXXXXX
--------------------------------------------------------------------------------
683178c4
後四位為78c4
因此我們跟蹤事件的N應該是:78c42030
轉化為10進位制
SQL> select to_number('78c42030','xxxxxxxxxxxxxx') from dual;
TO_NUMBER('78C42030','XXXXXXXX
------------------------------
2026119216
SQL> alter session set events '10049 trace name context forever,level 2026119216';
Session altered.
SQL> select count(*) from wxh_tbd where object_id=1;
COUNT(*)
----------
0
檢視跟蹤過程中的跟蹤檔案
父遊標handler為:0x6C869B28
子游標handler為:0x6CA15ECC
-------------第一種情況,硬解析
KGLTRCLCK kglget hd = 0x6C869B28 KGL Lock addr = 0x69843A4C mode = N
KGLTRCPIN kglpin hd = 0x6C869B28 KGL Pin addr = 0x69845080 mode = X
KGLTRCPIN kglpndl hd = 0x6C869B28 KGL Pin addr = 0x69845080 mode = X
KGLTRCLCK kglget hd = 0x6CA15ECC KGL Lock addr = 0x69846858 mode = N
KGLTRCPIN kglpin hd = 0x6CA15ECC KGL Pin addr = 0x6AFDE76C mode = X
KGLTRCPIN kglpndl hd = 0x6CA15ECC KGL Pin addr = 0x6AFDE76C mode = S
KGLTRCLCK kgllkdl hd = 0x6CA15ECC KGL Lock addr = 0x69846858 mode = N
KGLTRCLCK kgllkdl hd = 0x6C869B28 KGL Lock addr = 0x69843A4C mode = N
-------------第而種情況,軟解析
SQL> select count(*) from wxh_tbd where object_id=1;
COUNT(*)
----------
0
KGLTRCLCK kglget hd = 0x6C869B28 KGL Lock addr = 0x6AFDE3F4 mode = N
KGLTRCLCK kglget hd = 0x6CA15ECC KGL Lock addr = 0x6AFDD7B4 mode = N
KGLTRCPIN kglpin hd = 0x6CA15ECC KGL Pin addr = 0x6AFDF424 mode = S
KGLTRCPIN kglpndl hd = 0x6CA15ECC KGL Pin addr = 0x6AFDF424 mode = S
KGLTRCLCK kgllkdl hd = 0x6CA15ECC KGL Lock addr = 0x6AFDD7B4 mode = N
KGLTRCLCK kgllkdl hd = 0x6C869B28 KGL Lock addr = 0x6AFDE3F4 mode = N
---------第三種情況,軟軟解析
SQL> alter session set session_cached_cursors=10;
Session altered.
KGLTRCPIN kglpin hd = 0x6CA15ECC KGL Pin addr = 0x69845080 mode = S
KGLTRCPIN kglpndl hd = 0x6CA15ECC KGL Pin addr = 0x69845080 mode = S
軟軟解析只獲得了子LCO上的PIN就OK 了,而硬解析和軟解析則要獲得多次父遊標和子游標的LOCK和PIN。
附件是沈總畫的驚天地泣鬼神的圖,有機會讓沈總專門給大家講下
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-713401/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- enq:Library cache lock/pin等待事件ENQ事件
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- oracle 10049 event之library cache lockOracle
- library cache pin和library cache lock的診斷分析
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin 等待事件事件
- 定位Library Cache pin,Library Cache lock等待的解決方法
- Library cache lock/pin詳解(轉)
- library cache lock和library cache pin區別總結
- Library cache pin問題的處理過程
- library cache pin/lock的解決辦法
- LIBRARY CACHE LOCK 等待事件事件
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- zt_如何平面解決library cache lock和library cache pin
- library cache pin等待事件的模擬事件
- library cache lock\pin的查詢與處理
- 解決library cache pin等待事件事件
- zt_library cache pin和lock等待分析
- latch:library cache lock等待事件事件
- 一次library cache pin故障的解決過程
- Shared pool的library cache lock/pin及硬解析
- 0317Library Cache Pin/Lock Wait EventsAI
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- library cache lock和cursor: pin S wait on X等待AI
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- 模擬cache buffers chains與library cache pin等待事件AI事件
- 【ASK_ORACLE】Library Cache概念篇(二)之Library Cache Pin的定義Oracle
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- 記一次library cache pin事件解決事件
- 設定跟蹤事件不起作用。事件
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- 俺也談談 library cache lock 等待事件事件
- 由最長SQL想到的Latch Free( Library Cache Pin/Lock)整理~~草稿SQL
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle