ORACLE 查询共享池中SQL运行情况 (是否绑定变量)

一、执行多次查询：
SQL> begin
for i in 1..100 loop
execute immediate 'select * from emp where empno=' || i;
end loop;
end;
/

二、创建测试表
SQL> create table test as select sql_text from v$sqlarea;
 
Table created
三、添加字段
SQL> alter table test add constraintname varchar2(1000);
 
Table altered
四、创建存过
SQL> create or replace function remove_constants(p_query in varchar2) return varchar2
  2  as
  3   l_query long;
  4   l_char varchar2(1);
  5   l_in_quotes boolean default false;
  6  begin
  7   for i in 1..length(p_query) loop
  8   l_char := substr(p_query, i, 1);
  9   if (l_char = '''' and l_in_quotes) then
 10   l_in_quotes := false;
 11   elsif(l_char = '''' and not l_in_quotes) then
 12   l_in_quotes := true;
 13   l_query := l_query || '''#';
 14  
 15   end if;
 16   if(not l_in_quotes) then
 17   l_query := l_query || l_char;
 18   end if;
 19   end loop;
 20   l_query := translate(l_query, '0123456789','@@@@@@@@@@');
 21   for i in 0..8 loop
 22   l_query := replace(l_query, lpad('@',10-i,'@'), '@');
 23   l_query := replace(l_query, lpad(' ',10-i,' '), ' ');
 24   end loop;
 25   return upper(l_query);
 26  end;
 27  /
 
Function created

 
PL/SQL procedure successfully completed


五、更新数据
SQL> update test set constraintname = remove_constants(sql_text);
 
1263 rows updated
SQL> commit;
 
Commit complete

六查询、
SQL> select constraintname ,count(*) from test group by constraintname having count(*) > 50 order by 2;
 
CONSTRAINTNAME                                                                     COUNT(*)
-------------------------------------------------------------------------------- ----------
SELECT * FROM EMP WHERE EMPNO=@                                                         100

谓词条件被@代替，通过此函数，可以较为容易的找到哪些SQL没有绑定变量。