Environment info: Oracle DB 19.12 on RHEL 8.9
In one of my critical production databases, there was sudden performance drop for the application for around 14 minutes which caused some business impact.
During this performance drop, from ASH analytics, I can see that there are around 300+ sessions (and briefly around 650 sessions) waiting with "cursor: pin S wait on X" as the major wait event followed by "library cache lock"
*To have a magnified view of the below image, for Chrome/Firefox, right click on the image and choose 'Open image in new tab'
Unfortunately, I was informed of this issue several hours after the occurrence of this issue (the following morning).
So, the queries like
"select p2raw from v$session where event = 'cursor: pin S wait on X';" mentioned in MOS Doc 786507.1 is not useful as the processes have moved on and hence the blockers cannot be determined.
So, I ran the following query on dba_hist_active_sess_history
Query1 :
select event, sql_id,in_parse,in_sql_execution ,P1, P1TEXT, P2, P2TEXT, P3, P3TEXT, blocking_session, blocking_session_serial#, blocking_inst_id, count(*) cnt
from dba_hist_active_sess_history
where event = 'cursor: pin S wait on X'
and sample_time > to_date('12-NOV-2025 22:20:00','DD-MON-YYYY HH24:MI:SS')
and sample_time < to_date('12-NOV-2025 22:34:00','DD-MON-YYYY HH24:MI:SS')
group by event,sql_id, in_parse,in_sql_execution,P1, P1TEXT, P2, P2TEXT, P3, P3TEXT ,blocking_session, blocking_session_serial#, blocking_inst_id having count(*) > 1
order by cnt desc
EVENT SQL_ID I I P1 P1TEX P2 P2TEXT P3 P3TEXT BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID CNT
----------------------- ------------- - - ---------- ----- ---------- ------- ---------- ------- ---------------- ------------------------ ---------------- ----------
cursor: pin S wait on X a4f78zjub0k5b N Y 2517179718 idn 9.3716E+12 value 2.1475E+10 where 2182 17241 1 14742
cursor: pin S wait on X 7xtn6p31a69hu N Y 3265472026 idn 2.4163E+13 value 2.1475E+10 where 5626 64692 1 52
cursor: pin S wait on X 0tsg93hq95yy3 N Y 747830211 idn 1.3155E+13 value 2.1475E+10 where 3063 14700 1 52
cursor: pin S wait on X 7xtn6p31a69hu Y N 3265472026 idn 2.4163E+13 value 2.1475E+10 where 5626 64692 1 51
If I understand correctly, SQL ID a4f78zjub0k5b is blocked during this spike (I could be wrong). SQL text for a4f78zjub0k5b is the following SQL. Its an UPDATE.
UPDATE rtb_dtl SET RoutingStatus = :v0 , LastModified = :v1 WHERE rtbID = :v2
SQL> alter session set container = P_RTB_P;
Session altered.
select sql_text,
2 is_shareable,
3 is_bind_sensitive,
4 is_bind_aware
5 from v$sql where sql_id = 'a4f78zjub0k5b';
SQL_TEXT I I I
----------------------------------------------------------------------------------------------- - - -
UPDATE rtb_dtl SET RoutingStatus = :v0 , LastModified = :v1 WHERE rtbID = :v2 Y Y N
UPDATE rtb_dtl SET RoutingStatus = :v0 , LastModified = :v1 WHERE rtbID = :v2 Y Y N
-- RTB_DTL is regular unpartitioned table (with no LOBs) and is 26 GB in size
SQL> select count(*) from RTB_DTL;
COUNT(*)
----------
80617747
SQL> select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT from v$session where event ='cursor: pin S wait on X';
no rows selected
-- Trying to find blocking SID and Serial# listed in Query1 output in v$active_session_history
SELECT sql_id
2 FROM v$active_session_history
3 WHERE session_id = 2182
4 AND SESSION_SERIAL# = 17241
5 ORDER BY sample_id;
no rows selected
-- Trying to find blocking SID and Serial# listed in Query1 output in dba_hist_active_sess_history
Query on dba_hist_active_sess_history does show some SQL IDs for the blocking SID and Serial# listed in Query1 output. But, I don't know which one is the blocker
SELECT distinct sql_id
FROM dba_hist_active_sess_history
WHERE session_id = 2182
AND SESSION_SERIAL# = 17241;
SQL_ID
-------------
g94p3gf327qb7
aa4jh8zjwhccr
fmbtj909bsr66
53ajjyka4tpcs
a4f78zjub0k5b
gh68f4f9kfrj3
ctkghxd7k72x9
7zsc70fnvfcud
7157kvc94ufvs
10 rows selected.
Two other things I noticed:
1. I noticed that during this spike is that there were few SELECT queries running where values are passed as literals.
But, application team says that is nothing new and similar SQLs have been executed for more than 15 years.
2. I see that Auto gather stats job is running during this spike
"call dbms_stats.gather_database_stats_job_proc ( )"
Is there way to find the blocking SQL which is causing "cursor: pin S wait on X" and "library cache lock" waits which happened several hours back ?
Any other suggestions on the potential root cause of these "cursor: pin S wait on X" and "library cache lock" waits ?
-- At the PDB level
CON_NAME
------------------------------
P_RTB_P
SQL>
SQL> show parameter cursor_sharing
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------
cursor_sharing string EXACT
-- At the root container level
SQL> show parameter sga_target
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- -----------
sga_target big integer 72G
SQL> show parameter shared_pool
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
shared_pool_reserved_size big integer 512M
shared_pool_size big integer 0
AWR report for the 1 hour period when the spike occurred is attached with some data masking/obfuscation.
awr_report.html.gz