注: 本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
实验环境
操作系统：Red Hat Enterprise Linux release 8.8 (Ootpa)
数据库：oracle Version 19.3.0.0.0
一、存储对象的LOCK和PIN
在生产环境上，我们碰到的大部分library cache pin等待主要源头是存储过程。那该现象底层原理是什么？接下来通过实验来把这个等待链梳理清晰。
该实验的逻辑：
1、会话1执行某个运行时间很长的存储过程
2、会话2尝试编绎、修改、删除该存储过程
备注：以下操作在PDB下操作
dump library_cache 命令参考
1.1 创建存储过程
test_library_cache_lock_pin在内存中属于存储类型
1.2 dump库缓存
此时存储过程刚创建编绎完成，查看此时存储过程的dump信息。
1）查看存储过程的dump信息
接下来针对以上dump进行解析
2）解析dump信息
1.library cache对象信息
重点关注存储过程的library cache类型、句柄和堆上持有的锁类型。
· 存储过程只是编绎，并未执行，所以对象上的PIN和LOCK都是0
· 没有子游标信息
2.依赖信息
可以看到，存储过程有4个依赖对象
· DBMS_RANDOM
· STANDARD
· LIBRARY_CACHE_LOCK_PIN
· SYS_STUB_FOR_PURITY_ANALYSIS
3.底层调用的表信息
以表对象LIBRARY_CACHE_LOCK_PIN为例，查看 Handle=0x70b57c08的对象信息
· 表对象的类型Namespace=TABLE/PROCEDURE(01) Type=TABLE(02)
· 持有的锁LockMode=0 PinMode=0
4.存储过程的heap信息
可以看到存储过程包括
· heap0
· heap1
· heap2
· heap4
· heap7
· heap13
可以看到，存储过程没有heap6
1.3 调用存储过程
通过调用存储过程，看下执行存储过程中，调用的锁信息
1）在调用存储过程之前，调整一下存储过程内存，让它延时100000秒执行完成。
2）查看修改之后的存储过程对象信息
相比之前的dump，多了一个依赖对象信息。
3）调用该存储过程
看看执行存储过程的时候，对象上的pin和lock
1.执行语句的pin和lock
存储过程的类型也是游标，和SQL不一样，它属于存储对象
注：SQL类型是游标，属于瞬时对象，瞬时对象的LOCK锁只有0和N。
2.父游标heap0信息
可以看到父游标heap 0上的pins=0，表示heap上未加pin锁。
同时可以看到子游标句柄也在父游标heap 0中。这里的ChildTable,表示有几个子游标，这里id=0，表示只有一个子游标。
3.子游标信息
观察如下
· 子游标句柄上的申请的锁为LockMode=N PinMode=S，因为该游标正在执行，PIN是保护这个游标的OBJECT部分，也就是堆的部分。子游标的对象信息被保护，此时如果想修改这个对象，需要获取X模式的PIN锁，无法获取到
· 可以观察到，只有子游标下的HEAP0和HEAP6的Flags=I/-/P/A/-/-/-。这里P表示被PIN住。
4.通过依赖对象Handle=0x76a09550找到存储过程的信息
这里的对象信息就是存储过程调用的存储过程。
存储过程本身的 LockMode=N PinMode=S ，因为它在执行过程中需要申请的锁。
5.NamespaceDump 命名空间
Child Cursor：记录了子游标下的heap0和heap6的生成信息。Heap0=0x73c1f2f0 Heap6=0x6af3b320和DataBlocks中heap0、heap6的Pointer信息对应。
总结
创建创建过程，未执行
|
object name |
LOCK |
PIN |
|
ORCLPDB1.SYS.TEST_LIBRARY_CACHE_LOCK_PIN |
0 |
0 |
执行存储过程的时候
|
object name |
LOCK |
PIN |
|
BEGIN test_library_cache_lock_pin; END; |
N |
S |
|
ORCLPDB1.SYS.TEST_LIBRARY_CACHE_LOCK_PIN |
N |
S |
二、library cache pin竞争
从上面的实验我们知道，存储过程在执行过程中，针对TEST_LIBRARY_CACHE_LOCK_PIN持有的LOCK=N,PIN=S。
2.1 设想生产环境场景
设想场景1：在另一个会话中执行删除存储过程的命令
设想场景2：修改存储过程定义
设想场景3：编绎存储过程
通过实验发现，会话1在执行存储过程的时候，如果另外一个会话针对存储过程执行DDL操作，例如修改、 删除或编绎，会等待library cache pin。
2.2 通过dump验证
执行存储过程BEGIN test_library_cache_lock_pin; END;
执行语句的信息ORCLPDB1.SYS.TEST_LIBRARY_CACHE_LOCK_PIN
2.3 在执行compile之后，查看dump信息
这里我们看编绎命令的句柄
子游标句柄上的锁
#这里可以看LockMode=N PinMode=X，因为是修改操作，所以compile在子游标句柄上申请X模式的PIN琐，N模式的LOCK琐。PIN住的目的是为了获得句柄上的独占锁，为后面修改做准备。
2.4 存储对象上的dump
2.5 对象上的琐
存储过程对象上能申请到LockMode=X ，表示complie语句已经获得TEST_LIBRARY_CACHE_LOCK_PIN上模式X的LOCK锁。为什么能获取到？因为执行compile之前对象上的LockMode=N，N锁不阻塞X锁获取。
PinMode=S，表示此时execute语句是分析阶段，所以需要获取共享pin锁。那如果compile进入执行阶段，需要获取x模式的pin锁,与execute阶段锁冲突，所以无法获取X模式的pin锁。
这也是为什么compile会话只等待libary cache pin，而不等待libarary cache lock的原因。
如果此时在表上执行DDL，会报资源ORA-00054
修改DDL锁参数
发现此时的锁已经不是library cache上的，而是enq表锁，阻塞源是正在执行的存储过程，该存储过程调用了表,而表上的操作是dml，如果是dql还会报错吗？
实验证明，依然报错
三、分析验证
通过等待事件可以确认产生等待的对象，那如何确认等待链关系？
3.1 hangalayze
生成日志
1) 查看等待链关系
PL/SQL lock timer堵塞了library cache pin，那哪个会话是源头，哪个会话是受害者？
看下被堵塞会话session id: 426（等待libraray cache pin）的调用关系
|
序号 |
函数 |
定义 |
作用 |
|
0 |
ssthrdmain |
operating system dependent system main for every thread in a threaded oracle |
|
|
1 |
opimai_real |
oracle program interface main real oracle start point |
|
|
2 |
sou2o |
main oracle executable entry point. reads environment var ORACLE_SPAWNED_PROCESS. |
|
|
3 |
opidrv |
oracle program interface route current request driver, entry side into two task interface |
|
|
4 |
piodr |
oracle program interface oracle code request driver, route the current request |
|
|
5 |
opiino |
oracle program interface initialize opi |
|
|
6 |
opitsk |
oracle program interface two task function dispatcher |
|
|
7 |
ttcpip |
two task common pipe read/write |
1-7都是操作系统层调用 |
|
8 |
kpoal8 |
kernel programmatic interface oracle V8 bundled execution |
|
|
9 |
kpooprx |
kernel programmatic interface oracle open, parse, and optionally execute |
准备执行 |
|
10 |
opiosq0 |
oracle program interface prepare to parse a sql command 0 |
解析 |
|
11 |
opiexe |
oracle program interface execute |
执行 |
|
12 |
kkpcrt |
kernel compile compilation create |
创建编绎存储过程任务 |
|
13 |
(kkdl)GetCodeObject |
kernel compile dictionary lookup ?? |
查找编绎需要的数据字典 |
|
14 |
(kkdllk)0 |
kernel compile dictionary lookup lock an object ?? |
给对象加锁(编绎需要在存储过程上加X模式LOCK锁) |
|
15 |
kglpin |
kernel generic library cache management pin heaps and load data pieces of an object ?? |
句柄上X模式LOCK锁加成功，进一步需要PIN住对象上的heap |
|
16 |
kglpnal |
kernel generic library cache management pin allocate |
申请X模式的pin琐 |
|
17 |
kslwaitctx |
kernel service latching and post-wait wait for n centi-seconds or until posted wait context; wait until timeout |
申请失败，进入post-wait等待状态 |
|
18 |
ksliwat |
kernel service latching and post-wait inner wait function; setup a wait that times out |
继续等待，直到超时 |
|
19 |
skgpwwait |
operating system dependent kernel generic post/wait wait |
继续等待 |
|
20 |
semtimedop |
SYSCALL: semaphore timed operation |
|
|
21 |
sspuser |
operating system dependent system process management handle SIGUSR2 for Oracle |
|
|
22 |
ksdxcb |
kernel service debug internal errors ksdx callback for sosd layer signal handler |
|
|
23 |
ksdxfstk |
kernel service debug internal errors dump abridged os stack |
|
|
24 |
ksedsts |
kernel service error debug dump the call stack short stack |
继续查看等待链关系
LEAF是阻塞源头，NLEAF是被阻塞者
2) 对比动态性能视图结果
SID=426
1.查看历史等待情况
v$session_event
session id: 426中wait history中排名前3等待一致
2.查看会话当前等待情况
v$session
或
v$session_Wait
与waiting for 'library cache pin' with wait info信息一致
3.查看等待事件的P1,P2,P3
查看等待事件详情
P1RAW=00000000769C3C80，就是存储过程的handle地址
P3RAW=00011D5500010003
00011D55表示ojbect_id
0001表示namespace,从dump中得知01对应的Namespace=TABLE/PROCEDURE(01)
003表示pinmode，3表示x模式的pin锁
00011D55转换十进制73045，对象TEST_LIBRARY_CACHE_LOCK_PIN的OBJECT_ID
四、总结
通过以上实验，我们对存储对象相关操作产生的library cache pin有了很清晰的认识，那如果是library cache lock相关竞争，又该如何模拟验证？接下来的实验暂时留给读者，期待我们下一篇文章。
