[20240325]FORCE_MATCHING_SIGNATURE与DML.txt

[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的方式操作.

热门相关:全民女神之重生腹黑千金   福晋有喜:四爷,宠上天!   灭世魔帝   都市之九天大帝   帝少夜宠:小甜妻,乖!