Re: how do you decide your db_cache

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 14 Sep 2005 10:49:14 -0700
Message-ID: <bf463805091410494ab742f4@mail.gmail.com>

Comments inline:

> So how do you all decide your db_cache_size?

I use a collector and report based on this: http://www.ixora.com.au/scripts/sql/ideal_cache_size.sql

>From the 9i Performance Tuning Guide:

V$DB_CACHE_ADVICE can be used to size all pools configured on an instance. Make the initial cache size estimate, run the representative workload, then simply query the V$DB_CACHE_ADVICE view for the pool you want to use.

For example, to query data from the KEEP pool:

SELECT size_for_estimate, buffers_for_estimate
     , estd_physical_read_factor, estd_physical_reads
  FROM V$DB_CACHE_ADVICE
 WHERE name          = 'KEEP'
   AND block_size    = (SELECT value FROM V$PARAMETER 
                         WHERE name = 'db_block_size')

AND advice_status = 'ON';

I can't offer any comment on this, as I have not used it.

The script from Steve Adams site has proved useful, but I really should try the cache advice.

> Do you still say figure out your bad sql? No, you increase it, then
> work on the bad sql

Will increasing the db_cache_size make bad SQL run faster?

Or will it cause more contention in the buffer cache and slow everything down more?

Without testing the app in question, you can't really say what an ideal cache size is.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
11+ years of trying to appear to know what I'm doing.
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 14 2005 - 12:51:14 CDT

Re: how do you decide your db_cache_size