Users who unknowingly run multiple queries at the touch of a button, waiting for the application to return control to them and getting upset, are highly likely to feel the effect of the Oracle in-memory queries! Figure 9-24 shows that both logical and physical IO response times are around 0.022 ms. In Figure 9-23, notice that all of the top ten statements have nearly the same number of physical IO and logical IO activity. This means that by eliminating the physical IO-that is, ensuring the tables are completely cached-we would expect the elapsed time to drop by half. Figure 9-24 shows the top SQL statement average elapsed time is 0.632 ms/exec. By increasing the Oracle buffer cache, average elapsed time is likely to drop to perhaps 0.316 ms/exec (0.632/2).

With an increase in performance (decrease in response time), users may perform work more rapidly, increasing the workload. The more time users are sitting and waiting for the application to return control to them, the more of a workload increase we can expect to see. It is possible a significant increase in the workload could offset the gain in response time. But either way, the users win. If they don't increase the workload, online performance increases. If they do significantly increase the workload, they will get more work done!

When making a statement like this, someone is likely to ask how much more throughput can be expected. That's when it's time to once again show the response-time graph in Figure 9-25, which illustrates the current situation in a pure logical IO (CPU) perspective. If the service time does not decrease (this is detailed in the second analysis cycle), then it appears we can nearly double the workload before response time significantly increases. Unless you have a way to control the user workload or understand the application very well, there may be no way of knowing if the users can or will increase the workload. But regardless, you have graphically and with simplified numbers demonstrated the performance impact of increasing the buffer cache.