Holistic Tuning Part 1

What does the term “Holistic” really mean? From the online Webster Dictionary:

Holistic:

”… relating to or concerned with complete systems rather than with the analysis of, treatment of, or dissection into parts… ”

Given the above definition of “Holistic,” this next series of blogs will deal with looking at the entire database when performing a tuning exercise, not just the immediate statement or other item that seems to be the problem.

This Holistic view of tuning means that we must consider the total effects of design, structure, applications, and physical systems on tuning and treat each as a part of the whole rather than piecing them out into individual areas.

Holistic View of Oracle

Oracle is like an onion; each layer rests on the one underneath. If any layer is “rotten” then the entire structure is in danger. Likewise, a change to any layer may propagate effects up and down through other layers. Let’s look at an example.

Given: Statement X is generating excessive physical IOPS when it executes

Local Fix: Add indexes

Immediate local effect: Statement X runs faster with fewer IOPS

Holistic effects: Statements Y and Z switch from proper indexes for them to indexes added for statement X

Holistic Result: X runs faster, but Y and Z run slower, overall result: Slower system even though the tuning effort on X was a success!

Now, this is not to say that all local actions are going to have a negative effect. Usually a switch to a new index is because the optimizer has looked at statistics and determined the index would be better before using it. However, we all know this may not always work as designed!

Of course everyone generally agrees that caching is a good thing. The more we can cache data for Oracle to use, the faster the application will run. However, this comes at a cost. The cost of caching is that now instead of handing off IO to an external IO processor, IO has to be handled by the CPU using logical IO. Now, logical IO is several orders of magnitude faster than physical IO, except if it introduces so many additional CPU cycles that the system becomes CPU bound.

The converse is also true; without enough caching or disk IO bandwidth, adding memory or CPU to a system that is IO bound may not help at all. So again we have to truly understand the holistic view to take the proper course of action.

Local Actions that Have a Negative Holistic Effect

In my job with TMS I am tasked with doing system evaluations using statspack and AWR reports to determine if a client really needs a new IO subsystem or if other issues are causing their system performance issues. Believe me, it does us no good as a company to have someone say they bought our solution and it didn’t help their problem!

It is amazing how many people still suffer from basic, local problems that affect the holistic performance of the entire database. We will look at a few of these “local” issues that have a global effect over the next few blog entries.