Re: os cache vs. db cache
- From: Robyn <robyn.sands@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2007 19:30:51 -0400
Jonathan,
Thank you very much for your reply. One of the systems in question is a
warehouse and the issues you mention are very relevant. This database has
been around since '97 or so, and the range of queries and reports is huge,
so of course, the range of plans and methods for data access are varied as
well. I have been working on tuning some of the key queries, and I am seeing
more queries use direct access reads, but it is not yet the primarily access
method. However, I'm thinking the best approach for this database is to
continue tuning for more parallelization and more direct reads, and perhaps
increasing the os cache and decreasing the database cache will be effective
after we've made more progress on that front. It's certainly something I
hope to see although I've got quite a bit of work to do first.
However, I'm just getting starting on moving warehouse data into partitioned
tables and indexes (just got licensing for this option recently) so again we
don't currently have a lot partition index reads now but they should be
increasing soon. If partitioned indexes continued to use the buffer cache,
I suppose this could change the mix as well. Either way, I've got a lot of
work to do first ...
Our temporary space is already on a cached files system although I expect
that was by accident, so I'm glad to hear that we may be benefiting from
this configuration. I'll check into the PGA aggregate target suggestion;
hashing and sorting is a problem area for several of the queries I'm
concerned about this week and I'd noticed a decline in PGA cache ratio from
where it was about 3 months ago. We have a year end closing coming up and
our revenue data will be extremely active in the next few weeks.
Thank you again ...
Robyn
On 7/10/07, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
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@xxxxxxxxx>
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
