How To Identify High Number Of Child Cursors Leading To ORA-600 [17059]

最新推荐文章于 2021-04-03 09:23:23 发布
最新推荐文章于 2021-04-03 09:23:23 发布
阅读量95 收藏
点赞数
文章标签： 数据库
ID 1213715.1

In this Document


Goal

Fix
 Query for 10.2:
 Query for 11.1:
 Query for 11.2:
 Example of output:
 Conclusions and Summary of bugs due to reason code:

References

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.7.0 and later
Information in this document applies to any platform.

Goal

This article addresses non-sharable cursors with an ORA-600 [17059] approach.

The purpose of this article is to assist Support engineers and Customer to identify high number of non-sharable child cursors potentially causing ORA-600 [17059].

This article can also be used for non-sharable child cursors potentially leading ORA-4031.

The article provides SQL to query dictionary views V$SQLAREA and V$SQL_SHARED_CURSOR to get the reason code for why cursor is not shared. Based on the reason codes, a list of known defects related to non-sharable cursors are listed with links towards articles with more details about fixed releases and known workarounds

Fix

1. Investigate which cursor has high version count by running following query :
    

     SQL> SELECT version_count, sql_id, sql_text
          FROM   v$sqlarea
          WHERE  version_count >
          ORDER BY version_count;


   Starting value for can be 3.000 to limited number of rows that are returned.

   Example of output:
    

     VERSION_COUNT  SQL_ID  
     -------------  -------------------------
     3301                       430vzv5zkvv9k



2. Identify why the cursors are not shared and has so high number of version by running
    following query. Cursors that are not shared has higher value than 0. Use the SQL_ID
    value from step 1 in the following query:

Query for 10.2:

     SQL> SET PAGES 0
     SQL> SET HEADING OFF;
     SQL> SELECT 'UNBOUND_CURSOR:            '||SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))),
                 'SQL_TYPE_MISMATCH:         '||SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))),
                 'OPTIMIZER_MISMATCH:        '||SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))),
                 'OUTLINE_MISMATCH:          '||SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))),
                 'STATS_ROW_MISMATCH:        '||SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))),
                 'LITERAL_MISMATCH:          '||SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))),
                 'SEC_DEPTH_MISMATCH:        '||SUM(TO_NUMBER(DECODE(sec_depth_mismatch,'Y',1,'N','0'))),
                 'EXPLAIN_PLAN_CURSOR:       '||SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))),
                 'BUFFERED_DML_MISMATCH:     '||SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))),
                 'PDML_ENV_MISMATCH:         '||SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))),
                 'INST_DRTLD_MISMATCH:       '||SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))),
                 'SLAVE_QC_MISMATCH:         '||SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))),
                 'TYPECHECK_MISMATCH:        '||SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))),
                 'AUTH_CHECK_MISMATCH:       '||SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))),
                 'BIND_MISMATCH:             '||SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))),
                 'DESCRIBE_MISMATCH:         '||SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))),
                 'LANGUAGE_MISMATCH:         '||SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))),
                 'TRANSLATION_MISMATCH:      '||SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))),
                 'ROW_LEVEL_SEC_MISMATCH:    '||SUM(TO_NUMBER(DECODE(row_level_sec_mismatch,'Y',1,'N','0'))),
                 'ROW_LEVEL_SEC_MISMATCH:    '||SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))),
                 'INSUFF_PRIVS_REM:          '||SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))),
                 'REMOTE_TRANS_MISMATCH:     '||SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))),
                 'LOGMINER_SESSION_MISMATCH: '||SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))),
                 'INCOMP_LTRL_MISMATCH:      '||SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))),
                 'OVERLAP_TIME_MISMATCH:     '||SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))),
                 'SQL_REDIRECT_MISMATCH:     '||SUM(TO_NUMBER(DECODE(sql_redirect_mismatch,'Y',1,'N','0'))),
                 'MV_QUERY_GEN_MISMATCH:     '||SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))),
                 'USER_BIND_PEEK_MISMATCH:   '||SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))),
                 'TYPCHK_DEP_MISMATCH:       '||SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))),
                 'NO_TRIGGER_MISMATCH:       '||SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_CURSOR:          '||SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))),
                 'ANYDATA_TRANSFORMATION:    '||SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))),
                 'INCOMPLETE_CURSOR:         '||SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))),
                 'TOP_LEVEL_RPI_CURSOR:      '||SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))),
                 'DIFFERENT_LONG_LENGTH:     '||SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))),
                 'LOGICAL_STANDBY_APPLY:     '||SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))),
                 'LOGICAL_STANDBY_APPLY:     '||SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))),
                 'BIND_UACS_DIFF:            '||SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))),
                 'PLSQL_CMP_SWITCHS_DIFF:    '||SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))),
                 'CURSOR_PARTS_MISMATCH:     '||SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))),
                 'STB_OBJECT_MISMATCH:       '||SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))),
                 'ROW_SHIP_MISMATCH:         '||SUM(TO_NUMBER(DECODE(row_ship_mismatch,'Y',1,'N','0'))),
                 'PQ_SLAVE_MISMATCH:         '||SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))),
                 'TOP_LEVEL_DDL_MISMATCH:    '||SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))),
                 'MULTI_PX_MISMATCH:         '||SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))),
                 'BIND_PEEKED_PQ_MISMATCH:   '||SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))),
                 'MV_REWRITE_MISMATCH:       '||SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))),
                 'ROLL_INVALID_MISMATCH:     '||SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))),
                 'OPTIMIZER_MODE_MISMATCH:   '||SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))),
                 'PX_MISMATCH:               '||SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))),
                 'MV_STALEOBJ_MISMATCH:      '||SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_TABLE_MISMATCH:  '||SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))),
                 'LITREP_COMP_MISMATCH:      '||SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0')))
          FROM   v$sql_shared_cursor
          WHERE  address IN (SELECT address
                             FROM   v$sqlarea
                             WHERE  sql_id = '');

 

Query for 11.1:

     SQL> SET PAGES 0
     SQL> SET HEADING OFF;
     SQL> SELECT 'UNBOUND_CURSOR:                '||SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))),
                 'SQL_TYPE_MISMATCH:             '||SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))),
                 'OPTIMIZER_MISMATCH:            '||SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))),
                 'OUTLINE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))),
                 'STATS_ROW_MISMATCH:            '||SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))),
                 'LITERAL_MISMATCH:              '||SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))),
                 'FORCE_HARD_PARSE:              '||SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))),
                 'EXPLAIN_PLAN_CURSOR:           '||SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))),
                 'BUFFERED_DML_MISMATCH:         '||SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))),
                 'PDML_ENV_MISMATCH:             '||SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))),
                 'INST_DRTLD_MISMATCH:           '||SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))),
                 'SLAVE_QC_MISMATCH:             '||SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))),
                 'TYPECHECK_MISMATCH:            '||SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))),
                 'AUTH_CHECK_MISMATCH:           '||SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))),
                 'BIND_MISMATCH:                 '||SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))),
                 'DESCRIBE_MISMATCH:             '||SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))),
                 'LANGUAGE_MISMATCH:             '||SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))),
                 'TRANSLATION_MISMATCH:          '||SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))),
                 'ROW_LEVEL_SEC_MISMATCH:        '||SUM(TO_NUMBER(DECODE(row_level_sec_mismatch,'Y',1,'N','0'))),
                 'INSUFF_PRIVS:                  '||SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))),
                 'INSUFF_PRIVS_REM:              '||SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))),
                 'REMOTE_TRANS_MISMATCH:         '||SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))),
                 'LOGMINER_SESSION_MISMATCH:     '||SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))),
                 'INCOMP_LTRL_MISMATCH:          '||SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))),
                 'OVERLAP_TIME_MISMATCH:         '||SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))),
                 'EDITION_MISMATCH:              '||SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))),
                 'MV_QUERY_GEN_MISMATCH:         '||SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))),
                 'USER_BIND_PEEK_MISMATCH:       '||SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))),
                 'TYPCHK_DEP_MISMATCH:           '||SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))),
                 'NO_TRIGGER_MISMATCH:           '||SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_CURSOR:              '||SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))),
                 'ANYDATA_TRANSFORMATION:        '||SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))),
                 'INCOMPLETE_CURSOR:             '||SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))),
                 'TOP_LEVEL_RPI_CURSOR:          '||SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))),
                 'DIFFERENT_LONG_LENGTH:         '||SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))),
                 'LOGICAL_STANDBY_APPLY:         '||SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))),
                 'DIFF_CALL_DURN:                '||SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))),
                 'BIND_UACS_DIFF:                '||SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))),
                 'PLSQL_CMP_SWITCHS_DIFF:        '||SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))),
                 'CURSOR_PARTS_MISMATCH:         '||SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))),
                 'STB_OBJECT_MISMATCH:           '||SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))),
                 'CROSSEDITION_TRIGGER_MISMATCH: '||SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))),
                 'PQ_SLAVE_MISMATCH:             '||SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))),
                 'TOP_LEVEL_DDL_MISMATCH:        '||SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))),
                 'MULTI_PX_MISMATCH:             '||SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))),
                 'BIND_PEEKED_PQ_MISMATCH:       '||SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))),
                 'MV_REWRITE_MISMATCH:           '||SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))),
                 'ROLL_INVALID_MISMATCH:         '||SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))),
                 'OPTIMIZER_MODE_MISMATCH:       '||SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))),
                 'PX_MISMATCH:                   '||SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))),
                 'MV_STALEOBJ_MISMATCH:          '||SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_TABLE_MISMATCH:      '||SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))),
                 'LITREP_COMP_MISMATCH:          '||SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))),
                 'PLSQL_DEBUG:                   '||SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))),
                 'LOAD_OPTIMIZER_STATS:          '||SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))),
                 'ACL_MISMATCH:                  '||SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_ARCHIVE_MISMATCH:    '||SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))),
                 'LOCK_USER_SCHEMA_FAILED:       '||SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))),
                 'REMOTE_MAPPING_MISMATCH:       '||SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))),
                 'LOAD_RUNTIME_HEAP_FAILED:      '||SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))),
                 'HASH_MATCH_FAILED:             '||SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0')))
          FROM   v$sql_shared_cursor
          WHERE  address IN (SELECT address
                                       FROM   v$sqlarea
                                       WHERE  sql_id = '');

 

Query for 11.2:

     SQL> SET PAGES 0
     SQL> SET HEADING OFF;
     SQL> SELECT 'UNBOUND_CURSOR:                 '||SUM(TO_NUMBER(DECODE(unbound_cursor,'Y',1,'N','0'))),
                 'SQL_TYPE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(sql_type_mismatch,'Y',1,'N','0'))),
                 'OPTIMIZER_MISMATCH:             '||SUM(TO_NUMBER(DECODE(optimizer_mismatch,'Y',1,'N','0'))),
                 'OUTLINE_MISMATCH:               '||SUM(TO_NUMBER(DECODE(outline_mismatch,'Y',1,'N','0'))),
                 'STATS_ROW_MISMATCH:             '||SUM(TO_NUMBER(DECODE(stats_row_mismatch,'Y',1,'N','0'))),
                 'LITERAL_MISMATCH:               '||SUM(TO_NUMBER(DECODE(literal_mismatch,'Y',1,'N','0'))),
                 'FORCE_HARD_PARSE:               '||SUM(TO_NUMBER(DECODE(force_hard_parse,'Y',1,'N','0'))),
                 'EXPLAIN_PLAN_CURSOR:            '||SUM(TO_NUMBER(DECODE(explain_plan_cursor,'Y',1,'N','0'))),
                 'BUFFERED_DML_MISMATCH:          '||SUM(TO_NUMBER(DECODE(buffered_dml_mismatch,'Y',1,'N','0'))),
                 'PDML_ENV_MISMATCH:              '||SUM(TO_NUMBER(DECODE(pdml_env_mismatch,'Y',1,'N','0'))),
                 'INST_DRTLD_MISMATCH:            '||SUM(TO_NUMBER(DECODE(inst_drtld_mismatch,'Y',1,'N','0'))),
                 'SLAVE_QC_MISMATCH:              '||SUM(TO_NUMBER(DECODE(slave_qc_mismatch,'Y',1,'N','0'))),
                 'TYPECHECK_MISMATCH:             '||SUM(TO_NUMBER(DECODE(typecheck_mismatch,'Y',1,'N','0'))),
                 'AUTH_CHECK_MISMATCH:            '||SUM(TO_NUMBER(DECODE(auth_check_mismatch,'Y',1,'N','0'))),
                 'BIND_MISMATCH:                  '||SUM(TO_NUMBER(DECODE(bind_mismatch,'Y',1,'N','0'))),
                 'DESCRIBE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(describe_mismatch,'Y',1,'N','0'))),
                 'LANGUAGE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(language_mismatch,'Y',1,'N','0'))),
                 'TRANSLATION_MISMATCH:           '||SUM(TO_NUMBER(DECODE(translation_mismatch,'Y',1,'N','0'))),
                 'BIND_EQUIV_FAILURE:             '||SUM(TO_NUMBER(DECODE(bind_equiv_failure,'Y',1,'N','0'))),
                 'INSUFF_PRIVS:                   '||SUM(TO_NUMBER(DECODE(insuff_privs,'Y',1,'N','0'))),
                 'INSUFF_PRIVS_REM:               '||SUM(TO_NUMBER(DECODE(insuff_privs_rem,'Y',1,'N','0'))),
                 'REMOTE_TRANS_MISMATCH:          '||SUM(TO_NUMBER(DECODE(remote_trans_mismatch,'Y',1,'N','0'))),
                 'LOGMINER_SESSION_MISMATCH:      '||SUM(TO_NUMBER(DECODE(logminer_session_mismatch,'Y',1,'N','0'))) ,
                 'INCOMP_LTRL_MISMATCH:           '||SUM(TO_NUMBER(DECODE(incomp_ltrl_mismatch,'Y',1,'N','0'))),
                 'OVERLAP_TIME_MISMATCH:          '||SUM(TO_NUMBER(DECODE(overlap_time_mismatch,'Y',1,'N','0'))),
                 'EDITION_MISMATCH:               '||SUM(TO_NUMBER(DECODE(edition_mismatch,'Y',1,'N','0'))),
                 'MV_QUERY_GEN_MISMATCH:          '||SUM(TO_NUMBER(DECODE(mv_query_gen_mismatch,'Y',1,'N','0'))),
                 'USER_BIND_PEEK_MISMATCH:        '||SUM(TO_NUMBER(DECODE(user_bind_peek_mismatch,'Y',1,'N','0'))),
                 'TYPCHK_DEP_MISMATCH:            '||SUM(TO_NUMBER(DECODE(typchk_dep_mismatch,'Y',1,'N','0'))),
                 'NO_TRIGGER_MISMATCH:            '||SUM(TO_NUMBER(DECODE(no_trigger_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_CURSOR:               '||SUM(TO_NUMBER(DECODE(flashback_cursor,'Y',1,'N','0'))),
                 'ANYDATA_TRANSFORMATION:         '||SUM(TO_NUMBER(DECODE(anydata_transformation,'Y',1,'N','0'))),
                 'INCOMPLETE_CURSOR:              '||SUM(TO_NUMBER(DECODE(incomplete_cursor,'Y',1,'N','0'))),
                 'TOP_LEVEL_RPI_CURSOR:           '||SUM(TO_NUMBER(DECODE(top_level_rpi_cursor,'Y',1,'N','0'))),
                 'DIFFERENT_LONG_LENGTH:          '||SUM(TO_NUMBER(DECODE(different_long_length,'Y',1,'N','0'))),
                 'LOGICAL_STANDBY_APPLY:          '||SUM(TO_NUMBER(DECODE(logical_standby_apply,'Y',1,'N','0'))),
                 'DIFF_CALL_DURN:                 '||SUM(TO_NUMBER(DECODE(diff_call_durn,'Y',1,'N','0'))),
                 'BIND_UACS_DIFF:                 '||SUM(TO_NUMBER(DECODE(bind_uacs_diff,'Y',1,'N','0'))),
                 'PLSQL_CMP_SWITCHS_DIFF:         '||SUM(TO_NUMBER(DECODE(plsql_cmp_switchs_diff,'Y',1,'N','0'))),
                 'CURSOR_PARTS_MISMATCH:          '||SUM(TO_NUMBER(DECODE(cursor_parts_mismatch,'Y',1,'N','0'))),
                 'STB_OBJECT_MISMATCH:            '||SUM(TO_NUMBER(DECODE(stb_object_mismatch,'Y',1,'N','0'))),
                 'CROSSEDITION_TRIGGER_MISMATCH : '||SUM(TO_NUMBER(DECODE(crossedition_trigger_mismatch,'Y',1,'N','0'))),
                 'PQ_SLAVE_MISMATCH:              '||SUM(TO_NUMBER(DECODE(pq_slave_mismatch,'Y',1,'N','0'))),
                 'TOP_LEVEL_DDL_MISMATCH:         '||SUM(TO_NUMBER(DECODE(top_level_ddl_mismatch,'Y',1,'N','0'))),
                 'MULTI_PX_MISMATCH:              '||SUM(TO_NUMBER(DECODE(multi_px_mismatch,'Y',1,'N','0'))),
                 'BIND_PEEKED_PQ_MISMATCH:        '||SUM(TO_NUMBER(DECODE(bind_peeked_pq_mismatch,'Y',1,'N','0'))),
                 'MV_REWRITE_MISMATCH:            '||SUM(TO_NUMBER(DECODE(mv_rewrite_mismatch,'Y',1,'N','0'))),
                 'ROLL_INVALID_MISMATCH:          '||SUM(TO_NUMBER(DECODE(roll_invalid_mismatch,'Y',1,'N','0'))),
                 'OPTIMIZER_MODE_MISMATCH:        '||SUM(TO_NUMBER(DECODE(optimizer_mode_mismatch,'Y',1,'N','0'))),
                 'PX_MISMATCH:                    '||SUM(TO_NUMBER(DECODE(px_mismatch,'Y',1,'N','0'))),
                 'MV_STALEOBJ_MISMATCH:           '||SUM(TO_NUMBER(DECODE(mv_staleobj_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_TABLE_MISMATCH:       '||SUM(TO_NUMBER(DECODE(flashback_table_mismatch,'Y',1,'N','0'))),
                 'LITREP_COMP_MISMATCH:           '||SUM(TO_NUMBER(DECODE(litrep_comp_mismatch,'Y',1,'N','0'))),
                 'PLSQL_DEBUG:                    '||SUM(TO_NUMBER(DECODE(plsql_debug,'Y',1,'N','0'))),
                 'LOAD_OPTIMIZER_STATS:           '||SUM(TO_NUMBER(DECODE(load_optimizer_stats,'Y',1,'N','0'))),
                 'ACL_MISMATCH:                   '||SUM(TO_NUMBER(DECODE(acl_mismatch,'Y',1,'N','0'))),
                 'FLASHBACK_ARCHIVE_MISMATCH:     '||SUM(TO_NUMBER(DECODE(flashback_archive_mismatch,'Y',1,'N','0'))),
                 'LOCK_USER_SCHEMA_FAILED:        '||SUM(TO_NUMBER(DECODE(lock_user_schema_failed,'Y',1,'N','0'))),
                 'REMOTE_MAPPING_MISMATCH:        '||SUM(TO_NUMBER(DECODE(remote_mapping_mismatch,'Y',1,'N','0'))),
                 'LOAD_RUNTIME_HEAP_FAILED:       '||SUM(TO_NUMBER(DECODE(load_runtime_heap_failed,'Y',1,'N','0'))),
                 'HASH_MATCH_FAILED:              '||SUM(TO_NUMBER(DECODE(hash_match_failed,'Y',1,'N','0'))),
                 'PURGED_CURSOR:                  '||SUM(TO_NUMBER(DECODE(purged_cursor,'Y',1,'N','0'))),
                 'BIND_LENGTH_UPGRADEABLE:        '||SUM(TO_NUMBER(DECODE(bind_length_upgradeable,'Y',1,'N','0')))
          FROM   v$sql_shared_cursor
          WHERE  address IN (SELECT address
                             FROM   v$sqlarea
                             WHERE  sql_id = '');

 

Example of output:

 

        UNBOUND_CURSOR:                 0
        SQL_TYPE_MISMATCH:              0
        OPTIMIZER_MISMATCH:             0
        OUTLINE_MISMATCH:               0
        STATS_ROW_MISMATCH:             0
        LITERAL_MISMATCH:               0
        FORCE_HARD_PARSE:               0
        EXPLAIN_PLAN_CURSOR:            0
        BUFFERED_DML_MISMATCH:          0
        PDML_ENV_MISMATCH:              0
        INST_DRTLD_MISMATCH:            0
        SLAVE_QC_MISMATCH:              0
        TYPECHECK_MISMATCH:             0
        AUTH_CHECK_MISMATCH:            0
        BIND_MISMATCH:                  0
        DESCRIBE_MISMATCH:              0
        LANGUAGE_MISMATCH:              0
        TRANSLATION_MISMATCH:           0
        BIND_EQUIV_FAILURE:             0
        INSUFF_PRIVS:                   0
        INSUFF_PRIVS_REM:               0
        REMOTE_TRANS_MISMATCH:          0
        LOGMINER_SESSION_MISMATCH:      0
        INCOMP_LTRL_MISMATCH:           0
        OVERLAP_TIME_MISMATCH:          0
        EDITION_MISMATCH:               0
        MV_QUERY_GEN_MISMATCH:          0
        USER_BIND_PEEK_MISMATCH:        0
        TYPCHK_DEP_MISMATCH:            0
        NO_TRIGGER_MISMATCH:            0
        FLASHBACK_CURSOR:               0
        ANYDATA_TRANSFORMATION:         0
        INCOMPLETE_CURSOR:              0
        TOP_LEVEL_RPI_CURSOR:           0
        DIFFERENT_LONG_LENGTH:          0
        LOGICAL_STANDBY_APPLY:          0
        DIFF_CALL_DURN:                 0
        BIND_UACS_DIFF:                 0
        PLSQL_CMP_SWITCHS_DIFF:         0
        CURSOR_PARTS_MISMATCH:          0
        STB_OBJECT_MISMATCH:            0
        CROSSEDITION_TRIGGER_MISMATCH : 0
        PQ_SLAVE_MISMATCH:              0
        TOP_LEVEL_DDL_MISMATCH:         476
        MULTI_PX_MISMATCH:              0
        BIND_PEEKED_PQ_MISMATCH:        0
        MV_REWRITE_MISMATCH:            0
        ROLL_INVALID_MISMATCH:          0
        OPTIMIZER_MODE_MISMATCH:        0
        PX_MISMATCH:                    0
        MV_STALEOBJ_MISMATCH:           0
        FLASHBACK_TABLE_MISMATCH:       0
        LITREP_COMP_MISMATCH:           0
        PLSQL_DEBUG:                    0
        LOAD_OPTIMIZER_STATS:           0
        ACL_MISMATCH:                   0
        FLASHBACK_ARCHIVE_MISMATCH:     0
        LOCK_USER_SCHEMA_FAILED:        0
        REMOTE_MAPPING_MISMATCH:        0
        LOAD_RUNTIME_HEAP_FAILED:       0
        HASH_MATCH_FAILED:              0
        PURGED_CURSOR:                  0
        BIND_LENGTH_UPGRADEABLE:        0


This shows that the cursor is not shared because of TOP_LEVEL_DDL_MISMATCH which means is a top DDL-statement and are expected behavior.

Conclusions and Summary of bugs due to reason code:

Look for the reason code you have identified by the above queries, check the affected version and use
below table to identify what defects are potential to hit:

Reason CodeBugDescriptionConfirmed Affected VersionsFixedSupport Article
BIND_MISMATCHBug:5705795MANY CHILD CURSORS CREATED FOR HIGH WORKLOAD IN 10.2.0.310.2.0.310.2.0.4
11.1.0.7		Note:416727.1
PQ_SLAVE_MISMATCHBug:6981690Cursor not shared when running PX query on mounted RAC system10.2.0.3
10.2.0.4
11.1.0.7		10.2.0.4.4 (PSU)
10.2.0.5
11.1.0.7.1 (PSU)
11.2.0.1		Note:760777.1
AUTH_CHECK_MISMATCH and LANGUAGE_MISMATCHBug:7648406CHILD CURSOR IS NOT SHARED IF SET NLS_LENGTH_SEMANTICS=CHAR.10.2.0.3
10.2.0.4
11.1.0.7		10.2.0.5
11.1.0.7.4 (PSU)
11.2.0.1		Note:783120.1
USER_BIND_PEEK_MISMATCHBug:8981059High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking10.2.0.4
11.1.0.7
 11.2.0.1		11.2.0.1.2 (PSU)
11.2.0.2
12.1		Note:968930.1
AUTH_CHECK_MISMATCH and INSUFF_PRIVS_REMUnpublished Bug:8922013ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434]10.2.0.4
10.2.0.5		 Note:973149.1
PX_MISMATCHUnpublished
Bug:9226905		STPA - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KKFDPAPRM]11.1.0.7
11.2.0.1		11.2.0.2Note:1340558.1
BIND_MISMATCHBug:9689310SPORADIC BUNCHES OF ORA-600 [17059]10.2.0.4
10.2.0.5
11.1.0.7
11.2.0.1		11.1.0.7.7 (PSU)
11.2.0.2
12.1		Note:9689310.8
INST_DRTLD_MISMATCH
Bug:10151017MERGE STATEMENT NOT BEING SHARED UNDER SPECIFIC CIRCUMSTANCES11.1.0.7
11.2.0.1
11.2.0.2		11.2.0.2.1 (PSU)
11.2.0.3
12.1		Note:1365227.1
AUTH_CHECK_MISMATCH Bug:12320556HIGH VERSION COUNTS OCCUR DUE TO AUTH_CHECK_MISMATCH, INSUFF_PRIVS_REM=Y11.1.0.7
11.2.0.2		12.1Note:12320556.8



For deeper analyze of of non-sharable cursors please read Note:438755.1, Formatted V$SQL_SHARED_CURSOR Report by SQLID or Hash Value.

References

BUG:10151017 - MERGE STATEMENT NOT BEING SHARED UNDER SPECIFIC CIRCUMSTANCES
BUG:12320556 - HIGH VERSION COUNTS OCCUR DUE TO AUTH_CHECK_MISMATCH, INSUFF_PRIVS_REM=Y
BUG:5705795 - MANY CHILD CURSORS CREATED FOR HIGH WORKLOAD IN 10.2.0.3
BUG:6981690 - CURSOR NOT SHARED WHEN RUNNING PX QUERY ON MOUNTED RAC SYSTEM
BUG:7648406 - CHILD CURSOR IS NOT SHARED IF SET NLS_LENGTH_SEMANTICS=CHAR.
@ BUG:9226905 - STPA - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KKFDPAPRM]
BUG:9689310 - SPORADIC BUNCHES OF ORA-600 [17059]
NOTE:12320556.8 - Bug 12320556 - High version count for child cursors referencing a remote object due to AUTH_CHECK_MISMATCH
@ BUG:8922013 - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434]
BUG:8981059 - HIGH VERSION COUNT:BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,OPTIMIZER_MODE_MISMATCH
NOTE:1340558.1 - RMAN Backup Can Fail with ORA-600 [17059]
NOTE:1365227.1 - ORA-600 [17059] Error During Upgrade from 10.2 to 11.2.0.1
NOTE:138554.1 - ORA-600 [17059]
NOTE:416727.1 - Frequent ORA-600 [17059] from Grid Control
NOTE:438755.1 - High SQL Version Counts - Script. to determine reason(s)
NOTE:760777.1 - Child Cursors For The Pq_slave_mismatch Queries.
NOTE:783120.1 - High cursor version count when NLS_LENGTH_SEMANTICS=CHAR Can Lead To ORA-4031 or ORA-600[17059] Errors
NOTE:968930.1 - ORA-00600 [17059] And High BIND_MISMATCH, USER_BIND_PEEK_MISMATCH, OPTIMIZER_MODE_MISMATCH Counts When Running Batch Job
NOTE:9689310.8 - Bug 9689310 - Excessive child cursors / high VERSION_COUNT / OERI:17059 due to bind mismatch
NOTE:973149.1 - Select Fails With ORA-600 [17059]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38267/viewspace-732177/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38267/viewspace-732177/

cuhongyang4157
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle 2个以上游标,诊断Oracle high version count(高版本游标)问题
weixin_39828198的博客
04-03 392
为了查看子游标:对于Oracle 9.2.x.x及以下版本SQL>select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58';对于oracle 10.0.x.x及以上版本SQL> select * from v$sql_shared_cursor where address = '0000000386BC2...
v$sql_shared_cursor 参数说明
weixin_42344665的博客
07-17 2456
V$SQL_SHARED_CURSOR解释了为什么不与现有子游标共享特定子游标的原因。每列标识了无法共享游标的具体原因。 柱 数据类型 描述 SQL_ID VARCHAR2(13) SQL标识符 ADDRESS RAW(4 | 8) 父光标的地址 CHILD_ADDRESS RAW(4 | 8) 子光标的地址 CHILD_NUMBE...
V$SQL_SHARED_CURSOR查看硬解析的原因
cltt980248285的博客
12-17 458
在查看ADDM报告的时候，有个改善意见如下: Finding 2: Hard Parse Due to Sharing Criteria Impact is .13 active sessions, 30.49% of tota...
oracle+query+high,关于High Versions Count总结
weixin_34246529的博客
04-03 241
High Versions Count后果sql查询–>hash对比确定是否存在shared pool中(不讨论不存在情况)–>选择合适的children可能导致library cache latch contentionWhen you have unnecessary versions of a cursor, each time that cursor is executed,t...
诊断High Number Of Child Cursors 导致的ORA-600[17059]
沉默_Cloud
05-13 837
[ID 1213715.1] 对于ORA-600[17059]和ORA-4031都有参考价值 1. 查找具有高version count的sql  SELECT version_count, sql_id, sql_text            FROM   v$sqlarea           WHERE  version_count >           ORDER BY
Cute Cursors - Custom Cursor for-2.0.6.zip
最新发布
12-30
名称：Cute Cursors - Custom Cursor for ---------------------------------------- 版本：2.0.6 作者：https://cursorstore.com/ 分类：网页增强 ---------------------------------------- 概述：这是一款非常...
Custom Cursors(替换光标)-4.1.0.zip
12-30
名称：Custom Cursors(替换光标) ---------------------------------------- 版本：4.1.0 作者：cute-cursors.com 分类：网页增强 ---------------------------------------- 概述：用可爱，有趣和新潮的东西替换...
How to diagnose the high CPU utilization of ORACLE.EXE in Windows environment
01-12
7. **数据库参数调优**：优化数据库参数，如打开的游标数（OPEN_CURSORS）、并行执行服务器数（PARALLEL_SERVERS_ENABLED）等，以降低CPU使用。调整这些参数需要谨慎，以防止引入新的性能问题。 8. **索引和查询...
How To Use REF Cursors in JDBC Program
12-30
How To Use REF Cursors in JDBC Program
Cute Cursors - Big Collection Cursors-crx插件
04-04
语言:Bahasa Indonesia,Bahasa Melayu,Deutsch,English,English (UK),English (United States),Filipino,Français,Kiswahili,Nederlands,Norsk,Tiếng Việt,Türkçe,català,dansk,eesti,español,hrvatski,...
@dblink不共享产生的高version_count消耗内存事件(bug8922013)
N
08-24 5069
1.问题,高version_count,后台大量library cache lock, latch:shared pool等待事件，客户反应系统慢 2.bug8922013 Bug 8922013  OERI [17059] / excess child cursors for SQL referencing REMOTE objects  This note gives a bri
性能视图和性能参数
RuleV5的专栏
12-19 2446
性能视图和性能参数 一．性能视图     性能视图是Oracle中一些记录数据库性能方面的视图，通过查看这些视图，获得数据库当前或历史上某个时间的性能数据。 它比SQL_TRACE，AWR报告获取数据更及时，便捷。 1.1 V$SQL     V$SQL 视图是一个DBA 使用频率非常高的动态视图，它通常和V$SESSION 一起使用来获得当前会话的一些SQL执行情况。可以通过该视图查看正
bind变量的长度不同导致多个chlid cursor
weiwang的专栏
12-01 2420
bind变量的长度不同导致多个chlid cursor 测试如下： SQL> create table test (a varchar2(4000)); 表已创建。 SQL> declare   2  instring varchar2(4000);   3    begin   4     for i in 1..2001 loop   5       instring
ORA-600(17059)错误
ckawt40802的专栏
07-14 382
客户10.2.0.4 RAC出现大量的ORA-600[17059]错误。 [@more@] 错误信息如下： Tue May 21 09:55:30 2013 Errors in ...
简单判断sql解析类型
数据库天地
12-19 289
我们知道sql解析可以分为软解析和硬解析，（我去，这都不知道百度去），由于硬解析和会消耗大小的性能，特别是对于oltp系统来说。 怎么判断解析类型呢，一般想到10046事件，但是不要完全信10046事件，原因是是10046sql_trace 在trace session的时候，默认session执行的每个SQL都将从新进行hard parse,产生一个新的child cursor,自然，这个被...
Ora-600 [17059]
csdn201420142015的博客
05-08 242
前一段时间碰到的问题.环境：ＤＢ：Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64biＯＳ　SunOS 5.10 Generic_127111-11 s...
window多个oracle查版本,Oracle SQL多版本VERSION_COUNT产生的可能性
weixin_29556943的博客
04-03 422
Oracle SQL多版本VERSION_COUNT产生的可能性Oracle数据库中执行的SQL，很多时候会因为种种原因产生多个不同的执行版本，一个游标的版本过多很容易引起数据库的性能问题，甚至故障。有时候一个SQL的版本数量可能多达数万个，以下是我之前在恩墨讲堂分享过的一个案例。产生SQL多版本的原因很多，通过如下的一些测试我们可以稍微来看看如何分析和找到可能的原因。以下作为一个基础测试数据，一...
oracle cursor不能共享原因及示例
还不算晕的专栏
01-26 1888
1. cursor概念介绍 cursor游标,是ORACLE数据库中SQL解析和执行的载体，本质上是C语言中的一种结构structure. ORACLE数据库中cursor分类：shared cursor包括：parent cursor、child cursor session cursor包括： 隐式游标implicit cursor、 显式游标explicit cursor、 参考...
oracle父游标和子游标,叶摇 » Blog Archive » oracle 父游标、子游标分析
weixin_28864139的博客
04-02 259
oracle 父游标、子游标分析Troubleshooting: High Version Count Issues [ID 296377.1]High Version Count with CURSOR_SHARING = SIMILAR or FORCE [ID 261020.1]What is shared SQL ?When a SQL statement is entered, the ...
ora-01000如何解决游标数
05-31
要解决ORA-01000错误，您可以采取以下措施： 1. 关闭不需要的游标: 在您的应用程序中，确保在使用完游标后及时关闭它们，以释放资源并避免达到最大游标数。 2. 提高最大游标数限制: 您可以通过在Oracle数据库中...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值