解决library cache latch的简单方法

来自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('@',10-i,'@'), '@' );
        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. 

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

转载于:http://blog.itpub.net/20542911/viewspace-621953/