今天同事收到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
