合 解析失败导致大量的Library Cache Lock锁Library Cache Lock on Namespace SQL AREA BUILD(82) Caused by Failed Parse (Doc ID 2294281.1)

简介

Library Cache Lock on Namespace SQL AREA BUILD(82) Caused by Failed Parse (Doc ID 2294281.1)

存储过程解析错误或某频繁SQL语句解析错误导致Library Cache Lock on Namespace SQL AREA BUILD问题

SYMPTOMS

• AWR report shows high library cache lock and cursor: pin S wait on X:

  Top 5 Timed Foreground Events Event Waits Time(s) Avg wait (ms) % DB time Wait Class library cache lock 142,619 2,436 17 37.18 Concurrency cursor: pin S wait on X 155,258 2,303 15 35.14 Concurrency kksfbc child completion 22,404 1,141 51 17.41 Other

  1 2 3 4 5 6

  Top 5 Timed Foreground Events   Event Waits Time(s) Avg wait (ms) % DB time Wait Class library cache lock 142,619 2,436 17 37.18 Concurrency cursor: pin S wait on X 155,258 2,303 15 35.14 Concurrency kksfbc child completion 22,404 1,141 51 17.41 Other

• ASH report indicates the problem is mainly on a procedure:

  Top SQL with Top Events SQL ID Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text gbj1tkbcwcyv5 0 99.21 cursor: pin S wait on X 40.10 ** Row Source Not Available ** 40.10 begin xxxx(... gbj1tkbcwcyv5 0 99.21 library cache lock 34.04 ** Row Source Not Available ** 34.04 begin xxxx(... gbj1tkbcwcyv5 0 99.21 kksfbc child completion 16.73 ** Row Source Not Available ** 16.73 begin xxxx(..

  1 2 3 4 5

  Top SQL with Top Events SQL ID Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text gbj1tkbcwcyv5 0 99.21 cursor: pin S wait on X 40.10 ** Row Source Not Available ** 40.10 begin xxxx(... gbj1tkbcwcyv5 0 99.21 library cache lock 34.04 ** Row Source Not Available ** 34.04 begin xxxx(... gbj1tkbcwcyv5 0 99.21 kksfbc child completion 16.73 ** Row Source Not Available ** 16.73 begin xxxx(..

• Systemstate dump shows that the library cache contention happened on SQL AREA BUILD namespace which is relevant to parsing:

  PROCESS 154: ---------------------------------------- SO: 0x734bab158, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x734bab158, name=process, file=ksu.h LINE:12616, pg=0 (process) Oracle pid:154, ser:6, calls cur/top: 0x6bf2ee508/0x6bf2ee508 flags : (0x0) - <...snipped...> O/S info: user: xxxx, term: UNKNOWN, ospid: 1636 OSD pid info: Unix process pid: 1636, image: oracle@xxxx Short stack dump: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-ksfwaitctx()+14<-kgxWait()+700<-kgxSharedExamine()+425<-kxsGetRuntimeLock()+240<-kkscsCheckCursor()+556<-kkscsSearchChildList()+1262<-kksfbc()+12105<-kkspsc0()+1173<-kksParseCursor()+116<-opiosq0()+1588<-kpooprx()+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+253<-_start()+36 ---------------------------------------- SO: 0x729475520, type: 4, owner: 0x734bab158, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x734bab158, name=session, file=ksu.h LINE:12624, pg=0 (session) sid: 4220 ser: 25 trans: (nil), creator: 0x734bab158 flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC DID: , short-term DID: txn branch: (nil) oct: 47, prv: 0, sql: 0x74f6bc308, psql: 0x7476122d8, user: 76/xxxxx ksuxds FALSE at location: 0 service name: xxxxxx client details: O/S info: user: xxxxxx, term: xxxxxx, ospid: 6152:13936 machine: xxxx\xxxxprogram: xxxx.exe application name: xxxx.exe, hash value=1240902255 Current Wait Stack: 0: waiting for 'library cache lock' handle address=0x743bb3e98, lock address=0x743baf088, 100*mode+namespace=0x520002 ===========> namespace is 0x52 (dec:82), sql area build wait_id=9268 seq_num=9913 snap_id=1 wait times: snap=0.000729 sec, exc=0.000729 sec, total=0.000729 sec wait times: max=infinite, heur=0.000729 sec wait counts: calls=1 os=1 in_wait=1 iflags=0x5a2

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

  PROCESS 154: ---------------------------------------- SO: 0x734bab158, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x734bab158, name=process, file=ksu.h LINE:12616, pg=0 (process) Oracle pid:154, ser:6, calls cur/top: 0x6bf2ee508/0x6bf2ee508 flags : (0x0) - <...snipped...> O/S info: user: xxxx, term: UNKNOWN, ospid: 1636 OSD pid info: Unix process pid: 1636, image: oracle@xxxx Short stack dump: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-ksfwaitctx()+14<-kgxWait()+700<-kgxSharedExamine()+425<-kxsGetRuntimeLock()+240<-kkscsCheckCursor()+556<-kkscsSearchChildList()+1262<-kksfbc()+12105<-kkspsc0()+1173<-kksParseCursor()+116<-opiosq0()+1588<-kpooprx()+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+253<-_start()+36   ---------------------------------------- SO: 0x729475520, type: 4, owner: 0x734bab158, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x734bab158, name=session, file=ksu.h LINE:12624, pg=0 (session) sid: 4220 ser: 25 trans: (nil), creator: 0x734bab158 flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC DID: , short-term DID: txn branch: (nil) oct: 47, prv: 0, sql: 0x74f6bc308, psql: 0x7476122d8, user: 76/xxxxx ksuxds FALSE at location: 0 service name: xxxxxx client details: O/S info: user: xxxxxx, term: xxxxxx, ospid: 6152:13936 machine: xxxx\xxxxprogram: xxxx.exe application name: xxxx.exe, hash value=1240902255 Current Wait Stack: 0: waiting for 'library cache lock' handle address=0x743bb3e98, lock address=0x743baf088, 100*mode+namespace=0x520002 ===========> namespace is 0x52 (dec:82), sql area build wait_id=9268 seq_num=9913 snap_id=1 wait times: snap=0.000729 sec, exc=0.000729 sec, total=0.000729 sec wait times: max=infinite, heur=0.000729 sec wait counts: calls=1 os=1 in_wait=1 iflags=0x5a2

CHANGES

CAUSE

This issue is caused by failed parse.

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。