[20241105]利用tpt ashtop.sql脚本定位没有绑定变量的语句.txt

[20241105]利用tpt ashtop.sql脚本定位没有绑定变量的语句.txt --//今年3，4月份的遇到的一个问题,sql语句没有使用绑定变量,执行计划做全表扫描,通过awr报表上根本没有这类语句的相关信息，因 --//为每条语句仅仅执行1次，要使用ashtop.sql定位这些sql语句,当时想有一定难度。 --//为此我写了两个脚本fms.sql,dfms.sql先收集有问题sql语句的FORCE_MATCHING_SIGNATURE,再通过ashtop.sql来定位对应sql语句. --//实际上ashtop.sql脚本包含time_model_name的信息，通过它就是收集FORCE_MATCHING_SIGNATURE定位这些sql语句，正好4月份我修 --//改一些会话使用cursor_sharing=exact,这些会话使用大量的文字变量，验证看看： 1.环境: SYS@127.0.0.1:9014/ywdb> @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 2.测试: SYS@127.0.0.1:9014/ywdb> @ ashtop FORCE_MATCHING_SIGNATURE "time_model_name like '%HARD_PARSE%'" &day     Total                                                                                    Distinct Distinct    Distinct   Seconds     AAS %This   FORCE_MATCHING_SIGNATURE FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1 --------- ------- ------- ------------------------ ------------------- ------------------- ---------- -------- -----------      1052      .0   32% |     15745024744648455005 2024-11-04 08:36:59 2024-11-05 08:32:42          1     1047        1047       470      .0   14% |     14262069034948960698 2024-11-04 08:49:00 2024-11-05 08:29:58          1      469         469       435      .0   13% |      1026070201017912293 2024-11-04 08:49:28 2024-11-05 08:33:23          1      432         432       184      .0    6% |     14500178973276272621 2024-11-04 08:36:27 2024-11-05 08:34:09          1      184         184       178      .0    5% |     10987896892954474158 2024-11-04 08:44:19 2024-11-05 08:12:31          1      178         178        96      .0    3% |      4183882335747876063 2024-11-04 08:36:19 2024-11-05 08:19:04          1       96          96        85      .0    3% |      4727272036020236438 2024-11-04 08:43:13 2024-11-05 08:33:19          1       85          85        58      .0    2% |     17366483767459881882 2024-11-04 08:59:08 2024-11-05 07:19:21          1       58          58        53      .0    2% |     16079245940195837697 2024-11-04 08:51:56 2024-11-05 07:40:34          1       53          53        45      .0    1% |     16496930121877890073 2024-11-04 10:06:13 2024-11-05 03:55:25          1       45          45        44      .0    1% |                        0 2024-11-04 08:43:10 2024-11-05 08:10:47          1       44          44        32      .0    1% |      7313882853856561962 2024-11-04 09:21:05 2024-11-04 22:41:14          1       32          32        31      .0    1% |     12735174598624389107 2024-11-04 09:27:13 2024-11-05 08:27:29          1       31          31        30      .0    1% |     15919273349365534210 2024-11-04 09:39:51 2024-11-05 08:30:58          1       30          30        23      .0    1% |     11002990596209431950 2024-11-04 09:06:40 2024-11-05 07:28:40          1       23          23        20      .0    1% |      6269249409409467535 2024-11-04 08:41:40 2024-11-04 20:01:51          1       20          20        19      .0    1% |      8146433212619809775 2024-11-04 09:33:23 2024-11-05 07:52:57          1       19          19        15      .0    0% |     10224702229797097847 2024-11-04 10:10:57 2024-11-05 08:18:46          1       15          15        13      .0    0% |     11355110451918346551 2024-11-04 12:22:45 2024-11-05 06:40:57          1       13          13        11      .0    0% |      2572006241972466189 2024-11-04 11:24:04 2024-11-05 08:14:12          1       11          11        11      .0    0% |     16097812119959663580 2024-11-04 10:07:40 2024-11-05 07:55:40          1       11          11        10      .0    0% |       386299767188667671 2024-11-04 10:07:26 2024-11-04 21:05:26          1       10          10        10      .0    0% |     13048485815719417027 2024-11-04 09:07:28 2024-11-05 08:10:48          1       10          10         9      .0    0% |      2211169664950686511 2024-11-04 09:42:25 2024-11-05 01:32:45          1        9           9         9      .0    0% |      9758358656735315594 2024-11-04 08:42:13 2024-11-05 08:24:57          1        9           9         9      .0    0% |     15712589648486929767 2024-11-04 10:17:01 2024-11-05 01:47:39          1        9           9         6      .0    0% |      5914569928932508680 2024-11-04 09:27:10 2024-11-05 08:01:40          1        6           6         6      .0    0% |     14444899994486855655 2024-11-04 10:19:22 2024-11-05 08:31:16          1        6           6         6      .0    0% |     18152629001048801313 2024-11-04 12:02:27 2024-11-04 18:02:37          1        6           6         5      .0    0% |      1002764975189260347 2024-11-04 10:17:41 2024-11-04 21:09:20          1        5           5 30 rows selected. --//注：FORCE_MATCHING_SIGNATURE=0的不知道为什么。 --//注：11g之前如果绑定变量与常量混合，会出现EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE的情况，这样以上查询可能还 --//是漏掉这些绑定变量与常量混合的情况，从19c开始应该没有这类问题。 --//与我以前写的fms.sql脚本对比看看： SYS@127.0.0.1:9014/ywdb> @ fms 5 30 &day FORCE_MATCHING_SIGNATURE SQL_ID_COUNT TOTAL_SECONDS ------------------------ ------------ -------------     15745024744648455005          981          1071     14262069034948960698          461           482      1026070201017912293          400           460     14500178973276272621          182           187     10987896892954474158          171           180      4183882335747876063           92            97      4727272036020236438           82            90     12870471244859360545            5            88     17366483767459881882           65            74     16079245940195837697           55            55     16496930121877890073           47            51      7313882853856561962           40            42     12735174598624389107           32            32     10224702229797097847           26            30     15919273349365534210           29            29     11002990596209431950           21            23      8146433212619809775           19            19      8162809015353822087            8            13       386299767188667671           11            13     11355110451918346551           12            13     15712589648486929767           11            12      2211169664950686511           10            12     14543546769779662674            8            11     16097812119959663580           10            11      2572006241972466189           10            11     13048485815719417027           10            10     14444899994486855655            7             9      9758358656735315594            9             9      5914569928932508680            7             8     18152629001048801313            6             7 30 rows selected. --//基本完全符合我前面的测试. --//FORCE_MATCHING_SIGNATURE=15745024744648455005,在gv$active_session_history出现981次,大量仅仅执行1次.导致每次都是硬解析, --//实际上就是这个会话有几条语句在谓词使用to_char,考虑再三设置cursor_sharing=exact.而且测试这些语句许多逻辑读仅仅是1,而 --//现在每次的硬解析导致每次导致大量递归,每次逻辑读都是2XX. --//实际上如果综合考虑,不考虑那些有问题的sql语句,采用cursor_sharing=force总体效果更好. SYS@127.0.0.1:9014/ywdb> @ sqlff 15745024744648455005 1 time unit : millisecond BEGIN_INTERVAL_TIME    INST_ID SQL_ID        PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC  AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC ------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2024-11-04 10:00:06          1 4m99wcr7scg8k      1874477375          3              48              48           0.0           168              0               0         0.0               0               0               0 2024-11-04 11:00:26          1 d27p7fh3mng5f      1874477375          2              61              60           0.0           245              0               0         0.0               0               0               0 2024-11-04 12:00:22          2 05w6b0jxqfna9      1874477375          2              62              61           0.0           244              0               0         0.0               0               0               0 2024-11-04 13:00:36          2 05w6b0jxqfna9      1874477375          1               1               1           0.0            14              0               0         0.0               0               0               0 2024-11-04 14:00:10          2 05w6b0jxqfna9      1874477375          2               1               1           0.0            14              0               0         0.0               0               0               0 --//最终记录在awr历史表的信息很少. 3.附上fms.sql脚本. $ cat fms.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:  fms.sql  v1.0 -- Purpose:    Query gv$active_session_history Force_Matching_Signature the same, sql_id different of information -- Author:     lfree -- -- Usage: --    @ fms <count(*)_number> <display_record_number> <fromtime> <totime> --   <count(*)_number>=2> -- -- Example: --    @ fms 5 30 &day -- -- Other: --     This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use --     @dfms.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive -- -------------------------------------------------------------------------------- WITH a1 AS (SELECT sql_id,force_matching_signature, count(*) cnt1   FROM gv$active_session_history  WHERE force_matching_signature <> 0 AND sample_time BETWEEN &3 AND &4 and sql_opname<>'INSERT'  GROUP BY sql_id, force_matching_signature ) ,     a2 AS (SELECT force_matching_signature, count(*) sql_id_count,sum(cnt1) total_seconds   FROM a1  GROUP BY force_matching_signature HAVING count(*) >=  &&1  ORDER BY 3 desc) SELECT force_matching_signature, sql_id_count,total_seconds   FROM a2  WHERE rownum <= &2; $ cat sqlff.sql prompt prompt time unit : millisecond prompt @awr/sqlf &1 % sysdate-&&2 sysdate $ cat tpt/awr/sqlf.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -- awr_sqlstats_per_exec v1.01 SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null "" SET TERMOUT ON COL executions       FOR 99999990 COL ela_ms_per_exec  FOR 99999990 COL rows_per_exec    FOR 99999990.0 COL lios_per_exec    FOR 99999990 COL blkrd_per_exec   FOR 99999990 COL cpu_ms_per_exec  FOR 99999990 COL iow_ms_per_exec  FOR 99999990 COL avg_iow_ms       FOR 99999990.0 COL clw_ms_per_exec  FOR 99999990 COL apw_ms_per_exec  FOR 99999990 COL ccw_ms_per_exec  FOR 99999990 -- You can configure output column precision here -- COL executions       FOR 99999990.0 -- COL ela_ms_per_exec  FOR 99999990.0 -- COL rows_per_exec    FOR 99999990.0 -- COL lios_per_exec    FOR 99999990.0 -- COL blkrd_per_exec   FOR 99999990.0 -- COL cpu_ms_per_exec  FOR 99999990.0 -- COL iow_ms_per_exec  FOR 99999990.0 -- COL avg_iow_ms      FOR 99999990.0 -- COL clw_ms_per_exec  FOR 99999990.0 -- COL apw_ms_per_exec  FOR 99999990.0 -- COL ccw_ms_per_exec  FOR 99999990.0 SELECT     CAST(begin_interval_time AS DATE) begin_interval_time   , st.instance_number inst_id   , sql_id   , plan_hash_value   , SUM(executions_delta)     executions   , ROUND(SUM(elapsed_time_delta  ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         ela_ms_per_exec   , ROUND(SUM(cpu_time_delta      ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         cpu_ms_per_exec   , ROUND(SUM(rows_processed_delta) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            rows_per_exec   , ROUND(SUM(buffer_gets_delta   ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            lios_per_exec   , ROUND(SUM(disk_reads_delta    ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)),1)                            blkrd_per_exec   , ROUND(SUM(iowait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         iow_ms_per_exec   , ROUND(SUM(iowait_delta        ) / DECODE(SUM(physical_read_requests_delta)+SUM(physical_write_requests_delta),0,1                                             ,SUM(physical_read_requests_delta)+SUM(physical_write_requests_delta))/1000,1)  avg_iow_ms   , ROUND(SUM(clwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         clw_ms_per_exec   , ROUND(SUM(apwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         apw_ms_per_exec   , ROUND(SUM(ccwait_delta        ) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta))/1000)                         ccw_ms_per_exec --  , ROUND(SUM(elapsed_time_delta - cpu_time_delta - iowait_delta - clwait_delta - apwait_delta - ccwait_delta) / DECODE(SUM(executions_delta),0,1,SUM(executions_delta)) / 1000, 1) oth_ms_per_exec FROM     dba_hist_snapshot sn   , dba_hist_sqlstat st WHERE     sn.snap_id = st.snap_id AND sn.dbid    = st.dbid AND sn.instance_number = st.instance_number AND FORCE_MATCHING_SIGNATURE = '&1' AND plan_hash_value LIKE '&2' AND begin_interval_time >= &3 AND end_interval_time   <= &4 AND (elapsed_time_delta != 0 AND cpu_time_delta != 0) GROUP BY     CAST(begin_interval_time AS DATE)   , sql_id   , plan_hash_value   , st.instance_number ORDER BY     begin_interval_time   , sql_id   , plan_hash_value /