V$SQL_SHARED_CURSOR 뷰는 특정 자식 커서가 기존 자식 커서와 공유되지 않는 이유를 보여줍니다. 하지만 64개의 여부 속성을 통해 커서를 공유할 수 없는 이유를 파악해야 하므로 편이성이 떨어집니다.
편이성을 높이기 위해 아래와 같이 64개의 여부 속성이 unpivot된 뷰를 생성하겠습니다. SYS.X$KKSCS 테이블은 V$SQL_SHARED_CURSOR 뷰의 베이스 테이블로 bitvector 칼럼에 커서를 공유할 수 없는 이유를 저장합니다.
-- 1-1
CREATE OR REPLACE VIEW sys.gu_$sql_shared_cursor
AS
SELECT inst_id
, sql_id
, address
, child_address
, child_number
, reason
, reason_xml
FROM (SELECT inst_id
, sql_id
, kglhdpar AS address
, kglhdadr AS child_address
, childno AS child_number
, CASE WHEN BITAND (bitvector, POWER (2, 0)) > 0 THEN 'Y' END AS unbound_cursor
, CASE WHEN BITAND (bitvector, POWER (2, 1)) > 0 THEN 'Y' END AS sql_type_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 2)) > 0 THEN 'Y' END AS optimizer_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 3)) > 0 THEN 'Y' END AS outline_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 4)) > 0 THEN 'Y' END AS stats_row_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 5)) > 0 THEN 'Y' END AS literal_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 6)) > 0 THEN 'Y' END AS force_hard_parse
, CASE WHEN BITAND (bitvector, POWER (2, 7)) > 0 THEN 'Y' END AS explain_plan_cursor
, CASE WHEN BITAND (bitvector, POWER (2, 8)) > 0 THEN 'Y' END AS buffered_dml_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 9)) > 0 THEN 'Y' END AS pdml_env_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 10)) > 0 THEN 'Y' END AS inst_drtld_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 11)) > 0 THEN 'Y' END AS slave_qc_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 12)) > 0 THEN 'Y' END AS typecheck_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 13)) > 0 THEN 'Y' END AS auth_check_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 14)) > 0 THEN 'Y' END AS bind_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 15)) > 0 THEN 'Y' END AS describe_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 16)) > 0 THEN 'Y' END AS language_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 17)) > 0 THEN 'Y' END AS translation_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 18)) > 0 THEN 'Y' END AS bind_equiv_failure
, CASE WHEN BITAND (bitvector, POWER (2, 19)) > 0 THEN 'Y' END AS insuff_privs
, CASE WHEN BITAND (bitvector, POWER (2, 20)) > 0 THEN 'Y' END AS insuff_privs_rem
, CASE WHEN BITAND (bitvector, POWER (2, 21)) > 0 THEN 'Y' END AS remote_trans_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 22)) > 0 THEN 'Y' END AS logminer_session_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 23)) > 0 THEN 'Y' END AS incomp_ltrl_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 24)) > 0 THEN 'Y' END AS overlap_time_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 25)) > 0 THEN 'Y' END AS edition_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 26)) > 0 THEN 'Y' END AS mv_query_gen_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 27)) > 0 THEN 'Y' END AS user_bind_peek_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 28)) > 0 THEN 'Y' END AS typchk_dep_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 29)) > 0 THEN 'Y' END AS no_trigger_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 30)) > 0 THEN 'Y' END AS flashback_cursor
, CASE WHEN BITAND (bitvector, POWER (2, 31)) > 0 THEN 'Y' END AS anydata_transformation
, CASE WHEN BITAND (bitvector, POWER (2, 32)) > 0 THEN 'Y' END AS pddl_env_mismatch -- 12.1
, CASE WHEN BITAND (bitvector, POWER (2, 33)) > 0 THEN 'Y' END AS top_level_rpi_cursor
, CASE WHEN BITAND (bitvector, POWER (2, 34)) > 0 THEN 'Y' END AS different_long_length
, CASE WHEN BITAND (bitvector, POWER (2, 35)) > 0 THEN 'Y' END AS logical_standby_apply
, CASE WHEN BITAND (bitvector, POWER (2, 36)) > 0 THEN 'Y' END AS diff_call_durn
, CASE WHEN BITAND (bitvector, POWER (2, 37)) > 0 THEN 'Y' END AS bind_uacs_diff
, CASE WHEN BITAND (bitvector, POWER (2, 38)) > 0 THEN 'Y' END AS plsql_cmp_switchs_diff
, CASE WHEN BITAND (bitvector, POWER (2, 39)) > 0 THEN 'Y' END AS cursor_parts_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 40)) > 0 THEN 'Y' END AS stb_object_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 41)) > 0 THEN 'Y' END AS crossedition_trigger_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 42)) > 0 THEN 'Y' END AS pq_slave_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 43)) > 0 THEN 'Y' END AS top_level_ddl_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 44)) > 0 THEN 'Y' END AS multi_px_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 45)) > 0 THEN 'Y' END AS bind_peeked_pq_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 46)) > 0 THEN 'Y' END AS mv_rewrite_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 47)) > 0 THEN 'Y' END AS roll_invalid_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 48)) > 0 THEN 'Y' END AS optimizer_mode_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 49)) > 0 THEN 'Y' END AS px_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 50)) > 0 THEN 'Y' END AS mv_staleobj_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 51)) > 0 THEN 'Y' END AS flashback_table_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 52)) > 0 THEN 'Y' END AS litrep_comp_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 53)) > 0 THEN 'Y' END AS plsql_debug
, CASE WHEN BITAND (bitvector, POWER (2, 54)) > 0 THEN 'Y' END AS load_optimizer_stats
, CASE WHEN BITAND (bitvector, POWER (2, 55)) > 0 THEN 'Y' END AS acl_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 56)) > 0 THEN 'Y' END AS flashback_archive_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 57)) > 0 THEN 'Y' END AS lock_user_schema_failed
, CASE WHEN BITAND (bitvector, POWER (2, 58)) > 0 THEN 'Y' END AS remote_mapping_mismatch
, CASE WHEN BITAND (bitvector, POWER (2, 59)) > 0 THEN 'Y' END AS load_runtime_heap_failed
, CASE WHEN BITAND (bitvector, POWER (2, 60)) > 0 THEN 'Y' END AS hash_match_failed
, CASE WHEN BITAND (bitvector, POWER (2, 61)) > 0 THEN 'Y' END AS purged_cursor
, CASE WHEN BITAND (bitvector, POWER (2, 62)) > 0 THEN 'Y' END AS bind_length_upgradeable
, CASE WHEN BITAND (bitvector, POWER (2, 63)) > 0 THEN 'Y' END AS use_feedback_stats -- 12.1
, reason AS reason_xml -- 12.1
FROM sys.x$kkscs)
UNPIVOT (value FOR reason IN (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 -- 12.1
, 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 -- 12.1
))
WHERE value = 'Y';
-- 1-2
CREATE OR REPLACE VIEW sys.u_$sql_shared_cursor
AS
SELECT sql_id
, address
, child_address
, child_number
, reason
, reason_xml
FROM sys.gu_$sql_shared_cursor
WHERE inst_id = USERENV ('INSTANCE');
-- 1-3
GRANT SELECT ON sys.gu_$sql_shared_cursor TO select_catalog_role;
GRANT SELECT ON sys.u_$sql_shared_cursor TO select_catalog_role;
-- 1-4
CREATE OR REPLACE PUBLIC SYNONYM gu$sql_shared_cursor FOR sys.gu_$sql_shared_cursor;
CREATE OR REPLACE PUBLIC SYNONYM u$sql_shared_cursor FOR sys.u_$sql_shared_cursor;
아래는 뷰를 조회한 결과입니다.
-- 2
SELECT sql_id, child_number, reason
FROM u$sql_shared_cursor
WHERE sql_id = 'f946p6b0qhh5t';
SQL_ID CHILD_NUMBER REASON
------------- ------------ -----------------------
f946p6b0qhh5t 0 OPTIMIZER_MISMATCH
f946p6b0qhh5t 0 OPTIMIZER_MODE_MISMATCH
f946p6b0qhh5t 1 OPTIMIZER_MISMATCH
f946p6b0qhh5t 1 LANGUAGE_MISMATCH
f946p6b0qhh5t 1 ROLL_INVALID_MISMATCH
f946p6b0qhh5t 1 OPTIMIZER_MODE_MISMATCH
...
22 행이 선택되었습니다.
참고로 Fixed View의 정의를 조회할 수 있는 V$FIXED_VIEW_DEFINITION 뷰의 view_definition 칼럼은 VARCHAR2(4000) 타입입니다. 이로 인해 크기가 4,000 바이트를 초과하는 Fixed View의 정의를 조회할 수 없습니다.
-- 3
DESC V$FIXED_VIEW_DEFINITION
이름 널? 유형
--------------- --- --------------
VIEW_NAME VARCHAR2(128)
VIEW_DEFINITION VARCHAR2(4000)
CON_ID NUMBER
이런 경우 아래와 같이 DBMS_UTILITY.EXPAND_SQL_TEXT 프로시저를 사용하면 Fixed View의 정의를 쉽게 조회할 수 있습니다.
-- 4
SET SERVEROUT ON
DECLARE
v_input_sql_text CLOB;
v_output_sql_text CLOB;
BEGIN
v_input_sql_text := 'SELECT * FROM GV$SQL_SHARED_CURSOR';
DBMS_UTILITY.EXPAND_SQL_TEXT (v_input_sql_text, v_output_sql_text);
DBMS_OUTPUT.PUT_LINE (v_output_sql_text);
END;
/
관련 링크