客服微信
操作系统：Red Hat Enterprise Linux release 8.8 (Ootpa)
数据库：oracle Version 19.3.0.0.0
在生产环境上，我们碰到的大部分library cache pin等待主要源头是存储过程。那该现象底层原理是什么？接下来通过实验来把这个等待链梳理清晰。
该实验的逻辑：
1、会话1执行某个运行时间很长的存储过程
2、会话2尝试编绎、修改、删除该存储过程
接下来针对以上dump进行解析
· 存储过程只是编绎，并未执行，所以对象上的PIN和LOCK都是0
· 没有子游标信息
可以看到，存储过程有4个依赖对象
· DBMS_RANDOM
· STANDARD
· LIBRARY_CACHE_LOCK_PIN
· SYS_STUB_FOR_PURITY_ANALYSIS
以表对象LIBRARY_CACHE_LOCK_PIN为例，查看 Handle=0x70b57c08的对象信息
· 表对象的类型Namespace=TABLE/PROCEDURE(01) Type=TABLE(02)
· 持有的锁LockMode=0 PinMode=0
可以看到存储过程包括
· heap0
· heap1
· heap2
· heap4
· heap7
· heap13
相比之前的dump，多了一个依赖对象信息。
看看执行存储过程的时候，对象上的pin和lock
注：SQL类型是游标，属于瞬时对象，瞬时对象的LOCK锁只有0和N。
同时可以看到子游标句柄也在父游标heap 0中。这里的ChildTable,表示有几个子游标，这里id=0，表示只有一个子游标。
观察如下
· 子游标句柄上的申请的锁为LockMode=N PinMode=S，因为该游标正在执行，PIN是保护这个游标的OBJECT部分，也就是堆的部分。子游标的对象信息被保护，此时如果想修改这个对象，需要获取X模式的PIN锁，无法获取到
· 可以观察到，只有子游标下的HEAP0和HEAP6的Flags=I/-/P/A/-/-/-。这里P表示被PIN住。
这里的对象信息就是存储过程调用的存储过程。
存储过程本身的 LockMode=N PinMode=S ，因为它在执行过程中需要申请的锁。
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
|
设想场景1：在另一个会话中执行删除存储过程的命令
设想场景2：修改存储过程定义
设想场景3：编绎存储过程
通过实验发现，会话1在执行存储过程的时候，如果另外一个会话针对存储过程执行DDL操作，例如修改、 删除或编绎，会等待library cache pin。
执行存储过程BEGIN test_library_cache_lock_pin; END;
执行语句的信息ORCLPDB1.SYS.TEST_LIBRARY_CACHE_LOCK_PIN
这里我们看编绎命令的句柄
子游标句柄上的锁
#这里可以看LockMode=N PinMode=X，因为是修改操作，所以compile在子游标句柄上申请X模式的PIN琐，N模式的LOCK琐。PIN住的目的是为了获得句柄上的独占锁，为后面修改做准备。
如果此时在表上执行DDL，会报资源ORA-00054
修改DDL锁参数
发现此时的锁已经不是library cache上的，而是enq表锁，阻塞源是正在执行的存储过程，该存储过程调用了表,而表上的操作是dml，如果是dql还会报错吗？
实验证明，依然报错
通过等待事件可以确认产生等待的对象，那如何确认等待链关系？
生成日志
|
序号
|
函数
|
定义
|
作用
|
|
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是被阻塞者
SID=426
v$session_event
session id: 426中wait history中排名前3等待一致
v$session
v$session_Wait
与waiting for 'library cache pin' with wait info信息一致
查看等待事件详情
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
