首页 > 代码库 > 如何诊断cursor pin s wait on x 系列二
如何诊断cursor pin s wait on x 系列二
如何分析诊断收集信息
1. 查看AWR 报告中high paring 和high version部分内容
具体查看这几个部分的内容:‘SQLordered by Parse Calls‘ or ‘SQL ordered by Version Count‘
SQL ordered by Parse Calls关于这部分中的sql 解析执行是否过高,或者能否减小来。
SQL ordered by Version Count关于这部分中的high version sql ,需要找出为啥他们不能共享,可以通过 v$sql_shared_cursor 视图查找原因
2. systemstats 和errorstack 的关注点
对于systemstats 和errorstack 时效性非常重要。需要在问题发生时刻进行dump ,否则过时采集的信息是无效的。在一个高速运行的系统中,那些holders and waiter 进程转瞬即逝。
根据AWR 的 load profile 部分内容可以初步判断出 系统 sql 解析情况:
如果看到hard parses 很多,表明系统可能没有使用绑定变量,或者有新的sql 上线。
对于high version counts 也会导致 cursor:ping S wait on X
使用V$SQL_SHARED_CURSOR可以查找出 sql 不能共享的原因
有些bug可能会导致 high version counts:
Document 1057392.8 Bug 10157392 - High version counts forSQL with binds (BIND_MISMATCH)
Document 9689310.8 Bug 9689310 - Excessive child cursors /high VERSION_COUNT / OERI:17059 due to bind mismatch
Bug 可能会导致 cursor pin s wait on x :
NB | Bug | Fixed | Description |
5650841 | Hang / deadlock from ANALYZE of cluster index | ||
16191248 | 12.1.0.1.1, 12.1.0.2, 12.2.0.0 | Hang from concurrent drop of on-commit materialized views or using DBMS_REDEFINITION | |
14295250 | 11.2.0.4, 12.1.0.1 | Long parse time for large query with many nested views due to much time in epxression analysis code | |
14191508 | 11.2.0.3.8, 11.2.0.3.BP16, 11.2.0.4, 12.1.0.1 | Slow row cache load due to SEG$ and INDSUBPART$ queries | |
14176247 | 11.2.0.4, 12.1.0.1 | Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE) | |
18292893 | 12.1.0.2, 12.2.0.0 | Jobs don‘t execute per schedule with a large number of PDBs | |
18018515 | 12.2.0.0 | High CPU in qctHasFakeBind (can cause ‘cursor: pin S wait on X‘ waits) | |
16448569 | 11.2.0.4, 12.1.0.2, 12.2.0.0 | PQ hang/deadlock possible - "cursor: pin S wait on X" waits | |
16400122 | 12.2.0.0 | Spikes in library cache mutex contention for SQL using SQL Plan Baseline | |
15850031 | 11.2.0.4, 12.2.0.0 | Rare instance hang: deadlock between ‘row cache lock‘ and ‘cursor: pin S wait for X‘ | |
14469756 | 12.2.0.0 | Partition pruning causes delay in TBL$OR$IDX$PART$NUM | |
14302813 | 11.2.0.4, 12.2.0.0 | QC blocked / parse hang for parallel DML executed from remote stored procedure | |
14029891 | 11.2.0.4, 12.1.0.1 | mutex deadlock having SQL baselines on recursive dictionary cursor | |
11927619 | 11.2.0.1.BP11, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1 | DBMS_STATS slow on interval composite partitions | |
11855965 | 11.2.0.3, 12.1.0.1 | Truncate partition takes long time doing recursive delete on MLOG$ | |
10213073 | 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.1 | CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects | |
10171273 | 11.2.0.2.8, 11.2.0.2.BP08, 11.2.0.3, 12.1.0.1 | Long parse time with non-equi subpartitioning under interval partitioning | |
9944129 | 11.2.0.1.BP12, 11.2.0.2, 12.1.0.1 | SQL not shared due to INST_DRTLD_MISMATCH with global transaction | |
9935787 | 11.2.0.3, 12.1.0.1 | Long parse time for large inlists - can cause ‘cursor: pin S wait on X‘ waits | |
9694101 | 10.2.0.5.7, 11.2.0.2, 12.1.0.1 | Hang / deadlock between "cursor: pin S wait on X" and "library cache lock" involving dictionary objects | |
9499302 | 10.2.0.5.5, 11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2, 12.1.0.1 | Improve concurrent mutex request handling | |
9472669 | 11.2.0.1.BP12, 11.2.0.2, 12.1.0.1 | ‘cursor: pin S wait on X‘ waits for invalid SQL over DB link | |
8508078 | 11.2.0.2, 12.1.0.1 | Contention from many concurrent bad SQLs - superseded | |
12432089 | 11.2.0.3 | library cache lock/cursor: pin S wait on X with parallel partition stats gathering | |
8441239 | 11.2.0.1 | Library cache lock waits if long running TRUNCATE in progress | |
8348464 | 11.1.0.7.2, 11.2.0.1 | CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects | |
7234778 | 11.2.0.1 | Unnecessary "cursor: pin S wait on X" waits | |
5485914 | 10.2.0.4 | Mutex self deadlock on explain / trace of remote mapped SQL | |
6143420 | 10.2.0.5, 11.1.0.6 | Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X" | |
6011045 | 10.2.0.5.5 | DBMS_STATS causes deadlock between ‘cursor: pin S wait on X‘ and ‘library cache lock‘ | |
7462072 | 10.2.0.4.3, 10.2.0.5 | Unnecessary "cursor: pin S wait on X" waits | |
5983020 | 10.2.0.4 | MMON deadlock with user session executing ALTER USER | |
7226463 | 10.2.0.5 | EXECUTE IMMEDIATE no releasing mutex or library cache pin | |
+ | 5907779 | 10.2.0.4 | Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS) |
如何诊断cursor pin s wait on x 系列二