Shows the reason for multiple statement childs in shared pool
来源:互联网 发布:一页知秋 百川鱼海 编辑:程序博客网 时间:2024/04/27 10:20
rem output only those statements that have more than 5 versions:
define versions=5
select version_count,address,hash_value,parsing_schema_name,reason,sql_text from (
select
address,''
||decode(max( UNBOUND_CURSOR),'Y', ' UNBOUND_CURSOR')
||decode(max( SQL_TYPE_MISMATCH),'Y', ' SQL_TYPE_MISMATCH')
||decode(max( OPTIMIZER_MISMATCH),'Y', ' OPTIMIZER_MISMATCH')
||decode(max( OUTLINE_MISMATCH),'Y', ' OUTLINE_MISMATCH')
||decode(max( STATS_ROW_MISMATCH),'Y', ' STATS_ROW_MISMATCH')
||decode(max( LITERAL_MISMATCH),'Y', ' LITERAL_MISMATCH')
||decode(max( SEC_DEPTH_MISMATCH),'Y', ' SEC_DEPTH_MISMATCH')
||decode(max( EXPLAIN_PLAN_CURSOR),'Y', ' EXPLAIN_PLAN_CURSOR')
||decode(max( BUFFERED_DML_MISMATCH),'Y', ' BUFFERED_DML_MISMATCH')
||decode(max( PDML_ENV_MISMATCH),'Y', ' PDML_ENV_MISMATCH')
||decode(max( INST_DRTLD_MISMATCH),'Y', ' INST_DRTLD_MISMATCH')
||decode(max( SLAVE_QC_MISMATCH),'Y', ' SLAVE_QC_MISMATCH')
||decode(max( TYPECHECK_MISMATCH),'Y', ' TYPECHECK_MISMATCH')
||decode(max( AUTH_CHECK_MISMATCH),'Y', ' AUTH_CHECK_MISMATCH')
||decode(max( BIND_MISMATCH),'Y', ' BIND_MISMATCH')
||decode(max( DESCRIBE_MISMATCH),'Y', ' DESCRIBE_MISMATCH')
||decode(max( LANGUAGE_MISMATCH),'Y', ' LANGUAGE_MISMATCH')
||decode(max( TRANSLATION_MISMATCH),'Y', ' TRANSLATION_MISMATCH')
||decode(max( ROW_LEVEL_SEC_MISMATCH),'Y', ' ROW_LEVEL_SEC_MISMATCH')
||decode(max( INSUFF_PRIVS),'Y', ' INSUFF_PRIVS')
||decode(max( INSUFF_PRIVS_REM),'Y', ' INSUFF_PRIVS_REM')
||decode(max( REMOTE_TRANS_MISMATCH),'Y', ' REMOTE_TRANS_MISMATCH')
||decode(max( LOGMINER_SESSION_MISMATCH),'Y', ' LOGMINER_SESSION_MISMATCH')
||decode(max( INCOMP_LTRL_MISMATCH),'Y', ' INCOMP_LTRL_MISMATCH')
||decode(max( OVERLAP_TIME_MISMATCH),'Y', ' OVERLAP_TIME_MISMATCH')
||decode(max( SQL_REDIRECT_MISMATCH),'Y', ' SQL_REDIRECT_MISMATCH')
||decode(max( MV_QUERY_GEN_MISMATCH),'Y', ' MV_QUERY_GEN_MISMATCH')
||decode(max( USER_BIND_PEEK_MISMATCH),'Y', ' USER_BIND_PEEK_MISMATCH')
||decode(max( TYPCHK_DEP_MISMATCH),'Y', ' TYPCHK_DEP_MISMATCH')
||decode(max( NO_TRIGGER_MISMATCH),'Y', ' NO_TRIGGER_MISMATCH')
||decode(max( FLASHBACK_CURSOR),'Y', ' FLASHBACK_CURSOR')
||decode(max( ANYDATA_TRANSFORMATION),'Y', ' ANYDATA_TRANSFORMATION')
||decode(max( INCOMPLETE_CURSOR),'Y', ' INCOMPLETE_CURSOR')
||decode(max( TOP_LEVEL_RPI_CURSOR),'Y', ' TOP_LEVEL_RPI_CURSOR')
||decode(max( DIFFERENT_LONG_LENGTH),'Y', ' DIFFERENT_LONG_LENGTH')
||decode(max( LOGICAL_STANDBY_APPLY),'Y', ' LOGICAL_STANDBY_APPLY')
||decode(max( DIFF_CALL_DURN),'Y', ' DIFF_CALL_DURN')
||decode(max( BIND_UACS_DIFF),'Y', ' BIND_UACS_DIFF')
||decode(max( PLSQL_CMP_SWITCHS_DIFF),'Y', ' PLSQL_CMP_SWITCHS_DIFF')
||decode(max( CURSOR_PARTS_MISMATCH),'Y', ' CURSOR_PARTS_MISMATCH')
||decode(max( STB_OBJECT_MISMATCH),'Y', ' STB_OBJECT_MISMATCH')
||decode(max( ROW_SHIP_MISMATCH),'Y', ' ROW_SHIP_MISMATCH')
||decode(max( PQ_SLAVE_MISMATCH),'Y', ' PQ_SLAVE_MISMATCH')
||decode(max( TOP_LEVEL_DDL_MISMATCH),'Y', ' TOP_LEVEL_DDL_MISMATCH')
||decode(max( MULTI_PX_MISMATCH),'Y', ' MULTI_PX_MISMATCH')
||decode(max( BIND_PEEKED_PQ_MISMATCH),'Y', ' BIND_PEEKED_PQ_MISMATCH')
||decode(max( MV_REWRITE_MISMATCH),'Y', ' MV_REWRITE_MISMATCH')
||decode(max( ROLL_INVALID_MISMATCH),'Y', ' ROLL_INVALID_MISMATCH')
||decode(max( OPTIMIZER_MODE_MISMATCH),'Y', ' OPTIMIZER_MODE_MISMATCH')
||decode(max( PX_MISMATCH),'Y', ' PX_MISMATCH')
||decode(max( MV_STALEOBJ_MISMATCH),'Y', ' MV_STALEOBJ_MISMATCH')
||decode(max( FLASHBACK_TABLE_MISMATCH),'Y', ' FLASHBACK_TABLE_MISMATCH')
||decode(max( LITREP_COMP_MISMATCH),'Y', ' LITREP_COMP_MISMATCH')
reason
from v$sql_shared_cursor
group by address
) join v$sqlarea using(address) where version_count>&versions
order by version_count desc,address
;
______________________________________________________________
_____________________________________________________________-
VERSION_COUNT ADDRESS HASH_VALUE PARSING_SCHEMA_NAME
------------- -------- ---------- ------------------------------
REASON
----------------------------------------------------------------
SQL_TEXT
----------------------------------------------------------------
2 6C1CD4C8 4033563115 FRANCK
BIND_MISMATCH
select /* FRANCK */ * from dual where :a = :a
that was a test with same statement but different datatype for the bind variable (see http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=28085#94185)
We can see 2 versions with 'BIND_MISMATCH'
- Shows the reason for multiple statement childs in shared pool
- the for statement in python
- Tuning the shared pool
- Allocationand Reuse of Memory in the Shared Pool
- Oracle Concepts - Guidelines for Tuning the Oracle Shared Pool
- Oracle tuning the shared pool
- Tuning the Shared pool(1)
- Tuning the Shared pool(2)
- Tuning the Shared pool(3)
- Tuning the Shared pool(4)
- Getting Contiguous Space Currently in the Shared Pool(Oracle10g Tuning Technology)
- How to Pin a Cursor in the Shared Pool using DBMS_SHARED_POOL.KEEP (文档 ID 726780.1)
- Gathering Initial Troubleshooting Information for Analysis of ORA-4031 Errors on the Shared Pool
- JavaScript For...In Statement
- for/in statement
- about the for statement
- The Enhanced 'for' Statement
- Shared pool
- GridView 72般绝技
- 可重入性
- 正则表达式30分钟入门教程(第二版)
- Maintaining treeview state across your site in a master page, and no expandcollapse icons.
- Windows Vista Activation And OEM Info Version v2.1.2.1.1
- Shows the reason for multiple statement childs in shared pool
- 小猫说话叫自己阿贵 专家称条件反射所致(视频)
- 调用EJB时出现错误
- 程序设计: 猫大叫一声,所有的老鼠都开始逃跑,主人被惊醒。(C#语言)
- WEB2.0概念诠释
- 让侦测工具把壳识别为VC++
- 3个脱壳相关的重要函数介绍
- 表格隔行换色
- 核心瓶颈