Figure 9-31. Shown is the response-time curve shift as a result of the logical IO service time decrease (improvement). Not only does this increase performance with no workload change (69 lio/ms), but in the current situation (point B), the response time remains improved along with an 8% workload increase (74 lio/ms).

Now let's suppose the users would like even more of a performance improvement. Based on the OraPub 3-circle analysis, one obvious place to squeeze more performance out of the system is a reduction in logical IO consumption.

As shown in Figure 9-29, the statement with a SQL_ID ending in d6w consumed nearly 22.7 million logical IOs during the 30-minute reporting period, which is about 42.3 thousand logical IOs during each of its 536 executions. By tracing the d6w SQL statement, it was confirmed that a typical execution touches around 42.3 thousand logical buffers. It was also obvious that the large customer table was being full-table scanned! By simply creating an index on the status column and rerunning the query, only three logical buffers were touched. (While indexing a status column usually will not produce an improvement like this, in this application, it was indeed the case.) This means even if the statement is run 536 times, only 1,608 buffers will be touched. And since each logical IO take around 0.009 ms, during the 30-minute interval, the statement should consume only 14.5 seconds of CPU (1,608 _ 0.009).