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

Decrease high version count in Oracle

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.

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

Different version has different value: _cursor_obsolete_threshold=1024 _cursor_obsolete_threshold=1024 _cursor_obsolete_threshold=1024 _cursor_obsolete_threshold=8192
18c: _cursor_obsolete_threshold=8192
19c: _cursor_obsolete_threshold=8192

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;

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: Logo

You are commenting using your 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.