[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_SIGNATURE0.

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_SIGNATURE0,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 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 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  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","ER_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 BID,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的方式操作.
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告