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).
