oracle 12.2.0.1 crs call completion cpu使用率100% library cache lock
客戶的一套資料看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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 10049 event之library cache lockOracle
- oracle異常:library cache lockOracle
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- LIBRARY CACHE LOCK 等待事件事件
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- [Oracle]--Library cache lock 故障解決一例Oracle
- latch:library cache lock等待事件事件
- 定位Library Cache pin,Library Cache lock等待的解決方法
- zt_如何平面解決library cache lock和library cache pin
- enq:Library cache lock/pin等待事件ENQ事件
- library cache lock 阻塞程式查詢
- Library cache lock/pin詳解(轉)
- 常用定位library cache lock的方法
- LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUNDAIBloC
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- Oracle Library cacheOracle
- 短連線 引起的 library cache lock
- 查詢library cache lock的源頭
- 【ASM_ORACLE】Library Cache最佳化篇(二)Library cache load lock的概念和解決辦法ASMOracle
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 一次library cache lock 問題分析
- library cache pin/lock的解決辦法
- zt_library cache pin和lock等待分析
- 尋找 library cache lock 等待事件的session事件Session
- 'library cache lock'等待事件的處理方法事件
- 俺也談談 library cache lock 等待事件事件
- Oracle效能優化-資料庫CPU使用率100%Oracle優化資料庫
- 深入理解shared pool共享池之library cache的library cache lock系列四
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- library cache lock\pin的查詢與處理