12C library cache lock & library cache pin 模拟测试

孙显鹏,Oracle 十年从业经验,拥有11G ocp认证,精通内部原理,擅长调优,解决疑难问题,致力于帮助客户解决生产过程过出现的性能问题,提高生产效率!爱好书法!

目标:

该文档通过模拟library cache lock & library cache pin 等待了解产生该等待的过程有助于我们快速定位解决该类等待异常。

概述:

library cache lock:

This event controls the concurrency between clients of the library cache. It acquires a

lock on the object handle so that either:

? One client can prevent other clients from accessing the same object

? The client can maintain a dependency for a long time (for example, no other client

can change the object)

This lock is also obtained to locate an object in the library cache.

Wait Time: 3 seconds (1 second for PMON)

Parameter Description

handle address Address of the object being loaded

lock address Address of the load lock being used. This is not the same thing as a

latch or an enqueue, it is a State Object.

mode Indicates the data pieces of the object which must be loaded

library cache pin:

This event manages library cache concurrency. Pinning an object causes the heaps to

be loaded into memory. If a client wants to modify or examine the object, the client

must acquire a pin after the lock.

Wait Time: 3 seconds (1 second for PMON)

Parameter Description

handle address Address of the object being loaded

pin address Address of the load lock being used. This is not the same thing as a

latch or an enqueue, it is basically a State Object.

mode Indicates which data pieces of the object that must be loaded

DDL操作都需要library cache lock X 和 library cache pin X

执行存储过程library cache lock N library cache pin S

需要注意mutex只是替代了library cache lock latch 和 library cache pin latch ,library cache lock 和library cache pin 并没有改变这一点需要明确!!

模拟步骤:

测试环境:

Linux 7+Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

session A: 64

Name=CDB$ROOT.SYS.LOCK_TEST

LockMode=N PinMode=S LoadLockMode=0

 

session B: 68

Name=CDB$ROOT.SYS.LOCK_TEST

64持有:

LockMode=N PinMode=S LoadLockMode=0

68请求:

LockMode=X PinMode=X (trace 中X 没有成功仍然是S 模式)

64在parent上持有N模式,68在parent上请求X模式可以获取

64在child上持有S模式,68在child 上请求X模式等待

 

session C:43

 

Name=CDB$ROOT.SYS.LOCK_TEST

64持有:

LockMode=N PinMode=S LoadLockMode=0

68持有:LockMode=X,请求 PinMode=X(等待library cache pin)

43请求:

LockMode=X (等待library cache lock)PinMode=X

trace 文件没有drop procedure lock_test信息,也就是没有分配存储过程的游标handle

 

模拟修改表结构:

SESSION A:

DDL操作添加:LockMode=X PinMode=X.

未经允许不得转载:Oracle一体机用户组 » 12C library cache lock & library cache pin 模拟测试

相关推荐