High waits on 'row cache lock' and 'cursor: pin S wait on X' with the blocker waiting for 'Disk file operations I/O' on a password file (Doc ID 2910144.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

1. High concurrency waits on 'row cache lock' and 'cursor: pin S wait on X' while the blocker is waiting for 'Disk file operations I/O' on a password file.

2. AWR report

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Avg Wait % DB time Wait Class
row cache lock 1,212 112.5K 92.86 s 43.4 Concurrency
cursor: pin S wait on X 1,343 87K 64.75 s 33.6 Concurrency
DB CPU 44K 17.0

3. The blocking process was executing "ALTER USER SYSTEM PROFILE ...."

*** 2022-09-06T16:56:45.149423+01:00 (CDB$ROOT(1))
PROCESS 1208:
----------------------------------------
SO: 0x2b1ff25c60, type: process (2), map: 0x2b41b65b68
state: LIVE (0x4532), flags: 0x1
.............
O/S info: user: grid, term: UNKNOWN, ospid: 96192
OSD pid info:
Short stack dump: ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+223<-__sighandler()<-read()+43<-_ZN11OracleHooks8readHookEiPvm()+78<-0x00007FFFEC01D880<-kclgcbbacbk()+8192<-0x6068C03300000008
......................

----------------------------------------
SO: 0x2b1ffd8cd0, type: session (4), map: 0x2aa2058dd8
state: LIVE (0x4532), flags: 0x1
owner: 0x2b1ff25c60, proc: 0x2b1ff25c60
link: 0x2b1ffd8cf0[0x2b1ef8e7a8, 0x2b1c5e5ee8]
child list count: 408, link: 0x2b1ffd8d40[0x2a5f93daf8, 0x2a5f97dd28]
conid: 1, conuid: 1, SGA version=(1,0), pg: 0
SOC: 0x2aa2058dd8, type: session (4), map: 0x2b1ffd8cd0
state: LIVE (0x99fc), flags: INIT (0x1)
(session) sid: 936 ser: 43622 trans: 0x2a485ed770, creator: 0x2b41b65b68
flags: (0x100041) USR/- flags2: (0x48009) -/DDLT2/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
DID: 0002-04B8-000000C20002-04B8-000000C1, short-term DID:
txn branch: (nil)
con_id/con_uid/con_name: 1/1/CDB$ROOT
con_logonuid: 1 con_logonid: 1
con_scuid: 1 con_scid: 1
edition#: 134 user#/name: 79/C##DB_ADMIN
oct: 43, prv: 0, sql: 0x378c8a5f0, psql: 0x40c278490
stats: 0x1d633f4298, PX stats: 0x12e67184
service name: CPIAMO01N
client details:
O/S info: user: SVC-CLI-OAM, term: GBRPSM020007576, ospid: 17664:1500
machine: program: OracleAccountMaintenance.exe
application name: OracleAccountMaintenance.exe, hash value=1621719125
Current Wait Stack:
0: waiting for 'Disk file operations I/O'                                                         <<<########
FileOperation=0x2, fileno=0x0, filetype=0x1c                                               <<<######## Password file
wait_id=4006 seq_num=4697 snap_id=8
wait times: snap=1002 min 46 sec, exc=1002 min 46 sec, total=1002 min 46 sec
wait times: max=infinite, heur=1002 min 46 sec
wait counts: calls=0 os=0
in_wait=1 iflags=0x15a0
There are 74 sessions blocked by this session.                                            <<<########
Dumping one waiter:
inst: 2, sid: 3880, ser: 38891
wait event: 'row cache lock'
p1: 'cache id'=0xa
p2: 'mode'=0x5
p3: 'request'=0x3
...................

LibraryObjectLock: Address=0x359a8afa0 Handle=0x378c8a5f0 Mode=N
CanBeBrokenCount=1 Incarnation=1 ExecutionCount=1

User=0x2aa2058dd8 Session=0x2aa2058dd8 ReferenceCount=1
Flags=CNB/[0001] SavepointNum=37a5 Time=09/06/2022 00:13:58
LibraryHandle: Address=0x378c8a5f0 Hash=f4296787 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALTER USER SYSTEM PROFILE C##DBA_ORA_NONPER_NONEXP_PROFILE                                                                                                               <<<#########

...........

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.