Re: os cache vs. db cache

Wow ... I step away to get some work done and look what I've been missing.
I am caching a few of our smaller tables on one of our larger OLTP databases
(9.2.0.8) and we have seen very good results, but they are very small tables
in a database with adequate memory and other much larger tables.  I recently
set 4 tables in a key search query to cache, and per the developer, the
query is kicking butt. (The trace files back her up; 20 seconds to 2
seconds)  That was where my thought process started,  ie:

"If caching these tables worked well, are there additional tables I should
consider caching?"
"Hmmm, what's the largest table i should try this with?"
"If I start caching more tables, will I need a larger buffer cache?"

leading to ....

"damn, there's plenty more memory on the server, but the sys admin has
assigned it all to the OS cache"

and then the real question ...

"What will the ramifications of any change be on the stuff that is actually
working well?"

One of the systems in the mix is a data warehouse, and the concerns
mentioned in Jonathan's post are extremely relevant for this one.  Several
key queries have been tuned to use direct access, but many others have not.
So if the goal in the warehouse is more parallelization, then the approach
for this system would seem to be more os cache, and continue tuning the
queries that aren't taking advantage of parallelization yet.  The approach
for SAP probably remains the same; some of the larger queries and reports
also use direct path access.  Some of the other systems merit more study and
I'd like to compare how they run with a larger db cache and a smaller os
cache. If I find anything interesting, I'll let everyone know.

This is thread has been immensely helpful;  thanks to all ... Robyn

Other related posts: