Cursor: mutex X performance issue in AWR for Oracle 12.2 version

Error:
Performance issue found AWR with “Cursor: Mutex X” and “Cursor: Mutex S” high waits.
High number of child cursors or version count in AWR report.

Cause:
Hard parsing due to bind variable mismatches in Oracle 12.2 version.
Oracle increase the cursor threshold value in 12.2 drastically because of container or PDB database.

Different version has different value:
11.2.0.4: _cursor_obsolete_threshold=1024
12.1.0.1: _cursor_obsolete_threshold=1024
12.1.0.2: _cursor_obsolete_threshold=1024
12.2.0.1: _cursor_obsolete_threshold=8192
18c: _cursor_obsolete_threshold=8192
19c: _cursor_obsolete_threshold=8192

Solution:
Following are the solutions to overcome this problem:
1. Reduce the value of _cursor_obsolete_threshold to 1024 or less like 200.
2. Flush the shared pool time by time as a temporary solution.

Option 1: Reduce the _cursor_obsolete_threshold to 1024.

Check the current value of _cursor_obsolete_threshold.

col Name for a26
col Value for a10
col Description for a30
select ksppinm "Name",ksppstvl "Value",ksppdesc "Description" from x$ksppi a, x$ksppsv b 
where a.indx=b.indx and ksppinm = '_cursor_obsolete_threshold';

Name                       Value  Description                                                                           
-------------------------- ------ ----------------------------------------------     
_cursor_obsolete_threshold 8192   Number of cursors per parent before obsoletion.

Change the value of _cursor_obsolete_threshold.

ALTER SYSTEM SET "_cursor_obsolete_threshold"=1024 scope=spfile;
Shutdown immediate;
Startup;

Option 2: Flush the shared pool.

alter system flush shared_pool;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.