Library cache


Most of the tuning manuals refer to library cache contention and give plenty of handy SQL's on how to detect it. For example

select namespace,gethitratio 
from v$librarycache; 

select name,gets,misses,sleeps 
from v$latch where name like 'library%'; 

select name,pid 
from v$latch a , 
     v$latchholder b 
where a.addr=b.laddr 
and name = 'library cache%' ; 
and the typical response is "Reduce parsing, increase the shared pool".

Easily said but not so easily done, especially for pre-packaged applications. (A lot of ODBC developers come up with the "brainwave" of passing literal SQL strings due to the slowness of ODBC binding without really knowing the parsing impact that results).

Whilst not a perfect cure, reducing fragmentation in the shared pool can assist, so at regular "quiet" intervals, issue a

alter system flush shared_pool;