[20240325]FORCE_MATCHING_SIGNATURE与DML.txt

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

[20240325]FORCE_MATCHING_SIGNATURE与DML.txt --//生产系统遇到1个FORCE_MATCHING_SIGNATURE重合的奇怪现象，一般情况都是相似的sql语句(没有使用绑定变量的sql语句)， --//FORCE_MATCHING_SIGNATURE相同。 --//实际上insert语句真实FORCE_MATCHING_SIGNATURE=0,但是在v$active_session_history视图里面记录的不是0.补充看看 --//update,delete的情况. 1.环境: SCOTT@test01p> @ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 2.测试: SCOTT@test01p> create table deptx as select * from dept; Table created. SCOTT@test01p> update deptx set dname='OPERATIONs' where deptno=40; 1 row updated. SCOTT@test01p> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 195324603 91tfrg45u8upv 0 27323 953445556 ba46abb 2024-03-24 20:15:11 16777216 SCOTT@test01p> SELECT sql_id , FORCE_MATCHING_SIGNATURE , EXACT_MATCHING_SIGNATURE FROM v$sqlarea WHERE sql_id ='91tfrg45u8upv'; SQL_ID FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ------------- ------------------------ ------------------------ 91tfrg45u8upv 6835334835661492384 11664111839893633356 SCOTT@test01p> rollback ; Rollback complete. SCOTT@test01p> delete from deptx where deptno=40; 1 row deleted. SCOTT@test01p> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2038036759 gpm05hdwrmy8r 0 129303 4270570698 7979f917 2024-03-24 20:16:53 16777216 SCOTT@test01p> SELECT sql_id , FORCE_MATCHING_SIGNATURE , EXACT_MATCHING_SIGNATURE FROM v$sqlarea WHERE sql_id ='gpm05hdwrmy8r'; SQL_ID FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ------------- ------------------------ ------------------------ gpm05hdwrmy8r 2492147175363620523 10663115601857554706 --//可以看出update,delete语句FORCE_MATCHING_SIGNATURE<>0. 3.继续看看insert: SCOTT@test01p> SELECT sql_id , FORCE_MATCHING_SIGNATURE , EXACT_MATCHING_SIGNATURE,sql_text FROM v$sqlarea WHERE sql_id ='46b7gx2ucjuv0'; SQL_ID FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE SQL_TEXT ------------- ------------------------ ------------------------ ------------------------------------------------------------ 46b7gx2ucjuv0 0 0 insert into deptx values (50 ,'1','a') --//确实insert语句的FORCE_MATCHING_SIGNATURE=0. 4.总结: --//对于dml语句,update,delet的FORCE_MATCHING_SIGNATURE<>0,insert FORCE_MATCHING_SIGNATURE=0. --//至于oracle为什么这样设计,也许insert语句都是情况下涉及1条记录.也许oracle认为计算FORCE_MATCHING_SIGNATURE有点多余. --//这样通过FORCE_MATCHING_SIGNATURE定位没有使用绑定变量的insert语句就有点不可行. --//上面的补充测试说明总结有点问题,看下面的测试,不再说明. 5.补充: --//上班在19c下测试看看: SYS@192.168.100.235:1521/orcl> select * from V$SQLCOMMAND where COMMAND_NAME in ('INSERT','UPDATE','DELETE','SELECT'); COMMAND_TYPE COMMAND_NAME CON_ID ------------ ------------ ------ 2 INSERT 0 3 SELECT 0 6 UPDATE 0 7 DELETE 0 SYS@192.168.100.235:1521/orcl> select exact_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,COMMAND_TYPE,sql_id,sql_text c100 from v$sqlarea where COMMAND_TYPE in (6) and rownum<=3; EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE COMMAND_TYPE SQL_ID C100 ------------------------ ------------------------ ------------ ------------- --------------------------------------------------------------------------------------- 17865118844887934453 17865118844887934453 6 brdyxt33f000j update his_temp_order set state=:state where order_id in('3^1^502153150') 5091739948496261956 5091739948496261956 6 74zvqdmua800s update his_temp_order set state=:state where order_id in('1^2^28934060','1^2^28934060') 4225582535830192454 4225582535830192454 6 07sn9j5nrs01j update his_temp_order set state=:state where order_id in('2^1^201449744') --//注意EXACT_MATCHING_SIGNATURE=FORCE_MATCHING_SIGNATURE,主要因为常量与绑定变量混合. --//实际上第1条,第3条类似,oracle 23c版本以后解决了这个问题. SYS@192.168.100.235:1521/orcl> select exact_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,COMMAND_TYPE,sql_id,sql_text c100 from v$sqlarea where COMMAND_TYPE in (6) and FORCE_MATCHING_SIGNATURE=0; EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE COMMAND_TYPE SQL_ID C100 ------------------------ ------------------------ ------------ ------------- ---------------------------------------------------------------------------------------------------- 0 0 6 2vb9hsvpw0gtg update /* QOSD */ /*+ index(es) */ exp_stat$ es set dynamic_cost = :3, eval_count = :4, ctime = :6, last_modified = :7 where exp_id = :1 and objn = :2 and snapshot_id = :5 0 0 6 4m7m0t6fjcs5x update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audi t$=:9,flags=:10 where obj#=:1 0 0 6 9zg9qd9bm4spu update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where use r#=:1 0 0 6 c3utnxsnrx8tk update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=: 12,spare1=:13,spare2=:14,spare3=:15,signature=:16,spare7=:17,spare8=:18,spare9=:19, dflcollid=decode (:20,0,null,:20),creappid=:21,creverid=:22, modappid=:23,modverid=:24,crepatchid=:25,modpatchid=:26 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subnam e is null 0 0 6 0dfxfyy5r32qq update /* QOSD */ /*+ index(eo) */ exp_obj$ eo set exp_cnt = :3 where objn = :1 and snapshot_id = :2 0 0 6 4usy97b1zbbj5 update /* QOSD */ /*+ index(do) */ opt_directive_own$ do set dir_cnt = :2 where dir_own# = :1 0 0 6 0kkhhb2w93cx0 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11, iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3 7 rows selected. --//update还是小量FORCE_MATCHING_SIGNATURE=0的情况,似乎这些都是递归执行的sql语句. SYS@192.168.100.235:1521/orcl> select exact_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,COMMAND_TYPE,sql_id,sql_text c120 from v$sqlarea where COMMAND_TYPE in (7) and rownum<=3; EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE COMMAND_TYPE SQL_ID C120 ------------------------ ------------------------ ------------ ------------- ------------------------------------------------------------------------------------------------------------------------ 7757287074397251898 7757287074397251898 7 0f7zrpy7d002c delete from WRH$_DISPATCHER tab where (dbid = :dbid) and snap_id in (select snap_id from X$KEWRIPSL) 17959689988878125270 17959689988878125270 7 8ndb2w3rdc0tv delete from WRH$_MEMORY_RESIZE_OPS tab where (dbid = :dbid) and snap_id in (select snap_id from X$KEWRIPSL) 5160622103966497918 5160622103966497918 7 9k2d87r4cn0ux DELETE FROM wri$_adv_rationale a WHERE a.task_id = :task_id_num AND (:execution_name IS NULL OR :executio n_name1 = a.exec_name) SYS@192.168.100.235:1521/orcl> select exact_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,COMMAND_TYPE,sql_id,sql_text c100 from v$sqlarea where COMMAND_TYPE in (7) and FORCE_MATCHING_SIGNATURE=0; EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE COMMAND_TYPE SQL_ID C100 ------------------------ ------------------------ ------------ ------------- ---------------------------------------------------------------------------------------------------- 0 0 7 4rs3f2phhsb80 delete /* KSXM:CLEAN_DML_INF *//*+ dynamic_sampling(4) */ from sys.mon_mods_all$ m where not ex ists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = m.obj#) and rownum <= :1 0 0 7 6kucyxfkgsh2c delete /*+ dynamic_sampling(4) */ /* KSXM:CLEAN_COLGR_USAGE */ from sys.col_group_usage$ c where (((timestamp < sysdate - 367) and bitand(flags, 8) = 0) or not exists (select / *+ unnest */ 1 from sys.obj$ o where o.obj# = c.obj#)) and c.obj# < :1 and rownum <= :2 0 0 7 a81vzf0fa0q1p delete /* KSXM:CLEAN_PEND_IND *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_ind_history i w here not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = i.obj#) and savti me >= timestamp '3000-12-01 01:00:00 -0:0' and rownum <= :1 0 0 7 caz4ct3720rcy delete /* QOSD */ /*+ index(fo) */ from opt_finding_obj$ fo where f_id = :1 0 0 7 0vg7j6xx91fw8 delete /*+ index(d) */ from opt_directive$ d where dir_own# = :1 and dir_id = :2 0 0 7 av0kjjbhc642q delete from objauth$ where obj#=:1 0 0 7 4faa5w420ua14 delete /* KSXM:CLEAN_PEND_HIST *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history g where not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = g.obj#) and savtime >= timestamp '3000-12-01 01:00:00 -0:0' and rownum <= :1 0 0 7 fnafmumu52s48 delete /* KSXM:CLEAN_USER_PREF *//*+ dynamic_sampling(4) */ from sys.optstat_user_prefs$ p where not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = p.obj#) and rownum <= : 1 0 0 7 g94wn7w0dr4tp delete /* KSXM:CLEAN_PEND_COL *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history h where not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = h.obj#) and savtime >= timestamp '3000-12-01 01:00:00 -0:0' and rownum <= :1 0 0 7 gxrr466g0v9ck delete /* KSXM:CLEAN_PEND_TAB *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_tab_history t w here not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = t.obj#) and savti me >= timestamp '3000-12-01 01:00:00 -0:0' and rownum <= :1 0 0 7 59vjj34vugaav delete from obj$ where obj# = :1 0 0 7 fqwrqhnvszdyc delete /* QOSD*/ /*+ index(f) */ from opt_finding$ f where f_id = :1 0 0 7 dqucusk8avvuh delete /* KSXM:CLEAN_COL_USAGE *//*+ dynamic_sampling(4) */ from sys.col_usage$ c where ((times tamp < sysdate - 367) or not exists (select /*+ unnest */ 1 from sys.obj$ o whe re o.obj# = c.obj#)) and c.obj# < :1 and rownum <= :2 13 rows selected. --//delte语句与uodate语句类似. SYS@192.168.100.235:1521/orcl> select * from (select exact_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,COMMAND_TYPE,count(*) from v$sqlarea where COMMAND_TYPE in (2) group by exact_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,COMMAND_TYPE) where rownum<=5; EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE COMMAND_TYPE COUNT(*) ------------------------ ------------------------ ------------ ---------- 0 0 2 594 393115081183021282 393115081183021282 2 1 518037916034776970 518037916034776970 2 1 554814305626883822 554814305626883822 2 1 568855978993142464 568855978993142464 2 1 --//看来我前面的测试存在问题,可以发现insert语句还是存在FORCE_MATCHING_SIGNATURE<>0的情况.虽然大部分是等于0的情况,还是存 --//在少量<>0的情况,注意这些语句的count(*)=1,看看是那些语句. SYS@192.168.100.235:1521/orcl> select sql_id , sql_fulltext c200 from v$sqlarea where FORCE_MATCHING_SIGNATURE=393115081183021282; SQL_ID C200 ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 9mz660r1c0z3f INSERT INTO "SYS"."WRM$_PDB_INSTANCE" "A1" ("DBID","INSTANCE_NUMBER","STARTUP_TIME","CON_DBID","OPEN_TIME","OPEN_MODE","PDB_NAME","SNAP_ID","STARTUP_TIME_TZ","OPEN_TIME_TZ") SELECT :DBID,:INSTANCE_NU MBER,:STARTUP_TIME,"A2"."DBID",CAST(("A2"."STIME" AT TIME ZONE 'UTC')+:TIMEZONE AS timestamp(3)),DECODE(DECODE("A2"."STATE",0,'MOUNTED',1,'READ WRITE',2,'READ ONLY',3,'MIGRATE'),'READ WRITE','OPEN','R EAD ONLY','READ ONLY','INVALID'),"A2"."NAME",:SNAP_ID,TO_TIMESTAMP_TZ(TO_CHAR(:STARTUP_TIME,'YYYY/MM/DD HH24:MI:SS.FF3')||' '||TO_CHAR(EXTRACT(HOUR FROM :TIMEZONE),'fm00')||':'||TO_CHAR(EXTRACT(MINUTE FROM :TIMEZONE),'fm00'),'YYYY/MM/DD HH24:MI:SS.FF3 TZH:TZM'),"A2"."STIME" FROM "SYS"."X$CON"@! "A2" WHERE (DECODE("A2"."STATE",0,'MOUNTED',1,'READ WRITE',2,'READ ONLY',3,'MIGRATE')='READ WRITE' OR DE CODE("A2"."STATE",0,'MOUNTED',1,'READ WRITE',2,'READ ONLY',3,'MIGRATE')='READ ONLY') AND NOT EXISTS (SELECT 1 FROM "SYS"."WRM$_PDB_INSTANCE" "A3" WHERE "A3"."DBID"=:DBID AND "A3"."INSTANCE_NUMBER"=:I NSTANCE_NUMBER AND "A3"."STARTUP_TIME"=:STARTUP_TIME AND "A3"."CON_DBID"="A2"."DBID" AND "A3"."OPEN_TIME"=CAST(("A2"."STIME" AT TIME ZONE 'UTC')+:TIMEZONE AS timestamp(3))) AND "A2"."INST_ID"=:INSTANC E_NUMBER_01 AND "A2"."DTIME"=0 SYS@192.168.100.235:1521/orcl> select sql_id , sql_fulltext c200 from v$sqlarea where FORCE_MATCHING_SIGNATURE=518037916034776970; SQL_ID C200 ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- g8kn776jn7u28 INSERT INTO WRH$_SERVICE_NAME (dbid, per_pdb, con_dbid, snap_id, service_name_hash, service_name ) SELECT dbid, t2.per_pdb, con_dbid, snap_id, service_name_hash, service_name FROM x$kewrattrn ew t1, (SELECT * from WRHS$_SERVICE_NAME wrhs WHERE wrhs.dbid = :dbid AND wrhs.stage_id = :sweep_stgid AND wrhs.stage_inst_id = :sweep_inst ) t2 WHERE t1.NUM1_KEWRATTR = t2.SERVICE_ NAME_HASH AND t1.NUM2_KEWRATTR = t2.CON_DBID SYS@192.168.100.235:1521/orcl> select sql_id , sql_fulltext c200 from v$sqlarea where FORCE_MATCHING_SIGNATURE=568855978993142464; SQL_ID C200 ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- bfgtjwq3m8smh INSERT INTO "SYS"."WRH$_JAVA_POOL_ADVICE" "A1" ("DBID","PER_PDB","CON_DBID","SNAP_ID","INSTANCE_NUMBER","JAVA_POOL_SIZE_FOR_ESTIMATE","JAVA_POOL_SIZE_FACTOR","ESTD_LC_SIZE","ESTD_LC_MEMORY_OBJECTS"," ESTD_LC_TIME_SAVED","ESTD_LC_TIME_SAVED_FACTOR","ESTD_LC_LOAD_TIME","ESTD_LC_LOAD_TIME_FACTOR","ESTD_LC_MEMORY_OBJECT_HITS") SELECT :DBID,0,:SRCDBID,:SNAP_ID,:INSTANCE_NUMBER,"A2"."JAVA_SIZE",ROUND("A 2"."JAVA_SIZE"/"A2"."BASEJAVA_SIZE",4),"A2"."KGLJSIM_SIZE","A2"."KGLJSIM_OBJS","A2"."KGLJSIM_TIMESAVE",DECODE("A2"."KGLJSIM_BASETIMESAVE",0,TO_NUMBER(NULL),ROUND("A2"."KGLJSIM_TIMESAVE"/"A2"."KGLJSIM_ BASETIMESAVE",4)),"A2"."KGLJSIM_PARSETIME",DECODE("A2"."KGLJSIM_BASEPARSETIME",0,TO_NUMBER(NULL),ROUND("A2"."KGLJSIM_PARSETIME"/"A2"."KGLJSIM_BASEPARSETIME",4)),"A2"."KGLJSIM_HITS" FROM "SYS"."X$KGLJS IM"@! "A2" WHERE "A2"."INST_ID"=:INSTANCE_NUMBER_01 --//可以看出这类insert语句的特点就是采用的都是insert+select的方式操作.

编辑推荐：

返回列表

[20240325]FORCE_MATCHING_SIGNATURE与DML.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推