注: 本文为云贝教育 刘峰 原创,请尊重知识产权,转发请注明出处,不接受任何抄袭、演绎和未经注明出处的转载。
实验环境
操作系统: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相关竞争,又该如何模拟验证?接下来的实验暂时留给读者,期待我们下一篇文章。