In this case study, management is increasingly concerned about order-entry response time. Over the past six months, during month-end processing, order-entry response time has consistently increased to the point where it significantly impacts business. Your assignment is to return performance to previously acceptable levels.

The Oracle response-time analysis shows Oracle processes are primarily waiting to get cache buffer chain (CBC) latches, and Oracle processes are consuming nearly all of the available database server CPU. (This is not uncommon with severe latch contention.) CBCs are used to answer the question, "Is the block in the buffer cache?" CBCs get stressed when the answer is usually, "Yes, the block is in the cache." Repeatedly asking this question and accessing buffers in memory stresses the CPU subsystem. As I'll detail in Chapter 6, an Oracle-focused CBC solution is to increase the number of CBC latches by changing an instance parameter. This is a perfect example of why you must know Oracle's architecture. A fantastic diagnosis is important, but you must know what to do with that stellar diagnosis! This is why this book focuses on both diagnosis and Oracle internals.

Based on the Oracle analysis, you can anticipate what you will find in the application and the operating system analyses. In this situation, you would expect the operating system to be experiencing a raging CPU bottleneck and the application SQL to be asking for a lot of buffers (that is, buffer gets as opposed to block gets). Also, you will want to check the order-entry SQL. It's likely that the order-entry SQL is either causing the intense logical IO activity, and hence CBC activity, or being negatively affected by it.