[20170904]11Gr2 查詢游標為什麼不共享指令碼.txt

--//參考連結下面的註解指令碼:
https://carlos-sierra.net/2017/09/01/poors-man-script-to-summarize-reasons-why-cursors-are-not-shared/

--//做一個記錄.

SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

/* Formatted on 2017/9/4 9:54:31 (QP5 v5.252.13127.32867) */
SELECT    'select reason_not_shared, count(*) cursors, count(distinct sql_id) sql_ids
from v$sql_shared_cursor
unpivot(val for reason_not_shared in(
'
         || LISTAGG
            (
                  ' '
               || LISTAGG (column_name, ',') WITHIN GROUP (ORDER BY column_id)
              ,',
'
            )
            WITHIN GROUP (ORDER BY line_no)
         || '
))
where val = ''Y''
group by reason_not_shared
order by 2 desc, 3, 1;'
            sql_text
    FROM (SELECT column_name
                ,column_id
                ,CEIL (ROW_NUMBER () OVER (ORDER BY column_id) / 4) line_no
            FROM dba_tab_columns
           WHERE     owner = 'SYS'
                 AND table_name = 'V_$SQL_SHARED_CURSOR'
                 AND data_length = 1)
GROUP BY line_no;

--//生成的指令碼如下:
select reason_not_shared, count(*) cursors, count(distinct sql_id) sql_ids
from v$sql_shared_cursor
unpivot(val for reason_not_shared 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,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
))
where val = 'Y'
group by reason_not_shared
order by 2 desc, 3, 1;

--//輸出例子:
REASON_NOT_SHARED             CURSORS   SQL_IDS
--------------------------------------------------
LANGUAGE_MISMATCH             5928      3733
USE_FEEDBACK_STATS            3923      3305
BIND_LENGTH_UPGRADEABLE       1161      855
BIND_MISMATCH                 376       352
PURGED_CURSOR                 248       177
BIND_EQUIV_FAILURE            207       150
BIND_UACS_DIFF                120       120
LOAD_OPTIMIZER_STATS          88        78
USER_BIND_PEEK_MISMATCH       30        18
OPTIMIZER_MISMATCH            26        13
HASH_MATCH_FAILED             25        25
AUTH_CHECK_MISMATCH           23        15
OPTIMIZER_MODE_MISMATCH       18        14
TRANSLATION_MISMATCH          16        11
TOP_LEVEL_RPI_CURSOR          11        9
INSUFF_PRIVS_REM              7         4
INCOMP_LTRL_MISMATCH          4         4
PLSQL_CMP_SWITCHS_DIFF        4         4
INST_DRTLD_MISMATCH           3         3
INSUFF_PRIVS                  2         2
TOP_LEVEL_DDL_MISMATCH        2         2
MULTI_PX_MISMATCH             1         1
PQ_SLAVE_MISMATCH             1         1
ROLL_INVALID_MISMATCH         1         1

[20170904]11Gr2 查詢游標為什麼不共享指令碼

相關文章