比较“Cursor: pin S wait on X”等待期间和正常（基准）情况的 AWR 和 ADDM 报告。基准向我们展示了在问题和正常期间发生的典型“背景”下并发和活动情况，并且可能有助于识别出（例如）子游标版本数过高的 SQL。 建议半小时到一小时间隔运行 AWR 和 ADDM 来收集信息，收集信息命令如下所示：
1
2
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
有时为了匹配已知问题需要获取 system state dump 信息。例如，如果 AWR 中没有明显的候选 SQL，则需在 system state dump 中捕获 mutex bin 资源持有进程或等待进程以专注于潜在问题的分析。当进程发生“cursor: pin S wait on X”等待时，取得 System State Dump：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(a)Non-Rac
sqlplus"/ as sysdba"
oradebugsetmypid
oradebugunlimit
oradebugdumpsystemstate258
wait90seconds
oradebugdumpsystemstate258
wait90seconds
oradebugdumpsystemstate258
quit
(b)RAC
$sqlplus'/ as sysdba'
oradebugsetmypid
oradebugunlimit
oradebugsetinstall
oradebug-gallhanganalyze4
oradebug-galldumpsystemstate258
quit
Errorstack：获取进程信息的另一种方法是使用 Errorstack。假设可以识别一个阻塞源，可以获取 Errorstack，Errorstack 提供的信息与 System State 的信息大致相同，但 trace 量可以很大程度的减少。一旦找到阻塞源的 ospid，就可以生成一个 Errorstack：
1
2
3
4
5
6
7
8
$sqlplus
SQL>oradebugsetospid<p.spidfromabove>
oradebugdumperrorstack3
<<wait1min>>
oradebugdumperrorstack3
<<wait1min>>
oradebugdumperrorstack3
exit
尤其是通过 trace 中的 Stack 信息可用于匹配已知问题。
Systemstate 和 errorstack 有可能不是很易读，此时，需要提交 SR 来解析 trace 文件。
运行 system state dump 并不总是可行的， 因此也可以参阅以下文档查找阻塞进程：
Document 786507.1 How to Determine the Blocking Session for Event: 'cursor: pin S wait on X'
同时，请记住如果 shared pool 刷新了的话，SQL 也会做硬解析。这也会引发 Mutex 的等待。因此，一旦硬解析，请确保 SQL 在内存中，并监视互斥等待是否得到缓解。
高子游标版本数也会引发“cursor: pin S wait on X”等待。 使用 V$SQL_SHARED_CURSOR 视图确认高子游标版本数产生的潜在原因：
Document 438755.1 Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value Document 296377.1 Troubleshooting: High Version Count Issues
有一些值得注意的 BUG，其中高版本数是一个因素：
Document 10157392.8 Bug 10157392 - High version counts for SQL with binds (BIND_MISMATCH) Document 9689310.8 Bug 9689310 - Excessive child cursors / high VERSION_COUNT / OERI:17059 due to bind mismatch
如果从 11g 升级到 12c 以后，发生了“cursor: pin S wait on X”等待，请查阅下面的文档：
Document 1949691.1 High wait time for 'cursor: pin S wait on X' After Upgrade Document 18018515.8 High CPU in qctHasFakeBind (can cause 'cursor: pin S wait on X' waits) Document 16448569.8 PQ hang/deadlock possible - "cursor: pin S wait on X" waits Bug 22500027 : PX SLAVE BLOCKS 144 SESSIONS DUE ROWCACHE LOCK DURING DATAFILE RESIZE OPS Document 2006145.1 High Waits for 'cursor: pin S wait on X' due to Dynamic Sampling Against Parallel Queries
NOTE:2006145.1 - High Waits for 'cursor: pin S wait on X' due to Dynamic Sampling Against Parallel Queries NOTE:1949691.1 - High Wait Time for 'cursor: pin S wait on X' Event After Upgrade NOTE:2096561.1 - ORA-04031 Memory Errors with Argument KGLH0^ NOTE:459694.1 - Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes NOTE:438755.1 - High SQL Version Counts - Script to determine reason(s) NOTE:10157392.8 - Bug 10157392 - High version counts for SQL with binds (BIND_MISMATCH) NOTE:1291879.1 - Oracle Database Patch Set Update 11.2.0.2.2 Known Issues NOTE:1298015.1 - WAITEVENT: "cursor: pin S wait on X" Reference Note NOTE:9689310.8 - Bug 9689310 - Excessive child cursors / high VERSION_COUNT / ORA-600 [17059] due to bind mismatch BUG:10157392 - HIGH VERSION COUNT OF RECURSIVE SQL THOUGH THE FIX FOR BUG 10086843 WAS APPLIED NOTE:786507.1 - How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' NOTE:2119923.1 - Bug 20370037 - Shared Pool from KGLH0 constantly growing causing ORA-04031 and Latch contention BUG:9689310 - SPORADIC BUNCHES OF ORA-600 [17059] NOTE:1377446.1 - * Troubleshooting Performance Issues NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool NOTE:296377.1 - Troubleshooting: High Version Count Issues
