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