ITPub博客

首页 > 数据库 > 数据库开发技术 > How to find sql with need bind(from asktom)

How to find sql with need bind(from asktom)

原创 数据库开发技术 作者:jametong 时间:2005-03-28 13:43:16 0 删除 编辑
Lbrary cache latch contention is typically caused by NOT using bind variables. 
It is due to excessive parsing of statements.

One way to see if this might be the case in your situation is to run a script
like:


create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

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;
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 || '

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

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280588