升級 12.2 造成 cursor: mutex X 之案例

Oracle 版本: 12.2 ， RAC

OS 版本: Linux 7.6

問題描述:

資料庫從 11.2.0.3 升級至 12.2 之後發生效能問題，從 AWR Report 顯示嚴重的 cursor: mutex X 等待 :

問題分析:

cursor 等待事件表示發生在 SQL Parsing 階段的等待事件， cursor: mutex X 代表 SQL 於 shared pool 請求一個 exclusive lock (X 鎖) 的等待，進一步從 AWR Report 中的 Mutex Sleep Summary 發現此 mutex 是嘗試取得 Parent Cursor 的 lock :

而這邊為了取得 Parent Cursor 的 lock 是為了產生新的 Child Cursor 。

在此我們先暫停分析，來說明一下 Parent Cursor 與 Child Cursor 之間的關係。

一句全新的 SQL 執行後，會於 shared pool 裡面產生這句 SQL 的 sql id 與 hash value ，這個 sql id 與 hash value 對於 shared pool 來說就是 Parent Cursor ，在全新的 SQL 產生 Parent Cursor 的同時也會產生一個 Child Cursor ；下一次有同樣的 SQL 語法執行時，首先於 shared pool 找到與其相同的 Parent Cursor ，然後再找出此 Parent Cursor 底下的 Child Cursor 哪一個可以用來共享，如果找不到則再重新產生一個 Child Cursor 。

舉個例子來說，使用者 Scott 與 HR 都有 emp 這個 Table ，以使用者 Scott 的身分以及 HR 的身分都執行 select * from emp 這個語法，由於 select * from emp 是相同的，所以他們有相同的 Parent Cursor ，但是 Scott 底下的 emp 與 HR 底下的 emp 的 object id 一定不同，所以雖然語法一樣，但是這兩句 SQL 無法共享，因此會發生一個 Parent Cursor 會有兩個 Child Cursor 的現象。

首先 Scott 執行完 select * from emp 之後，查詢 V$SQL 可以看到 sql_id 為 a2dk8bdn0ujx7 並且同時產生 Parent Cursor 與 Child Cursor :

在 HR 執行完 select * from emp 之後再度查詢 V$SQL 可以發現同樣的 sql_id a2dk8bdn0ujx7 有兩筆資料，但是它的 Child Cursor 不同 :

因為這兩句 SQL 無法共享所以產生出新的 Child Cursor :

從 v$sql_shared_cursor 可以用來分析 SQL 無法共享的原因 :

SQL> select sql_id,address,child_address,translation_mismatch,reason        from v$sql_shared_cursor where sql_id='a2dk8bdn0ujx7';

我們可以看到其中的 translation_mismatch 為 Y ，表示雖然 SQL 語法相同，但是裡面的物件卻是不同的，所以這兩句 SQL 無法共享。

Cursor 無法共享有很多原因，可能與 Application 的行為有關，早期在 cursor_sharing 參數為 similar 的時候也有相關 Bug 造成 Cursor 無法共享。

那麼我們回到這個問題本身，既然發現系統都嘗試在取得 Parent Cursor 的 mutex X 鎖，是不是表示有 Cursor 無法共享並一直產生出新的 Child Cursor ? 由 AWR Report 中的 SQL version count 可以證實這一點 :

SQL Version Count 異常的大量，我們可以知道多數的 Cursor 無法共享，需要一直產生 Child Cursor ，而產生 Child Cursor 的過程中需要對它的 Parent Cursor 進行 lock ，等到產生完再釋放，每個 Cursor 都需要對它的 Parent Cursor 進行 lock 來產生 Child Cursor ，因此產生了 cursor: mutex X 等待。

那麼應用系統的程式都沒有異動，只是從 11.2.0.3 升級到 12.2 ，為何就產生了 cursor: mutex X ?

從 Oracle 11g 開始引進了 _cursor_obsolete_threshold 這個隱藏參數，用途在於避免過多的 Child Cursor ，當一句 SQL 的 Child Cursor 數量超出這個參數所設定的 threshold 時，便會將該 Parent Cursor 進行無效化 (obsolete) ，重新再產生新的 Parent Cursor ，用以避免 SQL Version Count 過多的問題，而這個參數的預設值在 11.2.0.3 為 100 、 11.2.0.4 為 1024 ，到了 12.2 的參數預設值增加到了 8192 ，所以在升級之後 Parent Cursor 被 age out 的時間拉長，進而發生了大量的 cursor: mutex X 等待。

解決方法:

將 _cursor_obsolete_threshold 的參數值改回 11.2.0.3 預設的 100 並重啟 DB 解決問題。

SQL> alter system set "_cursor_obsolete_threshold"=100 scope=spfile; $ srvctl stop database –d orcl $ srvctl start database –d orcl