from v$sql_shared_cursor
unpivot(val for reason_not_shared in(
UNBOUND_CURSOR,SQL_TYPE_MISMATCH,OPTIMIZER_MISMATCH,OUTLINE_MISMATCH,
STATS_ROW_MISMATCH,LITERAL_MISMATCH,FORCE_HARD_PARSE,EXPLAIN_PLAN_CURSOR,
BUFFERED_DML_MISMATCH,PDML_ENV_MISMATCH,INST_DRTLD_MISMATCH,SLAVE_QC_MISMATCH,
TYPECHECK_MISMATCH,AUTH_CHECK_MISMATCH,BIND_MISMATCH,DESCRIBE_MISMATCH,
LANGUAGE_MISMATCH,TRANSLATION_MISMATCH,BIND_EQUIV_FAILURE,INSUFF_PRIVS,
INSUFF_PRIVS_REM,REMOTE_TRANS_MISMATCH,LOGMINER_SESSION_MISMATCH,INCOMP_LTRL_MISMATCH,
OVERLAP_TIME_MISMATCH,EDITION_MISMATCH,MV_QUERY_GEN_MISMATCH,USER_BIND_PEEK_MISMATCH,
TYPCHK_DEP_MISMATCH,NO_TRIGGER_MISMATCH,FLASHBACK_CURSOR,ANYDATA_TRANSFORMATION,
PDDL_ENV_MISMATCH,TOP_LEVEL_RPI_CURSOR,DIFFERENT_LONG_LENGTH,LOGICAL_STANDBY_APPLY,
DIFF_CALL_DURN,BIND_UACS_DIFF,PLSQL_CMP_SWITCHS_DIFF,CURSOR_PARTS_MISMATCH,
STB_OBJECT_MISMATCH,CROSSEDITION_TRIGGER_MISMATCH,PQ_SLAVE_MISMATCH,TOP_LEVEL_DDL_MISMATCH,
MULTI_PX_MISMATCH,BIND_PEEKED_PQ_MISMATCH,MV_REWRITE_MISMATCH,ROLL_INVALID_MISMATCH,
OPTIMIZER_MODE_MISMATCH,PX_MISMATCH,MV_STALEOBJ_MISMATCH,FLASHBACK_TABLE_MISMATCH,
LITREP_COMP_MISMATCH,PLSQL_DEBUG,LOAD_OPTIMIZER_STATS,ACL_MISMATCH,
FLASHBACK_ARCHIVE_MISMATCH,LOCK_USER_SCHEMA_FAILED,REMOTE_MAPPING_MISMATCH,LOAD_RUNTIME_HEAP_FAILED,
HASH_MATCH_FAILED,PURGED_CURSOR,BIND_LENGTH_UPGRADEABLE,USE_FEEDBACK_STATS
))
where val = 'Y'
group by reason_not_shared
---->See output as below and see the issues with BIND MISMATCH and other MISMATCH
BIND_MISMATCH 111124 300
LANGUAGE_MISMATCH 6181 3023
AUTH_CHECK_MISMATCH 5073 2613
TRANSLATION_MISMATCH 5069 2611
BIND_LENGTH_UPGRADEABLE 1663 863
BIND_EQUIV_FAILURE 181 51
BIND_UACS_DIFF 69 69
OPTIMIZER_MISMATCH 51 45
LOAD_OPTIMIZER_STATS 33 29
Solution --To clean the queries periodically every 4 hours or so, you can write job also with procedure to execute in regular interval with sharable_mem > 400MB as an example.
SELECT *
FROM v$sqlarea
WHERE version_count > 100
ORDER BY sharable_mem desc;
-->Take top 5 shareble_memory sql ids and purge them to release shared pool area (SGA).
select 'exec sys.dbms_shared_pool.purge ('''||address||','||hash_value||''', ''C'');'
from v$sqlarea
where sql_id='656u1muc0d4h0';
-->Run below to purge
exec sys.dbms_shared_pool.purge ('0700010300A34620,2550567424', 'C');
--We also implemented below in 11gr2 in db
alter system set “_optimizer_use_feedback” = false scope=both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
--Extra - to check _ underscore hidden parameter values
col parameter for a35
col "Session value" for a10
col "Instance value" for a10
SELECT x.ksppinm "Parameter", Y.ksppstvl "Session Value",Z.ksppstvl "Instance Value" FROM x$ksppi X, x$ksppcv Y, x$ksppsv Z WHERE x.indx = Y.indx AND x.indx = z.indx AND x.ksppinm like '_optimizer%' order by x.ksppinm;
Cardinality Feedback (Oracle Database 10g to 11g Change)
Cardinality feedback is an enhancement made to the Optimizer in Oracle Database 11g Release
2. Cardinality feedback compares cardinality estimates used to derive the plan with the actual
cardinality seen in the first execute. If the estimate is 2X off, the cursor is marked for hard parse
next time around. The cardinality information seen at first execute is supplied at the next hard
parse thus allowing the Optimizer an opportunity to improve on the plan now that it knows more
about the actual cardinality seen in the query. If cardinality feedback is used, it is displayed in the
section of the execution plan. Cardinality feedback works for predicates on tables, indexes and
group by clauses. It does not help for cardinality mis-estimates for joins. Feedback is not
persistent on disk, it resides in memory only. The Optimizer will need to “relearn” something if the
database is shutdown and restarted. (Related to _optimizer_use_feedback parameter.)
