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

 


library cache lock和library cache pin到底是什么

可能有很多朋友从来就没有搞清楚过到底什么是library cache locklibrary cache pin,它们到底是enqueue还是latch?它们的作用是什么?

这里我尝试对上述问题做一番解释,这些解释可能是有问题的,因为里面包含了我的一些猜测。

最近连续写了一些基于我的猜测、没有确凿证据的文章,这也许不太合适。

 

我们通常说的library cache locklibrary cache pinenqueue,不是latch,它们是两种DDL lock。但需要我们注意的是,在11gR1之前,Oracle中又存在名为library cache locklibrary cache pinlatch

 

是不是感觉很混乱?没关系,我们一点一点往下看。很抱歉,我这里引用了大量英文,因为我觉得如果翻译出来就失去了原先的味道。

 

1、  作为enqueuelibrary cache locklibrary cache pin的作用是什么?

Both library cache lock and library cache pin are provided to access objects in the library cache. Library cache lock manages concurrency between processes, whereas library cache pin manages cache coherence. In order to access an object in library cache, a process must first lock the library cache object handle, and then pin the object data heap itself. Requests for both library cache lock and library cache pin will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.

By acquiring a library cache lock on the library cache object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can even maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a library cache lock is also the only way to locate an object in cache—a process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a library cache pin on the object data heap itself (after acquiring a library cache lock on the library cache object handle). Pinning the object causes information about the object and its data heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released. Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.

 

2、  作为enqueuelibrary cache locklibrary cache pin有哪几种lock mode

a)         Library cache lock有三种lock mode,分别是shareexclusivenullA process acquires a share library cache lock if it intends only to read the object. For example, it wants to reference the object during compilation. A process acquires an exclusive library cache lock if it intends to create or modify the object. For example, it wants to drop the object from the database. Null library cache locks are a special case. They are acquired on objects that are to be executed like child cursor, procedure, function, package, or type body. You can use them to maintain an interest on an object for a long period of time (session persistency), and to detect if the object becomes invalid. You can break null library cache lock at any time. This is used as a mechanism to notify a session that an executable object is no longer valid. If a null library cache lock is broken, and thus the object is invalidated, then it is an indication to the user who was holding the null library cache lock that the object needs to be recompiled. A null library cache lock is acquired during the parse phase of SQL statement execution and is held as long as the shared SQL area for that statement remains in the shared pool. A null library cache lock does not prevent any DDL operation, and can be broken to allow conflicting DDL operations, hence the term “breakable parse lock.” A null library cache lock on an object is broken when there is an exclusive library cache pin on the object.

b)         Library cache pin有两种lock mode,分别是shareexclusive When a process pins an object data heap that is not in memory, the process can determine whether the data heap is to be loaded in the PGA or SGA. An object must be pinned in Exclusive mode if it is to be modified. However, the process first will always pin the object in Share mode, examine it for errors and security checks, and then, if necessary, (such as needing modification) pin it in Exclusive mode. An object is never pinned in Exclusive mode if only read access is required. This is because all dependent transient objects (cursors) are invalidated (null locks broken) when an object is unpinned from Exclusive mode. The effect would be unnecessary recompilation and reparsing of all dependent packages, procedures, and functions.

 

3、  作为latchlibrary cache locklibrary cache pin的作用是什么?

这是一个很纠结的问题,既然已经有了作为enqueuelibrary cache locklibrary cache pin,为什么在11gR1以前,Oracle里还有同名latch,而且明显这些同名latch是在被使用:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

Connected as ipra

 

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

 

NAME                                                   LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache                                               5    9221760       1608        800           2596            76766

library cache lock                                          6   13548247        582          6              0                0

library cache lock allocation                               3     208273          0          0              0                0

library cache pin                                           6    4207462        193          0              2                0

library cache hash chains                                   9          0          0          0              0                0

library cache pin allocation                                3      57276          0          0              0                0

library cache load lock                                     5      24848          0          0              1                0

 

7 rows selected

从结果里我们可以看到,对于10.2.0.5而言,Oracle存在7种跟library cache相关的latch,除了library cache hash chains latch之外,其他的跟library cache相关的latchOracle都有使用。

 

那么library cache lock latchlibrary cache pin latch以及大家最耳熟能详的library cache latch等等,这些latch是做什么用的呢?

也许我们可以从下面的一段文字中找到答案:

The library cache latches serialize access to the objects in the library cache. Access to library cache objects always occurs through library cache locks. Because locking an object is not an atomic instruction, a library cache latch is acquired before the library cache lock request and is released after it. For most operations, the library cache latches are used, and therefore they can become a point of contention.

If an object is not in memory, then a library cache lock cannot be acquired on it. In order to prevent multiple processes to request the load of the same object simultaneously, another latch must be acquired before the load request. This is the library cache load lock latch. The library cache load lock latch is taken and held until a library cache load lock is allocated, then the latch is released. Loading of the object is performed under the library cache load lock and not under the library cache load lock latch as it may take quite a long time.

 

这里提到了几点值得我们关注:

a)        Oracle使用上述library cache latches(包括library cache latchlibrary cache lock latchlibrary cache pin latchlibrary cache pin allocation latchlibrary cache load lock latch)的目的是控制并发访问library cache object所需要的相关的enqueue或者是为了控制并发访问library cache中的相关的内存结构,比如用相关的library cache lock latch控制并发获得library cache lock。这里我猜测Oraclelibrary cache lock latch控制并发获得library cache lock,用library cache pin latch控制并发获得library cache pin,用library cache load lock latch控制并发获得library cache load locklibrary cache latch去控制并发访问library cache object handle中的某些结构,如library cache object handle中的flag中的special status flag special status flags are protected by the library cache latch. Examples of these flags indicate that: The object is valid; The object is authorized; The object has compilation errors)。

b)         Library cache load lock是另外一种enqueueThe session tries to find the library cache load lock for the database object so that it can load the object. The library cache load lock is always obtained in Exclusive mode, so that no other process can load the same object. If the library cache load lock is busy the session will wait on this event until the lock becomes available.

 

好了,现在我们来验证一下,还是上述10.2.0.5的环境,我将上述sqlselect name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’)马上再执行一遍,这是软解析,必然要获得library cache lock,不需要获得library cache load lock,所以对应的latch应该表现为library cache lock latchgets增加,library cache load lock latchgets不变:

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

 

NAME                                                   LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache                                               5    9222166       1608        800           2596            76766

library cache lock                                          6   13548760        582          6              0                0

library cache lock allocation                               3     208287          0          0              0                0

library cache pin                                           6    4207656        193          0              2                0

library cache hash chains                                   9          0          0          0              0                0

library cache pin allocation                                3      57278          0          0              0                0

library cache load lock                                     5      24848          0          0              1                0

 

7 rows selected

从结果里我们可以看到,library cache lock latchgets13548247递增到了13548760library cache pin latchgets4207462递增到了4207656,但library cache load lock latchgets还是保持24848不变。

 

现在我们来让library cache load lock latchgets发生变化,这是非常容易的事情,我们只需要执行一个需要硬解析的sql就可以了:

SQL> select * from scott.emp_temp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  ISINSPECT

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

 

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

 

NAME                                                   LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache                                               5    9223549       1608        800           2596            76766

library cache lock                                          6   13550296        582          6              0                0

library cache lock allocation                               3     208348          0          0              0                0

library cache pin                                           6    4208118        193          0              2                0

library cache hash chains                                   9          0          0          0              0                0

library cache pin allocation                                3      57294          0          0              0                0

library cache load lock                                     5      24856          0          0              1                0

 

7 rows selected

由于我们执行了一个需要硬解析的sql,导致Oracle需要获得library cache load lock以便load相关信息到这个sql的子cursorheap 6中,而要获得library cache load lock,必须先持有library cache load lock latch。从上述结果中我们可以看到,此时library cache load lock latchgets已经发生了变化,从24848递增到了24856

 

接下来我们再来看一看上述library cache latches的子latch情况:

SQL> show parameter cpu_count

 

NAME                                 TYPE        VALUE

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

cpu_count                            integer     2

 

这里cpu的个数为2,显然上述library cache latches的子latch应该为3

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name like ‘library%’;

 

NAME                                                   LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache                                               5    3274551       1301         94            187                0

library cache                                               5    2218356        116         80            933                0

library cache                                               5    3731320        191        626           1476            76766

library cache lock                                          6    5339737        362          3              0                0

library cache lock                                          6    6223353        194          3              0                0

library cache lock                                          6    1987799         26          0              0                0

library cache pin                                           6    1484918        184          0              0                0

library cache pin                                           6     891695          3          0              2                0

library cache pin                                           6    1831837          6          0              0                0

library cache pin allocation                                3      23177          0          0              0                0

library cache pin allocation                                3       8272          0          0              0                0

library cache pin allocation                                3      25849          0          0              0                0

library cache lock allocation                               3      75900          0          0              0                0

library cache lock allocation                               3      28229          0          0              0                0

library cache lock allocation                               3     104237          0          0              0                0

library cache hash chains                                   9          0          0          0              0                0

library cache hash chains                                   9          0          0          0              0                0

library cache hash chains                                   9          0          0          0              0                0

 

18 rows selected

注意,结果里并没有library cache load lock latch,说明library cache load lock latch没有children,它是一个solitary类型的latch

 

10.2.0.2开始,Oracle_kks_use_mutex_pin的默认值改成了true,这意味着从10.2.0.2开始,Oracle里将再不会有针对cursorlibrary cache pin等待,取而代之的是mutex等待,具体表现为cursor: pin *等待,如cursor: pin S wait on X

这里需要我们了解的是:

a)         10.2.0.2开始,Oracle只是用mutex替代了针对cursorlibrary cache pin,这并不代表从10.2.0.2开始Oracle里就没有library cache pin等待了。比如这个例子里的library cache pin等待就发生在10.2.0.4中:http://www.dbsnake.net/solve-library-cache-pin.html

 

b)         Mutexlatch是互相独立,没有任何关系的:Latches and mutexes are independent mechanisms i.e. a process can hold a latch and a mutex at the same time. In the case of process death, latches are always cleaned up before mutexes. There is no generic mutex deadlock detection (unlike latches). There is no mutex/latch hierarchy.

 

11gR1开始,Oraclemutex替换了library cache latches,并引了一个新的等待事件:library cache: mutex *我们来看一下这个知识点:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as nbs

 

SQL>  select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like ‘library%’;

 

NAME                                                                 LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

library cache load lock                                                   5          0          0          0              0                0

 

SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name like ‘library%’;

 

NAME                                                                 LEVEL#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

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

 

从结果里我们可以看到,在11.2.0.1里,各种library cache latches都没有了,只剩下了library cache load lock latch,而且Oracle还没有使用这个latch,因为gets0