Re: Table cache info please.

There's been lots of bad information circulating about the three buffer pools.

One of the Tips and Techniques books stated that those objects pulled into keep stayed there for the life of the instance, those pulled into recycle had no LRU, were overwritten as soon as unpinned, default followed the same LRU as always.

I recently returned from the Oracle Internals seminars and made it a point to ask our instructor what was the truth. I was told each buffer pool had its own LRU, with the same behavior as we're used to expecting in a single buffer pool.

• Original Message ----- From: Adams, Matthew (GEA, 088130) <MATT.ADAMS_at_APPL.GE.COM> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, August 10, 2000 8:56 AM Subject: RE: Table cache info please.

> I was told that the algorithms were different for
> each buffer pool. Oracle probably lied about that one.
>
> In addition, the keep buffer pool
> should be sized large enough to hold all objects
> (tables and indexes) designated as KEEP.
>
> > -----Original Message-----
> > From: Tom Pall [mailto:tpall_at_cdproc.com]
> > Sent: Wednesday, August 09, 2000 8:04 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Table cache info please.
> >
> >
> > Buffer_pool keep states that when the table/index/partition
> > is read into buffer cache,
> > place it into the buffer pool named keep. Keep, recycle,
> > default are merely convenient names.
> > They each follow the LRU age out algorithm. In each of the
> > buffer pools, if the objects are small
> > enough and cache big enough, the objects won't be aged out.
> >
> >
> > ----- Original Message -----
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, August 09, 2000 11:06 AM
> >
> >
> > > alter table XX cache does not cause that
> > > table to be cached in memory. It means that
> > > IF a full table scan is done on it, add the
> > > blocks read to the MRU end of the buffer cache chain
> > > rather than the LRU end of the buffer cache chain.
> > > (which is the default functionality).
> > > The blocks will be aged out of the buffer cache through
> > > the normal aging mechanism.
> > >
> > > If you want to hold all of a table in memory, try
> > > using the BUFFER_POOL KEEP option.
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: Ron Rogers [mailto:RROGERS_at_galottery.org]
> > > > Sent: Wednesday, August 09, 2000 9:28 AM
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: Table cache info please.
> > > >
> > > >
> > > > Good morning team,
> > > > I am thinking about caching a table that is used quite
> > > > frequently by our applications and have a question that is
> > > > puzzeling me.
> > > > The table is truncated daily and reloaded with fresh
> > > > information for the applications to use.
> > > > Question: If I alter table xx cache the table, what effect
> > > > does the truncate and reload have on the table cached in
> > > > memory? Is it automatically recached after the truncate and
> > > > reload or is the cached table in memory not truncated and
> > > > reloaded until the first block is read and the difference in
> > > > tables is detected?
> > > > Thanks,
> > > > Ron ROgers
> > > > DBA OCP
> > > > Atl.GA
> > > >
> > > >
> > > > --
> > > > Author: Ron Rogers
> > > > INET: RROGERS_at_galottery.org
> > > >
> > > > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > > > San Diego, California -- Public Internet access /
> > Mailing Lists
> > > >
> > --------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed
> > from). You may
> > > > also send the HELP command for other information (like
> > subscribing).
> > > >
> > > --
> > > Author: Adams, Matthew (GEA, 088130)
> > > INET: MATT.ADAMS_at_APPL.GE.COM
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access /
> > Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> > --
> > Author: Tom Pall
> > INET: tpall_at_cdproc.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Author: Adams, Matthew (GEA, 088130)
> INET: MATT.ADAMS_at_APPL.GE.COM
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Aug 11 2000 - 14:24:22 CDT