RE: Cache a table

Thanks for correction.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Tim Gorman
Sent: Tuesday, October 21, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L

The switch being referred to occurred with 8i, where Oracle went to the "touch-count" algorithm. See "http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=papers_main " for paper #136.

I guess "most frequently used" is a good way to describe it -- nice choice of words!

> Tom,
>
> I think you are correct, if we are talking about Oracle 9,
> where oracle switched from "most recently used" to "most
> frequently used" algorithm.
> But, prior to that, it seems possible to think of
> scenarios, where "cache" would be helpful. May be, that's
> one of the reasons, why oracle changed algorithm.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
>
> -----Original Message-----
> Mercadante, Thomas F
> Sent: Tuesday, October 21, 2003 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
> I always wondered why Oracle thought this was a useful
> table attribute.
> My gut feeling is that it is an extra that does little.
>
> For example, say we want to keep a code table in memory
> because it is constantly being hit for column verifiction.
> By definition, if a table is
> constantly being queried, it's segments will be in memory
> because they never
> age out. That sounds like cacheing to me.
>
> And then I remember a specific piece of Oracle
> documentation saying that,
> even though we may mark a table to be "cached", it *still*
> may be aged out
> if memory is needed for other data blocks.
>
> Like I said, sounds a little like "here you have it, and
> here you don't".
>
> I'm sure that my impression is wrong and someone will
> correct me. But I doubt I will use the "CACHE" option
> anytime soon.
> Tom Mercadante
> Oracle Certified Professional
>
>
> -----Original Message-----
> Sent: Tuesday, October 21, 2003 2:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> My understanding is that the KEEP and RECYCLE Pools are
> just 'names' in the
> sense that they are placeholders for assigning an object
> to the BUFFER_POOL
> { KEEP | RECYCLE | DEFAULT } clause, and that the 'aging'
> algorithms for KEEP and RECYCLE are exactly the same.
> Assigning a specific object to one of
> these named pools segregates objects by
> retention-requirements. Thus, KEEP
> does not imply a different treatment of the Buffers -
> rather it makes sure
> that objects that you would like to 'keep' around are
> specifically directed
> to a common pool and vice versa....
>
> Does anyone have additional information that can verify
> this? I heard this
> from a knowledgeable Oracle instructor in an Oracle Tuning
> training Class.
>
> John Kanagaraj
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Disappointment is inevitable, but Discouragement is
> optional!
> ** The opinions and facts contained in this message are
> entirely mine and do
> not reflect those of my employer or customers **
>
> >-----Original Message-----
> >From: Tim Gorman [mailto:tim_at_sagelogix.com]
> >Sent: Tuesday, October 21, 2003 6:59 AM
> >To: Multiple recipients of list ORACLE-L
> >Subject: Re: Cache a table
> >
> >
> >Good points, Arup.
> >
> >Actually, I would argue that there is better reason to
> >consider using the
> >RECYCLE pool than to consider how to "cache" tables or
> use the >KEEP pool.
> >The advantage of effective use of the RECYCLE pool is
> better >behavior in the
> >rest of the Buffer Cache...
> >
> >When you think of it, the default DEFAULT buffer pool and
> the >KEEP pool have
> >essentially the same purpose: long-term caching of
> blocks. >What keeps them
> >from accomplishing that mission but objects whose blocks
> waste >space and
> >energy cycling into and out from the Buffer Cache?
> >
> >It's kind of like a school teacher admonishing his/her
> class that "a >troublesome few have ruined things for
> everybody". When I was >in school,
> >"troublemakers" were segregated from the rest of the
> class, sometimes >cumulatively into a separate classroom
> (we called ourselves >"the mentals"
> >and read Mad magazines all the time, which accounts for a
> lot, then and >now). Nowadays, I'm sure that such a
> measure isn't considered >for fear of
> >lawsuit for hurting the "self-esteem" of the poor dears.
> >Never mind the
> >confusion between the useless feel-good phrase
> "self-esteem" >and the more
> >useful and thought-provoking phrase "self-respect". Oh
> well, >better stop
> >now...
> >
> >Anyway, marking a table as CACHE and placing it in a KEEP
> >buffer pool which
> >is large enough to accommodate all of the used blocks is
> the >closest thing
> >to pinning a table into the Buffer Cache as you'll get,
> as >Arup described.
> >
> >Of course, there is little benefit from such a move, as
> Arup >also mentioned.
> >
> >Just yesterday, I visited a customer who had a series of
> SQL >statements that
> >were executing some 10 million times _each_ per day,
> averaging >about 20-1500
> >LIOs per execution. They each had a 99.99999999999%
> "buffer cache hit >ratio", yet strangely enough the
> performance on the server is >absolute crap
> >because the eight brand-new 2Ghz CPUs on the server are
> busy >as hell with no
> >time to spare for anything.
> >
> >Well, you know and I know that they simply need more
> CPUs, >which is what HP
> >is busy telling them, today right as we "speak".
> Moreover, Oracle >Consulting is shoulder to shoulder with
> them, nodding their >heads. No way
> >does the crap custom-built application need to be altered
> in >any minor way,
> >so that it doesn't keep performing the same useless
> validation >query on the
> >same set of static lookup tables over and over again for
> each >row inserted,
> >when the JDBC thin client can easily query these tables
> only >once and store
> >the results. Nope. No sirree...
> >
> ><Cliff-Clavin-voice>
> >It's a little-known fact that Java code actually has the
> consistency of >concrete, once in production. There are
> so many interdependencies from >shared modules and RPCs
> that people are terrified of modifying >anything,
> >probably for good reason. Far easier to shift blame or
> say >"hear hear" when
> >the vendor proposes another 4-8 CPUs.
> >
> >Ah, I believe I'll have another beer when you're ready,
> Sammy... ></Cliff-Clavin-voice>
> >
> >Anyway, first tune the SQL. Then, tune to the
> application to >get rid of
> >unnecessary SQL. Then and only then, consider tuning the
> >Buffer Cache to
> >segregate "bad" tables to the RECYCLE pool or "pinning"
> tables >to the KEEP
> >pool. Reversing the order is a great way to convert a
> happy >application
> >capable of running on a small server to an unhappy
> application >demanding a
> >huge server...
> >
> >
> >
> >on 10/21/03 5:21 AM, Arup Nanda at orarup_at_hotmail.com
> wrote: >
> >> Never. Altering the table to cache does not gurantee
> that it >will be always
> >> be available in the cache. It simply means the table
> will be >placed in the
> >> Least recently used end of the LRU list and it will age
> away >as time goes
> >> by, just like any other table.
> >>
> >> A better approach is to use KEEP pool and place teh
> table >(and all other
> >> tables that are accessed frequently) there. This is
> >particualrly true for
> >> datawarehouses wherethe lookup tables or small
> dimension >tables can be
> >> placed in KEEP pool.
> >>
> >> Ah, come tho think about it, actually there is one
> situation >where I will
> >> consider the CACHE option, when I restart the instance
> and >want the hit
> >> ratio to look good :)
> >>
> >> HTH.
> >>
> >> Arup Nanda
> >>
> >> ----- Original Message -----
> >> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com> >> Sent: Tuesday, October 21, 2003
> 3:39 AM >>
> >>
> >>> Hi all,
> >>>
> >>> when you would consider to put a table a cache...
> >>>
> >>> rgds
> >>>
> >>> gb
> >>>
> >>>
> >_________________________________________________________
> ______ >_________
> >>> Want to chat instantly with your online friends? Get
> the >FREE Yahoo!
> >>> Messenger http://mail.messenger.yahoo.co.uk
> >>> --
> >>> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net >>> --
> >>> Author: =?iso-8859-1?q?Gunnar=20Berglund?=
> >>> INET: oracledbasweden_at_yahoo.co.uk
> >>>
> >>> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com >>> San Diego, California --
> Mailing list and web >hosting services
> >>>
> >---------------------------------------------------------
> ------------ >>> 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). >>>
> >
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.net >--
> >Author: Tim Gorman
> > INET: tim_at_sagelogix.com
> >
> >Fat City Network Services -- 858-538-5051
> http://www.fatcity.com >San Diego, California --
> Mailing list and web hosting services
> >---------------------------------------------------------
> ------------ >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). >
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: John Kanagaraj
> INET: john.kanagaraj_at_hds.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com San Diego, California --
> Mailing list and web hosting services
> ----------------------------------------------------------
> ----------- 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). --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: Mercadante, Thomas F
> INET: NDATFM_at_labor.state.ny.us
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com San Diego, California --
> Mailing list and web hosting services
> ----------------------------------------------------------
> ----------- 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).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: Igor Neyman
> INET: ineyman_at_perceptron.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com San Diego, California --
> Mailing list and web hosting services
> ----------------------------------------------------------
> ----------- 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).