[20240321]分析FORCE_MATCHING_SIGNATURE重合的奇怪情况.txt

来源：这里教程网时间：2026-03-03 19:43:00 作者：

[20240321]分析FORCE_MATCHING_SIGNATURE重合的奇怪情况.txt --//生产系统遇到1个FORCE_MATCHING_SIGNATURE重合的奇怪现象，一般情况都是相似的sql语句(没有使用绑定变量的sql语句)， --//FORCE_MATCHING_SIGNATURE相同。 --//注：11g之前如果绑定变量与常量混合，会出现EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE的情况. 1.环境: SYS@192.168.100.235: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. SYS@192.168.100.235: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的情况： SYS@192.168.100.235: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值很大,其他都很小. SYS@192.168.100.235: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 ; SYS@192.168.100.235: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) ; SYS@192.168.100.235: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语句出现,从概率讲重合的可能性很小。 SYS@192.168.100.235: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; SYS@192.168.100.235: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;

编辑推荐：

返回列表

[20240321]分析FORCE_MATCHING_SIGNATURE重合的奇怪情况.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推