RE: os cache vs. db cache

No need to beg - please differ at will :-)

Let me start with a disclaimer - I've NEVER used the CACHE clause or KEEP buffer pool, so I'm speaking strictly from what I've read, not based on any experience. That said, here is my understanding of the documentation, white papers, etc.:

What you describe below about tables not being cached in real life due to _small_table_threshold being exceeded only applies to tables with the default NOCACHE setting. If a table is flagged with CACHE, then it is cached at the MRU end of the LRU list regardless of _small_table_threshold.

However, I think the preferred method for caching large tables in 8i+ is to create a separate KEEP buffer cache and use the BUFFER_POOL KEEP setting for the specific segments you want to cache.

Either way, I'm pretty sure you can make it work with one of these methods and the resulting performance would be signifcantly better than caching it as the filesystem level, but again - I haven't tested it.

Just a side note - I checked the _small_tables_threshold parameter description from x$ksppi and it has changed from 8.1.7.3:

"threshold level of table size for forget-bit enabled during scan"

To 10.2.0.2:

"threshold level of table size for direct reads"

So, it looks like this parameter may not even serve the same purpose in current versions as it did in previous ones.

Regards,
Brandon

-----Original Message-----
From: K Gopalakrishnan [mailto:kaygopal_at_gmail.com]

I beg to differ here. Caching tables work excellently on paper. But when you put that in to practice it will be otherwise.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l