Oracle Database 12c -- Queries Hung on "cursor: mutex X" Due To High Version Count with LANGUAGE_MISMATCH

Problem

Queries get hung on wait events 'cursor: mutex X' or 'cursor: mutex S' and the version count of the parent cursor is very high like from hundredths to tens of thousands

The reason of the high version count is LANGUAGE_MISMATCH

Solution

LANGUAGE_MISMATCH means there are different NLS settings between database side and client side. On every execution of a query, a new child cursor will be generated. Check parameters for any differences on the both sides:  NLS_TERRITORY, NLS_LANGUAGE, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_ISO_CURRENCY

This is expected behavior. 

Workarounds:

1) Manually purge the cursor from the Shared Pool:

SQL> select address, hash_value from v$sqlarea where sql_id='<sql_id>';

SQL> exec dbms_shared_pool.purge('<address,hash_value>','C');

2) Set _cursor_obsolete_threshold parameter. After the number of child cursors has reached the threshold value, the parent cursor will be automatically purged from the Shared pool (though not immediately):

SQL> alter system set _cursor_obsolete_threshold=<value> scope=spfile;

Restart instance

Reference:

• Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1)