♣
题目部分
在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语句来查询：
代码语言：javascript复制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如下所示：
代码语言：javascript复制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/
有关绑定变量的更多内容请参考：
代码语言：javascript复制⊙ 【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程序员面试笔试宝典》，作者：小麦苗
