[20240321]分析FORCE_MATCHING_SIGNATURE重合的奇怪情况.txt
--//生产系统遇到1个FORCE_MATCHING_SIGNATURE重合的奇怪现象，一般情况都是相似的sql语句(没有使用绑定变量的sql语句)，
--//FORCE_MATCHING_SIGNATURE相同。
--//注：11g之前如果绑定变量与常量混合，会出现EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE的情况.
1.环境:
[email protected]:1521/orcl> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
[email protected]:1521/orcl> @ fms.sql 5 5 &day
FORCE_MATCHING_SIGNATURE SQL_ID_COUNT TOTAL_SECONDS
------------------------ ------------ -------------
4354576702770823574 17 425
17409746777760600368 28 287
11034937719941372916 13 228
8097234170242639654 12 149
16405634091854084691 8 141
2.查看FORCE_MATCHING_SIGNATURE=4354576702770823574的情况：
[email protected]:1521/orcl> @ ashtop sql_id,FORCE_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE=4354576702770823574 &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FORCE_MATCHING_SIGNATURE FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------------ ------------------- ------------------- ---------- -------- -----------
404 .0 96% | 5n49aq25gu7bf 4354576702770823574 2024-03-20 10:12:44 2024-03-21 10:06:51 397 400 404
3 .0 1% | 9rb30rt84k7ja 4354576702770823574 2024-03-20 16:37:53 2024-03-21 08:44:48 3 3 3
2 .0 0% | f4ahy63yn6f8s 4354576702770823574 2024-03-20 16:49:39 2024-03-21 08:23:16 2 2 2
1 .0 0% | 16jxtf48dbyuf 4354576702770823574 2024-03-21 07:47:29 2024-03-21 07:47:29 1 1 1
1 .0 0% | 2gycc8y95vwh9 4354576702770823574 2024-03-20 17:04:26 2024-03-20 17:04:26 1 1 1
1 .0 0% | 2xjh8gzgqrxgf 4354576702770823574 2024-03-21 09:09:34 2024-03-21 09:09:34 1 1 1
1 .0 0% | 4zyg7jnptgh4r 4354576702770823574 2024-03-20 16:40:23 2024-03-20 16:40:23 1 1 1
1 .0 0% | 55ughx5um1vsm 4354576702770823574 2024-03-20 15:07:40 2024-03-20 15:07:40 1 1 1
1 .0 0% | 9qyffudw64ky6 4354576702770823574 2024-03-20 10:27:55 2024-03-20 10:27:55 1 1 1
1 .0 0% | b7qnw60c5ghhu 4354576702770823574 2024-03-20 19:22:25 2024-03-20 19:22:25 1 1 1
1 .0 0% | bmgdy04a6mnk5 4354576702770823574 2024-03-20 17:44:45 2024-03-20 17:44:45 1 1 1
1 .0 0% | ch035j52xb28m 4354576702770823574 2024-03-20 15:01:44 2024-03-20 15:01:44 1 1 1
1 .0 0% | churdns3jjbj3 4354576702770823574 2024-03-20 14:47:49 2024-03-20 14:47:49 1 1 1
1 .0 0% | cqwgtbdwnjgfy 4354576702770823574 2024-03-20 10:38:16 2024-03-20 10:38:16 1 1 1
1 .0 0% | dgu1k30xzku8c 4354576702770823574 2024-03-20 17:46:11 2024-03-20 17:46:11 1 1 1
1 .0 0% | dzxxb7k8c8r6s 4354576702770823574 2024-03-20 17:06:22 2024-03-20 17:06:22 1 1 1
1 .0 0% | fjn40cddh6vt3 4354576702770823574 2024-03-20 12:21:17 2024-03-20 12:21:17 1 1 1
17 rows selected.
--//4354576702770823574 = 0x3c6e90708bd8c196,相当于64bit位呢？
--//注意看第一条的total seconds值很大,其他都很小.
[email protected]:1521/orcl> @ sql_id 5n49aq25gu7bf
--SQL_ID = 5n49aq25gu7bf
select ID,TENANT_ID as TenantId,TEST_NO,TEST_DATE,INST_ID,INST_NAME,ORDER_PAT_ID,PAT_TYPE_NAME,PAT_ID,PAT_BARCODE,VISIT_NO,PAT_NAME,DEPART_CODE,
--//....snip
BODY_NAME,EXPECT_OFFICE_NAME,HEIGHT,WEIGHT,PAGE_NO
from LIS_TEST where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state and rownum<5 ;
[email protected]:1521/orcl> @ sql_id 9rb30rt84k7ja
--SQL_ID = 9rb30rt84k7ja
insert into "LIS"."LIS_TEST"("ID", "TENANT_ID", "BARCODE", "TEST_NO", "TEST_DATE", "INST_ID", "NO_PREFIX", "INST_NAME", "ORDER_PAT_ID", "PAGE_NO", "IDENTITY_ID", "SOURCE_CODE", "PAT_TYPE_ID", "PAT_TYPE_NAME", "CHECKUP_CODE", "PAT_ID", "PAT_BARCODE", "VISIT_NO", "PAT_NAME", "DEPART_CODE", "AREA_CODE", "AREA_NAME", "DEPART_NAME", "BED", "PY", "PAT_SEX", "ORDER_PAT_AGE", "PAT_AGE", "PAT_BIRTHDAY", "AGE_UNIT", "REPORT_AGE", "ORDERITEM_IDS", "ORDER_ITEM_NAME", "STATE", "TEST_REMARK", "SAMPLE_CODE", "SAMPLE_TYPE_NAME", "OFFICE_ID", "DIAGNOSIS_CODE", "DIAGNOSIS_NAME", "OFFICE_NAME", "HEIGHT", "WEIGHT", "HOSP_ID", "HOSP_NAME", "DST_HOSP_ID", "DST_HOSP_NAME", "IS_EM", "SENT_INST_FLAG", "SENT_COUNT", "IS_EXCEPTION", "IS_REPEAT", "REPEAT_TIME", "IS_CRITICAL", "IS_SELF_DISPLAY", "REPEAT_USER_CODE", "ORDER_TIME", "ORDER_USER_CODE", "ORDER_USER_NAME", "PRINTBAR_TIME", "PRINTBAR_USER_CODE", "PRINTBAR_USER_NAME", "COLLECT_TIME", "COLLECT_USER_CODE", "COLLECT_USER_NAME", "SEND_TIME", "SEND_USER_CODE", "SEND_USER_NAME", "SERVE_TIME", "SERVE_USER_CODE", "SERVE_USER_NAME", "SIGN_TIME", "SIGN_USER_CODE", "EXPECT_OFFICE_ID", "EXPECT_OFFICE_NAME", "SIGN_USER_NAME", "TEST_TIME", "WRITE_USER_CODE", "TEST_USER_CODE_T", "TEST_USER_NAME_T", "TEST_USER_CODE", "TEST_USER_NAME", "SAMPLE_NO_TIME", "SAMPLE_NO_USER_CODE", "RESULT_FINISH_TIME", "FIRST_AUDIT_TIME", "FIRST_USER_CODE", "FIRST_USER_NAME", "AUDIT_USER_CODE", "AUDIT_USER_NAME", "AUDIT_USER_CODE_T", "AUDIT_USER_NAME_T", "AUDIT_TIME", "AUDIT_COUNT", "PRINT_TIME", "PRINT_USER_CODE", "PRINT_USER_NAME", "PRINT_FLAG", "UN_AUDIT_PERSON", "UN_AUDIT_TIME", "UN_AUDIT_COUT", "AUTO_AUDIT_FLAG", "AUTO_AUDIT_TIME", "AUDIT_ERR_MSG", "IS_AUDIT_LOCK", "IS_TC_ZWS", "IS_DC", "SPECIALITY_ID", "WSB_CODE", "PHONE_NO", "ORDER_REMARK", "IS_KN", "NOT_REPORT", "COST", "FEE", "COSTFLAG", "ITEM_COUNT", "ITEM_COUNT_ALL", "IS_BACK", "BACK_TIME", "IS_INITIAL", "ORIGINAL_BARCODE", "REPORT_TYPE", "FEE_CODE", "FEE_NAME", "EXPECT_REPORTTIME", "COLLECT_REPORTTIME", "EXPECT_SERVICETIME", "IS_MICRO_POS", "IS_INFECT", "CYCLE_PERIOD_CODE", "MSG_AFF", "MSG_AFF_TIME", "USER_NAME", "AFF_MSG", "PAT_ADDRESS", "IS_BABY", "PLACE", "BABY_NUM", "BROWSE_TIMES", "TUBE_ID", "SAMPLE_TRAIT", "IS_GCP", "COVID19_GNO", "INPUT_TYPE", "NAME_EN", "REAPET_REASON_CODE", "NATIONALITY", "COLLECT_DEPART", "SEND_EMR_FLAG", "APPEAR", "COLLECT_ADDRESS", "PAT_SOURCE", "PACK_BARCODE", "AFFIRM_CRIT", "CRIT_MODE", "BODY_NAME", "POLLUTE", "CARD_TYPE", "COPY_TS", "ISCA")
values (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, null, :p9, :p10, :p11, :p12, null, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20, null, :p21, :p22, :p23, :p24, :p25, :p26, :p27, :p28, :p29, :p30, null, :p31, :p32, :p33, null, :p34, :p35, null, null, :p36, :p37, :p38, :p39, :p40, :p41, :p42, :p43, :p44, null, :p45, :p46, null, :p47, :p48, :p49, :p50, :p51, :p52, :p53, :p54, :p55, null, null, null, null, null, null, :p56, :p57, :p58, :p59, :p60, :p61, :p62, null, null, :p63, :p64, :p65, :p66, null, null, null, null, null, null, null, null, null, :p67, null, null, null, :p68, null, null, :p69, :p70, null, null, :p71, :p72, :p73, :p74, null, null, :p75, :p76, :p77, :p78, :p79, :p80, :p81, :p82, :p83, null, :p84, null, :p85, null, null, :p86, null, null, :p87, :p88, :p89, :p90, null, null, null, null, :p91, null, null, null, :p92, null, null, null, :p93, :p94, null, null, null, :p95, null, null, null, null, null, null, null, null, null, null, null)
;
[email protected]:1521/orcl> @ sql_id f4ahy63yn6f8s
--SQL_ID = f4ahy63yn6f8s
insert into "LIS"."LIS_TEST"("ID", "TENANT_ID", "BARCODE", "TEST_NO", "TEST_DATE", "INST_ID", "NO_PREFIX", "INST_NAME", "ORDER_PAT_ID", "PAGE_NO", "IDENTITY_ID", "SOURCE_CODE", "PAT_TYPE_ID", "PAT_TYPE_NAME", "CHECKUP_CODE", "PAT_ID", "PAT_BARCODE", "VISIT_NO", "PAT_NAME", "DEPART_CODE", "AREA_CODE", "AREA_NAME", "DEPART_NAME", "BED", "PY", "PAT_SEX", "ORDER_PAT_AGE", "PAT_AGE", "PAT_BIRTHDAY", "AGE_UNIT", "REPORT_AGE", "ORDERITEM_IDS", "ORDER_ITEM_NAME", "STATE", "TEST_REMARK", "SAMPLE_CODE", "SAMPLE_TYPE_NAME", "OFFICE_ID", "DIAGNOSIS_CODE", "DIAGNOSIS_NAME", "OFFICE_NAME", "HEIGHT", "WEIGHT", "HOSP_ID", "HOSP_NAME", "DST_HOSP_ID", "DST_HOSP_NAME", "IS_EM", "SENT_INST_FLAG", "SENT_COUNT", "IS_EXCEPTION", "IS_REPEAT", "REPEAT_TIME", "IS_CRITICAL", "IS_SELF_DISPLAY", "REPEAT_USER_CODE", "ORDER_TIME", "ORDER_USER_CODE", "ORDER_USER_NAME", "PRINTBAR_TIME", "PRINTBAR_USER_CODE", "PRINTBAR_USER_NAME", "COLLECT_TIME", "COLLECT_USER_CODE", "COLLECT_USER_NAME", "SEND_TIME", "SEND_USER_CODE", "SEND_USER_NAME", "SERVE_TIME", "SERVE_USER_CODE", "SERVE_USER_NAME", "SIGN_TIME", "SIGN_USER_CODE", "EXPECT_OFFICE_ID", "EXPECT_OFFICE_NAME", "SIGN_USER_NAME", "TEST_TIME", "WRITE_USER_CODE", "TEST_USER_CODE_T", "TEST_USER_NAME_T", "TEST_USER_CODE", "TEST_USER_NAME", "SAMPLE_NO_TIME", "SAMPLE_NO_USER_CODE", "RESULT_FINISH_TIME", "FIRST_AUDIT_TIME", "FIRST_USER_CODE", "FIRST_USER_NAME", "AUDIT_USER_CODE", "AUDIT_USER_NAME", "AUDIT_USER_CODE_T", "AUDIT_USER_NAME_T", "AUDIT_TIME", "AUDIT_COUNT", "PRINT_TIME", "PRINT_USER_CODE", "PRINT_USER_NAME", "PRINT_FLAG", "UN_AUDIT_PERSON", "UN_AUDIT_TIME", "UN_AUDIT_COUT", "AUTO_AUDIT_FLAG", "AUTO_AUDIT_TIME", "AUDIT_ERR_MSG", "IS_AUDIT_LOCK", "IS_TC_ZWS", "IS_DC", "SPECIALITY_ID", "WSB_CODE", "PHONE_NO", "ORDER_REMARK", "IS_KN", "NOT_REPORT", "COST", "FEE", "COSTFLAG", "ITEM_COUNT", "ITEM_COUNT_ALL", "IS_BACK", "BACK_TIME", "IS_INITIAL", "ORIGINAL_BARCODE", "REPORT_TYPE", "FEE_CODE", "FEE_NAME", "EXPECT_REPORTTIME", "COLLECT_REPORTTIME", "EXPECT_SERVICETIME", "IS_MICRO_POS", "IS_INFECT", "CYCLE_PERIOD_CODE", "MSG_AFF", "MSG_AFF_TIME", "USER_NAME", "AFF_MSG", "PAT_ADDRESS", "IS_BABY", "PLACE", "BABY_NUM", "BROWSE_TIMES", "TUBE_ID", "SAMPLE_TRAIT", "IS_GCP", "COVID19_GNO", "INPUT_TYPE", "NAME_EN", "REAPET_REASON_CODE", "NATIONALITY", "COLLECT_DEPART", "SEND_EMR_FLAG", "APPEAR", "COLLECT_ADDRESS", "PAT_SOURCE", "PACK_BARCODE", "AFFIRM_CRIT", "CRIT_MODE", "BODY_NAME", "POLLUTE", "CARD_TYPE", "COPY_TS", "ISCA")
values (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, null, :p9, :p10, :p11, :p12, null, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20, :p21, :p22, :p23, :p24, :p25, :p26, :p27, :p28, :p29, :p30, :p31, null, :p32, :p33, :p34, null, null, :p35, null, null, :p36, :p37, :p38, :p39, :p40, :p41, :p42, :p43, :p44, null, :p45, :p46, null, :p47, :p48, :p49, :p50, :p51, :p52, :p53, :p54, :p55, :p56, :p57, :p58, :p59, :p60, :p61, :p62, :p63, :p64, :p65, :p66, :p67, :p68, null, null, :p69, :p70, :p71, :p72, null, null, null, null, null, null, null, null, null, :p73, null, null, null, :p74, null, null, :p75, :p76, null, null, :p77, :p78, :p79, :p80, null, :p81, :p82, :p83, :p84, :p85, :p86, :p87, :p88, :p89, :p90, null, :p91, null, :p92, null, null, :p93, null, null, :p94, :p95, :p96, :p97, null, null, null, null, :p98, null, null, null, :p99, null, null, null, :p100, :p101, null, null, null, :p102, null, null, null, null, null, null, null, null, null, null, null)
;
--//第1条select语句,后面2条都是insert，仅仅是有一些有值，有一些为NULL(注意看:p21)，产生许多变化正常的，但是根据以前我所
--//知道的FORCE_MATCHING_SIGNATURE计算规则,FORCE_MATCHING_SIGNATURE应该不同.
--//但是第一条是select语句出现,从概率讲重合的可能性很小。
[email protected]:1521/orcl> SELECT sql_id
, FORCE_MATCHING_SIGNATURE
, EXACT_MATCHING_SIGNATURE
FROM gv$sqlarea
WHERE sql_id in ('5n49aq25gu7bf','9rb30rt84k7ja','f4ahy63yn6f8s');
SQL_ID FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------- ------------------------ ------------------------
5n49aq25gu7bf 4354576702770823574 4354576702770823574
9rb30rt84k7ja 0 0
f4ahy63yn6f8s 0 0
--//实际上insert语句FORCE_MATCHING_SIGNATURE，EXACT_MATCHING_SIGNATURE记录的是0.不知道为什么v$active_session_history记录
--//会出现这样的情况,算是优化过程中一个小的插曲。
--//我猜测程序的执行代码先执行5n49aq25gu7bf,然后执行insert(表名一致),抓取的FORCE_MATCHING_SIGNATURE信息没有即时清除,这样
--//记录在insert语句v$active_session_history视图里面就是先前的FORCE_MATCHING_SIGNATURE值.
--//我检查发现这种FORCE_MATCHING_SIGNATURE重合的现象都是类似上面看到的情况，如何执行fms脚本时规避这些sql语句呢？不需要显
--//示这些FORCE_MATCHING_SIGNATURE.
--//看V$ACTIVE_SESSION_HISTORY视图可以发现字段sql_opname记录操作类型，排除掉这些就可以了,加入条件sql_opname<>'INSERT'.
--//select * from V$ACTIVE_SESSION_HISTORY where FORCE_MATCHING_SIGNATURE=4354576702770823574
--//修改如下：
$ 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;
[email protected]:1521/orcl> @ fms.sql 5 15 &day
FORCE_MATCHING_SIGNATURE SQL_ID_COUNT TOTAL_SECONDS
------------------------ ------------ -------------
17409746777760600368 26 249
11034937719941372916 13 197
8097234170242639654 11 130
16405634091854084691 7 122
11135588330860454200 14 119
7055080024159627634 24 76
6462934727001839235 23 24
11135749189737159965 11 21
829290737292318906 14 17
9685323836218277500 5 5
10 rows selected.
--//这样就规避这些相关insert语句了。
--//附上dfms.sql的修改。
$ cat dfms.sql
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name: dfms.sql v1.0
-- Purpose: Query dba_hist_active_sess_history Force_Matching_Signature the same, sql_id different of information
-- Author: lfree
--
-- Usage:
-- @ idfms <count(*)_number> <display_record_number> <fromtime> <totime>
-- <count(*)__number>=2>
--
-- Example:
-- @ fms 5 30 &day
--
-- Other:
-- This script uses only the DBA_HIST_ACTIVE_SESS_HISTORY
-- @ fms.sql for accessiong the in-memory GV$ACTIVE_SESSION_HISTORY
--
--------------------------------------------------------------------------------
WITH a1 AS (SELECT sql_id,force_matching_signature, count(*) cnt1
FROM dba_hist_active_sess_history
WHERE force_matching_signature <> 0 AND sample_time BETWEEN &3 AND &4 and dbid = (SELECT d.dbid FROM v$database d) and sql_opname<>'INSERT'
GROUP BY sql_id,force_matching_signature ) ,
a2 AS (SELECT force_matching_signature, count(*) sql_id_count,sum(cnt1)*10 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;