Aşağıdaki sorgu yardımı ile,
belirlediğimiz bir sql'in sqlid ni girerek, SGA içerisindeki
davranış detayını, oluşturduğu child tipini görebilirsiniz.
P.S: Neden ihtiyaç duyabiliriz ?
P.S.S: Pek çok kez sql detayını
incelediğimizde bir sql'in pek çok childinin oluştuğunu
görebiliriz. Bunların hangi type yapısınıda olduğunun detayını
bize verir. Ve buna göre aksiyon alabiliriz.
select
sql_id,sebep reason,sum(decode(sorun,'Y',1,0))
adet from
(
select
sql_id,
UNBOUND_CURSOR
,
SQL_TYPE_MISMATCH
,
OPTIMIZER_MISMATCH
,
OUTLINE_MISMATCH
,
STATS_ROW_MISMATCH
,
LITERAL_MISMATCH
,
FORCE_HARD_PARSE
,
EXPLAIN_PLAN_CURSOR
,
BUFFERED_DML_MISMATCH
,
PDML_ENV_MISMATCH
,
INST_DRTLD_MISMATCH
,
SLAVE_QC_MISMATCH
,
TYPECHECK_MISMATCH
,
AUTH_CHECK_MISMATCH
,
BIND_MISMATCH
,
DESCRIBE_MISMATCH
,
LANGUAGE_MISMATCH
,
TRANSLATION_MISMATCH
,
BIND_EQUIV_FAILURE
,
INSUFF_PRIVS
,
INSUFF_PRIVS_REM
,
REMOTE_TRANS_MISMATCH
,
LOGMINER_SESSION_MISMATCH
,
INCOMP_LTRL_MISMATCH
,
OVERLAP_TIME_MISMATCH
,
EDITION_MISMATCH
,
MV_QUERY_GEN_MISMATCH
,
USER_BIND_PEEK_MISMATCH
,
TYPCHK_DEP_MISMATCH
,
NO_TRIGGER_MISMATCH
,
FLasHBACK_CURSOR
,
ANYDATA_TRANSFORMATION
,
PDDL_ENV_MISMATCH
,
TOP_LEVEL_RPI_CURSOR
,
DIFFERENT_LONG_LENGTH
,
LOGICAL_STANDBY_APPLY
,
DIFF_CALL_DURN
,
BIND_UACS_DIFF
,
PLSQL_CMP_SWITCHS_DIFF
,
CURSOR_PARTS_MISMATCH
,
STB_OBJECT_MISMATCH
,
CROSSEDITION_TRIGGER_MISMATCH
,
PQ_SLAVE_MISMATCH
,
TOP_LEVEL_DDL_MISMATCH
,
MULTI_PX_MISMATCH
,
BIND_PEEKED_PQ_MISMATCH
,
MV_REWRITE_MISMATCH
,
ROLL_INVALID_MISMATCH
,
OPTIMIZER_MODE_MISMATCH
,
PX_MISMATCH
,
MV_STALEOBJ_MISMATCH
,
FLasHBACK_TABLE_MISMATCH
,
LITREP_COMP_MISMATCH
,
PLSQL_DEBUG
,
LOAD_OPTIMIZER_STATS
,
ACL_MISMATCH
,
FLasHBACK_ARCHIVE_MISMATCH
,
LOCK_USER_SCHEMA_FAILED
,
REMOTE_MAPPING_MISMATCH
,
LOAD_RUNTIME_HEAP_FAILED
,
HasH_MATCH_FAILED
,
PURGED_CURSOR
,
BIND_LENGTH_UPGRADEABLE
,
USE_FEEDBACK_STATS
from
v$sql_shared_cursor where
sql_id='&sql_id')
unpivot
(sorun for
sebep in
(
UNBOUND_CURSOR
as
'UNBOUND_CURSOR',
SQL_TYPE_MISMATCH
as
'SQL_TYPE_MISMATCH',
OPTIMIZER_MISMATCH
as
'OPTIMIZER_MISMATCH',
OUTLINE_MISMATCH
as
'OUTLINE_MISMATCH',
STATS_ROW_MISMATCH
as
'STATS_ROW_MISMATCH',
LITERAL_MISMATCH
as
'LITERAL_MISMATCH',
FORCE_HARD_PARSE
as
'FORCE_HARD_PARSE',
EXPLAIN_PLAN_CURSOR
as
'EXPLAIN_PLAN_CURSOR',
BUFFERED_DML_MISMATCH
as
'BUFFERED_DML_MISMATCH',
PDML_ENV_MISMATCH
as
'PDML_ENV_MISMATCH',
INST_DRTLD_MISMATCH
as
'INST_DRTLD_MISMATCH',
SLAVE_QC_MISMATCH
as
'SLAVE_QC_MISMATCH',
TYPECHECK_MISMATCH
as
'TYPECHECK_MISMATCH',
AUTH_CHECK_MISMATCH
as
'AUTH_CHECK_MISMATCH',
BIND_MISMATCH
as
'BIND_MISMATCH',
DESCRIBE_MISMATCH
as
'DESCRIBE_MISMATCH',
LANGUAGE_MISMATCH
as
'LANGUAGE_MISMATCH',
TRANSLATION_MISMATCH
as
'TRANSLATION_MISMATCH',
BIND_EQUIV_FAILURE
as
'BIND_EQUIV_FAILURE',
INSUFF_PRIVS
as
'INSUFF_PRIVS',
INSUFF_PRIVS_REM
as
'INSUFF_PRIVS_REM',
REMOTE_TRANS_MISMATCH
as
'REMOTE_TRANS_MISMATCH',
LOGMINER_SESSION_MISMATCH
as
'LOGMINER_SESSION_MISMATCH',
INCOMP_LTRL_MISMATCH
as
'INCOMP_LTRL_MISMATCH',
OVERLAP_TIME_MISMATCH
as
'OVERLAP_TIME_MISMATCH',
EDITION_MISMATCH
as
'EDITION_MISMATCH',
MV_QUERY_GEN_MISMATCH
as
'MV_QUERY_GEN_MISMATCH',
USER_BIND_PEEK_MISMATCH
as
'USER_BIND_PEEK_MISMATCH',
TYPCHK_DEP_MISMATCH
as
'TYPCHK_DEP_MISMATCH',
NO_TRIGGER_MISMATCH
as
'NO_TRIGGER_MISMATCH',
FLASHBACK_CURSOR
as
'FLASHBACK_CURSOR',
ANYDATA_TRANSFORMATION
as
'ANYDATA_TRANSFORMATION',
PDDL_ENV_MISMATCH
as
'PDDL_ENV_MISMATCH',
TOP_LEVEL_RPI_CURSOR
as
'TOP_LEVEL_RPI_CURSOR',
DIFFERENT_LONG_LENGTH
as
'DIFFERENT_LONG_LENGTH',
LOGICAL_STANDBY_APPLY
as
'LOGICAL_STANDBY_APPLY',
DIFF_CALL_DURN
as
'DIFF_CALL_DURN',
BIND_UACS_DIFF
as
'BIND_UACS_DIFF',
PLSQL_CMP_SWITCHS_DIFF
as
'PLSQL_CMP_SWITCHS_DIFF',
CURSOR_PARTS_MISMATCH
as
'CURSOR_PARTS_MISMATCH',
STB_OBJECT_MISMATCH
as
'STB_OBJECT_MISMATCH',
CROSSEDITION_TRIGGER_MISMATCH
as
'CROSSEDITION_TRIGGER_MISMATCH',
PQ_SLAVE_MISMATCH
as
'PQ_SLAVE_MISMATCH',
TOP_LEVEL_DDL_MISMATCH
as
'TOP_LEVEL_DDL_MISMATCH',
MULTI_PX_MISMATCH
as
'MULTI_PX_MISMATCH',
BIND_PEEKED_PQ_MISMATCH
as
'BIND_PEEKED_PQ_MISMATCH',
MV_REWRITE_MISMATCH
as
'MV_REWRITE_MISMATCH',
ROLL_INVALID_MISMATCH
as
'ROLL_INVALID_MISMATCH',
OPTIMIZER_MODE_MISMATCH
as
'OPTIMIZER_MODE_MISMATCH',
PX_MISMATCH
as
'PX_MISMATCH',
MV_STALEOBJ_MISMATCH
as
'MV_STALEOBJ_MISMATCH',
FLASHBACK_TABLE_MISMATCH
as
'FLASHBACK_TABLE_MISMATCH',
LITREP_COMP_MISMATCH
as
'LITREP_COMP_MISMATCH',
PLSQL_DEBUG
as
'PLSQL_DEBUG',
LOAD_OPTIMIZER_STATS
as
'LOAD_OPTIMIZER_STATS',
ACL_MISMATCH
as
'ACL_MISMATCH',
FLASHBACK_ARCHIVE_MISMATCH
as
'FLASHBACK_ARCHIVE_MISMATCH',
LOCK_USER_SCHEMA_FAILED
as
'LOCK_USER_SCHEMA_FAILED',
REMOTE_MAPPING_MISMATCH
as
'REMOTE_MAPPING_MISMATCH',
LOAD_RUNTIME_HEAP_FAILED
as
'LOAD_RUNTIME_HEAP_FAILED',
HASH_MATCH_FAILED
as
'HASH_MATCH_FAILED',
PURGED_CURSOR
as
'PURGED_CURSOR',
BIND_LENGTH_UPGRADEABLE
as
'BIND_LENGTH_UPGRADEABLE',
USE_FEEDBACK_STATS
as
'USE_FEEDBACK_STATS'
)
)
group
by
sql_id,sebep
having
sum(decode(sorun,'Y',1,0))>0;
