[20220328]查询光标为什么不共享脚本.txt

> @ ashtop machine,client_id sql_id='cks1xuhvjt1a2' &day
    Total                                                                                            Distinct Distinct
  Seconds     AAS %This   MACHINE              CLIENT_ID   FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- -------------------- ----------- ------------------- ------------------- ---------- --------
      195      .0   31% | oda1                 127.0.0.1   2022-03-27 03:15:27 2022-03-27 03:17:08          1      195


> @ sql_id cks1xuhvjt1a2
--SQL_ID = cks1xuhvjt1a2
SELECT /* pxhcdr.sql Cursor Sharing as per Reason */
CHR(10)||'<tr>'||CHR(10)||
'<td>'||ROWNUM||'</td>'||CHR(10)||
'<td>'||v2.reason||'</td>'||CHR(10)||
'<td>'||v2.inst_id||'</td>'||CHR(10)||
'<td>'||v2.cursors||'</td>'||CHR(10)||
'</tr>'
FROM (
SELECT 'ACL_MISMATCH' reason, inst_id, COUNT(*) cursors FROM gv$sql_shared_cursor WHERE ACL_MISMATCH = 'Y' GROUP BY inst_id
UNION ALL
SELECT 'ANYDATA_TRANSFORMATION' reason, inst_id, COUNT(*) cursors FROM gv$sql_shared_cursor WHERE ANYDATA_TRANSFORMATION = 'Y' GROUP BY inst_id
UNION ALL
...
UNION ALL
SELECT 'USER_BIND_PEEK_MISMATCH' reason, inst_id, COUNT(*) cursors FROM gv$sql_shared_cursor WHERE USER_BIND_PEEK_MISMATCH = 'Y' GROUP BY inst_id
UNION ALL
SELECT 'USE_FEEDBACK_STATS' reason, inst_id, COUNT(*) cursors FROM gv$sql_shared_cursor WHERE USE_FEEDBACK_STATS = 'Y' GROUP BY inst_id
ORDER BY reason, inst_id ) v2;

--//不知道那个管理者执行注意看时间是凌晨3点。查看为什么光标不共享的脚本,这样查询当然效率很低。
--//我记忆里我好像以前收集过一个生成脚本，查了一下日记,发现链接：
--//http://blog.itpub.net/267265/viewspace-2144458/ =>[20170904]11Gr2 查询光标为什么不共享脚本。
--//参考链接下面的注解脚本:
--//carlos-sierra.net/2017/09/01/poors-man-script-to-summarize-reasons-why-cursors-are-not-shared/

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;

--//利用unpivot,LISTAGG生成脚本。

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
----------------------------- ---------- ----------
ROLL_INVALID_MISMATCH               8756       3297
BIND_EQUIV_FAILURE                   876         63
USE_FEEDBACK_STATS                   783        314
PURGED_CURSOR                        375        370
OPTIMIZER_MISMATCH                   315        114
BIND_LENGTH_UPGRADEABLE              277        201
BIND_MISMATCH                        207        120
LANGUAGE_MISMATCH                    197         48
OPTIMIZER_MODE_MISMATCH              140         60
HASH_MATCH_FAILED                     75         68
BIND_UACS_DIFF                        60         60
USER_BIND_PEEK_MISMATCH               56         15
DIFFERENT_LONG_LENGTH                 37         15
LOAD_OPTIMIZER_STATS                  29         28
INST_DRTLD_MISMATCH                   16          4
AUTH_CHECK_MISMATCH                   12          8
TRANSLATION_MISMATCH                  10          6
MULTI_PX_MISMATCH                      5          1
STATS_ROW_MISMATCH                     5          2
PQ_SLAVE_MISMATCH                      4          2
TYPCHK_DEP_MISMATCH                    4          2
DIFF_CALL_DURN                         1          1
22 rows selected.

--//如果换成gv$sql_shared_cursor，时间增加不少，我的测试v$sql_shared_cursor 3秒，gv$sql_shared_cursor 17秒。
--//我看了以前我们旧系统LANGUAGE_MISMATCH  排名第一，这个是因为我们在安装部署应用软件时使用两套字符集的原因。
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

--//简单探究是什么原因导致的问题。
SELECT sql_id, COUNT (*)
    FROM v$sql_shared_cursor
   WHERE ROLL_INVALID_MISMATCH = 'Y'
GROUP BY sql_id
  HAVING COUNT (*) >= 20
ORDER BY 2 DESC;

SQL_ID          COUNT(*)
------------- ----------
70pnyzz8hgjxm        113
bp20933j1aa5p         94
1msndsv11mzc1         90
4t5w0d69u8sm7         69
b748a8a4y15ra         50
d4cdrjs9vnyaw         36
9zg9qd9bm4spu         31
2zpn9zwhv6w9m         25
9u8gp6xtzf60f         24
1va57mnax09r7         23
g93p68x9afrra         21
11 rows selected.

--//这个原因实际上由于晚上10点分析表导致原先的光标失效。以前的分析是关闭的。
--//BIND_EQUIV_FAILURE 的原因可能是返回记录出现很大变化时就会出现。