Re: how do you decide your db_cache_size
- From: Jared Still <jkstill@xxxxxxxxx>
- To: ganstadba@xxxxxxxxxxx
- Date: Wed, 14 Sep 2005 10:49:14 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
