Many Oracle educators teach that reducing the number of PIO (“physical” I/O) calls should be the top priority of SQL optimization. However, in our field work, we commonly eliminate 50% or more of the response time from slow Oracle applications, even after they’ve been tuned to execute no PIO calls. The secret is that Oracle LIO calls are more expensive than many people understand.
In this paper, I explain the following research results:
- LIO processing is the number-one bottleneck for many business processes today, even on systems with “excellent” database buffer cache hit ratios.
- Excessive LIO call frequency is a major scalability barrier, because LIOs consume two of the system’s most expensive resources: CPU and latches.
- Even if you could have an infinite amount of memory and achieve a perfect 100% database buffer cache hit ratio, your system will be inefficient and unscalable if it executes more Oracle LIO calls than it needs to.
- The statistics that database administrators commonly track can lead you to believe that PIO processing is your bottleneck when it’s not. Do not increase disk or memory capacity until after you determine the impact of PIO latency upon your end-user response times.
- If you will focus on LIO reduction from the very beginning of a SQL optimization task instead of PIO reduction, then you will usually eliminate most of your PIOs by side-effect, because most of your PIOs are motivated by LIO calls in the first place.
