|
Optimizing Oracle Performance |
Optimizing Oracle Performance
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:
Chapter 1, A Better Way to Optimize, explains why Oracle performance
improvement is so difficult using conventional methods. It explains three important advances from
other industries that Oracle performance analysts have ignored for decades. Finally, it describes the
new performance improvement method to which the remainder of the book is dedicated.
Chapter 2, Targeting the Right User Actions, describes why many performance
improvement projects are doomed from the beginning by poor project specifications. It explains how to
construct a foolproof specification for your performance improvement project. -
Chapter 3, Targeting the Right Diagnostic Data, describes how errors in
diagnostic data collection are the root cause of many failed performance
improvement projects. It describes why many projects cannot ever succeed
without properly scoped diagnostic data, and it
introduces three distinct sources of such information on Oracle systems.
Chapter 4, Targeting the Right Improvement Activity, explains how performance
improvement projects can be held to the same standard of informed consent that other scientific
endeavors require. It describes how to forecast performance improvement project costs and benefits and
how to find the economically optimal performance improvement activity from the universe of things you
could do about system performance.
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:
Chapter 5, Interpreting Extended SQL Trace Data, describes the content of an
Oracle extended SQL trace file. It describes the meaning of fields in a trace file, and it explains
the relationships of time statistics throughout a trace file. Chapter 6, Collecting Extended SQL Trace Data, explains how to collect the properly
scoped extended SQL trace data that you'll need to analyze a performance problem. Chapter 7, Oracle Kernel Timings, explains how software like the Oracle kernel
measures itself and how you can verify the self-diagnostic behavior of your own system. It goes on to
explain several sources of unaccounted-for time in Oracle trace files and why these lapses in timing data
often contain performance diagnostic data in and of themselves. Chapter 8, Oracle Fixed View Data, explains some of the many deficiencies of Oracle's
dynamic performance views. It presents descriptions of several popular V$ fixed views and examples
of their use. You might be surprised to find out that some of the things you thought you knew about
Oracle's dynamic performance views are untrue. Chapter 9, Queueing Theory for the Oracle Practitioner, is one of my favorites. It
explains the physical phenomenon of queueing and how to use the body of mathematical knowledge called
queueing theory to understand and even predict the performance of systems including Oracle
database applications.
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.
Chapter 10, Working the Resource Profile, describes a step-by-step method for
analyzing Oracle response time data that leads to maximized performance improvement at minimized cost.
It describes the tremendous economic benefits of waste removal and explains how to think “outside
the box” to achieve performance improvements you might never otherwise have considered. Finally,
it explains how to tell when your performance optimization work is complete, a task that is
astonishingly difficult in conventional performance improvement methods. Chapter 11, Responding to the Diagnosis, describes how to improve application
performance in response to various patterns you'll find in your performance diagnostic data. It places
particular emphasis upon how to eliminate wasteful work from your system, and it covers important
response time components that are documented either poorly or not at all in other works. Chapter 12, Case Studies, is the capstone chapter of the book. It documents four
complete cases from problem identification, through the targeting, analysis, and deployment processes,
to show you exactly how the method works in real life.
Errata
- Page xv, first paragraph:
The URL to Jared Still's site is missing a 'c'. It should read http://www.cybcon.com/~jkstill/util/util_master.html. - 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 ≤ ea ≤ 2.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 vprof.pl,
not vproP.pl. - 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:
- 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. 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 DBMS_PROFILER, 309 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 SET_SQL_TRACE_IN_SESSION procedure, 118 _SPIN_COUNT, 320–321 STAT, 83–84 Still, Jared, xv tim.pl, 134 tkprof, xv, 61, 320 Trace Analyzer, 61 trcsummary, 61 XCTEND, 84 |
Optimizing Oracle Performance
Oracle system performance inefficiencies often go undetected for months or even years—even under intense scrutiny—because traditional Oracle performance analysis methods and tools are fundamentally flawed. They're unreliable and inefficient. Oracle DBAs and developers are all too familiar with the outlay of time and resources, blown budgets, missed deadlines, and marginally effective performance fiddling that is commonplace with traditional methods of Oracle performance tuning. In this crucial book, Cary Millsap, former VP of Oracle's System Performance Group, and Jeff Holt, a tremendously talented optimization specialist, clearly and concisely explain how to use Oracle's response time statistics to diagnose and repair performance problems.
Cary and Jeff also show how queueing theory can be applied to response time statistics to predict the impact of upgrades and other system changes. Optimizing Oracle Performance eliminates the time-consuming, trial-and-error guesswork inherent in most conventional approaches to tuning. You can determine exactly where a system's performance problem is, and with equal importance, where it is not, in just a few minutes—even if the problem is several years old. This book cuts a path through the complexity of current tuning methods, and streamlines an approach that focuses on optimization techniques that any DBA can use quickly and successfully to make noticeable—even dramatic—improvements. The price of this essential book will be paid back in hours saved the first time its methods are used.
Oracle Insights: Tales of the Oak Table
Oracle Insights: Tales of the Oak Table presents eleven world-renowned industry specialists proffering their own highly experienced views, input, and insights on Oracle—where it's been, where it's going, how (and how not) to use it successfully, the software and techniques that they've introduced to help people achieve their goals, and some frightening tales of what can happen when fundamental design principles are ignored.
The collaborating authors have solved many of the worst Oracle performance problems in the world, and they've each saved at least one doomed flagship project. Over many years they've been sharing their unique knowledge with each other at conferences, around the OakTable, and in coffee shops, restaurants, and bars on five continents. Now they want to share their key insights with you.
A major focus of this book concerns the ways in which you can avoid common and debilitating mistakes when building Oracle software projects. From these stories, you'll learn the simple steps that will help you avoid real pain on your next (or current) Oracle project. |
|
|
