ITPub博客

首页 > 数据库 > Oracle > Oracle中如何查找未使用绑定变量的SQL语句?

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

原创 Oracle 作者:lhrbest 时间:2019-09-19 15:41:55 0 删除 编辑

Oracle中如何查找未使用绑定变量的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 语句。

可以使用如下的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.





About Me

........................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号( xiaomaimiaolhr)上有同步更新

● 本文itpub地址: http://blog.itpub.net/26736162

● 本文博客园地址: http://www.cnblogs.com/lhrbest

● 本文CSDN地址: https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号: 230161599 、618766405

● 微 信群:可加我微 信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友 646634621 ,注明添加缘由

● 于 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成

● 最新修改时间:2019-09-01 06:00 ~ 2019-09-31 24:00

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

........................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用网络班http://blog.itpub.net/26736162/viewspace-2148098/

小麦苗腾讯课堂主页https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号( xiaomaimiaolhr)及QQ群(DBA宝典)、添加小麦苗微 信, 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2657578/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1352
  • 访问量
    8139514