我要评论
[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的方式操作.
- 站内搜索
发表评论