Re:os cache vs. db cache

Robyn,

Since you're running a data warehouse, you may want to consider the effects of parallel tablescans and parallel index fast full scans, which do direct path reads and bypass the buffer cache.

Of course, there are parallel execution paths that still USE the buffer cache (parallel nested loop joins with indexed access, access through partitioned indexes etc.) but if a very large percentage of the work you do is bypassing the Oracle cache, you may want to switch memory to the file system cache instead to try and keep some important objects "close to" the action.

Alternatively, you might think about putting the temporary tablespace onto a filesystem that has caching enabled so that blocks written to the temp tablespace are in the cache (and not on disc) when you start to re-read them.

Yet another option is to keep the caching low - depending on a SAN cache (for exanple) to help you with the TEMP space - and make as much memory as you can available for PGA activity so that you minimise the number of cases where sorts or hash joins spill to disc.

It's not a clear cut choice, unfortunately - often it's a question of observing the current activity and making the best guess about how moving memory around will benefit the critical processes.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message -----
  > Date: Mon, 9 Jul 2007 14:03:42 -0400
  > From: Robyn <robyn.sands_at_gmail.com>
  > Subject: os cache vs. db cache
  >
  > Hello everyone,
  > Over the years, I've read differing opinions on balancing the os cache and
  > the database cache. Seems like I remember there were some advocates for
  > minimizing the os cache and maximizing the database cache to allow Oracle to
  > better determine which blocks should truly be cached and avoid necessary
  > blocks being cached because they were in proximity to hot data. I also have
  > heard opposite opinions.
  >
  > I'm specifically focused on performance for a data warehouse. Has anyone
  > tested performance with a minimized OS cache and a maximized database
  > cache? If so, what were the results? Does anyone have a really good paper
  > or book on the topic? I'm trying to collect some information before I start
  > discussions with the platforms guys.
  >
  > tia ... Robyn
  >
  

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