如何查询数据库中未使用绑定变量的SQL语句？

原创张鹏 2023-08-08

200

1098.如何查询数据库中未使用绑定变量的SQL语句？
[TOC]
背景：
我们会经常在一些客户的AWR报告中，能够看到如下类似信息，SQL的前缀部分都一样，执行时间也基本一致，详细查看具体的SQL_TEXT后，我们可以看到仅仅是where条件中的变量值不一样，其他都一样，没有采用绑定变量的方式，而是一个个常量。

对于这种在AWR报告中，能够体现出来的，我们比较容易发现，可及时反馈给开发人员处理。但是在一些环境中，系统中其实存在较多的未使用绑定变量的SQL，但是在AWR报告TOP SQL部分，并没有体现出来，这个时候，就需要我们去数据库中手动捞取。
当Oracle中存在大量的类似sql，基本结构一样，仅where条件的取值不一样时，我们应该采用绑定变量的方法，来减少sql的硬解析，能够提高数据库性能，避免出现shared pool相关的等待事件，同时也能节约CPU资源。
下面我们通过案例，如果查询数据库中未使用绑定变量的SQL？

构造环境
– 创建测试表，并插入数据
SQL> set timing on
SQL> drop table t purge;
Table dropped.
Elapsed: 00:00:00.18
SQL> create table t(x int);
Table created.
Elapsed: 00:00:00.04
SQL> begin
2 for i in 1 … 1000
3 loop
4 execute immediate
5 ‘insert into t values (:x)’ using i;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> select count() from t;
COUNT()

Elapsed: 00:00:00.00
2. 案例演示
– 方便演示，我们清理一下shared pool
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.12
2.1 未使用绑定变量
SQL> begin
2 for i in 1 … 1000
3 loop
4 execute immediate
5 ‘select /tag1/ count() from t where x= ‘||i||’’;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38
2.2 使用绑定变量
SQL> begin
2 for i in 1 … 1000
3 loop
4 execute immediate
5 'select /tag2/ count() from t where x= :1’ using i;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
3. 获取未使用绑定变量的SQL
方法一：通过force_matching_signature分析
10g以后vSQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为: The signature used when the CURSOR_SHARING parameter is set to FORCE，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。我们知道，如果cursor sharing设置为force ， oracle将类似的SQL的谓词用一个变量代替，同时将它们看做同一条SQL语句处理。 SQL> set numwidth 20 SQL> select force_matching_signature,count(1) from vsql group by force_matching_signature order by count(1) desc;
FORCE_MATCHING_SIGNATURE COUNT(1)

11420119939742656807                 1000
                   0                   14
 4650186927289073934                    2
 6434074771071323635                    2
 1435631005720608251                    1
12313764141852424472                    1
 9194475184364435657                    1
12005390545862180746                    1

SQL> select sql_text from v$sql where force_matching_signature=11420119939742656807 and rownum<10;
SQL_TEXT

select /tag1/ count() from t where x= 782
select /tag1/ count() from t where x= 497
select /tag1/ count() from t where x= 286
select /tag1/ count() from t where x= 937
select /tag1/ count() from t where x= 458
select /tag1/ count() from t where x= 969
select /tag1/ count() from t where x= 637
select /tag1/ count() from t where x= 161
select /tag1/ count(*) from t where x= 742
9 rows selected.
Elapsed: 00:00:00.00
将上面SQL整合到一起，如下：
select sql_text, sql_id, executions, parse_calls,force_matching_signature from v $sql where force_matching_signature in (select force_matching_signature from ( select force_matching_signature,count(1) from v$ sql group by force_matching_signature order by count(1) desc
) where rownum<10);

方法二：通过sql文本分析
select substr(sql_text,1,50),count(1) from v$sql group by substr(sql_text,1,50) order by count(1) desc;

思考绑定变量性能
4.1 未使用绑定变量
select t.sql_text, t.sql_id, t.executions, t.parse_calls,force_matching_signature
from v$sql t
where sql_text like ‘select /tag1/ count(*) from t%’;

结论：产生1000个的类似sql，基本结构一样，仅where条件的取值不一样，每个SQL都执行一次，解析一次。
4.2 使用绑定变量
select t.sql_text, t.sql_id, t.executions, t.parse_calls,force_matching_signature
from v$sql t
where sql_text like ‘select /tag2/ count(*) from t%’;

结论：1. 仅产生1个SQL，执行1000次，仅解析一次。和未使用绑定变量相比，性能更好。
2. 从案例演示中，2个SQL执行时间来看，使用绑定变量的方式也比未使用绑定变量的方式快很多。
Oracle中如何查找未使用绑定变量的SQL语句？
利用VSQL 视图的 FORCE_MATCHING_SIGNATURE 字段可以识别可能从绑定变量或 CURSOR_SHARING 获益的 SQL 语句。如果 SQL 已使用绑定变量或者 CURSOR_SHARING ，那么 FORCE_MATCHING_SIGNATURE 在对其进行标识时将给出同样的签名。换句话说，如果两个 SQL语句除了字面量的值之外都是相同的，它们将拥有相同的 FORCE_MATCHING_SIGNATURE ，这意味着如果为它们提供了绑定变量或者CURSOR_SHARING ，它们就成了完全相同的语句。所以，使用 FORCE_MATCHING_SIGNATURE 字段可以识别没有使用绑定变来的 SQL 语句。可以使用如下的SQL 语句来查询： with force_mathces as (select l.force_matching_signature, max(l.sql_id || l.child_number) max_sql_child, dense_rank() over(order by count(*) desc) ranking, count(*) counts from vsql l
where l.force_matching_signature <> 0
and l.parsing_schema_name <> ‘SYS’
group by l.force_matching_signature
having count(*) > 10)
select v.sql_id,
v.sql_text,
v.parsing_schema_name,
fm.force_matching_signature,
fm.ranking,
fm.counts
from force_mathces fm, v$sql v
where fm.max_sql_child = (v.sql_id || v.child_number)
and fm.ranking <= 50
order by fm.ranking;

SELECT *
FROM (SELECT a.PARSING_SCHEMA_NAME,
substr(sql_text, 1, 60),
count(1) counts,
dense_rank() over(order by count(*) desc) ranking
FROM vsql a where a.PARSING_SCHEMA_NAME <> 'SYS' GROUP BY a.PARSING_SCHEMA_NAME, substr(sql_text, 1, 60) HAVING count(1) > 10) where ranking <= 50; 测试SQL如下所示： declare v_ename scott.emp.ename%type; v_sal scott.emp.sal%type; v_sql clob; begin dbms_output.put_line('*********使用字面量************'); for vrt_emp in (select * from scott.emp) loop v_sql := 'select e.ename,e.sal from scott.emp e where e.empno =' || vrt_emp.empno; execute immediate v_sql into v_ename, v_sql; dbms_output.put_line(v_ename || ':' || v_sql); end loop; dbms_output.put_line(''); dbms_output.put_line('*********使用绑定变量************'); for vrt_emp in (select * from scott.emp) loop v_sql := 'select e.ename,e.sal from scott.emp e where e.empno =:empno'; execute immediate v_sql into v_ename, v_sql using vrt_emp.empno; dbms_output.put_line(v_ename || ':' || v_sql); end loop; end; select v.sql_text, v.sql_id, v.force_matching_signature from vsql v
where v.sql_text like
‘select e.ename,e.sal from scott.emp e where e.empno%’;
Oracle在解析SQL语句的时候，如果在共享池中发现匹配的SQL语句，就可以避免掉解析的大部分开销。在共享池中找到匹配的SQL语句所对应的解析被称为软解析(soft parse)。如果没有找到匹配的SQL语句，则必须进行硬解析（hard parse）。

硬解析不仅耗费CPU时间，在有大量会话想要同时缓存SQL语句到共享池时还会造成争用。通过使用绑定变量，可以最小化解析的代价。

1.CURSOR_SHARING参数
该参数转换SQL语句中的字面值到绑定变量。转换值提高了游标共享，且可能会影响SQL语句的执行计划。优化器是基于绑定变量的存在生成执行计划，而不是实际字面量值。
CURSOR_SHARING决定什么类型的SQL语句可以共享相同的游标。CURSOR_SHARING参数有三个值：
FORCE：只要有可能，字面量就会被替换为绑定变量。
SIMILAR：只有当替换不会影响到执行计划时，才会将字面量替换为绑定变量
EXACT：这是默认值。不将字面量替换为绑定变量。
注意：不推荐修改CURSOR_SHARING参数的默认值。如果实在无法修改现有应用的代码，可以通过设置CURSOR_SHARING参数来指示Oracle透明地将字面量替换为绑定变量。
2.识别没有使用绑定变量的SQL语句
利用v$sql视图的FORCE_MATCHING_SIGNATURE字段，可以识别可能从绑定变量或CURSOR_SHARING获益的SQL语句。如果SQL已使用绑定变量或者CURSOR_SHARING,则FORCE_MATCHING_SIGNATURE在对其进行标识时将给出同样的签名。换句话说，如果两个SQL语句除了字面量的值之外都是相同的，它们将拥有相同的FORCE_MATCHING_SIGNATURE,这意味着如果为他们提供了绑定变量或者CURSOR_SHARING,它们就成了完全相同的语句。
使用FORCE_MATCHING_SIGNATURE识别没有使用绑定变来的SQL语句。

with force_mathces as
(select l.force_matching_signature mathces,
max(l.sql_id || l.child_number) max_sql_child,
dense_rank() over(order by count(*) desc) ranking
from v $sql l where l.force_matching_signature <> 0 and l.parsing_schema_name <> 'SYS' group by l.force_matching_signature having count(*) > 10) select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.ranking from force_mathces fm, v$ sql v
where fm.max_sql_child = (v.sql_id || v.child_number)
and fm.ranking <= 5
order by fm.ranking;

3.通过执行动态SQL语句获取绑定变量的好处
通过执行动态SQL语句，比较字面量和绑定参数对SQL解析的影响。
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_sql clob;
begin

dbms_output.put_line(‘使用字面量***’);
for vrt_emp in (select * from emp) loop
v_sql := ‘select e.ename,e.sal from emp e where e.empno =’ ||
vrt_emp.empno;
execute immediate v_sql
into v_ename, v_sql;
dbms_output.put_line(v_ename || ‘:’ || v_sql);
end loop;

dbms_output.put_line(’’);
dbms_output.put_line(‘使用绑定变量***’);
for vrt_emp in (select * from emp) loop
v_sql := ‘select e.ename,e.sal from emp e where e.empno =:empno’;
execute immediate v_sql
into v_ename, v_sql
using vrt_emp.empno;
dbms_output.put_line(v_ename || ‘:’ || v_sql);
end loop;

end;
查询vsql视图，比较执行结果： SQL> select v.sql_text, v.sql_id, v.force_matching_signature 2 from vsql v
3 where v.sql_text like ‘select e.ename,e.sal from emp e where e.empno %’;

SQL_TEXT SQL_ID FORCE_MATCHING_SIGNATURE

select e.ename,e.sal from emp e where e.empno =7782 766syjydcn5fh 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7499 6ymy4hcb386vt 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7934 3t96y707p8by7 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7902 f9pyzxf7tnuzw 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7654 fvk1fzmrvjc4j 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7788 gsmatg9f4jd2z 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7566 4q9pzzpvvdpuu 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7369 3xhqmvm5vdqy0 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7698 bjjjw0gzaprzv 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7876 8nd8v8mrzxw4w 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7521 5tnyy066zfk1b 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7844 4kd7jb013g2zz 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =7900 grx9sh4fwrcwx 1.27397653964533E19
select e.ename,e.sal from emp e where e.empno =:empno 20wmyr4cvrr6k 3.49355109645567E18
select e.ename,e.sal from emp e where e.empno =7839 6u2ajyu05gw9s 1.27397653964533E19

在v$sql视图中，发现使用字面量的SQL语句有14条，而使用绑定变量的SQL语句只有一条。其中使用字面量的SQL语句除以了字面量值不同之外，其他部分都是相同。而FORCE_MATCHING_SIGNATURE的值是在假设该SQL语句使用绑定变量或者CURSOR_SHARING得到的，因此通过FORCE_MATCHING_SIGNATURE字段识别没有绑定变量的SQL语句。

从10G开始可以通过如下方式查找未使用绑定变量的语句
select FORCE_MATCHING_SIGNATURE, count(1)
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2;

10G以上通过如下过程可以查找对未使用绑定变量的语句
create table shsnc.long_sql（sql_text clob, FORCE_MATCHING_SIGNATURE number,count number)
create or replace procedure query_sql is
cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count
from v $sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 100 order by 2; v_fms number; v_sql01 varchar2(3999); v_sql02 varchar2(3999); begin for i in fms loop v_sql01:='insert into shsnc.long_sql(FORCE_MATCHING_SIGNATURE,sql_text) select FORCE_MATCHING_SIGNATURE,sql_fulltext from (select FORCE_MATCHING_SIGNATURE,sql_fulltext from v$ sql where FORCE_MATCHING_SIGNATURE=’||i.fms||’ and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum<2’;
v_sql02:=‘update shsnc.long_sql set count=’||i.count ||’ where FORCE_MATCHING_SIGNATURE=’||i.fms;
execute immediate v_sql01;
commit;
execute immediate v_sql02;
commit;
end loop;
end;
/

10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象，我们来具体看一下:

SQL> create table YOUYUS (t1 int);
Table created.

SQL> alter system flush shared_pool;
System altered.

SQL>select /test_matching_a/ * from YOUYUS where t1=1;
no rows selected

SQL>select /test_matching_a/ * from YOUYUS where t1=2;
no rows selected

SQL>select /test_matching_a/ * from YOUYUS where t1=3;
no rows selected

SQL> col sql_text format a55;
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like ‘%test_matching_a%’
4 and sql_text not like ‘%like%’;

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

select /test_matching_a/ * from YOUYUS where t1=2 4.59124694481197E18 1.00267830752731E19
select /test_matching_a/ * from YOUYUS where t1=3 4.59124694481197E18 1.61270448861426E19
select /test_matching_a/ * from YOUYUS where t1=1 4.59124694481197E18 1.36782048270058E18

/*以上将变量硬编码至SQL中的游标，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同说明在游标共享FORCE模式下，这些游标满足CURSOR SHARING的条件 */

SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=FORCE;
Session altered.

SQL>select /test_matching_a/ * from YOUYUS where t1=1;
no rows selected

SQL>select /test_matching_a/ * from YOUYUS where t1=2;
no rows selected

SQL>select /test_matching_a/ * from YOUYUS where t1=3;
no rows selected

SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like ‘%test_matching_a%’
4 and sql_text not like ‘%like%’;

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

select /test_matching_a/ * from YOUYUS where t1=:“SYS_B_0” 4.59124694481197E18 4.59124694481197E18

/FORCE模式下将SQL文本中的变量值转换成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了/

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL
2010/08/19 BY MACLEAN LIU 9条评论
做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。
看到下列SQL你必定觉得眼熟:
SELECT substr(sql_text, 1, 80), count(1)
FROM vsql GROUP BY substr(sql_text, 1, 80) HAVING count(1) > 10 ORDER BY 2 是的，以上这段抓取literal sql的脚本大约从8i时代就开始流行了，在那时它很popular也很休闲，使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是，它彻底过时了，落伍了，已经不是fashion master了。 10g以后vSQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象，我们来具体看一下:
SQL> create table YOUYUS (t1 int);
Table created.

SQL> alter system flush shared_pool;
System altered.

SQL>select /test_matching_a/ * from YOUYUS where t1=1;
no rows selected

SQL>select /test_matching_a/ * from YOUYUS where t1=2;
no rows selected

SQL>select /test_matching_a/ * from YOUYUS where t1=3;
no rows selected

SQL> col sql_text format a55;
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like ‘%test_matching_a%’
4 and sql_text not like ‘%like%’;

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=FORCE;
Session altered.

SQL>select /test_matching_a/ * from YOUYUS where t1=1;
no rows selected
SQL>select /test_matching_a/ * from YOUYUS where t1=2;
no rows selected
SQL>select /test_matching_a/ * from YOUYUS where t1=3;
no rows selected
SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like ‘%test_matching_a%’
4 and sql_text not like ‘%like%’;
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

select /test_matching_a/ * from YOUYUS where t1=:“SYS_B_0” 4.59124694481197E18 4.59124694481197E18

/FORCE模式下将SQL文本中的变量值转换成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了/
以上演示说明了FORCE_MATCHING_SIGNATURE列可以帮助我们找出那些潜在可以共享的游标(也包括了因非绑定问题造成的游标无法共享)，现在我们利用它来完善捕获非绑定变量SQL的脚本：
SQL> alter system flush shared_pool;
System altered.
SQL> select /test_matching_b/ * from YOUYUS where t1=1;
no rows selected

SQL> select /test_matching_b/ * from YOUYUS where t1=‘1’; //我有引号，我与众不同！
no rows selected
SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like ‘%test_matching_b%’
4 and sql_text not like ‘%like%’;
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

select /test_matching_b/ * from YOUYUS where t1=‘1’ 1.43666633406896E19 1.83327833675856E19
select /test_matching_b/ * from YOUYUS where t1=1 1.43666633406896E19 8.05526057286178E18

/多余的引号也会导致游标无法共享，此时的FORCE_MATCHING_SIGNATURE 也会是一致的/
select FORCE_MATCHING_SIGNATURE, count(1)
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2;
Enter value for a: 10
old 6: having count(1) > &a
new 6: having count(1) > 10
FORCE_MATCHING_SIGNATURE COUNT(1)

 8.81463386552502E18         12

So We find it!
在这里再推荐一种来自MOS，find Literal SQL的方法:
How to Find Literal SQL in Shared Pool
Applies to:
PL/SQL – Version: 8.1.7 to 10.2
Information in this document applies to any platform.
Goal
There is no direct way to query the dictionary for literal SQL only.
However the following example will try to exclude all SQL statements in the
shared pool that do use bind variables.
There still might be situations, with statements using subqueries, where the
example still will show SQL statements using bind variables.

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

如何查询数据库中未使用绑定变量的SQL语句？

SQL> select sql_text from v$sql where force_matching_signature=11420119939742656807 and rownum<10; SQL_TEXT

评论

SQL> select sql_text from v$sql where force_matching_signature=11420119939742656807 and rownum<10;
SQL_TEXT