RE: buffer cache and shared pool size tuning
Date: Wed, 9 Dec 2009 23:25:54 +0100
The performance gain would depend on the user action. For the once I traced it would be arround 30 to 40% performance gain (not taking in account cpu overhead and stuff). This is because the queries often need to read something like 500MB of data to get a resultset of maybe 1000 records. And these are queries used to populate a single end-user screen. The queries are just doing to much io, not to much physical io.
I actually have a trace, which showed that all data for a query was coming from memory and the response time was still to high....
Oracle Database Administrator
tel +32(0)3 451 23 82
From: Martin Berger [martin.a.berger_at_gmail.com] Sent: 09 December 2009 21:04
To: D'Hooge Freek
Subject: Re: buffer cache and shared pool size tuning
IF you have agreed target response times (and maybe additional memory
around) you can bring the risk to your application vendor:
grab their account manager and argue: "Tell me how much memory I
should give the buffer cache /large pool. I will do so. Then we test
again. IF the test shows the required target response times, you are
right and I will excuse my wrong assessment. But otherwise YOU excuse
yours and take over responsibility (and payment) for the additional
I'm aware this will never happen, but I like the face of these account managers (or performance-'pro's) at this particular point. - And it keeps the discussion running. From this point on they have to argue why they suggest something, they are not 100% certain.
just out of curiosity: how much time / percentage could you save if you eliminate all physical IOs?
> 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.
http://www.freelists.org/webpage/oracle-l Received on Wed Dec 09 2009 - 16:25:54 CST