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

[20220420]完善查询光标为什么不共享脚本.txt --//前几天遇到的问题，发现一条语句子光标很多，利用链接 --//http://blog.itpub.net/267265/viewspace-2884546/ => [20220328]查询光标为什么不共享脚本.txt --//生成的脚本，适当改写执行： > @ unshare 8cyaz1uy3wtu1 SQL_ID        NONSHARED_REASON                COUNT(*) ------------- ----------------------------- ---------- 8cyaz1uy3wtu1 ROLL_INVALID_MISMATCH               1537 8cyaz1uy3wtu1 BIND_EQUIV_FAILURE                  1539 8cyaz1uy3wtu1 USE_FEEDBACK_STATS                  1526 8cyaz1uy3wtu1 PURGED_CURSOR                          1 --//今天适当完善该脚本。 --//它是先利用它建立脚本，然后再执行。而我仅仅使用它查看单条sql语句的情况。 --//适当改写如下： $ cat gunshare.sql set head off feedback off term off column c300 format a300 spool /tmp/unshare.tmp 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'' and sql_id like ''&&1'' group by reason_not_shared order by 2 desc, 3, 1;'             c300     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; spool off set head on feedback on term on prompt prompt --- host vim /tmp/unshare.tmp prompt --- host cat /tmp/unshare.tmp prompt @ /tmp/unshare.tmp > @ gunshare 8cyaz1uy3wtu1 --- host vim /tmp/unshare.tmp --- host cat /tmp/unshare.tmp REASON_NOT_SHARED                CURSORS    SQL_IDS ----------------------------- ---------- ---------- ROLL_INVALID_MISMATCH                  1          1 1 row selected. --//这个是已经解决的情况,使用sql profile稳定执行计划，找一个有问题的情况。 > @ gunshare 9cwx6td8nfx0g --- host vim /tmp/unshare.tmp --- host cat /tmp/unshare.tmp REASON_NOT_SHARED                CURSORS    SQL_IDS ----------------------------- ---------- ---------- BIND_EQUIV_FAILURE                   522          1 USE_FEEDBACK_STATS                   506          1 ROLL_INVALID_MISMATCH                153          1 3 rows selected. > @ sql_id 9cwx6td8nfx0g --SQL_ID = 9cwx6td8nfx0g SELECT ZY_BQYZ.ZYH         FROM ZY_BQYZ,ZY_BRRY         WHERE ZY_BQYZ.ZYH = ZY_BRRY.ZYH AND         ZY_BQYZ.JGID = ZY_BRRY.JGID AND         ZY_BRRY.BRBQ = :al_bq AND         ZY_BRRY.CYPB <= 1 AND         ZY_BRRY.JGID = :al_jgid AND         ZY_BQYZ.TZSJ BETWEEN :adt_q AND :adt_z AND         ZY_BQYZ.LSYZ = 1; --//感觉这个版本存在某种bug，导致类似这类语句子光标很多。前面出问题的语句也是类似。 --//如果带入% 查询全部。 > @ gunshare % --- host vim /tmp/unshare.tmp --- host cat /tmp/unshare.tmp REASON_NOT_SHARED                CURSORS    SQL_IDS ----------------------------- ---------- ---------- ROLL_INVALID_MISMATCH               2539       2003 BIND_EQUIV_FAILURE                  1076         55 USE_FEEDBACK_STATS                   975        230 PURGED_CURSOR                        759        716 LANGUAGE_MISMATCH                    386        162 OPTIMIZER_MISMATCH                   347        165 BIND_LENGTH_UPGRADEABLE              324        234 HASH_MATCH_FAILED                    218        181 BIND_MISMATCH                        195        163 OPTIMIZER_MODE_MISMATCH              144         70 BIND_UACS_DIFF                       106        106 LOAD_OPTIMIZER_STATS                  57         53 DIFFERENT_LONG_LENGTH                 24         17 USER_BIND_PEEK_MISMATCH               20         13 INST_DRTLD_MISMATCH                   18          6 AUTH_CHECK_MISMATCH                   12          8 TRANSLATION_MISMATCH                  12          8 PQ_SLAVE_MISMATCH                      8          2 DIFF_CALL_DURN                         4          3 STATS_ROW_MISMATCH                     3          1 MULTI_PX_MISMATCH                      2          2 INCOMP_LTRL_MISMATCH                   1          1 PX_MISMATCH                            1          1 TOP_LEVEL_RPI_CURSOR                   1          1 24 rows selected.