SQL 플랜이 변경 되었는지 확인 하는 쿼리
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 이상이면 변경이 일어 났다고 판단해야 하며, 변경이 동일하게 풀릴 수도 있습니다.
상세 이유 확인 쿼리
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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'; |
I’m really inspired with your writing abilities and also with the format to your blog. Is this a paid theme or did you modify it yourself? Anyway keep up the nice quality writing, it is uncommon to look a great weblog like this one today..
Ahaa, its pleasant conversation on the topic of this post at this place at this blog, I have read all that, so at this time me also commenting here.