[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
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3045436/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
