<\/p>\n
The “Cursor Mutex S” wait event occurs when two different sessions try to make hard or soft parsing on the same query.<\/p>\n
As a result, it is possible to see a high number of query versions in the shared pool or see the high CPU usage.<\/p>\n
Queries must be identified, modified to use bind variable or cleaned from shared pool.<\/p>\n
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.<\/p>\n
First, let’s find mutex_identifier information for queries. You can reach this information with the following query.<\/p>\n
SQL> SELECT MUTEX_IDENTIFIER,\r\nMUTEX_TYPE,\r\nMAX (gets),\r\nMAX (sleeps),\r\nmutex_value\r\nFROM v$mutex_sleep_history\r\nWHERE mutex_type = 'Cursor Pin'\r\nGROUP BY MUTEX_IDENTIFIER, MUTEX_TYPE, mutex_value\r\nORDER BY 4 DESC;\r\n\r\nMUTEX_IDENTIFIER MUTEX_TYPE MAX(GETS) MAX(SLEEPS) MUTEX_VALUE\r\n---------------- ---------- ------------ ------------ -------------\r\n3220332543 Cursor Pin 1722384420 813613 0000000000000001\r\n3220332543 Cursor Pin 1721330975 569746 0000000000000006\r\n3220332543 Cursor Pin 2961274030 487219 0000000000000015\r\n3220332543 Cursor Pin 2961169907 470463 0000000000000010\r\n3220332543 Cursor Pin 1707265921 465149 000005F50000001C\r\n3220332543 Cursor Pin 2961284466 460056 0000000000000013\r\n3220332543 Cursor Pin 1709020847 450800 000001880000000D<\/pre>\nYou 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.<\/p>\n
SQL> select sql_id, sql_text, version_count,address,hash_value from v$sqlarea where hash_value=3220332543;<\/pre>\nIn 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.<\/p>\n
SQL> exec DBMS_SHARED_POOL.PURGE ('000000069387B220, 3220332543', 'C');\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\n\n<\/div>\n