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

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