For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
iii
Contents at a Glance
About the Author ........................................................................................................ x
About the Technical Reviewer ................................................................................... xi
Acknowledgments .................................................................................................... xii
Introduction ............................................................................................................. xiii
■Chapter 1: Getting Started . . . ................................................................................. 1
■Chapter 2: Redo and Undo ....................................................................................... 5
■Chapter 3: Transactions and Consistency ............................................................. 25
■Chapter 4: Locks and Latches ............................................................................... 59
■Chapter 5: Caches and Copies ............................................................................... 93
■Chapter 6: Writing and Recovery ......................................................................... 121
■Chapter 7: Parsing and Optimizing ...................................................................... 159
■Chapter 8: RAC and Ruin ..................................................................................... 199
■Appendix: Dumping and Debugging .................................................................... 231
Glossary .................................................................................................................. 245
Index ....................................................................................................................... 255
xiii
Introduction
When I wrote Practical Oracle 8i, there was a three-week lag between publication and the first e-mail
asking me when I was going to produce a 9i version of the book—thanks to Larry Ellison’s timing of the
launch of 9i. That question has been repeated many times (with changes in version number) over the
last 12 years. This book is about as close as I’m going to come to writing a second edition of the book—
but it only covers the first chapter (and a tiny bit of the second and third) of the original.
There were two things that encouraged me to start writing again. First, was the number of times I
saw questions of the form: How does Oracle do XXX? Second, was the realization that it’s hard to find
answers to such questions that are both adequate and readable. Generally, you need only hunt through
the manuals and you will find answers to many of the commonly-asked questions; and if you search the
internet, you will find many articles about little features of how Oracle works. What you won’t find is a
cohesive narrative that put all the right bits together in the right order to give you a picture of how the
whole thing works and why it has to work the way it does. This book is an attempt to do just that. I want
to tell you the story of how Oracle works. I want to give you a narrative, not just a collection of bits and
pieces.
Targets
Since this book is only a couple of hundred pages and the 11g manuals extend to tens of thousands of
pages, it seems unlikely that I could possibly be describing “the whole thing,” so let me qualify the claim.
The book is about the core mechanics of the central database engine—the bit that drives everything else;
essentially it boils down to undo, redo, data caching, and shared SQL. Even then I’ve had to be ruthless
in eliminating lots of detail and interesting special cases that would make the book too long, turgid, and
unreadable. Consider, for example, the simple question: How does Oracle do a logical I/O?, then take a
look at structure x$kcbsw, which is a list of all the functions that Oracle might call to visit a block. You will
find (for 11.2.0.2) that there are 1,164 different functions for doing a logical I/O—do you really want a
detailed breakdown of all the options, or would a generic description of the common requirements be
sufficient?
The problem of detail repeats itself at a different level—how much rocket science do you want to
know; and how much benefit would anyone get from the book be if I did spend all my time writing about
some of the incredibly intricate detail. Again, there’s a necessary compromise to reach between
completeness, accuracy, and basic readability. I think the image I’ve followed is one that I first saw
expressed by Andrew Holdsworth of Oracle’s Real-World Performance Group at Oracle OpenWorld in
2006. In a presentation about the optimizer and how to collect statistics, he talked about the 90/9/1
methodology, as follows:
• 90 percent of the time the default sample works
• 9 percent of the time a larger sample works
• 1 percent of the time the sample size is irrelevant
■ INTRODUCTION
xiv
It’s an enhancement of the famous 80/20 Pareto rule, and one that I think applies reasonably well to
the typical requirement for understanding Oracle’s internal mechanisms, but for the purposes of
explaining this book, I want to rearrange the order as follows: 90 percent of the time you only need the
barest information about how Oracle works to keep a system running adequately; 1 percent of the time
you need to be a bit of a rocket scientist to figure out what’s going wrong; and, I’m aiming this book at
the 9 percent group who could get a little more out of their databases and lose a little less time if they
had a slightly better idea of how much work is going on under the covers.
Where Next
Some time ago Tanel Põder (my technical reviewer) made the following comment in answer to the
question of when he was going to write a book on Oracle internals:
“The answer is never, if talking about regular, old-fashioned, printed-on-paper books. I think the subject
just changes too fast. Also, it takes at least a year of full-time work to come up with a book that would be
any good, and by the time of publishing, many details would already be outdated.”
This is a good answer, and adds weight to my comments about avoiding the 1 percent and sticking
to the general requirements and approximations. Tanel’s response to the problem is his “living book” at
http://tech.e2sn.com/oracle.
But paper is nice (even if it’s electronic paper)—and I believe the imposition of the book format
introduces a difference between the content of a collection of internet articles (even very good ones) and
the content a book. Again it comes back to narrative; there is a continuity of thought that you can get
from a book form that doesn’t work from collating short articles. As I write this introduction, I have 650
articles on my blog (a much greater volume of text than I have in this book); and although I might be
able to draw a few articles together into a mini-series, if I tried to paste the whole lot together into a
single book, it wouldn’t be a terrible book—even if I spent days trying to write linking paragraphs
between articles. Even technical books need a cohesive narrative.
To address the problems of a “non-living” book, I’ve posted a set of pages on my blog at
http://jonathanlewis.wordpress.com/oracle-core/, one page for each chapter of the book. Over time,
this will report any errors or brief additions to the published version; but as a blog it will also be open for
questions and comments. When asked about a second edition for my other books, I said there wouldn’t
be any. But with feedback from the readers, I may find that with this book, some of the topics could
benefit from further explanation, or that there are popular topics I’ve omitted, or even whole new areas
that demand a chapter or appendix of their own.
I’ve offered my opening gambit to satisfy a popular requirement—now it’s up to you, the reader, to
respond.
