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

[TOC]

​ 背景：

​ 我们会经常在一些客户的AWR报告中，能够看到如下类似信息，SQL的前缀部分都一样，执行时间也基本一致，详细查看具体的SQL_TEXT后，我们可以看到仅仅是where条件中的变量值不一样，其他都一样，没有采用绑定变量的方式，而是一个个常量。

对于这种在AWR报告中，能够体现出来的，我们比较容易发现，可及时反馈给开发人员处理。但是在一些环境中，系统中其实存在较多的未使用绑定变量的SQL，但是在AWR报告TOP SQL部分，并没有体现出来，这个时候，就需要我们去数据库中手动捞取。

当Oracle中存在大量的类似sql，基本结构一样，仅where条件的取值不一样时，我们应该采用绑定变量的方法，来减少sql的硬解析，能够提高数据库性能，避免出现shared pool相关的等待事件，同时也能节约CPU资源。

下面我们通过案例，如果查询数据库中未使用绑定变量的SQL？

1. 构造环境

-- 创建测试表，并插入数据
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(*)
----------
      1000

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以后v$SQL动态性能视图增加了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 v$sql 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. 思考绑定变量性能

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执行时间来看，使用绑定变量的方式也比未使用绑定变量的方式快很多。