SQL 플랜이 변경된 이유 확인 하는 방법

haisins

7년 ago

SQL 플랜이 변경 되었는지 확인 하는 쿼리

select sql_id,
  PARSING_SCHEMA_NAME "USER",
  PLAN_HASH_VALUE,
  OPTIMIZER_MODE,
  child_number,
  parse_calls,
  USERS_OPENING,
  USERS_EXECUTING,
  loads,
  executions,
  invalidations,
  decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,
  FIRST_LOAD_TIME,
  LAST_LOAD_TIME
from v$sql 
where sql_id='&&sql_id'
and  sql_text not like '%v$sql%';

select sql_id,

PARSING_SCHEMA_NAME "USER",

PLAN_HASH_VALUE,

OPTIMIZER_MODE,

child_number,

parse_calls,

USERS_OPENING,

USERS_EXECUTING,

loads,

executions,

invalidations,

decode(sign(invalidations), 1, (loads-invalidations), 0) reloads,

FIRST_LOAD_TIME,

LAST_LOAD_TIME

from v$sql

where sql_id='&&sql_id'

and sql_text not like '%v$sql%';

child_number가 0 이상이면 변경이 일어 났다고 판단해야 하며, 변경이 동일하게 풀릴 수도 있습니다.

상세 이유 확인 쿼리

select SQL_ID,
       ADDRESS,
       CHILD_ADDRESS ,
       CHILD_NUMBER,
       decode(UNBOUND_CURSOR , 'Y', 'UNBOUND_CURSOR,' , 'N', null , UNBOUND_CURSOR )|| 
       decode(SQL_TYPE_MISMATCH , 'Y', 'SQL_TYPE_MISMATCH,' , 'N', null , SQL_TYPE_MISMATCH )|| 
       decode(OPTIMIZER_MISMATCH , 'Y', 'OPTIMIZER_MISMATCH,' , 'N', null , OPTIMIZER_MISMATCH )|| 
       decode(OUTLINE_MISMATCH , 'Y', 'OUTLINE_MISMATCH,' , 'N', null , OUTLINE_MISMATCH )|| 
       decode(STATS_ROW_MISMATCH , 'Y', 'STATS_ROW_MISMATCH,' , 'N', null , STATS_ROW_MISMATCH )|| 
       decode(LITERAL_MISMATCH , 'Y', 'LITERAL_MISMATCH,' , 'N', null , LITERAL_MISMATCH )|| 
       decode(EXPLAIN_PLAN_CURSOR , 'Y', 'EXPLAIN_PLAN_CURSOR,' , 'N', null , EXPLAIN_PLAN_CURSOR )|| 
       decode(BUFFERED_DML_MISMATCH , 'Y', 'BUFFERED_DML_MISMATCH,' , 'N', null , BUFFERED_DML_MISMATCH )|| 
       decode(PDML_ENV_MISMATCH , 'Y', 'PDML_ENV_MISMATCH,' , 'N', null , PDML_ENV_MISMATCH )|| 
       decode(INST_DRTLD_MISMATCH , 'Y', 'INST_DRTLD_MISMATCH,' , 'N', null , INST_DRTLD_MISMATCH )|| 
       decode(SLAVE_QC_MISMATCH , 'Y', 'SLAVE_QC_MISMATCH,' , 'N', null , SLAVE_QC_MISMATCH )|| 
       decode(TYPECHECK_MISMATCH , 'Y', 'TYPECHECK_MISMATCH,' , 'N', null , TYPECHECK_MISMATCH )|| 
       decode(AUTH_CHECK_MISMATCH , 'Y', 'AUTH_CHECK_MISMATCH,' , 'N', null , AUTH_CHECK_MISMATCH )|| 
       decode(BIND_MISMATCH , 'Y', 'BIND_MISMATCH,' , 'N', null , BIND_MISMATCH )|| 
       decode(DESCRIBE_MISMATCH , 'Y', 'DESCRIBE_MISMATCH,' , 'N', null , DESCRIBE_MISMATCH )|| 
       decode(LANGUAGE_MISMATCH , 'Y', 'LANGUAGE_MISMATCH,' , 'N', null , LANGUAGE_MISMATCH )|| 
       decode(TRANSLATION_MISMATCH , 'Y', 'TRANSLATION_MISMATCH,' , 'N', null , TRANSLATION_MISMATCH )|| 
       decode(INSUFF_PRIVS , 'Y', 'INSUFF_PRIVS,' , 'N', null , INSUFF_PRIVS )|| 
       decode(INSUFF_PRIVS_REM , 'Y', 'INSUFF_PRIVS_REM,' , 'N', null , INSUFF_PRIVS_REM )|| 
       decode(REMOTE_TRANS_MISMATCH , 'Y', 'REMOTE_TRANS_MISMATCH,' , 'N', null , REMOTE_TRANS_MISMATCH )|| 
       decode(LOGMINER_SESSION_MISMATCH , 'Y', 'LOGMINER_SESSION_MISMATCH,' , 'N', null , LOGMINER_SESSION_MISMATCH )|| 
       decode(INCOMP_LTRL_MISMATCH , 'Y', 'INCOMP_LTRL_MISMATCH,' , 'N', null , INCOMP_LTRL_MISMATCH )|| 
       decode(OVERLAP_TIME_MISMATCH , 'Y', 'OVERLAP_TIME_MISMATCH,' , 'N', null , OVERLAP_TIME_MISMATCH )|| 
       decode(MV_QUERY_GEN_MISMATCH , 'Y', 'MV_QUERY_GEN_MISMATCH,' , 'N', null , MV_QUERY_GEN_MISMATCH )|| 
       decode(USER_BIND_PEEK_MISMATCH , 'Y', 'USER_BIND_PEEK_MISMATCH,' , 'N', null , USER_BIND_PEEK_MISMATCH )|| 
       decode(TYPCHK_DEP_MISMATCH , 'Y', 'TYPCHK_DEP_MISMATCH,' , 'N', null , TYPCHK_DEP_MISMATCH )|| 
       decode(NO_TRIGGER_MISMATCH , 'Y', 'NO_TRIGGER_MISMATCH,' , 'N', null , NO_TRIGGER_MISMATCH )|| 
       decode(FLASHBACK_CURSOR , 'Y', 'FLASHBACK_CURSOR,' , 'N', null , FLASHBACK_CURSOR )|| 
       decode(ANYDATA_TRANSFORMATION , 'Y', 'ANYDATA_TRANSFORMATION,' , 'N', null , ANYDATA_TRANSFORMATION )|| 
       decode(TOP_LEVEL_RPI_CURSOR , 'Y', 'TOP_LEVEL_RPI_CURSOR,' , 'N', null , TOP_LEVEL_RPI_CURSOR )|| 
       decode(DIFFERENT_LONG_LENGTH , 'Y', 'DIFFERENT_LONG_LENGTH,' , 'N', null , DIFFERENT_LONG_LENGTH )|| 
       decode(LOGICAL_STANDBY_APPLY , 'Y', 'LOGICAL_STANDBY_APPLY,' , 'N', null , LOGICAL_STANDBY_APPLY )|| 
       decode(DIFF_CALL_DURN , 'Y', 'DIFF_CALL_DURN,' , 'N', null , DIFF_CALL_DURN )|| 
       decode(BIND_UACS_DIFF , 'Y', 'BIND_UACS_DIFF,' , 'N', null , BIND_UACS_DIFF )|| 
       decode(PLSQL_CMP_SWITCHS_DIFF , 'Y', 'PLSQL_CMP_SWITCHS_DIFF,' , 'N', null , PLSQL_CMP_SWITCHS_DIFF )|| 
       decode(CURSOR_PARTS_MISMATCH , 'Y', 'CURSOR_PARTS_MISMATCH,' , 'N', null , CURSOR_PARTS_MISMATCH )|| 
       decode(STB_OBJECT_MISMATCH , 'Y', 'STB_OBJECT_MISMATCH,' , 'N', null , STB_OBJECT_MISMATCH )|| 
       decode(PQ_SLAVE_MISMATCH , 'Y', 'PQ_SLAVE_MISMATCH,' , 'N', null , PQ_SLAVE_MISMATCH )|| 
       decode(TOP_LEVEL_DDL_MISMATCH , 'Y', 'TOP_LEVEL_DDL_MISMATCH,' , 'N', null , TOP_LEVEL_DDL_MISMATCH )|| 
       decode(MULTI_PX_MISMATCH , 'Y', 'MULTI_PX_MISMATCH,' , 'N', null , MULTI_PX_MISMATCH )|| 
       decode(BIND_PEEKED_PQ_MISMATCH , 'Y', 'BIND_PEEKED_PQ_MISMATCH,' , 'N', null , BIND_PEEKED_PQ_MISMATCH )|| 
       decode(MV_REWRITE_MISMATCH , 'Y', 'MV_REWRITE_MISMATCH,' , 'N', null , MV_REWRITE_MISMATCH )|| 
       decode(ROLL_INVALID_MISMATCH , 'Y', 'ROLL_INVALID_MISMATCH,' , 'N', null , ROLL_INVALID_MISMATCH )|| 
       decode(OPTIMIZER_MODE_MISMATCH , 'Y', 'OPTIMIZER_MODE_MISMATCH,' , 'N', null , OPTIMIZER_MODE_MISMATCH )|| 
       decode(PX_MISMATCH , 'Y', 'PX_MISMATCH,' , 'N', null , PX_MISMATCH )|| 
       decode(MV_STALEOBJ_MISMATCH , 'Y', 'MV_STALEOBJ_MISMATCH,' , 'N', null , MV_STALEOBJ_MISMATCH )|| 
       decode(FLASHBACK_TABLE_MISMATCH , 'Y', 'FLASHBACK_TABLE_MISMATCH,' , 'N', null , FLASHBACK_TABLE_MISMATCH )|| 
       decode(LITREP_COMP_MISMATCH , 'Y', 'LITREP_COMP_MISMATCH' , 'N', null , LITREP_COMP_MISMATCH ) as WHY 
from v$sql_shared_cursor 
where sql_id='&&sql_id';

select SQL_ID,

ADDRESS,

CHILD_ADDRESS ,

CHILD_NUMBER,

decode(UNBOUND_CURSOR , 'Y', 'UNBOUND_CURSOR,' , 'N', null , UNBOUND_CURSOR )||

decode(SQL_TYPE_MISMATCH , 'Y', 'SQL_TYPE_MISMATCH,' , 'N', null , SQL_TYPE_MISMATCH )||

decode(OPTIMIZER_MISMATCH , 'Y', 'OPTIMIZER_MISMATCH,' , 'N', null , OPTIMIZER_MISMATCH )||

decode(OUTLINE_MISMATCH , 'Y', 'OUTLINE_MISMATCH,' , 'N', null , OUTLINE_MISMATCH )||

decode(STATS_ROW_MISMATCH , 'Y', 'STATS_ROW_MISMATCH,' , 'N', null , STATS_ROW_MISMATCH )||

decode(LITERAL_MISMATCH , 'Y', 'LITERAL_MISMATCH,' , 'N', null , LITERAL_MISMATCH )||

decode(EXPLAIN_PLAN_CURSOR , 'Y', 'EXPLAIN_PLAN_CURSOR,' , 'N', null , EXPLAIN_PLAN_CURSOR )||

decode(BUFFERED_DML_MISMATCH , 'Y', 'BUFFERED_DML_MISMATCH,' , 'N', null , BUFFERED_DML_MISMATCH )||

decode(PDML_ENV_MISMATCH , 'Y', 'PDML_ENV_MISMATCH,' , 'N', null , PDML_ENV_MISMATCH )||

decode(INST_DRTLD_MISMATCH , 'Y', 'INST_DRTLD_MISMATCH,' , 'N', null , INST_DRTLD_MISMATCH )||

decode(SLAVE_QC_MISMATCH , 'Y', 'SLAVE_QC_MISMATCH,' , 'N', null , SLAVE_QC_MISMATCH )||

decode(TYPECHECK_MISMATCH , 'Y', 'TYPECHECK_MISMATCH,' , 'N', null , TYPECHECK_MISMATCH )||

decode(AUTH_CHECK_MISMATCH , 'Y', 'AUTH_CHECK_MISMATCH,' , 'N', null , AUTH_CHECK_MISMATCH )||

decode(BIND_MISMATCH , 'Y', 'BIND_MISMATCH,' , 'N', null , BIND_MISMATCH )||

decode(DESCRIBE_MISMATCH , 'Y', 'DESCRIBE_MISMATCH,' , 'N', null , DESCRIBE_MISMATCH )||

decode(LANGUAGE_MISMATCH , 'Y', 'LANGUAGE_MISMATCH,' , 'N', null , LANGUAGE_MISMATCH )||

decode(TRANSLATION_MISMATCH , 'Y', 'TRANSLATION_MISMATCH,' , 'N', null , TRANSLATION_MISMATCH )||

decode(INSUFF_PRIVS , 'Y', 'INSUFF_PRIVS,' , 'N', null , INSUFF_PRIVS )||

decode(INSUFF_PRIVS_REM , 'Y', 'INSUFF_PRIVS_REM,' , 'N', null , INSUFF_PRIVS_REM )||

decode(REMOTE_TRANS_MISMATCH , 'Y', 'REMOTE_TRANS_MISMATCH,' , 'N', null , REMOTE_TRANS_MISMATCH )||

decode(LOGMINER_SESSION_MISMATCH , 'Y', 'LOGMINER_SESSION_MISMATCH,' , 'N', null , LOGMINER_SESSION_MISMATCH )||

decode(INCOMP_LTRL_MISMATCH , 'Y', 'INCOMP_LTRL_MISMATCH,' , 'N', null , INCOMP_LTRL_MISMATCH )||

decode(OVERLAP_TIME_MISMATCH , 'Y', 'OVERLAP_TIME_MISMATCH,' , 'N', null , OVERLAP_TIME_MISMATCH )||

decode(MV_QUERY_GEN_MISMATCH , 'Y', 'MV_QUERY_GEN_MISMATCH,' , 'N', null , MV_QUERY_GEN_MISMATCH )||

decode(USER_BIND_PEEK_MISMATCH , 'Y', 'USER_BIND_PEEK_MISMATCH,' , 'N', null , USER_BIND_PEEK_MISMATCH )||

decode(TYPCHK_DEP_MISMATCH , 'Y', 'TYPCHK_DEP_MISMATCH,' , 'N', null , TYPCHK_DEP_MISMATCH )||

decode(NO_TRIGGER_MISMATCH , 'Y', 'NO_TRIGGER_MISMATCH,' , 'N', null , NO_TRIGGER_MISMATCH )||

decode(FLASHBACK_CURSOR , 'Y', 'FLASHBACK_CURSOR,' , 'N', null , FLASHBACK_CURSOR )||

decode(ANYDATA_TRANSFORMATION , 'Y', 'ANYDATA_TRANSFORMATION,' , 'N', null , ANYDATA_TRANSFORMATION )||

decode(TOP_LEVEL_RPI_CURSOR , 'Y', 'TOP_LEVEL_RPI_CURSOR,' , 'N', null , TOP_LEVEL_RPI_CURSOR )||

decode(DIFFERENT_LONG_LENGTH , 'Y', 'DIFFERENT_LONG_LENGTH,' , 'N', null , DIFFERENT_LONG_LENGTH )||

decode(LOGICAL_STANDBY_APPLY , 'Y', 'LOGICAL_STANDBY_APPLY,' , 'N', null , LOGICAL_STANDBY_APPLY )||

decode(DIFF_CALL_DURN , 'Y', 'DIFF_CALL_DURN,' , 'N', null , DIFF_CALL_DURN )||

decode(BIND_UACS_DIFF , 'Y', 'BIND_UACS_DIFF,' , 'N', null , BIND_UACS_DIFF )||

decode(PLSQL_CMP_SWITCHS_DIFF , 'Y', 'PLSQL_CMP_SWITCHS_DIFF,' , 'N', null , PLSQL_CMP_SWITCHS_DIFF )||

decode(CURSOR_PARTS_MISMATCH , 'Y', 'CURSOR_PARTS_MISMATCH,' , 'N', null , CURSOR_PARTS_MISMATCH )||

decode(STB_OBJECT_MISMATCH , 'Y', 'STB_OBJECT_MISMATCH,' , 'N', null , STB_OBJECT_MISMATCH )||

decode(PQ_SLAVE_MISMATCH , 'Y', 'PQ_SLAVE_MISMATCH,' , 'N', null , PQ_SLAVE_MISMATCH )||

decode(TOP_LEVEL_DDL_MISMATCH , 'Y', 'TOP_LEVEL_DDL_MISMATCH,' , 'N', null , TOP_LEVEL_DDL_MISMATCH )||

decode(MULTI_PX_MISMATCH , 'Y', 'MULTI_PX_MISMATCH,' , 'N', null , MULTI_PX_MISMATCH )||

decode(BIND_PEEKED_PQ_MISMATCH , 'Y', 'BIND_PEEKED_PQ_MISMATCH,' , 'N', null , BIND_PEEKED_PQ_MISMATCH )||

decode(MV_REWRITE_MISMATCH , 'Y', 'MV_REWRITE_MISMATCH,' , 'N', null , MV_REWRITE_MISMATCH )||

decode(ROLL_INVALID_MISMATCH , 'Y', 'ROLL_INVALID_MISMATCH,' , 'N', null , ROLL_INVALID_MISMATCH )||

decode(OPTIMIZER_MODE_MISMATCH , 'Y', 'OPTIMIZER_MODE_MISMATCH,' , 'N', null , OPTIMIZER_MODE_MISMATCH )||

decode(PX_MISMATCH , 'Y', 'PX_MISMATCH,' , 'N', null , PX_MISMATCH )||

decode(MV_STALEOBJ_MISMATCH , 'Y', 'MV_STALEOBJ_MISMATCH,' , 'N', null , MV_STALEOBJ_MISMATCH )||

decode(FLASHBACK_TABLE_MISMATCH , 'Y', 'FLASHBACK_TABLE_MISMATCH,' , 'N', null , FLASHBACK_TABLE_MISMATCH )||

decode(LITREP_COMP_MISMATCH , 'Y', 'LITREP_COMP_MISMATCH' , 'N', null , LITREP_COMP_MISMATCH ) as WHY

from v$sql_shared_cursor

where sql_id='&&sql_id';