A quick operating system analysis clearly shows a raging CPU bottleneck. As expected, the CPU subsystem is 90% busy on average, with a run queue nearly always greater than the number of CPU cores (which means processes are needing to wait for CPU resources). While the IO subsystem is doing some real work, there are no volumes busier than 60%, and their response times are well under the rule of thumb of 10 ms. There is no memory swapping. As in many cases, the network has been deemed out of scope. A solution focused on the operating system is to acquire more CPU cycles for Oracle processes. There are many ways to go about doing this, such as by identifying processes that do not need to be or should not be running during peak times.

For the application analysis, referencing a top SQL report, you look for SQL consuming the most buffers (buffer gets, or what I call logical IO). You quickly identify three statements that are consuming around 90% of all the logical IO. As expected, the top two statements are part of the order-entry application. An application-focused solution is to tune the SQL with the objective of reducing logical IO. It's fine to also reduce the physical IO, but the Oracle response-time analysis and the operating system analysis clearly support first and foremost focusing on logical IO-that is, buffer gets.

In summary, the database server is clearly suffering from intense Oracle buffer cache management. This is supported by a CPU bottleneck, a very high level of easily identifiable logical IO SQL statements, and Oracle processes waiting to get a latch related to buffer cache management. Fortunately, there are a number of solutions that should resolve the problem, from an Oracle, an application, and an operating system perspective.