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@xxxxxxxxx tel +32(0)3 451 23 82 http://www.uptime.be disclaimer: www.uptime.be/disclaimer ________________________________________ From: Martin Berger [martin.a.berger@xxxxxxxxx] Sent: 09 December 2009 21:04 To: D'Hooge Freek Cc: oracle-l@xxxxxxxxxxxxx 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. > -- > //www.freelists.org/webpage/oracle-l-- //www.freelists.org/webpage/oracle-l