来吧，cursor: pin S wait on X

缺少 cursor: pin S wait on X 等待事件的诊断，说明优化生涯是不完整的。

原理：
游标等待与某种形式的解析相关联。当一个会话试图在共享模式下获取互斥锁时，但另一个会话在同一个光标对象上独占持有互斥锁，它可能会等待此事件。通常，这个等待事件是一种症状，而不是原因。

首先检查共享池大小是否正确
其次看是否频繁的硬解析
然后看高版本计数是否高
最后排除掉BUG外，要看是否有解析错误情况，即ORA-923

共享池大小
col name for a40
set pages 100
select name,round(bytes/1024/1024)size_mb from v$sgainfo;

内存抖动

 SELECT COMPONENT,
       OPER_TYPE,
       round(a.INITIAL_SIZE/1024/1024) initial_size_mb,
       round(FINAL_SIZE/1024/1024) FINAL_SIZE_mb,
       round((FINAL_SIZE-INITIAL_SIZE)/1024/1024) resize_mb,
       to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') Started_time,
       to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') end_time
  FROM V$SGA_RESIZE_OPS a
 where start_time >= to_date('2021-12-20 00:00', 'yyyy-mm-dd hh24:mi')
 and start_time <= to_date('2021-12-23 23:59', 'yyyy-mm-dd hh24:mi')
  order by Started_time;

查找引起此事件的会话
select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT
     from v$session where event ='cursor: pin S wait on X';

相关sql
SELECT s.sid, t.sql_text
FROM v$session s, v$sql t
WHERE s.event LIKE '%cursor: pin S wait on X%'
AND t.sql_id = s.sql_id

How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (Doc ID 786507.1)

awr中看这里



如果硬解析高，这可能表明文字使用率高或引入了许多新的SQL。在这种情况下，
请考虑使用绑定变量代替文字。 
如果软解析的百分比很高，则检查应用程序以查看它是否正在使用可共享的 SQL。 
理想情况下，Execute to Parese 应该接近 100%

还有





高版本诊断请看 blog.chinaunix.net/uid-20687159-id-5853384.html

进一步深入诊断

单机
$ sqlplus "/ as sysdba"

oradebug setmypid
oradebug unlimit
oradebug dump systemstate 258
wait 90 seconds
oradebug dump systemstate 258
wait 90 seconds
oradebug dump systemstate 258
quit

RAC
$ sqlplus "/ as sysdba"

oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4
oradebug -g all dump systemstate 258
quit

获取error stack
$ sqlplus "/ as sysdba"

SQL> oradebug setospid <p.spid from above>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
exit

如果遇到解析错误，即大量ORA-923，可以诊断一下

ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';
等应用重复
ALTER SYSTEM SET EVENTS '10035 trace name context off';

好像19c数据库的告警日志中不设置此事件就有错误的sql。

剑走偏锋，获取大量硬解析的方法：

select INSTANCE_NUMBER,TOP_LEVEL_SQL_ID,SQL_ID,count(*) from dba_hist_active_sess_history
where IN_HARD_PARSE='Y' group by INSTANCE_NUMBER,TOP_LEVEL_SQL_ID,SQL_ID
having count(*)>100 order by count(*) desc;

应该找到原因了吧？