library cache lock和library cache pin到底是什么(续)

这篇文章是“library cache locklibrary cache pin到底是什么”的姊妹篇,在这篇文章里,我们通过测试得到了如下结论:

1、  针对cursorlibrary cache locklock mode确实是null,无论该cursor所对应的sql是硬解析还是软解析;

2、  MOS上说Oracle说从10.2.0.2以后,会用mutex取代针对cursorlibrary cache pin但我的测试结果是在10.2.0.5中,虽然在sql的软解析时确实已经不存在library cache pin,但如果是硬解析,依然存在library cache pin

3、  sql的软解析时,library cache pinlock mode始终是S

4、  sql的硬解析时,library cache pinlock mode一般是X,但在10.2.0.1中,即使是硬解析,也存在lock modeSlibrary cache pin

 

这里测试所采用的方法就是event 10049,这个事件在10gR2以后,专门被用来trace library cache locklibrary cache pin。但好多朋友不太会用这个事件,我这里以一个实例的方式介绍了如何用10049事件来trace单个sqllibrary cache locklibrary cache pin

 

我们先从10.2.0.1开始说起:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

 

10.2.0.1_kks_use_mutex_pin的值为false,表示Oracle不会用mutex取代针对cursorlibrary cache pin

SQL> select name,value,description from sys.all_parameters where name like ‘_kks%’;

 

NAME                           VALUE      DESCRIPTION

—————————— ———- ————————————————–

_kks_use_mutex_pin             FALSE      Turning on this will make KKS use mutex for cursor pins.

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

 7981 CUIHUA                7981                                

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 ……省略显示部分内容

 7800 JAME3      CLERK      7698 1981-12-3      950.00               30

 

13 rows selected

 

SQL> select hash_value,sql_text from v$sqlarea where sql_text like ‘select * from scott.emp%’;

 

HASH_VALUE SQL_TEXT

———- ——————————————————————————–

  52404428 select * from scott.emp

 

SQL> select to_char(52404428,’XXXXXXXX’) from dual;

 

TO_CHAR(52404428,’XXXXXXXX’)

—————————-

  31FA0CC

 

现在我们要来trace针对上述sqllibrary cache pinlibrary cache lock,方法我之前已经说了,就是用event 10049,用10049的难点在于如何确定level

确定10049针对单个sqllevel值的算法如下:

首先,10049level可能会有如下一些值:

#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 CALL STACK WITH TRACE */

#define KGLDMPOBJ  0x0200                  /* DUMP KGL OBJECT WITH TRACE */

#define KGLDMPENQ  0x0400                 /* DUMP KGL ENQUEUE WITH TRACE */

#define KGLTRCHSH  0x2000                          /* DUMP BY HASH VALUE */

 

其次,我们是要针对单个sql,所以需要用到这个sqlhash value,以便将10049和这个sql联系起来,即我们一定要用到KGLTRCHSH,也就是0x2000

另外我们是要trace library cache locklibrary cache pin,所以我们一定要用到KGLTRCLCKKGLTRCPIN,即0x00100x0020

最后就是我们需要把这个sqlhash value16进制的后两个byte拿出来,作为10049level的前缀。

 

从上面结果中我们可以看到,select * from scott.emphash value16进制的后两个byte0xA0CC。另外KGLTRCHSH | KGLTRCLCK | KGLTRCPIN 0x2000 | 0x0010 | 0x0020 = 0x2030。按照上述算法,select * from scott.emp10049的最终level值就是0xa0cc2030,也就是2697732144

SQL> select to_number(‘a0cc2030′,’XXXXXXXXXXXX’) from dual;

 

TO_NUMBER(‘A0CC2030’,’XXXXXXXX

——————————

                    2697732144

 

现在我们设置好10049后再执行一遍上述sql,以观察10.2.0.1sql的软解析时library cache pinlibrary cache lock

SQL> oradebug setmypid

已处理的语句

SQL> oradebug event 10049 trace name context forever,level 2697732144

已处理的语句

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

 7981 CUIHUA                7981                                

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 ……省略显示部分内容

 7800 JAME3      CLERK      7698 1981-12-3      950.00               30

 

13 rows selected

 

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc

 

相应的trace文件(d:\oracle\admin\cuihua\udump\cuihua_ora_5808.trc)的内容为:

*** 2011-06-01 11:59:35.500

KGLTRCLCK kglget     hd = 0x33938118  KGL Lock addr = 0x3174A99C mode = N

KGLTRCLCK kglget     hd = 0x33938034  KGL Lock addr = 0x31716F50 mode = N

KGLTRCPIN kglpin     hd = 0x33938034  KGL Pin  addr = 0x31718A28 mode = S

KGLTRCPIN kglpndl    hd = 0x33938034  KGL Pin  addr = 0x31718A28 mode = S

KGLTRCLCK kgllkdl    hd = 0x33938034  KGL Lock addr = 0x31716F50 mode = N

KGLTRCLCK kgllkdl    hd = 0x33938118  KGL Lock addr = 0x3174A99C mode = N

 

hd = 0x33938118所对应的library cache objectname就是select * from scott.emp

SQL> select sql_text from v$sqlarea where address=’33938118′;

 

SQL_TEXT

——————————————————————————–

select * from scott.emp

 

hd = 0x33938034就是hd = 0x33938118的子cursor

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where kglhdadr=’33938034′;

 

KGLHDADR KGLHDPAR KGLNAOBJ

——– ——– ——————————————————————————–

33938034 33938118 select * from scott.emp

 

很明显,从上述trace文件中我们可以得出如下结论:

110.2.0.1中,sql软解析时,针对cursorlibrary cache locklock mode确实是null

210.2.0.1中,sql软解析时,针对cursorlibrary cache pinlock mode确实是S

 

现在我们来观察10.2.0.1sql的硬解析时library cache pinlibrary cache lock

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。

 

Total System Global Area  608174080 bytes

Fixed Size                  1250404 bytes

Variable Size             318770076 bytes

Database Buffers          281018368 bytes

Redo Buffers                7135232 bytes

数据库装载完毕。

数据库已经打开。

 

SQL> select hash_value,sql_text from v$sqlarea where sql_text like ‘select * from scott.emp%’;

 

HASH_VALUE SQL_TEXT

———- ——————————————————————————–

 

SQL> oradebug setmypid

已处理的语句

SQL> oradebug event 10049 trace name context forever,level 2697732144

已处理的语句

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

 7981 CUIHUA                7981                                

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 ……省略显示部分内容

 7800 JAME3      CLERK      7698 1981-12-3      950.00               30

 

13 rows selected

 

SQL> oradebug tracefile_name

d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc

 

相应的trace文件(d:\oracle\admin\cuihua\udump\cuihua_ora_5016.trc)的内容为:

KGLTRCLCK kglget     hd = 0x206ECF90  KGL Lock addr = 0x3174E068 mode = N

KGLTRCPIN kglpin     hd = 0x206ECF90  KGL Pin  addr = 0x317187C0 mode = X

KGLTRCPIN kglpndl    hd = 0x206ECF90  KGL Pin  addr = 0x317187C0 mode = X

KGLTRCLCK kglget     hd = 0x33B19238  KGL Lock addr = 0x3174E618 mode = N

KGLTRCPIN kglpin     hd = 0x33B19238  KGL Pin  addr = 0x31717F28 mode = X

KGLTRCPIN kglpndl    hd = 0x33B19238  KGL Pin  addr = 0x31717F28 mode = S

KGLTRCLCK kgllkdl    hd = 0x33B19238  KGL Lock addr = 0x3174E618 mode = N

KGLTRCLCK kgllkdl    hd = 0x206ECF90  KGL Lock addr = 0x3174E068 mode = N

 

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where kglhdadr=’33B19238′;

 

KGLHDADR KGLHDPAR KGLNAOBJ

——– ——– ——————————————————————————–

33B19238 206ECF90 select * from scott.emp

 

很明显,从上述trace文件中我们可以得出如下结论:

110.2.0.1中,sql硬解析时,针对cursorlibrary cache locklock mode依然是null

210.2.0.1中,sql硬解析时,针对cursorlibrary cache pinlock mode一般是X,但也存在lock modeSlibrary cache pin,且这个S是针对子cursor的。

 

好了,10.2.0.1就告一段落,我们现在来看看10.2.0.5

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

Connected as SYS

 

MOS上说:从10.2.0.2开始,Oracle_kks_use_mutex_pin的默认值改成了true,表明Oracle将用mutex替代针对cursorlibrary cache pin

但实际情况并不完全是这样,详情见后面的测试:

SQL> select name,value,description from sys.all_parameters where name like ‘_kks%’;

 

NAME                           VALUE      DESCRIPTION

—————————— ———- ————————————————–

_kks_use_mutex_pin             TRUE       Turning on this will make KKS use mutex for cursor pins.

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB   MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

7369 SMITH      CLERK          7902 17-DEC-80        800                    20

7499 ALLEN    SALESMAN        7698 20-FEB-81       1600        300         30

……省略显示部分内容

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

14 rows selected.

 

SQL> select hash_value,sql_text from v$sqlarea where sql_text like ‘select * from scott.emp%’;

 

HASH_VALUE SQL_TEXT

———- ——————————–

  52404428 select * from scott.emp

 

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 2697732144

Statement processed.

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB   MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

7369 SMITH      CLERK          7902 17-DEC-80        800                    20

7499 ALLEN    SALESMAN        7698 20-FEB-81       1600        300         30

……省略显示部分内容

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

14 rows selected.

 

SQL> oradebug tracefile_name

/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

 

$ cat /u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

/u01/app/oracle/admin/testdb/udump/testdb_ora_1237156.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/10.2.0

System name:    AIX

Node name:      P550_03_LD

Release:        3

Version:        5

Machine:        0001DA17D600

Instance name: testdb

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 1237156, image: oracle@P550_03_LD (TNS V1-V3)

 

*** 2011-06-01 13:38:07.949

*** ACTION NAME:() 2011-06-01 13:38:07.944

*** MODULE NAME:(sqlplus@P550_03_LD (TNS V1-V3)) 2011-06-01 13:38:07.944

*** SERVICE NAME:(SYS$USERS) 2011-06-01 13:38:07.944

*** SESSION ID:(146.3) 2011-06-01 13:38:07.944

KGLTRCLCK kgllkal    hd = 0x700000022595c38  KGL Lock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kglget     hd = 0x700000022595c38  KGL Lock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kgllkal    hd = 0x7000000226ec4f8  KGL Lock addr = 0x70000001f74e128 mode = N

KGLTRCLCK kgllkdl    hd = 0x7000000226ec4f8  KGL Lock addr = 0x70000001f74e128 mode = N

KGLTRCLCK kgllkdl2   hd = 0x7000000226ec4f8  KGL Lock addr = 0x70000001f74e128 mode = 0

KGLTRCLCK kgllkdl    hd = 0x700000022595c38  KGL Lock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kgllkdl2   hd = 0x700000022595c38  KGL Lock addr = 0x70000001f724d78 mode = 0

这里mode=0应该是表示调用kgllkdl2所产生的library cache lock在调用完上述方法后已经释放了。

 

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where lower(kglhdadr)=’07000000226ec4f8′;

 

KGLHDADR         KGLHDPAR         KGLNAOBJ

—————- —————- ——————————————————————————–

07000000226EC4F8 0700000022595C38 select * from scott.emp

 

很明显,从上述trace文件中我们可以得出如下结论:

10.2.0.5中,sql软解析时,针对cursorlibrary cache pin确实已经不存在;

 

现在我们来观察10.2.0.5sql的硬解析时library cache pinlibrary cache lock

$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 10.2.0.5.0 – Production on Wed Jun 1 13:42:11 2011

 

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  2096032 bytes

Variable Size              96470112 bytes

Database Buffers          209715200 bytes

Redo Buffers                6291456 bytes

Database mounted.

Database opened.

 

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 2697732144

Statement processed.

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB   MGR HIREDATE         SAL       COMM     DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

7369 SMITH      CLERK          7902 17-DEC-80        800                    20

7499 ALLEN    SALESMAN        7698 20-FEB-81       1600        300         30

……省略显示部分内容

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

 

14 rows selected.

 

SQL> oradebug tracefile_name

/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

 

$ cat /u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

/u01/app/oracle/admin/testdb/udump/testdb_ora_1536246.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/10.2.0

System name:    AIX

Node name:      P550_03_LD

Release:        3

Version:        5

Machine:        0001DA17D600

Instance name: testdb

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 1536246, image: oracle@P550_03_LD (TNS V1-V3)

 

*** ACTION NAME:() 2011-06-01 13:42:44.913

*** MODULE NAME:(sqlplus@P550_03_LD (TNS V1-V3)) 2011-06-01 13:42:44.913

*** SERVICE NAME:(SYS$USERS) 2011-06-01 13:42:44.913

*** SESSION ID:(159.3) 2011-06-01 13:42:44.913

DBRM(kskinitrm) cpu_count : old(0) -> new(2)

kwqmnich: current time::  5: 42: 44

kwqmnich: instance no 0 check_only flag 1

kwqmnich: initialized job cache structure

*** 2011-06-01 13:44:13.657

KGLTRCLCK kgllkal    hd = 0x7000000225ccfa8  KGL Lock addr = 0x70000001f725560 mode = N

KGLTRCLCK kglget     hd = 0x7000000225ccfa8  KGL Lock addr = 0x70000001f725560 mode = N

KGLTRCPIN kglpin     hd = 0x7000000225ccfa8  KGL Pin  addr = 0x70000001f726378 mode = X

KGLTRCPIN kglpndl    hd = 0x7000000225ccfa8  KGL Pin  addr = 0x70000001f726378 mode = X

KGLTRCLCK kgllkal    hd = 0x7000000225abf18  KGL Lock addr = 0x70000001f733120 mode = N

KGLTRCLCK kglget     hd = 0x7000000225abf18  KGL Lock addr = 0x70000001f733120 mode = N

KGLTRCPIN kglpin     hd = 0x7000000225abf18  KGL Pin  addr = 0x70000001f726840 mode = X

KGLTRCPIN kglpndl    hd = 0x7000000225abf18  KGL Pin  addr = 0x70000001f726840 mode = X

KGLTRCLCK kgllkdl    hd = 0x7000000225abf18  KGL Lock addr = 0x70000001f733120 mode = N

KGLTRCLCK kgllkdl2   hd = 0x7000000225abf18  KGL Lock addr = 0x70000001f733120 mode = 0

KGLTRCLCK kgllkdl    hd = 0x7000000225ccfa8  KGL Lock addr = 0x70000001f725560 mode = N

KGLTRCLCK kgllkdl2   hd = 0x7000000225ccfa8  KGL Lock addr = 0x70000001f725560 mode = 0

 

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where lower(kglhdadr)=’07000000225abf18′;

 

KGLHDADR         KGLHDPAR         KGLNAOBJ

—————- —————- ——————————————————————————–

07000000225ABF18 07000000225CCFA8 select * from scott.emp

 

很明显,从上述trace文件中我们可以得出如下结论:

110.2.0.5中,sql硬解析时,依然存在library cache pin

210.2.0.5中,sql硬解析时,针对cursorlibrary cache pinlock mode始终是X

 


5 Comments on “library cache lock和library cache pin到底是什么(续)”

  1. wxjzqymtl says:

    首先非常博主的这篇文章,最近一直想搞清楚在sql语句被解析与执行阶段
    对应的lock与pin的模式以及引用的数据库对象的lock和pin的模式分别是什么,
    您的这篇文章给了我一个清晰的思路,我想向您确认两个问题
    1.10049事件对lock,pin模式的跟踪周期是从sql开始解析一直到执行结束吗?
    因为通过10049事件trace的最后结果是无论软,硬解析lock,pin的模式最后分别都是N和S;
    可是一个sql语句执行结束后他的lock和pin的模式都会变为0,也就是释放锁资源,这个结果
    是通过library_cache event看到的,还是说10049只跟踪从解析开始到结束(除锁资源释放的那个过程)
    2.您的这个案例中只是针对cursor类对象来跟踪其解析与执行阶段lock,pin模式的不断变化,如果
    我还想跟踪相应的游标中引用的对象的lock,pin模式的变化的话有办法吗?

    • cui hua says:

      1、10049只跟踪从解析开始到执行结束中间的过程,如果要查看cursor的lock和pin在执行结束后的状态,可以对相关library cache做dump
      2、10049是用来跟踪一些基本的library cache function(如pin、lock等),不仅限于cursor

  2. taowang2016 says:

    你好,很感谢你的工作,对我帮助很大,有个疑问?

    《在library cache lock和library cache pin到底是什么》一文中,
    作为enqueue,library cache lock和library cache pin有哪几种lock mode?段落的第三行,有如下:
    A process acquires an exclusive library cache lock if it intends to create or modify the object.

    也就是说在硬解析时,需要以X模式获得library cache lock,可是在本文的如上跟踪得到如下结论:
    10.2.0.1中,sql硬解析时,针对cursor的library cache lock的lock mode依然是null;

    这样那个X模式的library cache lock在哪里体现?

    求释疑,谢谢。

    • Cui Hua says:

      Oracle中的Library Cache Object的种类有几十种,Shared Cursor只是其中的一种。针对Shared Cursor的Library Cache Lock的mode为null并不代表针对其它类型的Library Cache Object的Library Cache Lock的mode就不能是X,因为Library Cache Lock不仅仅适用于Shared Cursor。你在“Oracle数据库里什么情况下select操作会hang住”(http://www.dbsnake.net/oracle-select-hang-case.html)这篇文章里就能看到X模式的Library Cache Lock


Leave a Reply

Your email address will not be published. Required fields are marked *