[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.