ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How To Identify High Number Of Child Cursors Leading To ORA-600 [17059]

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

原创 Linux操作系统 作者:spider0283 时间:2012-06-07 17:35:31 0 删除 编辑
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_MISMATCH Bug:5705795 MANY CHILD CURSORS CREATED FOR HIGH WORKLOAD IN 10.2.0.3 10.2.0.3 10.2.0.4
11.1.0.7
Note:416727.1
PQ_SLAVE_MISMATCH Bug:6981690 Cursor not shared when running PX query on mounted RAC system 10.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_MISMATCH Bug:7648406 CHILD 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_MISMATCH Bug:8981059 High Version Count (due to USER_BIND_PEEK_MISMATCH) with bind peeking 10.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_REM Unpublished Bug:8922013 ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [17059], [0X93953434] 10.2.0.4
10.2.0.5
  Note:973149.1
PX_MISMATCH Unpublished
Bug:9226905
STPA - ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KKFDPAPRM] 11.1.0.7
11.2.0.1
11.2.0.2 Note:1340558.1
BIND_MISMATCH Bug:9689310 SPORADIC 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:10151017 MERGE STATEMENT NOT BEING SHARED UNDER SPECIFIC CIRCUMSTANCES 11.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:12320556 HIGH VERSION COUNTS OCCUR DUE TO AUTH_CHECK_MISMATCH, INSUFF_PRIVS_REM=Y 11.1.0.7
11.2.0.2
12.1 Note: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]
Back to TopBack to Top

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

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    609370