Re: Pinning Objects
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 25 Jul 2009 07:49:48 -0700 (PDT)
Message-ID: <d3334bc4-2dd9-42bb-9414-149d104f382b_at_c14g2000yqm.googlegroups.com>
On Jul 20, 2:08 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 20, 9:57 am, The Magnet <a..._at_unsu.com> wrote:
>
> > Hi,
>
> > We want to pin a couple of tables, hoping to increase some
> > performance. From the docs, a pinned table means less reads from
> > disk, etc.
>
> > If the table gets updated or inserted into, is there any advantage to
> > this? If yes, how does the committed transactions get saved?
>
> If you just let Oracle do its thing, blocks that get used a lot will
> stay in memory, since Oracle uses an LRU algorithm. Also, small
> tables use a different algorithm for full scanning ("The definition of
> a small table is the maximum of 2% of the buffer cache and 20,
> whichever is bigger.") I used to have noticeable results on certain
> objects with a recycle pool, but nowadays don't seem to need to
> bother. Be careful about catching obsessive tuning disorder. Take
> any rule of thumb that uses percentages with a very large dose of
> salt. Remember that most performance problems come from the app
> code. "Hoping" is not a particularly good tuning methodology. You
> want to use a methodology that tells you how to find what is wrong and
> where to put your effort.
>
> See metalink Note: 135223.1 and note the auto-tuning part. And keep a
> lot of salt handy.
>
> See commit transactions in the concepts manual for the basic idea on
> how that works.
>
> Do you have an actual problem to solve? You need to state it
> exactly. http://dbaoracle.net/readme-cdos.htm
>
> jg
> --
> _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/jul/20/1m20judges23536-fe...
Date: Sat, 25 Jul 2009 07:49:48 -0700 (PDT)
Message-ID: <d3334bc4-2dd9-42bb-9414-149d104f382b_at_c14g2000yqm.googlegroups.com>
On Jul 20, 2:08 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 20, 9:57 am, The Magnet <a..._at_unsu.com> wrote:
>
> > Hi,
>
> > We want to pin a couple of tables, hoping to increase some
> > performance. From the docs, a pinned table means less reads from
> > disk, etc.
>
> > If the table gets updated or inserted into, is there any advantage to
> > this? If yes, how does the committed transactions get saved?
>
> If you just let Oracle do its thing, blocks that get used a lot will
> stay in memory, since Oracle uses an LRU algorithm. Also, small
> tables use a different algorithm for full scanning ("The definition of
> a small table is the maximum of 2% of the buffer cache and 20,
> whichever is bigger.") I used to have noticeable results on certain
> objects with a recycle pool, but nowadays don't seem to need to
> bother. Be careful about catching obsessive tuning disorder. Take
> any rule of thumb that uses percentages with a very large dose of
> salt. Remember that most performance problems come from the app
> code. "Hoping" is not a particularly good tuning methodology. You
> want to use a methodology that tells you how to find what is wrong and
> where to put your effort.
>
> See metalink Note: 135223.1 and note the auto-tuning part. And keep a
> lot of salt handy.
>
> See commit transactions in the concepts manual for the basic idea on
> how that works.
>
> Do you have an actual problem to solve? You need to state it
> exactly. http://dbaoracle.net/readme-cdos.htm
>
> jg
> --
> _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/jul/20/1m20judges23536-fe...
I agree with Joel. Most DBA's will be better off just letting Oracle manage the single buffer pool.
If you really thing this table should be cached and Oracle needs help to make that decision then the table should be fairly small. You can then consider using a keep buffer pool. As far as I know the cache hint still works though Oracle does not use a true LRU list any more. The documentation has not been changed to reflect the fact Oracle uses a touch count mechanism to manage aging out of the buffers. You can see the buffer block touches via v$bh (x$bh). This can be useful for finding "hot" blocks.
HTH -- Mark D Powell -- Received on Sat Jul 25 2009 - 09:49:48 CDT