Oracle中如何查找未使用绑定变量的SQL语句？

2022-11-22 20:33:26

利用V$SQL 视图的 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 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.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 v$sql 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 v$sql 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;

查询v$sql视图，比较执行结果：

SQL> select v.sql_text, v.sql_id, v.force_matching_signature

2 from v$sql 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 v$sql
GROUP BY substr(sql_text, 1, 80)
HAVING count(1) > 10
ORDER BY 2
是的，以上这段抓取literal sql的脚本大约从8i时代就开始流行了，在那时它很popular也很休闲，使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是，它彻底过时了，落伍了，已经不是fashion master了。
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的脚本：
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.

码农公寓

How to Find Literal SQL in Shared Pool

Applies to:

Goal

相关文章