前段时间遇到的一个案例，当编译一个invalid procedure时，自已会话堵塞自己等待’library cache lock’. 数据库版本Oracle 12.2， 当然这个procedure里面用到了dblink 嵌套procedure跨了3个数据库，在查看procedures定义时发现附加了”EDITIONABLE”, EDITIONABLE在11.2引入，刚发现在12c开始EDITIONABLE成为了默认， EDITIONABLE是指可以在数据库中为一个对象创建多个版本， 如view, synonym, PL/SQL object , Tim Hall’s notes 介绍过可以参考。这里只是简单记录一下处理方法。
SQL> @s 4878
SID SQLID_AND_CHILD STATUS STATE EVENT SEQ# SEC_IN_WAIT BLOCKING_SID P1 P2 P3 P1TRANSL
------- -------------------- -------- ------- ---------------------------------------- ---------- ----------- ------------ ------------------ ------------------ ------------------ ------------------------------------------
4878 dc26hs56954yy 0 ACTIVE WAITING library cache lock 958 292 4878 handle address= lock address= 100*mode+namespace
0x000000033CB97E18 0x000000021ED50468 = 65539
SQL> @usid 4878
USERNAME SID AUDSID OSUSER MACHINE PROGRAM SPID OPID CPID SQL_ID HASH_VALUE LASTCALL STATUS SADDR PADDR TADDR LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ --------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
SYS '4878,8981' 4294967295 oracle anbob2 (TNS V1-V3) 75954 112 75953 dc26hs56954yy 1284674526 44 ACTIVE 0000000404159630 00000004234D9CC0 000000043D733238 20191231 09:39:36
SQL> oradebug setorapid 112
Oracle pid: 112, Unix process pid: 75954, image: oracle@anbob2 (TNS V1-V3)
SQL> oradebug dump errorstack 1;
Statement processed.
SQL> oradebug short_stack;
ksedsts()+346<-ksdxfstk()+71<-ksdxcb()+912<-sspuser()+217<-__sighandler()<-__poll()+16<-ipcgxp_selectex()+409<-ipclw_wait()+1045<-ksxpwait_ipclw()+3844<-ksxpwait_int()+22103<-ksxpwait()+845<-ksliwat()+10782<-kslwaitctx()+197<-kjusuc()+9058<-ksipgetctxia()+5359<-ksixpgetia()+167<-kqlmLock()+3201<-kqlmClusterLock()+209<-kgllkal()+3424<-kglLock()+1307<-kglget()+290<-kkdllk0()+427<-kqlrde()+1804<-kglrdi()+470<-kglrlo()+1016<-kqlrld()+3812<-kqlrldop()+121<-kqlLoadRemoteObject()+615<-kqllod()+242<-kglobld()+1080<-kglobpn()+2375<-kglpim()+425<-kglpin()+1672<-kglgob()+545<-kgldpo0()+673<-kgldpo()+89<-kgldon()+280<-pkldon()+94<-pkloud()+278<-phnnrl_name_resolve_by_loading()+3939<-phngdl_get_defining_libunit()+155<-phnrpls_resolve_prefix_libscope()+224<-phnrp_resolve_prefix()+138<-phnr_resolve()+224<-ph2o_get_cands()+343<-ph2o_overload_diana()+265<-ph2stm()+12894<-ph2sms()+243<-ph2blo()+539<-ph2obl()+111<-ph2uni()+4595<-ph2dr2()+338<-ph2drv()+304<-phpsem()+62<-phpcmp()+1543<-pcicmp0()+468<-kkxcmp0()+976<-rpiswu2()+627<-kkxcmp()+258<-kkpalt()+2564<-opiexe()+22930<-opiosq0()+4766<-opiodr()+1229<-rpidrus()+201<-skgmstack()+65<-rpidru()+134<-rpiswu2()+627<-rpidrv()+1540<-rpisplu_internal()+471<-kqlvld()+4104<-kglgob()+2737<-kkdlLoadDDL()+3444<-qcdlgbo()+8243<-qcdlgob()+1005<-qcsfgob()+290<-qcsprfro()+531<-qcsprfro_tree()+380<-qcsprfro_tree()+150<-qcspafq()+246<-qcspqbDescendents()+278<-qcspqb()+272<-kkmdrv()+192<-opiSem()+1978<-opiprs()+333<-kksParseChildCursor()+541<-rpiswu2()+627<-kksLoadChild()+5470<-kxsGetRuntimeLock()+2035<-kksfbc()+15083<-kkspsc0()+2130<-kksParseCursor()+123<-opiosq0()+2391<-kpooprx()+404<-kpoal8()+850<-opiodr()+1229<-ttcpip()+1257<-opitsk()+1940<-opiino()+941<-opiodr()+1229<-opidrv()+1021<-sou2o()+145<-opimai_real()+455<-ssthrdmain()+417<-main()+262<-__libc_start_main()+245
SQL> oradebug short_stack;
ksedsts()+346<-ksdxfstk()+71<-ksdxcb()+912<-sspuser()+217<-__sighandler()<-__poll()+16<-ipcgxp_selectex()+409<-ipclw_wait()+1045<-ksxpwait_ipclw()+3844<-ksxpwait_int()+22103<-ksxpwait()+845<-ksliwat()+10782<-kslwaitctx()+197<-kjusuc()+9058<-ksipgetctxia()+5359<-ksixpgetia()+167<-kqlmLock()+3201<-kqlmClusterLock()+209<-kgllkal()+3424<-kglLock()+1307<-kglget()+290<-kkdllk0()+427<-kqlrde()+1804<-kglrdi()+470<-kglrlo()+1016<-kqlrld()+3812<-kqlrldop()+121<-kqlLoadRemoteObject()+615<-kqllod()+242<-kglobld()+1080<-kglobpn()+2375<-kglpim()+425<-kglpin()+1672<-kglgob()+545<-kgldpo0()+673<-kgldpo()+89<-kgldon()+280<-pkldon()+94<-pkloud()+278<-phnnrl_name_resolve_by_loading()+3939<-phngdl_get_defining_libunit()+155<-phnrpls_resolve_prefix_libscope()+224<-phnrp_resolve_prefix()+138<-phnr_resolve()+224<-ph2o_get_cands()+343<-ph2o_overload_diana()+265<-ph2stm()+12894<-ph2sms()+243<-ph2blo()+539<-ph2obl()+111<-ph2uni()+4595<-ph2dr2()+338<-ph2drv()+304<-phpsem()+62<-phpcmp()+1543<-pcicmp0()+468<-kkxcmp0()+976<-rpiswu2()+627<-kkxcmp()+258<-kkpalt()+2564<-opiexe()+22930<-opiosq0()+4766<-opiodr()+1229<-rpidrus()+201<-skgmstack()+65<-rpidru()+134<-rpiswu2()+627<-rpidrv()+1540<-rpisplu_internal()+471<-kqlvld()+4104<-kglgob()+2737<-kkdlLoadDDL()+3444<-qcdlgbo()+8243<-qcdlgob()+1005<-qcsfgob()+290<-qcsprfro()+531<-qcsprfro_tree()+380<-qcsprfro_tree()+150<-qcspafq()+246<-qcspqbDescendents()+278<-qcspqb()+272<-kkmdrv()+192<-opiSem()+1978<-opiprs()+333<-kksParseChildCursor()+541<-rpiswu2()+627<-kksLoadChild()+5470<-kxsGetRuntimeLock()+2035<-kksfbc()+15083<-kkspsc0()+2130<-kksParseCursor()+123<-opiosq0()+2391<-kpooprx()+404<-kpoal8()+850<-opiodr()+1229<-ttcpip()+1257<-opitsk()+1940<-opiino()+941<-opiodr()+1229<-opidrv()+1021<-sou2o()+145<-opimai_real()+455<-ssthrdmain()+417<-main()+262<-__libc_start_main()+245
-- dia trace (HM) 中也有记录
*** 2019-12-31T10:04:41.401172+08:00
HM: Session with ID 4878 serial # 8981 (FG) on read/write instance 2 is hung
and is waiting on 'library cache lock' for 96 seconds.
Session was previously waiting on 'SQL*Net message from dblink'.
Final Blocker is Session ID 4878 serial# 8981 on instance 2
which is waiting on 'library cache lock' for 96 seconds
p1: 'handle address'=0x33cb97e18, p2: 'lock address'=0x21ed50468, p3: '100*mode+namespace'=0x10003
*** 2019-12-31T10:15:10.176899+08:00
HM: Short Stack of self-deadlocked session ID 4878, OSPID 75954 of hang ID 12
Short stack dump:
ksedsts()+346<-ksdxfstk()+71<-ksdxcb()+912<-sspuser()+217<-__sighandler()<-__poll()+16<-ipcgxp_selectex()+409<-ipclw_wait()+1045<-ksxpwait_ipclw()+3844<-ksxpwait_int()+22103<-ksxpwait()+845<-ksliwat()+10782<-kslwaitctx()+197<-kjusuc()+9058<-ksipgetctxia()+5359<-ksixpgetia()+167<-kqlmLock()+3201<-kqlmClusterLock()+209<-kgllkal()+3424<-kglLock()+1307<-kglget()+290<-kkdllk0()+427<-kqlrde()+1804<-kglrdi()+470<-kglrlo()+1016<-kqlrld()+3812<-kqlrldop()+121<-kqlLoadRemoteObject()+615<-kqllod()+242<-kglobld()+1080<-kglobpn()+2375<-kglpim()+425<-kglpin()+1672<-kglgob()+545<-kgldpo0()+673<-kgldpo()+89<-kgldon()+280<-pkldon()+94<-pkloud()+278<-phnnrl_name_resolve_by_loading()+3939<-phngdl_get_defining_libunit()+155<-phnrpls_resolve_prefix_libscope()+224<-phnrp_resolve_prefix()+138<-phnr_resolve()+224<-ph2o_get_cands()+343<-ph2o_overload_diana()+265<-ph2stm()+12894<-ph2sms()+243<-ph2blo()+539<-ph2obl()+111<-ph2uni()+4595<-ph2dr2()+338<-ph2drv()+304<-phpsem()+62<-phpcmp()+1543<-pcicmp0()+468<-kkxcmp0()+976<-rpiswu2()+627<-kkxcmp()+258<-kkpalt()+2564<-opiexe()+22930<-opiosq0()+4766<-opiodr()+1229<-rpidrus()+201<-skgmstack()+65<-rpidru()+134<-rpiswu2()+627<-rpidrv()+1540<-rpisplu_internal()+471<-kqlvld()+4104<-kglgob()+2737<-kkdlLoadDDL()+3444<-qcdlgbo()+8243<-qcdlgob()+1005<-qcsfgob()+290<-qcsprfro()+531<-qcsprfro_tree()+380<-qcsprfro_tree()+150<-qcspafq()+246<-qcspqbDescendents()+278<-qcspqb()+272<-kkmdrv()+192<-opiSem()+1978<-opiprs()+333<-kksParseChildCursor()+541<-rpiswu2()+627<-kksLoadChild()+5470<-kxsGetRuntimeLock()+2035<-kksfbc()+15083<-kkspsc0()+2130<-kksParseCursor()+123<-opiosq0()+2391<-kpooprx()+404<-kpoal8()+850<-opiodr()+1229<-ttcpip()+1257<-opitsk()+1940<-opiino()+941<-opiodr()+1229<-opidrv()+1021<-sou2o()+145<-opimai_real()+455<-ssthrdmain()+417<-main()+262<-__libc_start_main()+245
-- 函数调用 --
ipcgxp_selectex()+409 inter process calls [partial hit for: ipc ]
ipclw_wait()+1045 inter process calls lightweight (exafusion) [partial hit for: ipclw ]
ksxpwait_ipclw()+3844 kernel service cross instance cross instance ipc [partial hit for: ksxp ]
ksxpwait_int()+22103 kernel service cross instance cross instance ipc [partial hit for: ksxp ]
ksxpwait()+845 kernel service cross instance cross instance ipc [partial hit for: ksxp ]
ksliwat()+10782 kernel service latching and post-wait inner wait function; setup a wait that times out
kslwaitctx()+197 kernel service latching and post-wait wait for n centi-seconds or until posted wait context; wait until timeout
kjusuc()+9058 kernel lock management global enqueue service synchronous open and convert a lock
ksipgetctxia()+5359 kernel service instance locking get a group lock (synchronous interface to DLM for lock get) [partial hit for: ksipget ]
ksixpgetia()+167 kernel service instance locking [partial hit for: ksi ]
kqlmLock()+3201 kernel query library cache multi-instance manager [partial hit for: kqlm ]
kqlmClusterLock()+209 kernel query library cache multi-instance manager [partial hit for: kqlm ]
kgllkal()+3424 kernel generic library cache management library cache lock allocate
kglLock()+1307 kernel generic library cache management library cache lock
kglget()+290 kernel generic library cache management get a lock on an object
kkdllk0()+427 kernel compile dictionary lookup lock an object [partial hit for: kkdllk ]
kqlrde()+1804 kernel query library cache remote [partial hit for: kqlr ]
kglrdi()+470 kernel generic library cache management [partial hit for: kgl ]
kglrlo()+1016 kernel generic library cache management [partial hit for: kgl ]
kqlrld()+3812 kernel query library cache remote load a remote library object
kqlrldop()+121 kernel query library cache remote load a remote library object [partial hit for: kqlrld ]
kqlLoadRemoteObject()+615 kernel query library cache [partial hit for: kql ]
kqllod()+242 kernel query library cache database object load
kglobld()+1080 kernel generic library cache management object load
kglobpn()+2375 kernel generic library cache management object pin heaps and load data pieces
kglpim()+425 kernel generic library cache management pin and load more heaps
kglpin()+1672 kernel generic library cache management pin heaps and load data pieces of an object
kglgob()+545 kernel generic library cache management get an objected locked and pinned
kgldpo0()+673 kernel generic library cache management depend on an object [partial hit for: kgldpo ]
kgldpo()+89 kernel generic library cache management depend on an object
kgldon()+280 kernel generic library cache management depend on an object
pkldon()+94 PLSQL KG interface [partial hit for: pk ]
pkloud()+278 PLSQL KG interface [partial hit for: pk ]
phnnrl_name_resolve_by_loading()+3939 PLSQL semantics [partial hit for: phn ]
phngdl_get_defining_libunit()+155 PLSQL semantics [partial hit for: phn ]
phnrpls_resolve_prefix_libscope()+224 PLSQL semantics [partial hit for: phn ]
phnrp_resolve_prefix()+138 PLSQL semantics [partial hit for: phn ]
phnr_resolve()+224 PLSQL semantics [partial hit for: phn ]
ph2o_get_cands()+343 PLSQL phase 2 (semantic analyzer) [partial hit for: ph2 ]
ph2o_overload_diana()+265 PLSQL phase 2 (semantic analyzer) [partial hit for: ph2 ]
ph2stm()+12894 PLSQL phase 2 (semantic analyzer) statement(?)
ph2sms()+243 PLSQL phase 2 (semantic analyzer) process statements
ph2blo()+539 PLSQL phase 2 (semantic analyzer) idl node D_BLOCK, D_DECL
-- ctrl +c --
SQL> alter procedure xxx.ap_rec_statxxxx compile;
^Calter procedure xxx.ap_rec_statxxxx compile
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object xxx.AP_UPDATE_xxxx@db1xx
ORA-01013: user requested cancel of current operation
Solution:
开始以为是dblink问题，尝试半天最后发现drop procedure,重新创建就解决了。 仅记录未发现已知BUG.