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

采集数据库中未绑定变量的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
Categories: database Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.