【DB笔试面试806】在Oracle中，如何查找未使用绑定变量的SQL语句？

AiDBA宝典

发布于 2020-05-25 15:42:44

6.2K0

发布于 2020-05-25 15:42:44

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在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%';

& 说明：

有关查找未使用绑定变量的SQL的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2657578/

有关绑定变量的更多内容请参考：

⊙ 【DB笔试面试587】在Oracle中，常规游标共享和自适应游标共享的联系和区别有哪些？⊙ 【DB笔试面试586】在Oracle中，什么是自适应游标共享（4）？⊙ 【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？⊙ 【DB笔试面试586】在Oracle中，什么是自适应游标共享（2）？⊙ 【DB笔试面试586】在Oracle中，什么是自适应游标共享（1）？⊙ 【DB笔试面试585】在Oracle中，什么是常规游标共享？⊙ 【DB笔试面试584】在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？⊙ 【DB笔试面试583】在Oracle中，什么是绑定变量分级？⊙ 【DB笔试面试582】在Oracle中，什么是绑定变量窥探（下）？⊙ 【DB笔试面试582】在Oracle中，什么是绑定变量窥探（上）？⊙ 【DB笔试面试581】在Oracle中，绑定变量是什么？绑定变量有什么优缺点？⊙ 【DB笔试面试580】在Oracle中，什么是High Version Count？