Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Poor’s man script to summarize reasons WHY cursors are not shared

with 8 comments

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
Advertisements

Written by Carlos Sierra

September 1, 2017 at 1:01 pm

8 Responses

Subscribe to comments with RSS.

  1. Here is a version that will run 50 times faster:

    select reason_not_shared, count(*) cursors, count(distinct sql_id) sql_ids
    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
    order by 2 desc, 3, 1;

    To generate it (requires at least 11.2)

    select 'select reason_not_shared, count(*) cursors, count(distinct sql_id) sql_ids
    from v$sql_shared_cursor
    unpivot(val for reason_not_shared in(
    ' 
    || listagg(
      '  '||listagg(column_name,',') within group (order by column_id) ,
      ',
    ') within group(order by line_no)
    ||'
    ))
    where val = ''Y''
    group by reason_not_shared
    order by 2 desc, 3, 1;'
    sql_text
    from (
      select column_name,
      column_id,
      ceil(row_number() over(order by column_id) / 4) line_no
      from dba_tab_columns where owner = 'SYS' and table_name = 'V_$SQL_SHARED_CURSOR'
      and data_length = 1
    )
    group by line_no;
    

    stewashton

    September 3, 2017 at 5:49 am

  2. 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

  3. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: