ITPub博客

首页 > 应用开发 > IT综合 > 绑定变量

绑定变量

原创 IT综合 作者:chance2000 时间:2006-03-26 00:08:45 0 删除 编辑
绑定变量[@more@]

刷新共享池
alter system flush shared_pool;
使用常量编码
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for 'select object_name from all_objects where object_id='||i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');
end;
/

使用绑定变量
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for 'select object_name from all_objects where object_id=:x' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');
end;
/

关于如何查找没有使用绑定变量的sql语句?
-----------------------------------------------
create or replace function remove_constants(p_query in varchar2) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default false;
i integer;
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;
l_query := translate(l_query,'0123456789','@@@@@@@@@@');
for i in 0..8 loop
l_query := replace(l_query,lpad(
- i,'@'),'@');
l_query := replace(l_query,lpad('',10 - i,''),'');
end loop;
end loop;
return upper(l_query);
end remove_constants;
/

create global temporary table sql_area_tmp
on commit preserve rows as select sql_text,sql_text sql_text_wo_constants
from v$sqlarea where 1 = 0;

insert into sql_area_tmp(sql_text) select sql_text from v$sqlarea;

update sql_area_tmp set sql_text_wo_constants = remove_constants(sql_text);

--查找糟糕的sql语句
select sql_text_wo_constants,count(*) from sql_area_tmp
group by sql_text_wo_constants having count(*) > 10 group by 2;

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

请登录后发表评论 登录
全部评论
  • 博文量
    78
  • 访问量
    837065