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

哎呀我的天吶發表於2018-01-20

                   客戶的一套資料看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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章