Re: dbms_shared_pool.keep

From: Matthias Hoys <>
Date: Sat, 16 May 2009 15:43:45 +0200
Message-ID: <4a0ec310$0$2854$>

<> wrote in message On May 15, 2:17 pm, Mladen Gogala <> wrote:
> Today, I noticed a performance problem on one of my databases. Statspack
> was showing quite a few of library cache waits. Most of the waiting
> sessions were trying to execute the same procedure and the problem was
> alleviated to significant degree by pinning the the package that contains
> the offending procedure, as well as all of the other PL/SQL objects
> belonging to FLOWS_03000.
> Trouble is, I distinctly remember rading an article claiming that using
> DBMS_SHARED_POOL.KEEP is not good, that it is, in fact, obsolete. I can't
> locate the article so I will ask anyone who thinks that I shouldn't have
> done what I did, to explain me why? In other words, am I missing something
> or not? I have sufficient free memory, no danger of 4031 in the near
> future.
> --

>>You can't believe everything you read ... especially if you cannot
>>find it any more eh?

>>What's wrong exactly with pinning important packages if you have
>>enough memory?

Nothing, but maybe the caching mechanism of 10g/11g evolved to that extent that it's "intelligent" enough to understand that frequently executed packages should be kept in the shared pool instead of flushing them. In my opinion, if you really need to manually pin frequently-used packages because they are constantly flushed, there's something wrong with your configuration: or the shared pool is simply too small, or too much unique sql code is flooding the shared pool (one reason could be because no bind variables where used).

Matthias Received on Sat May 16 2009 - 08:43:45 CDT

Original text of this message