Decrease high version count in Oracle

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 increases the cursor threshold value in 12.2 drastically because of the 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;