The “Cursor Mutex S” wait event occurs when two different sessions try to make hard or soft parsing on the same query.
As a result, it is possible to see a high number of query versions in the shared pool or see the high CPU usage.
Queries must be identified, modified to use bind variable or cleaned from shared pool.
For the detection of queries that cause this wait event, we can access the MUTEX_IDENTIFIER information from the “v$mutex_sleep_history” view and use the MUTEX_IDENTIFIER value to obtain detailed information from the “v$sqlarea” view.
First, let’s find mutex_identifier information for queries. You can reach this information with the following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> SELECT MUTEX_IDENTIFIER, MUTEX_TYPE, MAX (gets), MAX (sleeps), mutex_value FROM v$mutex_sleep_history WHERE mutex_type = 'Cursor Pin' GROUP BY MUTEX_IDENTIFIER, MUTEX_TYPE, mutex_value ORDER BY 4 DESC; MUTEX_IDENTIFIER MUTEX_TYPE MAX(GETS) MAX(SLEEPS) MUTEX_VALUE ---------------- ---------- ------------ ------------ ------------- 3220332543 Cursor Pin 1722384420 813613 0000000000000001 3220332543 Cursor Pin 1721330975 569746 0000000000000006 3220332543 Cursor Pin 2961274030 487219 0000000000000015 3220332543 Cursor Pin 2961169907 470463 0000000000000010 3220332543 Cursor Pin 1707265921 465149 000005F50000001C 3220332543 Cursor Pin 2961284466 460056 0000000000000013 3220332543 Cursor Pin 1709020847 450800 000001880000000D |
You can access the related query by combining the MUTEX_IDENTIFIER information returned from this query and the hash_value field in the “v$sqlarea” view.
|
1 |
SQL> select sql_id, sql_text, version_count,address,hash_value from v$sqlarea where hash_value=3220332543; |
In order to remove the query you have detected with the above query from the shared pool, you must give the “address” and “hash_value” information as the parameter to the command below.
|
1 2 3 |
SQL> exec DBMS_SHARED_POOL.PURGE ('000000069387B220, 3220332543', 'C'); PL/SQL procedure successfully completed. |
