RE: buffer cache and shared pool size tuning

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Wed, 9 Dec 2009 23:25:54 +0100
Message-ID: <4814386347E41145AAE79139EAA398980D39BBE854_at_ws03-exch07.iconos.be>



Martin,

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....

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: Martin Berger [martin.a.berger_at_gmail.com] Sent: 09 December 2009 21:04
To: D'Hooge Freek
Cc: oracle-l_at_freelists.org
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 memory."
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?

> 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.
> --
> http://www.freelists.org/webpage/oracle-l--
http://www.freelists.org/webpage/oracle-l Received on Wed Dec 09 2009 - 16:25:54 CST

Original text of this message