Chapter 10. Working the Resource Profile | Optimizing Oracle Performance

As I illustrate in Chapter 1, response time optimization is a routine part of humans ' everyday lives. The foundation of response time optimization is the commonsense notion, formalized by Gene Amdahl, that improving the largest component of response time creates the greatest opportunity for response time improvement.

Recall the resource profile format, also introduced in Chapter 1, which is shown again in Example 10-1. Response time optimization is so "built into us" that most people ”including users and business managers with no performance analysis training ”can understand a resource profile with very little effort. Technical and non-technical audiences alike never fail to respond correctly to Example 10-1 within ten seconds of seeing the data:

I don't know what those two "SQL*Net" things are, but whatever they are, they're consuming nearly three quarters of the report's total duration. What causes SQL*Net message from client and SQL*Net more data from client ?

This is exactly the right way to attack the problem. Business managers and users to whom I show this example are routinely confused about how professional performance analysts could have gone for three months believing that latch contention and CPU capacity were the root causes of this performance problem. (Example 10-1 is the same Oracle Payroll performance problem that I describe in Chapter 1.) Of course the answer is that the performance analysts on this project spent three months looking at the wrong diagnostic data.

Example 10-1. Technical and non-technical audiences alike make sense quickly of the resource profile format

 Response Time Component                Duration        # Calls     Dur/Call ----------------------------- ----------------- -------------- ------------ SQL*Net message from client       984.0s  49.6%         95,161    0.010340s SQL*Net more data from client     418.8s  21.1%          3,345    0.125208s db file sequential read           279.3s  14.1%         45,084    0.006196s CPU service                       248.7s  12.5%        222,760    0.001116s unaccounted-for                    27.9s   1.4% latch free                         23.7s   1.2%         34,695    0.000683s log file sync                       1.1s   0.1%            506    0.002154s SQL*Net more data to client         0.8s   0.0%         15,982    0.000052s log file switch completion          0.3s   0.0%              3    0.093333s enqueue                             0.3s   0.0%            106    0.002358s SQL*Net message to client           0.2s   0.0%         95,161    0.000003s buffer busy waits                   0.2s   0.0%             67    0.003284s db file scattered read              0.0s   0.0%              2    0.005000s SQL*Net break/reset to client       0.0s   0.0%              2    0.000000s ----------------------------- ----------------- -------------- ------------ Total                           1,985.4s 100.0%

My favorite performance improvement epigraph comes from, of all places, an instructional book about golf [Pelz (2000) 215]:

There's nothing worse than working hard on the wrong thing, expecting improvement from it, then ending up with nothing.

Resource profiles are superb at telling you what you need to fix whether you know how to fix it or not. They help you avoid the common Oracle tuning pitfall of fixing the thing you know how to fix without regard to whether your work will make any difference.

Top