使用remove_constants工具查看Oracle是否使用绑定变量

这个也是Tom大神写的工具。
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580

下面模拟没有绑定变量的环境,然后使用该工具查看
创建实验帐号
conn / as sysdba
create user edmond identified by edmond;
grant connect,resource,dba to edmond;

create table t(x int);
insert into t select rownum from dual connect by level<=10000;

create or replace procedure p1
as
    l_cnt number;
begin
    for i in 1 .. 10000
    loop
        execute immediate 'select count(*) from t where x=' || i into l_cnt;
    end loop;
end;
/

在一个终端执行p1过程,开启另一个终端执行如下脚本:

1. create table t1 as select sql_text from v$sqlarea;
   
2. 
   
3. alter table t1 add sql_text_wo_constants varchar2(1000);
   
4. 
   
5. create or replace function
   
6. remove_constants( p_query in varchar2 ) return varchar2
   
7. as
   
8.     l_query long;
   
9.     l_char varchar2(1);
   
10.     l_in_quotes boolean default FALSE;
    
11. begin
    
12.     for i in 1 .. length( p_query )
    
13.     loop
    
14.         l_char := substr(p_query,i,1);
    
15.         if ( l_char = '''' and l_in_quotes )
    
16.         then
    
17.             l_in_quotes := FALSE;
    
18.         elsif ( l_char = '''' and NOT l_in_quotes )
    
19.         then
    
20.             l_in_quotes := TRUE;
    
21.             l_query := l_query || '''#';
    
22.         end if;
    
23.         if ( NOT l_in_quotes ) then
    
24.             l_query := l_query || l_char;
    
25.         end if;
    
26.     end loop;
    
27.     l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    
28.     for i in 0 .. 8 loop
    
29.         l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
    
30.         l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    
31.     end loop;
    
32.     return upper(l_query);
    
33. end;
    
34. /
    
35. update t1 set sql_text_wo_constants = remove_constants(sql_text);
    

set linesize 200;
col sql_text_wo_constants format a100;

1. select sql_text_wo_constants, count(*)
   
2.   from t1
   
3.  group by sql_text_wo_constants
   
4. having count(*) > 100
   
5.  order by 2
   
6. /

可以看到结果如下,已经找到了没有绑定变量的语句。

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

转载于:http://blog.itpub.net/29254281/viewspace-1273980/