Hotsos Home Page

Optimizing Oracle Performance

Visit Optimizing Oracle Performance at

Optimizing Oracle Performance, by Cary Millsap with Jeff Holt, defines the method that Hotsos-trained professionals use to optimize Oracle performance faster and more permanently than they had ever imagined.

You can read a sample chapter or order the book directly from the publisher.

Here is what Oracle Corporation has said about the project:

Structure of the Book

The book is divided into four parts, with twelve chapters and five appendixes in total.

Part I, Method, is about targeting. It is written in an informal, narrative style that sponsors and managers of performance improvement projects can read from front to back without getting distracted by a lot of technical details. It includes the following chapters:

Part II, Reference, is about detail. It is written in a deeply technical style in which I try to provide what a performance analyst needs to implement the method. It contains the following chapters:

Part III, Deployment, returns to the informal, narrative style that I hope will encourage project sponsors and managers to follow along. It covers the issues of how to complete the job for maximal positive impact.


Page xv, first paragraph:

The URL to Jared Still's site is missing a 'c'. It should read

Page xxv

Missing names include: Brad and Vonda McFarling, George Polisner, Zach Friese.

Page 11, Example 1-2:

There should be no blank line after the SQL*Net message from client... line.

Page 32, Reliability item:

Method R performs reliably in virtually every performance problem situation imaginable; ....

Page 79, lid items:

Tests in Oracle version 9 show that regardless of whether the execution was done via definer or invoker rights, the uid and lid always seem to show up in the trace data as the same value: the id of the user who was logged in and running the procedure.

Page 83, value field definition, append the following paragraph after the existing paragraph:

When no value field is emitted to the trace file, it is an indication that the NULL value has been bound into the placeholder variable. For example, in the fnd_profile.get_specific sample shown earlier, the absence of a value field for bind 4 indicates that the application has provided the NULL value for the :val placeholder. The bind value length specification of zero (avl=00) is corroborative evidence.

Page 96, Example 5-7, example input file content:

Line break positions should be...

#   WAIT #2: ...
#   WAIT #2: ...
#   FETCH #2:...

Page 113, last paragraph:

The second occurrence of "TIMED_STATISTICS" is misspelled.

Page 117, final code segment before the bear trap icon:

select sid, serial#
from v$session

Page 118, paragraph beside the bear trap icon:

Do not use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to activate extended SQL trace, because this procedure can activate SQL tracing only at level 1. You cannot activate extended SQL tracing with SET_SQL_TRACE_IN_SESSION.

Page 120, Table 6-2:

The correct operating system for file MERKUR_S7_FG_ORACLE_013.trc is OpenVMS 7.2-1.

Page 133, exercise 1:

The command to enable trace is listed as alter system set events... when it should be alter session set events.... The intention is to create a trace file for a single command in a single session, not turn on trace for all new sessions on the instance.

Page 134, Example 6-10, seventh line from the bottom:

$frac = sprintf "%06.0f", $frac/(10**($1-6));

Page 136, second paragraph from the bottom:

Notice the highlighted portion of the final tim field value: the trace file contains information about what happened up to time ...23.690992 (expressed in seconds), and in fact 4 μs afterward, ...

Page 137, third paragraph:

In the Hotsos Sparky data collector, we execute a query that is similar to this one immediately prior to executing the commands to activate and deactivate tracing.

Page 149, next-to-final sentence before Example 7-3:

Strike the term user-mode: The difference between the two marks (c0 and c1) is the approximate amount of CPU capacity that was consumed by the database call.

Page 157, first paragraph:

It's the Heisenberg uncertainty principle, not principal.

Page 157, Table 7-1 and the inequality above it:

Centisecond times should be rendered only to five significant digits, not seven. For example, 2.0000 cs ≤ ea2.9999 cs. This is because gettimeofday returns information that is precise only to 1 μs, which is 0.0001 cs.

Pages 168–169:

Change all (seven) occurrences of the string “265” to “266”, and add a zero in the final number of the sidebar at the top of page 169: 100,000,000,000,000,000,000,000,000.

Page 183, “Susceptibility to Overflow and Other Errors,” second sentence should read:

The problem is that an n-bit counter variable can store only 2n distinct values.

Page 203, Example 8-3 $Header line:

Program name should be, not

Page 212, First paragraph in "The denominator problem":

"This SQL*Plus program is an attempt to produce a true resource profile...".

Page 215, first paragraph:

A big part of the problem is a principle that....

Page 231, Figure 9-6:

The label “Previous arrival” should say Next arrival, as shown here:

Figure 9-6

Page 238, Figure 9-8 and caption:

Because the Poisson distribution is a discrete function (defined only for x = 0, 1, 2, ...), it is not correct to say that the distribution has a probability distribution function. Rather, a discrete distribution has a probability mass function. Instead of cluttering the text with a new definition for pmf, the drawing and caption can be corrected as follows:

Figure 9-8

Figure 9-8. The probability density function (pdf) for the function that generates the Poisson distribution with λ = 2 shows the probability P(A = x) that there will be exactly x arrivals in a one-second observation interval

Page 268, "M/M/m/∞/FCFS (exponential service time)" definition:

Similarly, you must ensure that....

Page 288, “Why targeting is vital,” first paragraph:

...(the one corresponding to the resource profile in Example 10-2, ...).

Page 290, third paragraph:

...we were actually taught principles that encourage waste.

Page 309, "Large c value for a PARSE call" definition:

If you have many small c values distributed across ....

Page 310, third line from the bottom:

...or swapping, then patch your Oracle kernel so that your user action's code...

Page 339, Fourth paragraph, second line:

...we started looking through our Hotsos Profiler output.

Page 343, First paragraph:

Using bind variables and making the code look like the scalable application code shown in Example 11-2 (p322) will result in....

Page 358, “Methodology” entry:

The word “been” is missing. Furthermore, the quoted matter should be offset from the surrounding text in a manner similar to that shown in the Oracle connection entry on page 359:

In recent years, however, the word “methodology” has been used as a pretentious substitute for “method” in scientific and technical contexts.... The misuse of “methodology” obscures an important conceptual distinction between the tools of scientific investigation (properly “methods”) and the principles that determine how such tools are deployed and interpreted—a distinction that the scientific and scholarly communities, if not the wider public, should be expected to maintain.

The following index entries on pages 381–388 are missing or incorrect:

buffer busy wait events, 54, 317

Central limit theorem, 169


Greek alphabet, motive for use, 228

Hotsos Profiler, xv, 61

Kyte, Tom, 55, 183, 194–196

Laplace, Pierre Simon de, 169

ora_check_sql, 322, 366

prof-cid, 95–96


_SPIN_COUNT, 320–321

STAT, 83–84

Still, Jared, xv, 134

tkprof, xv, 61, 320

Trace Analyzer, 61

trcsummary, 61