UNPIVOT query for High Version Count

All SQLs are implicitly sharable. After entering the SQL statement, Oracle Database will calculate a hash. A hash key usable to locate the cursor in the library cache. A cursor is the instance of query or dml in the library cache. If the SQL statement carries particular conditions, Oracle creates a version of the cursor with those conditions. A version of a cursor is also known as child cursor

According to Oracle Support Note Troubleshooting: High Version Count Issues (Doc ID 296377.1), there is no definitive definition of what a ‘High’ number of versions for a particular cursor is. Different systems may be able to deal with different ranges of versions. However, AWR reports start reporting versions over 20 for a particular cursor and so that is as good an indicator of a potential problem as any

Version count for a particular SQL could be tracked in GV$SQL_SHARED_CURSOR It explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared

There are 64 columns with reasons in Oracle 18c and 19c, and it is convenient to use UNPIVOT to aggregate them and retrieving the most common of them for a particular cursor:

SQL> SELECT INST_ID, SQL_ID, REASON_NAME, REASON_VALUE, COUNT(*) FROM (
 SELECT * FROM GV$SQL_SHARED_CURSOR
 UNPIVOT INCLUDE NULLS
 (REASON_VALUE
     FOR REASON_NAME IN (ACL_MISMATCH AS 'ACL_MISMATCH',ANYDATA_TRANSFORMATION AS 'ANYDATA_TRANSFORMATION',AUTH_CHECK_MISMATCH AS 'AUTH_CHECK_MISMATCH',BIND_EQUIV_FAILURE AS 'BIND_EQUIV_FAILURE',BIND_LENGTH_UPGRADEABLE AS 'BIND_LENGTH_UPGRADEABLE',BIND_MISMATCH AS 'BIND_MISMATCH',BIND_PEEKED_PQ_MISMATCH AS 'BIND_PEEKED_PQ_MISMATCH',BIND_UACS_DIFF AS 'BIND_UACS_DIFF',BUFFERED_DML_MISMATCH AS 'BUFFERED_DML_MISMATCH',CROSSEDITION_TRIGGER_MISMATCH AS 'CROSSEDITION_TRIGGER_MISMATCH',CURSOR_PARTS_MISMATCH AS 'CURSOR_PARTS_MISMATCH',DESCRIBE_MISMATCH AS 'DESCRIBE_MISMATCH',DIFF_CALL_DURN AS 'DIFF_CALL_DURN',DIFFERENT_LONG_LENGTH AS 'DIFFERENT_LONG_LENGTH',EDITION_MISMATCH AS 'EDITION_MISMATCH',EXPLAIN_PLAN_CURSOR AS 'EXPLAIN_PLAN_CURSOR',FLASHBACK_ARCHIVE_MISMATCH AS 'FLASHBACK_ARCHIVE_MISMATCH',FLASHBACK_CURSOR AS 'FLASHBACK_CURSOR',FLASHBACK_TABLE_MISMATCH AS 'FLASHBACK_TABLE_MISMATCH',FORCE_HARD_PARSE AS 'FORCE_HARD_PARSE',HASH_MATCH_FAILED AS 'HASH_MATCH_FAILED',INCOMP_LTRL_MISMATCH AS 'INCOMP_LTRL_MISMATCH',INST_DRTLD_MISMATCH AS 'INST_DRTLD_MISMATCH',INSUFF_PRIVS AS 'INSUFF_PRIVS',INSUFF_PRIVS_REM AS 'INSUFF_PRIVS_REM',LANGUAGE_MISMATCH AS 'LANGUAGE_MISMATCH',LITERAL_MISMATCH AS 'LITERAL_MISMATCH',LITREP_COMP_MISMATCH AS 'LITREP_COMP_MISMATCH',LOAD_OPTIMIZER_STATS AS 'LOAD_OPTIMIZER_STATS',LOAD_RUNTIME_HEAP_FAILED AS 'LOAD_RUNTIME_HEAP_FAILED',LOCK_USER_SCHEMA_FAILED AS 'LOCK_USER_SCHEMA_FAILED',LOGICAL_STANDBY_APPLY AS 'LOGICAL_STANDBY_APPLY',LOGMINER_SESSION_MISMATCH AS 'LOGMINER_SESSION_MISMATCH',MULTI_PX_MISMATCH AS 'MULTI_PX_MISMATCH',MV_QUERY_GEN_MISMATCH AS 'MV_QUERY_GEN_MISMATCH',MV_REWRITE_MISMATCH AS 'MV_REWRITE_MISMATCH',MV_STALEOBJ_MISMATCH AS 'MV_STALEOBJ_MISMATCH',NO_TRIGGER_MISMATCH AS 'NO_TRIGGER_MISMATCH',OPTIMIZER_MISMATCH AS 'OPTIMIZER_MISMATCH',OPTIMIZER_MODE_MISMATCH AS 'OPTIMIZER_MODE_MISMATCH',OUTLINE_MISMATCH AS 'OUTLINE_MISMATCH',OVERLAP_TIME_MISMATCH AS 'OVERLAP_TIME_MISMATCH',PDDL_ENV_MISMATCH AS 'PDDL_ENV_MISMATCH',PDML_ENV_MISMATCH AS 'PDML_ENV_MISMATCH',PLSQL_CMP_SWITCHS_DIFF AS 'PLSQL_CMP_SWITCHS_DIFF',PLSQL_DEBUG AS 'PLSQL_DEBUG',PQ_SLAVE_MISMATCH AS 'PQ_SLAVE_MISMATCH',PURGED_CURSOR AS 'PURGED_CURSOR',PX_MISMATCH AS 'PX_MISMATCH',REMOTE_MAPPING_MISMATCH AS 'REMOTE_MAPPING_MISMATCH',REMOTE_TRANS_MISMATCH AS 'REMOTE_TRANS_MISMATCH',ROLL_INVALID_MISMATCH AS 'ROLL_INVALID_MISMATCH',SLAVE_QC_MISMATCH AS 'SLAVE_QC_MISMATCH',SQL_TYPE_MISMATCH AS 'SQL_TYPE_MISMATCH',STATS_ROW_MISMATCH AS 'STATS_ROW_MISMATCH',STB_OBJECT_MISMATCH AS 'STB_OBJECT_MISMATCH',TOP_LEVEL_DDL_MISMATCH AS 'TOP_LEVEL_DDL_MISMATCH',TOP_LEVEL_RPI_CURSOR AS 'TOP_LEVEL_RPI_CURSOR',TRANSLATION_MISMATCH AS 'TRANSLATION_MISMATCH',TYPCHK_DEP_MISMATCH AS 'TYPCHK_DEP_MISMATCH',TYPECHECK_MISMATCH AS 'TYPECHECK_MISMATCH',UNBOUND_CURSOR AS 'UNBOUND_CURSOR',USE_FEEDBACK_STATS AS 'USE_FEEDBACK_STATS',USER_BIND_PEEK_MISMATCH AS 'USER_BIND_PEEK_MISMATCH'))
 ORDER BY 2,1)
 WHERE SQL_ID='1q1spprb9m55h'
 GROUP BY INST_ID, SQL_ID, REASON_NAME, REASON_VALUE
 HAVING REASON_VALUE='Y'
 ORDER BY COUNT(*) DESC;

INST_ID SQL_ID        REASON_NAME            REASON_VALUE COUNT(*)
------- ------------- ---------------------- ------------ --------
      1 1q1spprb9m55h PQ_SLAVE_MISMATCH      Y                 191      
      1 1q1spprb9m55h DIFF_CALL_DURN         Y                  41
      1 1q1spprb9m55h ROLL_INVALID_MISMATCH  Y                  32      
      1 1q1spprb9m55h MULTI_PX_MISMATCH      Y                  31
      1 1q1spprb9m55h LANGUAGE_MISMATCH      Y                  20
      1 1q1spprb9m55h HASH_MATCH_FAILED      Y                   5
      1 1q1spprb9m55h PURGED_CURSOR          Y                   1

7 rows selected.

Also useful reporting tool could be used for diagnosis High SQL Version Counts – Script to determine reason(s) (Doc ID 438755.1)

Detailed explanation given in the Abel Macias blog