采集数据库中未绑定变量的sql

今天同事收到hard parse的报警，在监控系统中看到hard parse的值比平时高出了数十倍，通常情况下是由于应用程序中的sql未绑定变量导致，在江枫的工具中还没有采集未绑定变量的sql，于是在网上看到一个脚本，该脚本将V$sqlarea中的sql通过一个函数对查询条件中的出现的‘’的地方用‘#’代替，出现数字的地方用@代替，比如：

select * from test where name=‘test‘—替换为：select * from test where name=’#’;

select * from test where id=12—-替换为:select * from test where id=@;

该函数为：

create or replace function

remove_constants( p_query in varchar2 ) return varchar2

as

l_query long;

l_char  varchar2(1000);

l_in_quotes boolean default FALSE;

begin

for i in 1 .. length( p_query )

loop

l_char := substr(p_query,i,1);

if ( l_char = ”” and l_in_quotes )

then

l_in_quotes := FALSE;

elsif ( l_char = ”” and NOT l_in_quotes )

then

l_in_quotes := TRUE;

l_query := l_query || ”’#’;—–遇到’‘则替换为#

end if;

if ( NOT l_in_quotes ) then

l_query := l_query || l_char;

end if;

end loop;

l_query := translate( l_query, ‘0123456789’, ‘@@@@@@@@@@’ );–遇到数字则替换为@

for i in 0 .. 8 loop

l_query := replace( l_query, lpad(‘@’,10-i,’@’), ‘@’ );

l_query := replace( l_query, lpad(‘ ‘,10-i,’ ‘), ‘ ‘ );

end loop;

return upper(l_query);

end;

/

然后将统计过程封装一下：

create or replace procedure find_no_bind_sql(limit_rows number,

my_cursor  out sys_refcursor) as

————– limit_rows参数为控制V$sqlarea中出现的sql的次数

v_sqlerrm varchar2(200);

begin

open my_cursor for

select t.sql_text_to_constants, count(*)

from (select sql_text,

remove_constants(sql_text) sql_text_to_constants

from v$sqlarea) t

group by t.sql_text_to_constants

having count(*) > limit_rows

order by 2;

exception

when others then rollback;

v_sqlerrm := substr(sqlerrm, 1, 200);

dbms_output.put_line(v_sqlerrm);

end find_no_bind_sql;

/

调用：

SQL> var a refcursor

SQL> exec find_no_bind_sql(3,:a);

SQL>  print a

SQL_TEXT_TO_CONSTANTS                                COUNT(*)

SELECT * FROM test I WHERE NAME=’#’           6

SELECT * FROM test WHERE ID=@                      7