Queries that cause a “cursor: mutex S” wait event

 

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.

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.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *