# 描述

出現異常等待

![](http://img.blog.itpub.net/blog/2021/07/28/8be992765cdeac08.png?x-oss-process=style/bb)

# cursor:mutex X 簡介

某個程式申請以獨佔(exclusive)模式獲取 cursor mutex 時產生的等。

本質上是一些會話長時間持有互斥鎖，以至於其他會話不得不等待資源。如果在保護庫快取結構的latches/mutexes 上發生爭用，這意味著解析面臨壓力。解析 SQL 需要更長的時間，因為它無法獲得所需的資源。這會延遲其他操作，並且通常會降低系統的速度。

觸發該事件的情況有：

1. 在一個父遊標下面建立一個新的子游標
2. 捕獲 SQL 中的繫結變數
3. 更新或構建 SQL 統計資訊 V$SQLSTAT
4. 頻繁硬解析
5. high version count
6. cursor 失效

# 分析

根據如上可能事項進行逐步排查，發現如下情況

![](http://img.blog.itpub.net/blog/2021/07/28/8daafd61b5f38bff.png?x-oss-process=style/bb)

在進一步排查，確定該sql多版本的原因

```sql
select REASON
  from v$sql_shared_cursor
 where sql_id='5v8prdhng15c8';
```

![](http://img.blog.itpub.net/blog/2021/07/28/a4cab6e2095c1383.png?x-oss-process=style/bb)

此處可以看出，是繫結變數不匹配造成的，此處可以分為兩個方向，一個是查詢寫入磁碟的歷史資料——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;
```

![](http://img.blog.itpub.net/blog/2021/07/28/dd4cffb3dc000a09.png?x-oss-process=style/bb)

```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;
```

確定不匹配的情況

![](http://img.blog.itpub.net/blog/2021/07/28/ccb4c0ede1bc1b84.png?x-oss-process=style/bb)

我們挑了一個變數查詢具體資訊，很明顯，多個欄位變數長度不同導致 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.

Oracle：cursor:mutex X

相關文章