Section 10.3. How to Tell When Your Work Is Done

10.3 How to Tell When Your Work Is Done

Of the profound problems that plague conventional Oracle tuning methods , one of the most serious is the absence of a termination condition. As a result, conventional performance improvement projects are notorious for lingering until either the patience or the money runs out (hence the compulsive tuning disorder (CTD) phenomenon I mentioned in Chapter 3). By contrast, Method R specifies a termination condition:

If even the best net-payoff activity produces insufficient net payoff, then suspend your performance improvement activities until something changes.

How can you determine when the best net-payoff activity produces insufficient net payoff? If you can accurately forecast a performance improvement project's net payoff, then it's a straightforward financial analysis problem. Your company should allocate each unit of its cash to the activity that does the best job of net profit, return on investment, and cash flow simultaneously . If your project's expected financial performance is better than all the other ways your company could spend its next bit of capital, then your project should be next in line for implementation.

In reality, it is entirely appropriate for most performance improvement projects to be executed with no formal financial analysis. Many companies have full-time staff who are responsible for executing performance improvement activities whenever they're deemed necessary. In many circumstances, full-time analysts tinker with performance theories for a few hours each week. The incremental expense incurred by an analyst's affliction with even third-degree, adult-onset CTD is small enough to go unnoticed in most companies.

Even in situations where performance analysts have some extra time on their hands, it's still inconvenient not to be able to figure out whether a given user action has been truly optimized ”that is, made as fast as it can possibly go. When you measure performance with the system-wide statistics that conventional tuning methods prescribe, it is virtually impossible to know. However, the response-time focus of Method R provides the means to know whether a user action contains any more room for performance improvement. It's actually pretty easy to determine from a resource profile and its underlying extended SQL trace data whether it's going to be possible to squeeze any more response time improvement out of a user action.

Example 10-6Example 10-6 illustrates what a resource profile looks like when a targeted user action has been optimized . Notice the following attributes of the pattern:

Total response time is small. This attribute is essential. If your total response time is not sufficiently small, then you're not done yet ”it doesn't matter what other patterns exist within your resource profile. Your business defines the value of "sufficiently small," as I describe in Chapter 4.
Total response time is dominated by CPU consumption (usually more than 80% of total response time), but the application uses no unnecessary CPU capacity.
Database file reading or writing consumes more time than any response time component other than CPU service. The number of read or write calls is small.
Aside from CPU service and database file reads or writes , there's very little other time being consumed.

If a resource profile looks like Example 10-6, then you're finished if the total response time ( [1] ) is small enough. If total response time is not small enough and the remaining attributes hold ( [2] , [3] , and [4] ), then the only thing you can do to improve performance appreciably is to upgrade your CPU speed.

Example 10-7. The goal state resource profile

 Response Time Component               Duration         # Calls     Dur/Call ----------------------------- ----------------- -------------- ------------ CPU service  [2]  [   small   ]s  80.0% [   database reads or writes   ]  [3]  [   small   ]s <20.0%          [   few   ] [   everything else   ]  [4]  [   small   ]s <10.0%          [   few   ] ----------------------------- ----------------- -------------- ------------ Total  [1]  [   small   ]s 100.0%

Why does an optimal resource profile have CPU service and physical I/O at the top? It's natural, really. Something has to be at the top, of course, as long as the total response time is non-zero . What would you like it to be? A database has a very simple job to do, really: to manage data held in long- term storage. A database reads, processes, and writes data. CPU service shows up ahead of physical I/O in an optimal resource profile because I/O performance problems are usually cheaper and easier to fix (usually by optimizing application SQL). When a problem is cheap to fix, you're likely to fix it, which reduces the duration motivated by that problem until another event surfaces to the top of the profile. Thus, CPU service bubbles to the top of the profile on a system as its performance is improved. Even when the CPU service number is small, it usually becomes the top-line item because most applications force Oracle to spend a lot more time processing data than reading or writing it.

All other activities that a database does are generally "necessary but unwanted." For example, the latch free event denotes the use of a resource that is necessary to prevent the corruption of several Oracle internal data structures under conditions of high concurrency use [Millsap (2001c)]. We all need for the "sleep if a latch is unavailable" feature to be there, but our applications will run faster if we can make them wait for latch free events as infrequently as possible. This "need the feature but want to avoid using it" category accounts for the vast majority of Oracle's few hundred so-called wait events.

If a query is well- tuned , then it will probably consume more CPU than any other resource. However, this is not equivalent to saying, "If a query consumes more CPU than anything else, then it is well tuned." It is quite possible that a query can consume less CPU capacity than it does and still return the correct answer. The most likely way to accomplish this is... (drum roll, please ) ...eliminate unnecessary LIO calls.

You're finished when the cost of call reduction and latency reduction exceeds the cost of the performance you're getting today.

Top