To summarize this exercise's performance situation, the online users are experiencing poor performance due to Oracle being required to retrieve blocks from the IO subsystem and then process them. There is plenty of CPU, IO, and memory capacity. It just needs to be shifted to maximize performance. The planned shifts are to increase Oracle's buffer cache to virtually eliminate all physical IO requests and to tune the most CPU-consuming SQL statement. Both changes will have a dramatic performance improvement impact.

It is always best and more reliable to focus on one change at a time. As anyone working in IT has experienced, multiple simultaneous changes can have unanticipated effects. We need to know the impact of each change. Therefore, only one change will be implemented at a time.

Because it's the easiest to implement and should result in a significant performance improvement, we will increase the buffer cache first. Increasing the buffer cache to 1GB will effectively cache the customer table, resulting in virtually no physical IO requests. From a total response-time perspective and looking at Figure 9-26, we see that the physical IO arrival rate will drop to nearly zero. So, it is insignificant how fast or how slow the requested blocks are returned to Oracle. Figure 9-22 shows physical IO accounts for nearly 30% of the total Oracle response time. By eliminating nearly all the physical IO, assuming the users don't perform more work and we don't hit some locking type of performance issue (for example, row-level locking), general performance will improve by 30%.