oracle 10049 event之library cache lock

最新推荐文章于 2021-04-13 07:42:19 发布

clg10051

最新推荐文章于 2021-04-13 07:42:19 发布

阅读量200

点赞数

文章标签：数据库

测试环境：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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-759214/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-759214/