10g is pretty good size, unless you're still seeing very high I/O. In that case, EVEN if you've got a 97% hit ratio, increasing the size of db_block_buffers *could* greatly increase throughput. However, it may not. Increasing the size of the SGA is not NEARLY as effective as tuning the code that is doing lots of reads to and from disk. But, if that is already done to a reasonable level, then increasing the buffer cache could deliver great benefits, but then again maybe not. You'd have to get some benchmarks, make the change and see.
What people don't generally grasp is that when your buffer cache hit ratio is 97% - the remaining three percent represents just about 70% - 90% of your disk i/o (except redo logs and archive logs). So, theoretically a 1% increase in the hit ratio to 98% from 97% which looks small, represents as much as 1/3 reduction of total i/o (except for redo and arch logs)!
From my experience this is largely true and accurate.
The problem is, that the increase in SGA that got you from say 96% to 97% may cost you as little as 1G more or so, while the jump from 97% to 98% may cost you 6G more, and the jump to 99% may cost you another 15Ggig!
In other words, it follows the law of diminishing returns. Now, at that point of getting another 1% reduction in I/O you've now got an offset in performance from the brand-new CPU load that you've got from now maintaining, let's say 6 G more of 8k blocks - that's housekeeping and maintenance for 786,432 more blocks! Well, if you weren't CPU bound(per process and in total) before - then you may be much better off now. Glance, or even better - perfview will tell you this now, along with your DBA's help by using statspack. The next question is, does the next increment of 1% or so, which would require even more blocks to achieve get you going forward?
Someone mentioned the shared pool. This is where the executing code lives. It is vitally important that the cache hit ratio for this area remains high too. Make sure that is checked. Keep in mind that a 1% increase in this area is MUCH easier in terms of commmitted memory to achieve than db_buffer_cache. So, check this, and resolve it first.
Overall, I agree with the others who say more memory is better. In general it is, but it would be best to know what you're doing and why. Then, measure the system to make that the intended effects are positive, because you could be trading in your i/o problems for cpu ones.
We are the people our parents warned us about --Jimmy Buffett
