1、你这个库硬解析较高，和session_cached_cursors没关系(66.3/s)
2、主要blocking_session是220，等待事件为cursor: pin S wait on X

从ash参数看：

SQL> SELECT decode(trunc(&&P3/4294967296),
2 0,trunc(&&P3/65536),
3 trunc(&&P3/4294967296)) LOCATION_ID
4 FROM dual;
Enter value for p3: 21474836480
old 1: SELECT decode(trunc(&&P3/4294967296),
new 1: SELECT decode(trunc(21474836480/4294967296),
old 2: 0,trunc(&&P3/65536),
new 2: 0,trunc(21474836480/65536),
old 3: trunc(&&P3/4294967296)) LOCATION_ID
new 3: trunc(21474836480/4294967296)) LOCATION_ID

LOCATION_ID

      5
复制

SQL> SELECT MUTEX_TYPE, LOCATION
2 FROM x$mutex_sleep
3 WHERE mutex_type like ‘Cursor Pin%’
4 and location_id=&&LOCATION_ID;
Enter value for location_id: 5
old 4: and location_id=&&LOCATION_ID
new 4: and location_id=5

MUTEX_TYPE

LOCATION

Cursor Pin
kkslce [KKSCHLPIN2]

SQL> SELECT decode(trunc(&&P2/4294967296),
2 0,trunc(&&P2/65536),
3 trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
4 FROM dual;
Enter value for p2: 944892805120
old 1: SELECT decode(trunc(&&P2/4294967296),
new 1: SELECT decode(trunc(944892805120/4294967296),
old 2: 0,trunc(&&P2/65536),
new 2: 0,trunc(944892805120/65536),
old 3: trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
new 3: trunc(944892805120/4294967296)) SID_HOLDING_MUTEX

SID_HOLDING_MUTEX

          220
复制

大量session是220阻塞的，所以要通过ash裸数据看220会话的cursor: pin S wait on X参数是什么？如果也是220，则可能是死锁了！

3、等待row cache mutex的参数
cachid：10，dc_users

kqrpre - kernel query dictionary/row cache read a parent cache object
kqrGetClusterLock - kernel query dictionary/row cache get a row cache lock in cluster mode (cluster version of kqrget)
kqreqd - kernel query dictionary/row cache enqueue delete

这个等待可能是cursor: pin S wait on X阻塞的。

建议：
1、通过ash裸数据分析最终阻塞源
2、降低sql硬解析数量。

Bug 30623138

谢谢各位大佬解答

有得解释是，21C以后版本解决游标问题，或者更新最新补丁试试；

https://www.modb.pro/doc/21113