Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 26640 invoked from network); 19 Dec 2007 14:15:07 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 19 Dec 2007 14:15:07 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 583A97DD0B8; Wed, 19 Dec 2007 15:15:07 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23295-02; Wed, 19 Dec 2007 15:15:07 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 240B37DD067; Wed, 19 Dec 2007 15:15:05 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 19 Dec 2007 14:27:38 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 83DE77DC0BF for ; Wed, 19 Dec 2007 14:27:38 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11950-06 for ; Wed, 19 Dec 2007 14:27:38 -0500 (EST) Received: from rn-out-0102.google.com (rn-out-0910.google.com [64.233.170.189]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F33727DC0E9 for ; Wed, 19 Dec 2007 14:27:37 -0500 (EST) Received: by rn-out-0102.google.com with SMTP id a46so610011rne.10 for ; Wed, 19 Dec 2007 11:27:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=kcw/wSN1JfU5RTbkbtACOZMpnNpKNiRiX8R7PmZFveI=; b=B4F9pvnbiKtZxPuQGQ8IQJH1sdfE3EbG6U1kMMKUto8r9fbT9eSKIVC3A+PgCi44/uEAFnmDTJ7jq0pAgBV+sRcKr1SL3re2f4f+YIa0W9vCZ3w6SL3BKQDARJ1veWylJW8NIivT/eq41CQtVJEtwtAJY85GmfM6j2pr9jYZf6o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=qTD260Ojpre1SnK90eRLB1r5O5J7jm4P3JpxETzJxVI73mAmkuK4hlzg3IwtObSsdz5TIR7rtQjwRsL+FzZvqSuH7ZL6o5kpC+ZAOAQ4US4nWXwjA9bCNZ2W/TQf96WNdb2A1YVuCByCDpy9PGDSYEm2cdKLKm2D2BgCo4EQiB0= Received: by 10.150.135.2 with SMTP id i2mr3744045ybd.127.1198092456919; Wed, 19 Dec 2007 11:27:36 -0800 (PST) Received: by 10.150.137.2 with HTTP; Wed, 19 Dec 2007 11:27:36 -0800 (PST) Message-ID: <6e9345580712191127n570087a5ue2b9da0cfd18bf79@mail.gmail.com> Date: Wed, 19 Dec 2007 20:27:36 +0100 From: "LS Cheng" To: kutrovsky.oracle@gmail.com Subject: Re: os cache vs. db cache Cc: robyn.sands@gmail.com, oracle-l In-Reply-To: <52a152eb0712190942l6d728af9x8c7f34a6f2789ebd@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7134_27887774.1198092456916" References: <20070710065332.B47F070F1E8@turing.freelists.org> <003201c7c312$674e2270$0200a8c0@Primary> <52a152eb0712190942l6d728af9x8c7f34a6f2789ebd@mail.gmail.com> X-archive-position: 4023 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: exriscer@gmail.com Precedence: normal Reply-to: exriscer@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_7134_27887774.1198092456916 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline In a database with tons of Full Table Scan you see the benefits! -- LSC On 12/19/07, Christo Kutrovsky wrote: > > Joining a bit late into this converation. > > Here's my opinion on OS vs Oracle cache vs PGA target. Strictly with > linux in mind. > > I would *always* go for the Oracle cache. The reason for this is not > so much the more efficient hot/cold management. > - The Oracle cache is more efficient. Using the Oracle cache reduces > your CPU usage (your *licensed* cpu) as well as is more concurent then > the OS cache. The Oracle cache can be used on the spot, there is no > extra work associated, just use it. > - I can monitor the Oracle cache, i can see which objects are > consuming it, how much dirty data and etc, all on a very granual level > - I get to have *real* disk responce time statistics. As opposed to a > blended rate due to OS cache > - I have more control of what goes into cache and when. I have control > how fast to write out dirty data to disk. > > Same goes for the PGA. I would always give more PGA then OS cache. Of > course, there's the special case where each "work area" can be at most > 4gb (even on 64 bit) but that can be worked arround using parallelism, > where each parallel process can get it's 4gb. > - I get to see which areas are taking up space, which areas spilled to > disk, which queries are cached which are not. > - Perhaps I can the the ones that are causing disk activity - you > loose all this info with a blended OS cache. > - It's more efficient, no extra CPU work required > - It avoids double buffering - you will always have double buffering > if you rely on OS cache. > - The last thing you want on a CPU intensive task is more CPU to be used. > > Now mind you not, the default settings for PGA and sort/hash related > writes are very unfriendly for the datawarehouse, but tunning these > you can achieve some exceptional performance. Up to 2x on a going to > TEMP simply by tunning the IO patterns. SAN cache or not. > > I just cant see the case where the OS cache would be more advantegous. > > Christo Kutrovsky > DBA Team Lead > The Pythian Group > > > On 7/10/07, Robyn wrote: > > 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 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 > > > > 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 > > > > > > > > > > > > > > > > -- > Christo Kutrovsky > Senior Database/System Administrator > The Pythian Group - www.pythian.com > I blog at http://www.pythian.com/blogs/ > -- > http://www.freelists.org/webpage/oracle-l > > > ------=_Part_7134_27887774.1198092456916 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
In a database with tons of Full Table Scan you see the benefits!
 
--
LSC


 
On 12/19/07, Christo Kutrovsky <kutrovsky.oracle@gmail.com> wrote:
Joining a bit late into this converation.

Here's my opinion on OS vs Oracle cache vs PGA target. Strictly with
linux in mind.

I would *always* go for the Oracle cache. The reason for this is not
so much the more efficient hot/cold management.
- The Oracle cache is more efficient. Using the Oracle cache reduces
your CPU usage (your *licensed* cpu) as well as is more concurent then
the OS cache. The Oracle cache can be used on the spot, there is no
extra work associated, just use it.
- I can monitor the Oracle cache, i can see which objects are
consuming it, how much dirty data and etc, all on a very granual level
- I get to have *real* disk responce time statistics. As opposed to a
blended rate due to OS cache
- I have more control of what goes into cache and when. I have control
how fast to write out dirty data to disk.

Same goes for the PGA. I would always give more PGA then OS cache. Of
course, there's the special case where each "work area" can be at most
4gb (even on 64 bit) but that can be worked arround using parallelism,
where each parallel process can get it's 4gb.
- I get to see which areas are taking up space, which areas spilled to
disk, which queries are cached which are not.
- Perhaps I can the the ones that are causing disk activity - you
loose all this info with a blended OS cache.
- It's more efficient, no extra CPU work required
- It avoids double buffering - you will always have double buffering
if you rely on OS cache.
- The last thing you want on a CPU intensive task is more CPU to be used.

Now mind you not, the default settings for PGA and sort/hash related
writes are very unfriendly for the datawarehouse, but tunning these
you can achieve some exceptional performance. Up to 2x on a going to
TEMP simply by tunning the IO patterns. SAN cache or not.

I just cant see the case where the OS cache would be more advantegous.

Christo Kutrovsky
DBA Team Lead
The Pythian Group


On 7/10/07, Robyn < robyn.sands@gmail.com> wrote:
> 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@jlcomp.demon.co.uk> 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@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
> >
> >
> >
>
>


--
Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
http://www.freelists.org/webpage/oracle-l



------=_Part_7134_27887774.1198092456916-- -- http://www.freelists.org/webpage/oracle-l