Re: dbms_shared_pool.keep

From: <matthias.hoys_at_gmail.com>
Date: Mon, 18 May 2009 01:54:29 -0700 (PDT)
Message-ID: <19c474a8-a708-4364-becb-31eb20382e03_at_r34g2000vba.googlegroups.com>



On May 16, 6:02 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Matthias Hoys" <a..._at_spam.com> wrote in message
>
> news:4a0ec310$0$2854$ba620e4c_at_news.skynet.be...
>
>
>
>
>
> > <johnbhur..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

I noticed that DBMS_SHARED_POOL is not by default installed in 10g - any idea why that is?

Matthias Received on Mon May 18 2009 - 03:54:29 CDT

Original text of this message