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

DB宝 2020-05-21

1027

♣题目部分在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语句来查询：
 1with force_mathces as
 2 (select l.force_matching_signature,
 3         max(l.sql_id || l.child_number) max_sql_child,
 4         dense_rank() over(order by count(*) desc) ranking,
 5         count(*) counts
 6    from v$sql l
 7   where l.force_matching_signature <> 0
 8  and l.parsing_schema_name <> 'SYS'
 9   group by l.force_matching_signature
10  having count(*) > 10)
11select v.sql_id,
12       v.sql_text,
13       v.parsing_schema_name,
14       fm.force_matching_signature,
15       fm.ranking,
16       fm.counts
17  from force_mathces fm, v$sql v
18 where fm.max_sql_child = (v.sql_id || v.child_number)
19   and fm.ranking <= 50
20 order by fm.ranking;
21
22
23SELECT *
24  FROM (SELECT a.PARSING_SCHEMA_NAME,
25               substr(sql_text, 1, 60),
26               count(1) counts,
27               dense_rank() over(order by count(*) desc) ranking
28          FROM v$sql a
29         where a.PARSING_SCHEMA_NAME <> 'SYS'
30         GROUP BY a.PARSING_SCHEMA_NAME, substr(sql_text, 1, 60)
31        HAVING count(1) > 10)
32 where ranking <= 50;

测试SQL如下所示：
 1declare
 2  v_ename scott.emp.ename%type;
 3  v_sal   scott.emp.sal%type;
 4  v_sql   clob;
 5begin
 6
 7  dbms_output.put_line('*********使用字面量************');
 8  for vrt_emp in (select * from scott.emp) loop
 9    v_sql := 'select e.ename,e.sal from scott.emp e where e.empno =' ||
10             vrt_emp.empno;
11    execute immediate v_sql
12      into v_ename, v_sql;
13    dbms_output.put_line(v_ename || ':' || v_sql);
14  end loop;
15
16  dbms_output.put_line('');
17  dbms_output.put_line('*********使用绑定变量************');
18  for vrt_emp in (select * from scott.emp) loop
19    v_sql := 'select e.ename,e.sal from scott.emp e where e.empno =:empno';
20    execute immediate v_sql
21      into v_ename, v_sql
22      using vrt_emp.empno;
23    dbms_output.put_line(v_ename || ':' || v_sql);
24  end loop;
25
26end;
27
28select v.sql_text, v.sql_id, v.force_matching_signature
29  from v$sql v
30 where v.sql_text like
31       '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？


本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗