設定事件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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)4.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt
- library cache pin和library cache lock(一)
- library cache pin和library cache lock (zt)
- library cache pin和library cache lock(二)
- 【等待事件】library cache pin事件
- Library Cache最佳化篇(一)降低library cache lock和library cache pin的方法
- latch:library cache lock等待事件事件
- Library Cache 診斷:Lock, Pin 以及 Load Lock (文件 ID 1548524.1)
- 【ASK_ORACLE】Library Cache概念篇(二)之Library Cache Pin的定義Oracle
- library cache pin(轉)
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- library cache lock和library cache bin實驗_2.0
- DBA手記(學習)-library cache pin
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件
- [20240828]分析為什麼出現library cache lock等待事件5.txt事件
- 一次library cache lock 問題分析
- 【ASM_ORACLE】Library Cache最佳化篇(二)Library cache load lock的概念和解決辦法ASMOracle
- Cookie 作用,互動過程解析,設定,獲取,刪除,生效時間的設定Cookie
- 【TUNE_ORACLE】等待事件之“library cache pins”Oracle事件
- 徹底搞清楚library cache lock的成因和解決方法(轉)
- [20190401]跟蹤dbms_lock.sleep呼叫.txt
- 觸控事件獲取座標事件
- input 獲取游標位置與設定游標位置
- ORACLE LOCK,LATCH,PINOracle
- win10 如何跳過建立pin win10 如何跳過設定pinWin10
- Oracle Library cacheOracle
- MYSQL sql執行過程的一些跟蹤分析(一)MySql
- [20200219]strace跟蹤設定ENABLE=BROKEN的情況.txt
- Oracle 11g 密碼延遲認證與 library cache lock 等待Oracle密碼
- oracle跟蹤常用內部事件號Oracle事件
- 結合ReentrantLock獲得鎖分析AQS,lock過程分析ReentrantLockAQS
- 重啟大法失效?詳述Oracle11g因JDBC bug引發異常Library Cache Lock等待處理事件OracleJDBC事件
- opencv的目標跟蹤演算法OpenCV演算法
- Oracle儲存過程中定義多個遊標Oracle儲存過程