ITPub博客

首页 > 数据库 > Oracle > oracle 12.2.0.1 crs call completion cpu使用率100% library cache lock

oracle 12.2.0.1 crs call completion cpu使用率100% library cache lock

原创 Oracle 作者:哎呀我的天呐 时间:2018-01-20 21:36:31 0 删除 编辑

                   客户的一套数据看12.2.0.1 客户反映连接不上数据库,处理不了更新操作,后处理问题

发现2节点的cpu使用率100%,并且都被crsctl.bin process 占用。

查看数据库等待事件,存在大量的library cache lock

blocking_session为3626,并且等待事件是crs call completion

之前有一个客户遇到类似的问题,是3个节点的第三节点,这次是两个节点的第2节点,不知道是否有巧合,都是instance_number最大的节点。

下面是抛出的堆栈信息:
Short stack dump: 
ksedsts <- ksdxfstk <- ksdxcb <- sspuser <- __sighandler 
<- __connect <- sgipcnClscIpcConnect <- sgipcnConnect <- gipcmodNetworkProcessConnect <- gipcmodNetworkProcessEndpoint 
<- gipcmodNetworkNonBlockComplete <- sgipcnCompletion <- sgipcwWaitHelper <- sgipcwWait <- gipcWaitOsd 
<- gipcInternalWait <- gipcWaitF <- prom_waitforconnect <- prom_connect_ext <- prom_connect 
<- proac_con_init <- proac_init <- proa_init <- procr_init_ext2 <- procr_init_ext 
<- clse_get_auth_loc <- clscrsconGipcConnect <- clscrsconInvokeCmd <- clscrscmd_doAPI_internal <- clscrscmd_get_crsd_version 
<- clscrscmd_doAPI3 <- clscrscmd_doAPI2 <- clscrs_stat2 <- clsrapii_stat_reg_attrs <- clsrapii_resattr_by_filter 
<- clsrapii_resattr <- clsr_get_db <- clsr_get_db_pwfile <- kjha_get_db_pwfile <- kzsrfpf 
<- kzsrlav <- kzsrGetPWFileFormat <- kzsrUpdateUserInfo <- kziaUpdateAcctStat <- kzia_password_lifecycle 
<- kziavua <- kpolnb <- kpoauth <- opiodr <- ttcpip 
<- opitsk <- opiino <- opiodr <- opidrv <- sou2o 
<- opimai_real <- ssthrdmain <- main 


------------------------------------------------------------------------------- 
Process diagnostic dump actual duration=5.902000 sec 
(max dump time=30.000000 sec) 


............ 


SO: 0x4729e9db0, type: 4, owner: 0x4b2163b68, flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3 
proc=0x4b2163b68, name=session, file=ksu.h LINE:15737, pg=0, conuid=0 
(session) sid: 3626 ser: 10708 trans: 0x41f900068, creator: 0x4b2163b68 
flags: (0x41) USR/- flags2: (0x40009) -/-/INC 
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/- 
0000-0000-000000000002-028B-0000727D DID: , short-term DID: 
txn branch: (nil) 
edition#: 0 user#/name: 123/ZHDD_MEM 
oct: 0, prv: 0, sql: (nil), psql: 0x27ec55258 
stats: 0x1c2f3ebe0, PX stats: 0x1101de44 
service name: ZDMEMDB 
client details: 
O/S info: user: grid, term: , ospid: 95791 
machine: zd-hdd-db2 program: oracle@zd-hdd-db2 (TNS V1-V3) 
application name: xxxxx (TNS V1-V3), hash value=3351328086 
Current Wait Stack: 
0: waiting for 'CRS call completion' 
clsrrestype=0xe, kjha_action=0x6, =0x0 
wait_id=9 seq_num=10 snap_id=1 
wait times: snap=14 min 56 sec, exc=14 min 56 sec, total=14 min 56 sec 
wait times: max=infinite, heur=14 min 56 sec 
wait counts: calls=0 os=0 
in_wait=1 iflags=0x5a0 
There are 3128 sessions blocked by this session. 
Dumping one waiter: 
inst: 2, sid: 5592, ser: 37199 
wait event: 'library cache lock' 
p1: 'handle address'=0x2bfb1dbb0 
p2: 'lock address'=0x26d7a70d0 
p3: '100*mode+namespace'=0x4f0002 
row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0 
min_blocked_time: 890 secs, waiter_cache_ver: 41547 
Wait State: 
fixed_waits=0 flags=0x22 boundary=(nil)/-1 
Session Wait History: 
elapsed time of 0.004458 sec since current wait 
0: waited for 'library cache lock' 
handle address=0x2bfb1dbb0, lock address=0x129c375c0, 100*mode+namespace=0x4f0003 
wait_id=8 seq_num=9 snap_id=1 
wait times: snap=0.000622 sec, exc=0.000622 sec, total=0.000622 sec 
wait times: max=15 min 0 sec 
wait counts: calls=1 os=1 
occurred after 0.009263 sec of elapsed time 
1: waited for 'PGA memory operation' 
=0x10000, =0x1, =0x0 
wait_id=7 seq_num=8 snap_id=1 
wait times: snap=0.000008 sec, exc=0.000008 sec, total=0.000008 sec 
wait times: max=infinite 
wait counts: calls=0 os=0 
occurred after 0.001909 sec of elapsed time 
2: waited for 'PGA memory operation' 
=0x20000, =0x1, =0x0 
wait_id=6 seq_num=7 snap_id=1 
wait times: snap=0.000009 sec, exc=0.000009 sec, total=0.000009 sec 
wait times: max=infinite 
wait counts: calls=0 os=0 
occurred after 0.001348 sec of elapsed time 
3: waited for 'library cache load lock' 
object address=0x2bfb1dbb0, lock address=0x129c37938, 100*mask+namespace=0x4f0003 
wait_id=5 seq_num=6 snap_id=1 
wait times: snap=0.048842 sec, exc=0.048842 sec, total=0.048842 sec 
wait times: max=infinite 
wait counts: calls=1 os=1 
occurred after 0.000214 sec of elapsed time 
...... 


8: waited for 'PGA memory operation' 
=0x10000, =0x1, =0x0 
wait_id=0 seq_num=1 snap_id=1 
wait times: snap=0.000011 sec, exc=0.000011 sec, total=0.000011 sec 
wait times: max=infinite 
wait counts: calls=0 os=0 
occurred after 0.000000 sec of elapsed time 
....... 


4520B78E0 00000000 00000000 [........] 
Enqueue blocker waiting on 'CRS call completion' 

后开sr,给出的建议是先,先设置如下参数,绕过底层crs就不会在crs call completion,问sr设置有什么不好的地方,回答是go ahead。

KNOWLEDGE  
-----------------  
Keywords: CRS call completion  

-    : LONG WAIT ON CRS CALL COMPLETION  
-    : RCA FOR CRSD LOCK CAUSING SESSIONS TO ASM TO LOCK-UP  

WORKAROUND:  
-----------  
if setting SQL> alter system set "_notify_crs"=FALSE scope=spfile; skips the  
notification to CRS of the change and thus avoids the wait.  
restart database;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-2150354/,如需转载,请注明出处,否则将追究法律责任。

上一篇: in-memory
请登录后发表评论 登录
全部评论
从事Oracle/MySQL工作多年,Oracle OCM、MySQL OCP,擅长Oracle/MySQL SQL Tuning & DB Performance

注册时间:2014-10-30

  • 博文量
    293
  • 访问量
    2002245