Re: dbms_shared_pool.keep

"Matthias Hoys" <anti_at_spam.com> wrote in message news:4a0ec310$0$2854$ba620e4c_at_news.skynet.be...
>
> <johnbhurley_at_sbcglobal.net> wrote in message
> news:44b68d3e-ceaa-4f0d-a23e-193d1584934b_at_s20g2000vbp.googlegroups.com...
> On May 15, 2:17 pm, Mladen Gogala <mla..._at_bogus.email.com> 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.
>>
>> --http://mgogala.freehostia.com
>
>>>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
>
>
>
>

I think the aim of the keep procedure was to protect objects that were used fairly frequently, but not frequently enough to stay near the top of the LRU.

Combine this with the fact that the object could be larger and the shared pool could get hugely fragmented and you might find that when it needed to be reloaded you would (a) get a massive shared pool flush and possibly (b) fail to reload it (ORA-04031) because there wasn't a large enough piece of contiguous memory.

If anyone has said you no longer need to KEEP objects then it's possible that they are thinking of the way in which 10g has made has re-engineered things so that objects are generally made of many smaller pieces, and introduced a change to memory allocation strategies in the shared pool that help to reduce fragmentation - thus reducing significantly the risk of the flush and failure to reload.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html