RE: buffer cache and shared pool size tuning
Date: Fri, 11 Dec 2009 03:46:25 +0100
Message-ID: <4814386347E41145AAE79139EAA398980D39D61551_at_ws03-exch07.iconos.be>
Jared,
Thank you for sharing the queries.
But I'm looking for a more general method that is not looking to the advisory tables.
Reason for this is that I think that the figures in the advisory are pushed higher because of a query that is executed every 10, 15 minutes and that is performing a full table scan on a 2 GB (and growing) table.
ps) The vendor is writing a patch to deal with this problem query.
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
From: Jared Still [mailto:jkstill_at_gmail.com] Sent: donderdag 10 december 2009 0:14
To: D'Hooge Freek
Cc: oracle-l_at_freelists.org
Subject: Re: buffer cache and shared pool size tuning
I've used the attached scripts to query v$db_cache_advice and v$shared_pool_advice.
I have not done any testing to verify the accuracy of the claims of the cache advice, but have found the v$advice tables to be useful.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
On Mon, Dec 7, 2009 at 9:58 AM, D'Hooge Freek <Freek.DHooge_at_uptime.be> wrote: Hi,
I have a situation in which the application vendor is asking to increase the buffer cache size to resolve a performance issue, pointing to the advisories from the dbconsole. Although I have already proved via tracing that the problem is mainly in the queries and schema design, and that even if we cached everything we still would not be able to get the requested response times, they are still pointing to the advisory. All this asside, it made me wonder what would be a good method to verify if the buffer cache is correctly sized? Same question for the shared pool.
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 10 2009 - 20:46:25 CST