# 描述 出现异常等待  # cursor:mutex X 简介 某个进程申请以独占(exclusive)模式获取 cursor mutex 时产生的等。 本质上是一些会话长时间持有互斥锁，以至于其他会话不得不等待资源。如果在保护库缓存结构的latches/mutexes 上发生争用，这意味着解析面临压力。解析 SQL 需要更长的时间，因为它无法获得所需的资源。这会延迟其他操作，并且通常会降低系统的速度。 触发该事件的情况有： 1. 在一个父游标下面创建一个新的子游标 2. 捕获 SQL 中的绑定变量 3. 更新或构建 SQL 统计信息 V$SQLSTAT 4. 频繁硬解析 5. high version count 6. cursor 失效 # 分析 根据如上可能事项进行逐步排查，发现如下情况  在进一步排查，确定该sql多版本的原因 ```sql select REASON from v$sql_shared_cursor where sql_id='5v8prdhng15c8'; ```  此处可以看出，是绑定变量不匹配造成的，此处可以分为两个方向，一个是查询写入磁盘的历史数据——dba_hist_sqlbind和内存中的数据——v$sql_bind_capture查绑定类型、长度等信息，确认绑定值不匹配的原因 ```sql SELECT NAME, COUNT(*) FROM v$sql_bind_capture WHERE SQL_ID = '5v8prdhng15c8' GROUP BY NAME HAVING count(*)>1 ORDER BY NAME; ```  ```sql SELECT NAME, VALUE_STRING, DATATYPE_STRING, COUNT(*) FROM v$sql_bind_capture WHERE SQL_ID = '5v8prdhng15c8' AND NAME = ':999' GROUP BY NAME, VALUE_STRING, DATATYPE_STRING; ``` 确定不匹配的情况  我们挑了一个变量查询具体信息，很明显，多个字段变量长度不同导致 SQL 的多版本的情况发生 # 总结 传入变量共计999个，有 550 个变量的传入参数设置的类型长度的变化版本为2~3个。有一个不同则会生成一个执行计划版本 建议：使用如下方式来减少 sql 版本数量 1. 拆分 sql 为多个 sql 在一个事务中执行(官方建议一条 sql 变量数不要超过 40 个) 2. 传入的变量类型以及长度需固定 # 参考 《Troubleshooting cursor: mutex X Due to High Version Count on the Insert Statements with Binds using JDBC (Doc ID 1469726.1) 》 > The insert statements were coming into the database from the applications, which were using JDBC thin driver connection to connect to the database. Details for the JDBC driver are below: > > ``` > Oracle JDBC Driver version - "11.1.0.6.0-Production+" > JDK Version- JDK6 > ``` > > The couple of insert statements in question were getting fired from the JDBC connection, and all the bind variables were passed through JDBC driver. Since the inserts statements have huge numbers of binds (~40) , some of the binds were passed as a nulls for different inserts statements. > > In JDBC, the default datatype for nulls variables is defined as varchar2(32). This means if a value for any bind variable is passed as null , the datatype for that bind variable explictly gets set to varchar2 irrespective of the datatype of the column datatype for which it holds the data for.On the other hand, when the bind variable holds not null values, then it uses the datatype of the column. > > One particular column which was defined as char(1) during insert statement on the table was using bind variables. Some insert statements were passing null value for this column and some were passing as not nulls. So the datatype of the particular binds keep switching between varchar and char. Hence, the existing cursor couldn't be shared due to datatype mismatch of the binds. > > Similarly, there were several such columns in that insert statements which can get nulls or not nulls values. This caused cursor sharing failure due to datatype mismatch. This resulted in thousands of versions for this particular insert statement.