附件
前言
在某客户审核问题SQL时,客户提出需求,想快速提取没有使用绑定变量的SQL,遂成此文。测试结论
1,使用基于v$sql.force_matching_signature分组提取当前共享池中未使用绑定变量的sql
2,以快照和force_matching_signature分组为基准获取历史执行过的未使用绑定变量的sql
3,本文只是一个思路,供大家参与,细节脚本请大家具体进行调整
测试明细
会话1
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table t_bind(a int,b int);
Table created.
SQL> select count(a) from t_bind where a=1;
COUNT(A)
----------
0
SQL> select count(a) from t_bind where a=2;
COUNT(A)
----------
0
SQL> select count(a) from t_bind where a=3;
COUNT(A)
----------
0
会话2
可见未使用绑定变量的SQL force_matching_signature值是相同的
SQL> select sql_id,sql_text,force_matching_signature from v$sql where sql_text like '%select count(a) from t_bind where a=%' order by force_matching_signature;
SQL_ID SQL_TEXT FORCE_MATCHING_SIGNATURE
------------- -------------------------------------------------- ------------------------
0mkg17vkss5h6 select count(a) from t_bind where a=3 1.5692E+19
g3dsghmr0wa8z select count(a) from t_bind where a=1 1.5692E+19
7r5cv1k227uvr select count(a) from t_bind where a=2 1.5692E+19
f2q0pf2amutbj select sql_id,sql_text,force_matching_signature fr 1.5697E+19
om v$sql where sql_text like '%select count(a) fro
m t_bind where a=%'
获取执行次数大于1且force_matching_signature大于0,而且分组后sql_id个数也要大于1,这样才有意义,
SQL> select force_matching_signature,count(sql_id) from v$sql where force_matching_signature>0 and executions>1 group by force_matching_signature having count(sql_id)>1;
FORCE_MATCHING_SIGNATURE COUNT(SQL_ID)
------------------------ -------------
1.3565E+19 2
5.2489E+18 2
1.3389E+19 2
1.4356E+18 2
1.2314E+19 2
6.4341E+18 2
9.1945E+18 2
1.2005E+19 2
1.0844E+19 2
1.0080E+18 2
6.0433E+18 2
FORCE_MATCHING_SIGNATURE COUNT(SQL_ID)
------------------------ -------------
5.2882E+18 2
8.3410E+18 2
1.0165E+19 2
1.8036E+19 2
1.4857E+19 2
1.0497E+19 2
7.2390E+18 2
4.5663E+18 2
1.7325E+18 2
7.9281E+18 2
1.7880E+19 2
FORCE_MATCHING_SIGNATURE COUNT(SQL_ID)
------------------------ -------------
1.8142E+19 2
4.4691E+18 2
6.2568E+18 2
6.5540E+18 2
4.4839E+18 2
4.6502E+18 2
1.7601E+19 2
4.2544E+17 2
7.3444E+17 2
1.8126E+19 2
1.7274E+17 2
FORCE_MATCHING_SIGNATURE COUNT(SQL_ID)
------------------------ -------------
3.4930E+18 2
4.9079E+18 2
9.8323E+18 2
1.7646E+19 2
1.1964E+19 2
8.8245E+18 2
1.2894E+19 2
7.1257E+18 2
1.2042E+19 2
1.5496E+18 2
5.4942E+18 2
44 rows selected.
SQL>
获取未使用绑定变量的SQL
SQL> select distinct sql_id from v$sql where force_matching_signature in (select force_matching_signature from v$sql where force_matching_signature>0 and executions>1 group by force_matching_signature having count(sql_id)>1);
SQL_ID
-------------
9gkq7rruycsjp
d8pya4s4fx4cr
axmdf8vq7k1rh
3ktacv9r56b51
4fbrr0fay6z3y
3g7sxtj9d6zd3
cb21bacyh3c7d
7mafyb7cbry43
1gfaj4z5hn1kf
20vv6ttajyjzq
9rfqm06xmuwu0
SQL_ID
-------------
5n1fs4m2n2y0r
6aq34nj2zb2n7
bd4bznqbq73hk
7r5cv1k227uvr
9p6bq1v54k13j
ga9j9xk5cy9s0
342hx2j0kkyv2
cjk1ffy5kmm5s
0fr8zhn4ymu3v
3w4qs0tbpmxr6
2fakq32xxbzdx
SQL_ID
-------------
cvn54b7yz0s8u
87gaftwrm2h68
37n8tzu0vzf38
dcstr36r0vz0d
gx4mv66pvj3xz
2r4t4dj3gtfg8
b1wc53ddd6h3p
32bhha21dkv0v
8swypbbr0m372
1a8n1zgb7m90w
1gu8t96d0bdmu
SQL_ID
-------------
0mkg17vkss5h6
33my5uzwtnugv
cm5vu20fhtnq1
3nkd3g3ju5ph1
c6awqs517jpj0
9rzz4d2a5xtyb
2q93zsrvbdw48
5u6squzyq62tm
grwydz59pu6mc
0m78skf1mudnb
a84mpauy6amqw
SQL_ID
-------------
7nuw4xwrnuwxq
9tgj4g8y4rwy8
6qz82dptj0qr7
du14b63vs0rvq
721d7993vjur9
7sx5p1ug5ag12
53saa2zkr6wc3
7ng34ruy5awxq
39m4sx9k63ba2
74anujtt8zw4h
9g485acn2n30m
SQL_ID
-------------
g3dsghmr0wa8z
f3g84j69n0tjh
83taa7kaw59c1
az9k7rpgs1yvs
3nksz8xfcpzmu
d7y4tdacc7f3j
g3wrkmxkxzhf2
62 rows selected.
获取历史执行过的未使用绑定变量的SQL,以快照和force_matching_signature分组为基准
SQL> select distinct snap_id,sql_id from (select snap_id,force_matching_signature from dba_hist_sqlstat where executions_delta>1 and force_matching_signature>0 and parsing_schema_name<>'SYS' group by snap_id,force_matching_signature);
个人简介
8年oracle从业经验,具备丰富的oracle技能,目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户:
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式:
手机:18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博:wisdomone9
qq群:275813900
itpub博客名称:wisdomone1
http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1696572/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1696572/