Proracle’s Weblog

October 31, 2007

Library cache latch contention & bind variables

Filed under: useful — proracle @ 9:35 am

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
l_query long;
l_char  varchar2(1);
l_in_quotes boolean default FALSE;
for i in 1 .. length( p_query )
l_char := substr(p_query,i,1);
if ( l_char = ”” and l_in_quotes )
l_in_quotes := FALSE;
elsif ( l_char = ”” and NOT l_in_quotes )
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);
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 ( 😡 )”, and bind the value of 1 — then the next person that runs that insert
will benefit from your work and run that much faster.

Next Page »

Blog at