ANBOB™

前段时间遇到的一个案例，当编译一个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.

打赏

微信扫一扫，打赏作者吧～