附件

查询没有使用绑定变量bind variable的sql--part1.pdf

前言

在某客户审核问题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审核项目
深圳穆迪软件有限公司数据库性能优化项目

联系方式：

手机：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/