sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle性能优化之统计指定SQL没有共享的原因

时间:2016-10-27 10:36   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库SQL语句性能优化文章,该脚本可以统计出指定SQL语句没有共享的原因。
下面脚本包括10G,11GR1,11GR2版本

[oracle@oracleplus.net sql]$cat sql_shared_cursor102.sql
set echo off
set verify off
SET PAGES 0
SET HEADING OFF;
set lines 100
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 = ‘&sqlid’);
set heading on
set lines 200
undefine sqlid
set echo on[oracle@oracleplus.net sql]$cat sql_shared_cursor111.sql
set echo off
set verify off
SET PAGES 0
SET HEADING OFF;
set lines 100
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 = ‘&sqlid’);
set heading on
set lines 200
undefine sqlid
set echo on[oracle@oracleplus.net sql]$cat sql_shared_cursor112.sql
set echo off
set verify off
SET PAGES 0
SET HEADING OFF;
set lines 100
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′))),
— NOTE: Next column only for 11.2.0.1
– Please refer to http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3059.htm#REFRN30254
‘INCOMPLETE_CURSOR: ‘||SUM(TO_NUMBER(DECODE(incomplete_cursor,’Y’,1,’N’,’0′))),
— NOTE: Next column only for 11.2.0.2 and above
– Please refer to http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3059.htm#REFRN30254
‘PDDL_ENV_MISMATCH: ‘||SUM(TO_NUMBER(DECODE(pddl_env_mismatch,’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(DECODhttp://www.oracleplus.netE(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 = ‘&sqlid’);
set heading on
set lines 200
undefine sqlid
set echo on
[oracle@oracleplus.net sql]$cat sql_shared_cursor1121.sql
set echo off
set verify off
SET PAGES 0
SET HEADING OFF;
set lines 100
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′))),
— NOTE: Next column only for 11.2.0.1
– Please refer to http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3059.htm#REFRN30254
‘PDDL_ENV_MISMATCH: ‘||SUM(TO_NUMBER(DECODE(pddl_env_mismatch,’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 = ‘&sqlid’);
set heading on
set lines 200
undefine sqlid

本文固定链接: http://www.htz.pw/2014/07/07/%e5%b8%b8%e7%94%a8%e8%84%9a%e6%9c%ac6%ef%bc%9a%e7%bb%9f%e8%ae%a1%e6%8c%87%e5%ae%9asql%e6%b2%a1%e6%9c%89%e5%85%b1%e4%ba%ab%e7%9a%84%e5%8e%9f%e5%9b%a0.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle性能优化之统计指定SQL没有共享的原因

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1081.html

Oracle研究中心

关键词:

统计Oracle指定SQL没有共享的原因

Oracle SQL语句共享优化