oracle 10049 event之library cache lock

測試環境：windows 7 oracle 10.2.0.1

測試過程
SQL> create table t_table(a int);

Table created.

SQL> select /*+first*/ count(1) from t_table;

  COUNT(1)
----------
         0

SQL> select sql_text,hash_value from v$sql where sql_text like '%first%';

SQL_TEXT
-----------------------------------------------------------------------------

HASH_VALUE
----------
select sql_text,hash_value from v$sql where sql_text like '%first%'
1129238428

select /*+first*/ count(1) from t_table
1896708881

---獲取hash value的後2個位元組即7b11
SQL> select to_char(1896708881,'xxxxxxxxxxxxxx') from dual;

TO_CHAR(1896708
---------------
       710d7b11

---2030即10049 event分析跟蹤library cache lock and library cache pin
確定10049針對單個sql的level值的演算法如下：

                    首先，10049的level可能會有如下一些值：
                   
                    #define KGLTRCLCK 0x0010/* trace lock operations */
                   
                    #define KGLTRCPIN 0x0020/* trace pin operations */
                   
                    #define KGLTRCOBF 0x0040 /* trace objectfreeing */
                   
                    #define KGLTRCINV 0x0080 /* traceinvalidations */
                   
                    #define KGLDMPSTK 0x0100 /* DUMP CALL STACKWITH TRACE */
                   
                    #define KGLDMPOBJ 0x0200 /* DUMP KGL OBJECTWITH TRACE */
                   
                    #define KGLDMPENQ 0x0400 /* DUMP KGL ENQUEUE WITH TRACE */
                   
                    #define KGLTRCHSH 0x2000/* DUMP BY HASH VALUE */
                   
----10049 event level為hash value的後2個位元組+上述2030的組合，轉化為十進位制即可                   
SQL> select to_number('7b112030','xxxxxxxxxxxxxx') from dual;

TO_NUMBER('7B112030','XXXXXXXXXXXXXX')
--------------------------------------
                            2064719920

---oradebug分析
SQL> oradebug setmypid
Statement processed.

SQL> oradebug event 10049 trace name context forever,level 2064719920
Statement processed.

---分析的sql
SQL> select /*+first*/ count(1) from t_table;

  COUNT(1)
----------
         0

SQL> oradebug tracefile_name
d:\oracle\product\10.2.0\db_1\admin\orcl\udump\orcl_ora_12856.trc
SQL>

KGLTRCLCK kglget     hd = 0x9092E758  KGL Lock addr = 0x8EBD8680 mode = N
KGLTRCLCK kglget     hd = 0x90853ABC  KGL Lock addr = 0x8EBD8290 mode = N
KGLTRCPIN kglpin     hd = 0x90853ABC  KGL Pin  addr = 0x8EB52B00 mode = S
KGLTRCPIN kglpndl    hd = 0x90853ABC  KGL Pin  addr = 0x8EB52B00 mode = S
KGLTRCLCK kgllkdl    hd = 0x90853ABC  KGL Lock addr = 0x8EBD8290 mode = N
KGLTRCLCK kgllkdl    hd = 0x9092E758  KGL Lock addr = 0x8EBD8680 mode = N

小結：1,可知獲取了以null mode的library cache lock及shared mode的library cache pin
      2,此為二次解析，即軟解析
     

測試下硬解析情況

-----檢視下ddl操作持library cache lock及library cache pin的變化情況
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10049 trace name context forever,level 528
Statement processed.
SQL> alter table t_table add b int;

Table altered.

SQL> oradebug tracefile_name
d:\oracle\product\10.2.0\db_1\admin\orcl\udump\orcl_ora_12856.trc
SQL>     

--------自trace可知在alter table add 操作期間頭尾會持x mode的librache cache lock,中間為null mode 的library cache lock
------所以說會在alter 操作時阻塞select操作(根據hash value 過濾即得到如下記錄
GLTRCLCK kglget     hd = 0x90928C6C  KGL Lock addr = 0x8EBD8450 mode = X
LIBRARY OBJECT HANDLE: handle=90928c6c mutex=90928D20(0)
name=SYS.T_TABLE
hash=e7aa8d8cb1072affe5729af178f71f15 timestamp=04-24-2013 16:08:10

 

KGLTRCLCK kglget     hd = 0x90928C6C  KGL Lock addr = 0x8EBD83E0 mode = N
LIBRARY OBJECT HANDLE: handle=90928c6c mutex=90928D20(0)
name=SYS.T_TABLE
hash=e7aa8d8cb1072affe5729af178f71f15 timestamp=04-24-2013 16:08:10

KGLTRCLCK kgllkdl    hd = 0x90928C6C  KGL Lock addr = 0x8EBD83E0 mode = N
LIBRARY OBJECT HANDLE: handle=90928c6c mutex=90928D20(0)
name=SYS.T_TABLE
hash=e7aa8d8cb1072affe5729af178f71f15 timestamp=04-24-2013 16:08:10

GLTRCLCK kgllkdl    hd = 0x90928C6C  KGL Lock addr = 0x8EBD8450 mode = X
LIBRARY OBJECT HANDLE: handle=90928c6c mutex=90928D20(0)
name=SYS.T_TABLE
hash=e7aa8d8cb1072affe5729af178f71f15 timestamp=04-24-2013 16:31:22

---看下alter table modify情況
同上，也是首尾持x mode library cache lock，中間為null mode的library cache lock

 

 

 

 

 

--再看下alter table drop column, 與alter table modify column與alter table add column不同,先是持s mode的library cache lock,然後持null mode的
--library cache lock,再接著持null mode,最後持x;

KGLTRCLCK kglget     hd = 0x909000C0  KGL Lock addr = 0x8A39A6F8 mode = S
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW

 

KGLTRCLCK kglget     hd = 0x909000C0  KGL Lock addr = 0x8A39A538 mode = S
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW
hash=56af4db26991e4855c5bd5eb4390bf91 timestamp=04-24-2013 19:33:23
namespace=TABL flags=KGHP/TIM/SML/[02000000]

 

KGLTRCLCK kgllkdl    hd = 0x909000C0  KGL Lock addr = 0x8A39A538 mode = S
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW
hash=56af4db26991e4855c5bd5eb4390bf91 timestamp=04-24-2013 19:33:23

 

KGLTRCLCK kgllkdl    hd = 0x909000C0  KGL Lock addr = 0x8A39A6F8 mode = S
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW
hash=56af4db26991e4855c5bd5eb4390bf91 timestamp=04-24-2013 19:33:23
namespace=TABL flags=KGHP/TIM/SML/[02000000]

 

KGLTRCLCK kglget     hd = 0x909000C0  KGL Lock addr = 0x8A39A538 mode = X
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW
hash=56af4db26991e4855c5bd5eb4390bf91 timestamp=04-24-2013 19:33:23
namespace=TABL flags=KGHP/TIM/SML/[02000000]

 

KGLTRCLCK kgllkdl    hd = 0x909000C0  KGL Lock addr = 0x8A39A6F8 mode = N
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW
hash=56af4db26991e4855c5bd5eb4390bf91 timestamp=04-24-2013 19:33:23
namespace=TABL flags=KGHP/TIM/FUL/FUP/SML/[0e000000]

 

KGLTRCLCK kgllkdl    hd = 0x909000C0  KGL Lock addr = 0x8A39A538 mode = X
LIBRARY OBJECT HANDLE: handle=909000c0 mutex=90900174(0)
name=SCOTT.T_NEW
hash=56af4db26991e4855c5bd5eb4390bf91 timestamp=04-24-2013 19:33:37
namespace=TABL flags=KGHP/TIM/FUL/SML/[06000000]

小結：1,alter table drop column與其它的ddl不同

            2,大併發的生產環境下，千萬別隨便alter table,不然會造成其它併發會話的hang，即競爭library cache lock