Thanks for correction. Igor Neyman, OCP DBA [EMAIL PROTECTED]
-----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 > [EMAIL PROTECTED] > > > > -----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:[EMAIL PROTECTED] > >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 [EMAIL PROTECTED] > 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" > <[EMAIL PROTECTED]> >> 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: [EMAIL PROTECTED] > >>> > >>> 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: [EMAIL PROTECTED] > (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: [EMAIL PROTECTED] > > > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).