Poor’s man script to summarize reasons WHY cursors are not shared
Having a large number of child cursors can affect parsing performance as hinted by Abel Macias on his blog post about Diagnosis of a High Version Count (HVC). On his post, Abel also refers to a note on MOS which includes a script that dives into the reasons WHY our cursors are not getting shared. Then, for deep-dives in this area, I strongly suggest to read his post and use the referenced script provided at MOS.
Besides longer parse times, and potential library cache contention, manifested by some waits (such as on mutex), there is another side effect that may bite us: CBO may produce a different plan when a SQL statement is hard-parsed while creating a new child cursor. This latter side effect can be critical for transactional applications with SLA depending on very short latencies of some queries.
This post is about a poor’s man script, that with no installation whatsoever, it lists an aggregated summary of the reasons why our cursors are not shared, including child cursor counts and distinct SQL_IDs counts for each reason (see sample output below). I had to write such script since in our environments we cannot simply run diagnostics scripts that create objects in the database, such as the one provided by MOS.
CURSORS SQL_IDS REASON_NOT_SHARED
---------- ---------- -----------------------------
226916 7826 ROLL_INVALID_MISMATCH
29387 105 BIND_EQUIV_FAILURE
21794 4027 HASH_MATCH_FAILED
11588 2134 OPTIMIZER_MISMATCH
11027 413 BIND_LENGTH_UPGRADEABLE
11008 384 BIND_MISMATCH
10125 2697 USE_FEEDBACK_STATS
4540 109 OPTIMIZER_MODE_MISMATCH
1652 72 PURGED_CURSOR
1245 81 BIND_UACS_DIFF
1062 316 LANGUAGE_MISMATCH
771 103 LOAD_OPTIMIZER_STATS
500 52 STATS_ROW_MISMATCH
238 86 MV_QUERY_GEN_MISMATCH
94 3 MULTI_PX_MISMATCH
28 4 AUTH_CHECK_MISMATCH
23 1 INSUFF_PRIVS
Once I get to see some reasons for not sharing, some responsible for a large number of child cursors (and distinct SQL_IDs), then I can search on MOS as Abel suggested. Ideally, if you are interested in plan stability, you may want to reduce the times the CBO is tasked to create a new child cursor (and potentially a new Execution Plan).
In output sample above, top in our list is ROLL_INVALID_MISMATCH, causing 226,916 child cursors in as many as 7,826 SQL statements. This particular reason for not sharing cursors is due to a persistent gathering of schema object statistics with the explicit request to invalidate cursors. Since we want to promote plan stability, we would need to suspend such aggressive gathering of CBO statistics and validate reason ROLL_INVALID_MISMATCH is reduced.
Anyways, free script used is below. Enjoy it!
*** edited *** a new version of the script is now available (below). Thanks to stewashton for his input.
-- sql_shared_cursor.sql SET HEA OFF LIN 300 NEWP NONE PAGES 0 FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF SQLBL ON BLO . RECSEP OFF; SPO all_reasons.sql SELECT CASE WHEN ROWNUM = 1 THEN '( ' ELSE ', ' END||column_name FROM dba_tab_columns WHERE table_name = 'V_$SQL_SHARED_CURSOR' AND owner = 'SYS' AND data_type = 'VARCHAR2' AND data_length = 1 / SPO OFF; GET all_reasons.sql I ) I ) I WHERE value = 'Y' I GROUP BY reason_not_shared I ORDER BY cursors DESC, sql_ids DESC, reason_not_shared 0 ( value FOR reason_not_shared IN 0 FROM v$sql_shared_cursor UNPIVOT 0 SELECT COUNT(*) cursors, COUNT(DISTINCT sql_id) sql_ids, reason_not_shared L SET HEA ON NEWP 1 PAGES 30 PRO please wait / !rm all_reasons.sql
Carlos Sierra's Tools and Tips 
Here is a version that will run 50 times faster:
To generate it (requires at least 11.2)
stewashton
September 3, 2017 at 5:49 am
Thanks Stew. Indeed your version executes very fast. Thanks for sharing!
Carlos Sierra
September 5, 2017 at 5:22 pm
Thank you Carlos for sharing this info!
I found that the script below is more useful to identify specific sql_ids
select inst_id, parsing_schema_name, reason_not_shared, sql_id, count(*) cnt
from gv$sql_shared_cursor
join gv$sql using (inst_id, sql_id)
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 inst_id, parsing_schema_name, reason_not_shared, sql_id
having count(*) >= 20
order by 5 desc, 1, 2, 3
Alex
November 11, 2017 at 10:56 am
Sorry, I have provided incorrect version of the script – instead of gv$sql it must be gv$sqlarea
select inst_id, parsing_schema_name, reason_not_shared, sql_id, count(*) cnt
from gv$sql_shared_cursor
join gv$sqlarea using (inst_id, sql_id)
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 inst_id, parsing_schema_name, reason_not_shared, sql_id
having count(*) >= 20
order by 5 desc, 1, 2, 3
Alex
November 11, 2017 at 12:05 pm
Thanks Alex for sharing. I provided a version of the script that is dynamic on nature, since different versions of the databases have different columns on the gv$sql_shared_cursor view.
Carlos Sierra
November 19, 2017 at 3:01 pm
Thanks Alex, but I get below error in a database running 12.1.0.2
ORA-01790: expression must have same datatype as corresponding expression
Mathavan
January 7, 2019 at 11:56 am
Mathavan, Just noticed your comment.
It worked on 11.2 SE and works on Exadata 18c. I copy-pasted the statement from the comment above and replaced ` (ascii 96) with ‘ (ascii 39).
See the partial result below (removed schema name for obvious reason):
nst_id reason_not_shared sql_id cnt
1 ROLL_INVALID_MISMATCH ca9zmsn8yw87b 194
1 BIND_MISMATCH ca9zmsn8yw87b 191
2 ROLL_INVALID_MISMATCH ca9zmsn8yw87b 182
2 BIND_MISMATCH ca9zmsn8yw87b 180
1 ROLL_INVALID_MISMATCH dvn0r7q0bgbz6 172
2 ROLL_INVALID_MISMATCH dvn0r7q0bgbz6 171
2 ROLL_INVALID_MISMATCH 8wfh1x067sjwr 170
2 BIND_MISMATCH dvn0r7q0bgbz6 165
1 BIND_MISMATCH dvn0r7q0bgbz6 164
1 ROLL_INVALID_MISMATCH 73fupypg40kh7 161
2 ROLL_INVALID_MISMATCH 73fupypg40kh7 160
2 BIND_MISMATCH 8wfh1x067sjwr 159
1 BIND_MISMATCH 73fupypg40kh7 158
2 BIND_MISMATCH 73fupypg40kh7 158
1 BIND_LENGTH_UPGRADEABLE ca9zmsn8yw87b 146
2 BIND_LENGTH_UPGRADEABLE ca9zmsn8yw87b 146
1 BIND_LENGTH_UPGRADEABLE 73fupypg40kh7 136
2 BIND_LENGTH_UPGRADEABLE 73fupypg40kh7 132
1 BIND_MISMATCH 07vrkc0g5574c 131
1 ROLL_INVALID_MISMATCH 07vrkc0g5574c 127
Alex
April 25, 2019 at 9:27 pm
Thx. Yes. It seems the browser changes single quotes…
Carlos Sierra
April 28, 2019 at 7:39 am
Hi,
The view gv$sql_shared_cursor have any history table on awr? Is some cases gv$sql_shared_cursor doesn’t show any rows and query was invalidate days ago.
Regards.
Oscar CR
February 18, 2021 at 6:22 am
I doubt such historical data exists.
Carlos Sierra
February 18, 2021 at 6:37 am
Can you customized your script to work with SQLcl?
Alan Sterger
May 25, 2022 at 12:08 pm
I offer my scripts “as is”. I do not use SQLcl.
Carlos Sierra
May 25, 2022 at 2:39 pm
[…] select count(*) as version_count, sql_id from v$sql where is_obsolete = ‘N’ group by sql_id having count(*) > 125; High version Count: https://carlos-sierra.net/2017/09/01/poors-man-script-to-summarize-reasons-why-cursors-are-not-share… […]
All about Library cache lock | orasolution
July 31, 2022 at 7:31 pm