Re: What are the implications of a Library Cache Hit Ratio low ?

"F.Marchioni" <fmarchioniNIENTESPAM_at_libero.it> wrote in message news:<bfj1b.256717$Ny5.7878682_at_twister2.libero.it>...
> Hi all,
> I'd like to know what is the impact on my queries when I have
> a low Library Cache Hit Ratio? maybe all the query that reference
> objects in the dictionary will slow down ?
> Thanks a lot
> Francesco

F, the library cache hit ratio in isolation by itself means nothing. You should consider it along with other ratios and measures of shared pool activity such as the cursor cache hit ratio, the dictionary cache hit ratio, the loads value in v$db_object_cache, etc.... If the ratios are all poor then this might mean that your shared pool is too small, but it is probably an indication that your application uses a lot of non-sharable SQL.

Look at v$sql for nearly identical statements that differ perhaps only in the value of a constant. Change these statements to use a bind variable instead of a constant.

Look for what appears to be identical SQL that is not being shared. If you check the applications that call the code you may find that they have defined different lengths for the bind variables. This causes Oracle to not be able to share the SQL so instead it create child cursors. (See metalink)

HTH -- Mark D Powell -- Received on Fri Aug 22 2003 - 08:32:44 CDT