一个查找未使用绑定变量SQL的脚本(tom)

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 || '''#';
        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(), );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

 

The output of that last query will show you statements that are identical in the shared
pool after all numbers and character string constants have been removed.  These
statements -- and more importantly their counts -- are the potential bottlenecks.  In
addition to causing the contention, they will be HUGE cpu consumers. 

If you discover your applications do not use bind variables -- you must have this
corrected.  You'll never have a good hit ratio if everyone submits "unique" sql.  Your
shared pool will never be used right and you'll be using excessive CPU (90% of the time
it takes to process "insert into t values ( 1 )" is parsing.  If you use "insert into t
values ( :x )", and bind the value of 1 -- then the next person that runs that insert
will benefit from your work and run that much faster.