실행계획이 변경된 SQL 확인 및 이유 확인

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 실행계획이 변경된 SQL 확인 및 이유 확인

조회할 sql_id : fq548gbcv4fc1

실행계획이 변경된 SQL 확인

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%';
 
Enter value for sql_id: fq548gbcv4fc1
old  16: where sql_id='&&sql_id'
new  16: where sql_id='fq548gbcv4fc1'
 
SQL_ID          USER                 PLAN_HASH_VALUE OPTIMIZER_ CHILD_NUMBER PARSE_CALLS USERS_OPENING USERS_EXECUTING        LOADS EXECUTIONS INVALIDATIONS    RELOADS
------------- ------------------------------ --------------- ---------- ------------ ----------- ------------- --------------- ---------- ---------- ------------- ----------
FIRST_LOAD_TIME                LAST_LOAD_TIME
-------------------------------------- --------------------------------------
fq548gbcv4fc1 SYS                  1828992746 ALL_ROWS           0           4         0             0        1       4         0        0
2019-01-08/11:05:10               2019-01-08/11:05:10
 
fq548gbcv4fc1 SYS                  1828992746 RULE           1           2         0             0        1       2         0        0
2019-01-08/11:05:10               2019-01-08/11:05:12
 
fq548gbcv4fc1 SYS                  1828992746 ALL_ROWS           2           4         0             0        1      12         0        0
2019-01-08/11:05:10               2019-01-08/11:05:16
 
 
3 rows selected.

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

상세 이유 확인

SQL>
col WHY for a60
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';
 
SQL_ID          ADDRESS           CHILD_ADDRESS    CHILD_NUMBER WHY
------------- ---------------- ---------------- ------------ ------------------------------------------------------------
fq548gbcv4fc1 00000000880FEC40 0000000088D5C990        0 (null)
fq548gbcv4fc1 00000000880FEC40 0000000088261240        1 OPTIMIZER_MODE_MISMATCH,
fq548gbcv4fc1 00000000880FEC40 0000000088DB57C0        2 OPTIMIZER_MISMATCH,OPTIMIZER_MODE_MISMATCH,

결과값 : 나의경우 OPTIMIZER_MODE_MISMATCH로 인해 실행 계획이 변경되었음

참조 : http://haisins.epac.to/wordpress/?p=4044