如何使用event 10049分析定位library cache lock and library cache pin

2022-04-07 19:02:14

Oracle Library Cache 的 lock 与 pin 说明

一. 相关的基本概念

之前整理了一篇blog，讲了Library Cache 的机制，参考：

Oracle Library cache 内部机制说明

http://blog.csdn.net/tianlesoftware/article/details/6629869

在这个机制中，没有详细讲library 上的lock 和pin。这2个概念对DB 的理解非常重要。所以单独拿出来，进行说明。

根据hellodba 和 dbsnake 的相关blog，进行了整理，原文链接如下：

http://www.hellodb.net/2010/07/oracle-library-cache.html

http://dbsnake.com/2011/05/lib-cache-lck-and-pin.html

http://dbsnake.com/2011/06/lib-cache-lck-and-pin-1.html

在library cache 机制的文章里提到，Library cache Handle 里保存了lock 和 pin 的信息。而且在Library cache handle 和child cursor 上都有lock 和pin。它们称为library cache lock和library cache pin。

常说的library cache lock和library cache pin是enqueue，不是latch，它们是两种DDL lock。意的是，在11gR1之前，Oracle中又存在名为library cache lock和library cache pin的latch。

对于这个library cache lock 是不是enqueue，在dbsnake的blog上有讨论。我这里沿用dbsnake的观点：该lock 是enqueue 来整理这篇blog。

在DSI 405 的第二篇里有介绍Enqueue Structures。

1. Library cache中的并发控制：

Oracle利用Library cache lock和Library cache pin来实现并发控制，Library cache lock是在handle上获取的，而Library cache pin则是在data heap上获取。访问对象时，首先必须获取handle上的lock，然后将访问的数据pin在内存中。lock的作用是控制进程间的并发访问，而pin的作用是保证数据一致性，防止数据在访问时被交换出去。

lock和pin的实现类似于enqueue，在每个handle上都有lock和pin的holder list和waiter list，用来保存持有该资源和等待该资源的队列。

2. 阻塞分析：

现实情况中，我们有一个数据库中存在被应用大量频繁访问的procedure，当依赖的表发生变更时，导致该procedure失效，这时会出现大量的library cache lock和library cache pin的等待，堵塞应用访问，造成了重大故障。出现这个问题的原因是：当procedure失效后，所有访问该对象的进程都尝试去编译，大量进程尝试获取exclusive类型的lock和pin，出现了大量的等待。后续的Oracle版本作出了改进，当出现这种情况时，只允许第一个进程尝试去编译该对象，编译通过后，所有的进程就可以并发访问，避免了大量进程同时尝试编译的情况出现。

3. Library cache中的Latch：

Librarycache中相关的latch包括：shared pool latch，library cahce latch，library cache lock latch，library cache pin latch。

Sharepool latch的主要作用是分配或释放空间时使用，从Oracle9i开始，shared pool被分成了很多个subpool，由多个shared pool latch保护，Oracle开始支持更大的sharedpool。

Librarycache latch的主要作用是在hashbucket中定位handle时使用，library cache lock latch和library cache pin latch分别是获取lock和pin时，需要取得的latch。

sharedpool大小不合理，大量的硬解析以及SQL版本过多都可能导致shared pool latch和library cache latch的争用。

从Oracle10g开始，Oracle正在逐步用mutex取代library cache中的latch，cursor：pin S和cursor：pin X相当于share和exclusive类型的library cache pin，cursor：pin S wait on X则表示share方式正在等待exclusive锁定。

4. enqueue，library cache lock和librarycache pin的作用

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

Byacquiring a library cache lock on the library cache object handle, a processcan prevent other processes from accessing the object, or even finding out whattype it is. It can even maintain a dependency on an object without preventingother processes from accessing the object. Acquiring alibrary cache lock is also the only way to locate an object in cache--a processlocates and locks an object in a single operation.

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

5. library cache lock和library cache pin的几种lockmode

5.1 Library cache lock有三种lockmode，分别是share、exclusive和null

Aprocess acquires a share library cache lock if it intends only to read theobject. For example, it wants toreference the object during compilation. A process acquires an exclusivelibrary cache lock if it intends to create or modify the object.

Forexample, it wants to drop the object from the database. Null library cachelocks are a special case. They are acquired on objects that are to be executedlike child cursor, procedure, function, package, or type body. You can use themto maintain an interest on an object for a long period of time (sessionpersistency), and to detect if the object becomes invalid. You can break null library cache lock at any time. Thisis used as a mechanism to notify a session that an executable object is nolonger valid. If a null library cache lock is broken,and thus the object is invalidated, then it is an indication to the user whowas 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 SQLstatement execution and is held as long as the shared SQL area for thatstatement remains in the shared pool. A null librarycache lock does not prevent any DDL operation, and can be broken to allowconflicting DDL operations, hence the term "breakable parse lock."A null library cache lock on an object is broken when there is an exclusivelibrary cache pin on the object.

5.2 Library cache pin有两种lock mode，分别是share和exclusive。

Whena process pins an object data heap that is not in memory, the process candetermine 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 bemodified. However, the process first will alwayspin the object in Share mode, examine it for errors and security checks, andthen, 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. Thisis because all dependent transient objects (cursors) are invalidated (nulllocks broken) when an object is unpinned from Exclusive mode. The effect wouldbe unnecessary recompilation and reparsing of all dependent packages,procedures, and functions.

5.3下面详细解释在修改和访问对象时，lock和pin的作用：

5.3.1修改对象：

编译SQL或PLSQL对象，获取该对象（cursor，procedure）handle上exclusive类型的lock，并且持有data heap上exclusive类型的pin，防止其他人读取和修改。同时，在该对象所依赖的对象（table）上，必须持有一个share类型的lock和pin，防止在修改的过程中，被其他进程所修改。

5.3.2访问对象：

访问SQL或PLSQL对象，获取该对象（cursor，procedure）handle上NULL类型的lock，并且持有data heap上share类型的pin，同时，在其依赖的对象（table）上持有share类型的lock和pin。如果一个procedure依赖另外一个function，那么在被依赖的function上，也需要持有share类型的lock和pin。

NULL类型的lock比较特殊，它只存在于cursor和procedure等只读对象上，它并不起到任何并发控制的作用，它更象是一个trigger，当对象失效时，通知所有访问这个cursor的进程。比如：select* from emp这个SQL，依赖emp表，当emp表发生变化时，cursor上的NULL lock被打破，所有有访问这个cursor的进程都会知道该对象已经失效。

当持有对象的library cache pin时，会在row cache中对相应的对象加锁，就是row cache lock，阻止可能导致数据字典信息混乱的DDL发生。

6. latch，librarycache lock和library cache pin作用

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

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.5.0

Connected as ipra

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAMELEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

------------------------------------------------------------ ---------- ---------- ---------- -------------- ----------------

library cache 5 9221760 1608 800 2596 76766

library cache lock 6 13548247 5826 0 0

librarycache lock allocation 3 208273 0 0 0 0

library cache pin 6 4207462 1930 2 0

library cache hash chains 9 0 0 0 0 0

librarycache pin allocation 3 57276 0 0 0 0

librarycache load lock 5 24848 0 0 1 0

7 rowsselected

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

那么library cache lock latch、library cache pin latch以及大家最耳熟能详的library cache latch等等，这些latch是做什么用的呢？也许我们可以从下面的一段文字中找到答案：

The library cache latches serialize access to the objects inthe library cache. Access to library cache objects always occurs throughlibrary cache locks. Because locking an object is not an atomicinstruction, a library cache latch is acquired before the library cache lockrequest and is released after it. For most operations, the library cachelatches are used, and therefore they can become a point of contention.

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

6.1 几点关注的地方：

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

（2）Librarycache load lock是另外一种enqueue。The session tries to find the library cacheload lock for the database object so that it can load the object. The library cache load lock is always obtained in Exclusivemode, so that no other process can load the same object. If the librarycache load lock is busy the session will wait on this event until the lockbecomes available.

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

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAMELEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

------------------------------------------------------------ ---------- ---------- ---------- -------------- ----------------

librarycache 5 9222166 1608 800 2596 76766

library cache lock 6 13548760 5826 0 0

librarycache lock allocation 3 208287 0 0 0 0

library cache pin 6 4207656193 0 2 0

librarycache hash chains 9 0 0 0 0 0

librarycache pin allocation 3 57278 0 0 0 0

library cache load lock 5 248480 0 1 0

7 rowsselected

从结果里我们可以看到，library cache lock latch的gets从13548247递增到了13548760，library cache pin latch的gets从4207462递增到了4207656，但library cache load lock latch的gets还是保持24848不变。

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

SQL> select * from scott.emp_temp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO ISINSPECT

----- ---------- --------- ---------------- --------- --------- ------ ----------

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAMELEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES

------------------------------------------------------------ ---------- ---------- ---------- -------------- ----------------

librarycache 5 9223549 1608 800 2596 76766

librarycache lock 6 13550296 582 6 0 0

librarycache lock allocation 3 208348 0 0 0 0

librarycache pin 6 4208118 193 0 2 0

librarycache hash chains 9 0 0 0 0 0

librarycache pin allocation 3 57294 0 0 0 0

library cache load lock 5 248560 0 1 0

7 rowsselected

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

接下来我们再来看一看上述librarycache 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_missesfrom v$latch_children where name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_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 00

library cache pin allocation 3 8272 0 0 0 0

library cache pin allocation 3 25849 0 0 00

library cache lock allocation 3 75900 0 0 00

library cache lock allocation 3 28229 0 0 00

library cache lock allocation 3 104237 0 00 0

library cache hash chains 9 0 0 0 0 0

18 rows selected

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

6.2 Mutex 说明

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

这里需要我们了解的是：

（1）从10.2.0.2开始，Oracle只是用mutex替代了针对cursor的librarycache pin，这并不代表从10.2.0.2开始Oracle里就没有librarycache pin等待了。比如这个例子里的library cache pin等待就发生在10.2.0.4中：http://dbsnake.com/2010/06/solve-library-cache-pin.html

（2）Mutex和latch是互相独立，没有任何关系的：Latches and mutexes are independent mechanisms i.e. a process canhold 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 mutexdeadlock detection (unlike latches). There is no mutex/latch hierarchy.

从11gR1开始，Oracle用mutex替换了librarycache latches，并引了一个新的等待事件：librarycache: mutex *，我们来看一下这个知识点：

Connected to Oracle Database 11g EnterpriseEdition Release 11.2.0.1.0

Connected as nbs

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latchwhere name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

-------------------------------------------------------------------------- ---------- ---------- ---------- -------------- ----------------

library cache load lock 5 0 0 0 0 0

SQL> selectname,level#,gets,misses,sleeps,immediate_gets,immediate_misses fromv$latch_children where name like 'library%';

NAME LEVEL# GETS MISSES SLEEPSIMMEDIATE_GETS IMMEDIATE_MISSES

-------------------------------------------------------------------------- ---------- ---------- ---------- -------------- ----------------

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

二. 深入研究

在这部分主要说明如下结论：

1、针对cursor的library cachelock的lock mode确实是null，无论该cursor所对应的sql是硬解析还是软解析；

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

3、 sql的软解析时，library cache pin的lock mode始终是S；

4、 sql的硬解析时，library cache pin的lock mode一般是X，但在10.2.0.1中，即使是硬解析，也存在lock mode为S的library cache pin。

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

2.1 Oracle 10.2.0.1 下的测试

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.1.0

Connected as SYS

10.2.0.1中_kks_use_mutex_pin的值为false，表示Oracle不会用mutex取代针对cursor的library cache pin：

SQL> select name,value,description from sys.all_parameterswhere name like '_kks%';

NAME VALUE DESCRIPTION

------------------------------ ------------------------------------------------------------

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

注意这里的all_parameters是自己创建的视图。主要是为了方便查看隐含参数。

/* Formatted on2011/7/28 15:01:16 (QP5 v5.163.1008.3004) */

CREATE VIEW all_parameters

SELECT i.ksppinm name,

i.ksppdesc description,

CV.ksppstvl VALUE,

CV.ksppstdf isdefault,

DECODE (BITAND (CV.ksppstvf, 7),

1, 'MODIFIED',

4, 'SYSTEM_MOD',

'FALSE')

ismodified,

DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted

FROM sys.x$ksppi i, sys.x$ksppcv CV

WHERE i.inst_id = USERENV ('Instance')

AND CV.inst_id = USERENV ('Instance')

AND i.indx = CV.indx

AND i.ksppinm LIKE '/_%' ESCAPE '/'

ORDER BY REPLACE (i.ksppinm, '_', '');

Oracle 参数分类和参数的查看方法

http://blog.csdn.net/tianlesoftware/article/details/5583655

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

----- ---------- --------- ---------------- --------- --------- ------

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.00 30

......省略显示部分内容

7800 JAME3 CLERK 7698 1981-12-3 950.00 30

13 rows selected

SQL> select hash_value,sql_text fromv$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针对上述sql的library cache pin和library cache lock，方法我之前已经说了，就是用event 10049，用10049的难点在于如何确定level。

确定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 */

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

另外我们是要tracelibrary cache lock和library cache pin，所以我们一定要用到KGLTRCLCK和KGLTRCPIN，即0x0010和0x0020；

最后就是我们需要把这个sql的hash value的16进制的后两个byte拿出来，作为10049的level的前缀。

从上面结果中我们可以看到，select * from scott.emp的hash value的16进制的后两个byte是0xA0CC。

另外KGLTRCHSH | KGLTRCLCK | KGLTRCPIN ＝ 0x2000 | 0x0010 | 0x0020 = 0x2030。按照上述算法，select * from scott.emp的10049的最终level值就是0xa0cc2030，也就是2697732144：

SQL> selectto_number('a0cc2030','XXXXXXXXXXXX') from dual;

TO_NUMBER('A0CC2030','XXXXXXXX

------------------------------

2697732144

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

SQL> oradebug setmypid

已处理的语句

SQL> oradebug event10049 trace name context forever,level 2697732144

已处理的语句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

----- ---------- --------- ---------------- --------- --------- ------

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.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 Pinaddr = 0x31718A28 mode = S

KGLTRCPIN kglpndl hd = 0x33938034 KGL Pinaddr = 0x31718A28 mode = S

KGLTRCLCK kgllkdl hd = 0x33938034 KGL Lockaddr = 0x31716F50 mode = N

KGLTRCLCK kgllkdl hd = 0x33938118 KGL Lockaddr = 0x3174A99C mode = N

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

SQL> select sql_text from v$sqlareawhere address='33938118';

SQL_TEXT

--------------------------------------------------------------------------------

select * from scott.emp

hd = 0x33938034就是hd = 0x33938118的child cursor：

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where kglhdadr='33938034';

KGLHDADR KGLHDPAR KGLNAOBJ

-------- ----------------------------------------------------------------------------------------

33938034 33938118 select * from scott.emp

从上述trace文件中我们可以得出如下结论：

1、10.2.0.1中，sql软解析时，针对cursor的library cachelock的lock mode确实是null；

2、10.2.0.1中，sql软解析时，针对cursor的library cachepin的lock mode确实是S；

现在我们来观察10.2.0.1下sql的硬解析时librarycache pin和library 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 fromv$sqlarea where sql_text like 'select * from scott.emp%';

HASH_VALUE SQL_TEXT

------------------------------------------------------------------------------------------

SQL> oradebug setmypid

已处理的语句

SQL> oradebug event 10049 trace namecontext forever,level 2697732144

已处理的语句

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

----- ---------- --------- ---------------- --------- --------- ------

7981 CUIHUA 7981

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00300.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 Pinaddr = 0x317187C0 mode = X

KGLTRCPIN kglpndl hd = 0x206ECF90 KGL Pinaddr = 0x317187C0 mode = X

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

KGLTRCPIN kglpin hd = 0x33B19238 KGL Pinaddr = 0x31717F28 mode = X

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

KGLTRCLCK kgllkdl hd = 0x33B19238 KGL Lockaddr = 0x3174E618 mode = N

KGLTRCLCK kgllkdl hd = 0x206ECF90 KGL Lockaddr = 0x3174E068 mode = N

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where kglhdadr='33B19238';

KGLHDADR KGLHDPAR KGLNAOBJ

-------- ----------------------------------------------------------------------------------------

33B19238 206ECF90 select * from scott.emp

从上述trace文件中我们可以得出如下结论：

1、10.2.0.1中，sql硬解析时，针对cursor的library cachelock的lock mode依然是null；

2、10.2.0.1中，sql硬解析时，针对cursor的library cachepin的lock mode一般是X，但也存在lock mode为S的library cachepin，且这个S是针对子cursor的。

2.2 Oracle 10.2.0.5 下的测试

Connected to Oracle Database 10g EnterpriseEdition Release 10.2.0.5.0

Connected as SYS

MOS上说：从10.2.0.2开始，Oracle将_kks_use_mutex_pin的默认值改成了true，表明Oracle将用mutex替代针对cursor的library cache pin。但实际情况并不完全是这样，详情见后面的测试：

SQL> select name,value,description fromsys.all_parameters where name like '_kks%';

NAME VALUE DESCRIPTION

------------------------------ ------------------------------------------------------------

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

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30030

......省略显示部分内容

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> select hash_value,sql_text fromv$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 namecontext forever,level 2697732144

Statement processed.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30030

......省略显示部分内容

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 EditionRelease 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand 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 (TNSV1-V3)) 2011-06-01 13:38:07.944

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

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

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

KGLTRCLCK kglget hd = 0x700000022595c38 KGLLock addr = 0x70000001f724d78 mode = N

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

KGLTRCLCK kgllkdl hd = 0x7000000226ec4f8KGL Lock addr = 0x70000001f74e128 mode = N

KGLTRCLCK kgllkdl2 hd = 0x7000000226ec4f8KGL Lock addr = 0x70000001f74e128 mode = 0

KGLTRCLCK kgllkdl hd = 0x700000022595c38KGL Lock addr = 0x70000001f724d78 mode = N

KGLTRCLCK kgllkdl2 hd = 0x700000022595c38KGL Lock addr = 0x70000001f724d78 mode = 0

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

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where lower(kglhdadr)='07000000226ec4f8';

KGLHDADR KGLHDPAR KGLNAOBJ

---------------- ------------------------------------------------------------------------------------------------

07000000226EC4F80700000022595C38 select * from scott.emp

从上述trace文件中我们可以得出如下结论：

10.2.0.5中，sql软解析时，针对cursor的librarycache pin确实已经不存在；

现在我们来观察10.2.0.5下sql的硬解析时librarycache pin和library cache lock：

$ sqlplus '/ as sysdba';

SQL*Plus: Release 10.2.0.5.0 - Productionon Wed Jun 1 13:42:11 2011

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand 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 namecontext forever,level 2697732144

Statement processed.

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30030

......省略显示部分内容

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 EditionRelease 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand 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 (TNSV1-V3)) 2011-06-01 13:42:44.913

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

*** SESSION ID:(159.3) 2011-06-0113: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 KGLLock addr = 0x70000001f725560 mode = N

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

KGLTRCPIN kglpndl hd = 0x7000000225ccfa8 KGL Pin addr = 0x70000001f726378mode = X

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

KGLTRCLCK kglget hd = 0x7000000225abf18 KGLLock addr = 0x70000001f733120 mode = N

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

KGLTRCPIN kglpndl hd = 0x7000000225abf18 KGL Pin addr = 0x70000001f726840mode = X

KGLTRCLCK kgllkdl hd = 0x7000000225abf18KGL Lock addr = 0x70000001f733120 mode = N

KGLTRCLCK kgllkdl2 hd = 0x7000000225abf18KGL Lock addr = 0x70000001f733120 mode = 0

KGLTRCLCK kgllkdl hd = 0x7000000225ccfa8KGL Lock addr = 0x70000001f725560 mode = N

KGLTRCLCK kgllkdl2 hd = 0x7000000225ccfa8KGL Lock addr = 0x70000001f725560 mode = 0

SQL> select kglhdadr,kglhdpar,kglnaobjfrom x$kglob where lower(kglhdadr)='07000000225abf18';

KGLHDADR KGLHDPAR KGLNAOBJ

---------------- ------------------------------------------------------------------------------------------------

07000000225ABF1807000000225CCFA8 select * from scott.emp

从上述trace文件中我们可以得出如下结论：

1、10.2.0.5中，sql硬解析时，依然存在library cache pin；

2、10.2.0.5中，sql硬解析时，针对cursor的library cachepin的lock mode始终是X；

码农公寓

相关文章