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 laterInformation 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 :
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:
------------- -------------------------
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 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 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 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:
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 Code | Bug | Description | Confirmed Affected Versions | Fixed | Support 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 CIRCUMSTANCESBUG: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/