Oracle8i Internal Services for Waits, Latches, Locks, and Memory

Oracle8i Internal Services for Waits, Latches, Locks, and
Memory
Preface
Why This Book?
Warnings
Audience for This Book
About the APT Scripts
Conventions Used in This Book
Comments and Questions
Acknowledgments
1. Introduction
1.1 The Oracle Kernel Layers
1.2 The Kernel Services
2. Waits
2.1 Semaphores
2.2 Wait Statistics
2.3 Reference
3. Latches
3.1 Latches and Locks
3.2 Parent and Child Latches
3.3 Latch Gets
3.4 Advanced Latching Control
3.5 Reference
4. Locks
4.1 Lock Usage
4.2 Lock Modes
4.3 Enqueue Locks
4.4 Row Cache Enqueues
4.5 Library Cache Locks and Pins
4.6 DML Locks
4.7 Buffer Locks
4.8 Sort Locks
4.9 Reference
5. Instance Locks
5.1 The Lock Manager
5.2 Global Locks
5.3 PCM Instance Locks
5.4 Other Instance Locks
5.5 Reference
6. Memory
6.1 The SGA
6.2 The Shared Pool
6.3 Process Memory
6.4 Reference
Colophon

 

---------------------------------------------------------------------------------------------------------------------------

Preface

A few years ago, I set my heart on researching and writing a truly advanced
Oracle performance-tuning book. Soon, I had a detailed outline running to more
than thirty pages. But when I started to write, I began to realize how much I had
yet to learn about Oracle. Each chapter was going to require considerably more
research than I had at first imagined. In particular, I began to realize that an
understanding of some aspects of Oracle internals would be vital to my quest. So
I began to learn what I could of Oracle internals, starting with the X$ tables.
If I had known then what I know now, about how vast an undertaking I was
commencing, I would probably never have attempted it. And many times I would
have given up in despair, except for the encouragement of my friends. They
always believed that I could comprehend the incomprehensible and construct a
coherent understanding of how Oracle works and should be tuned. It has been
somewhat like trying to determine the exact shape of an iceberg by walking all
over it and taking careful measurements of subsurface vibrations.
Why This Book?
My advanced Oracle performance-tuning book is still a dream. This little book is
something else: an introduction to Oracle internals. It builds the foundation
necessary for advanced performance tuning by explaining some of the basic
aspects of Oracle internals in detail.
Here you will find many of the undocumented system statistics explained. You
will learn how to gather additional statistics from the X$ tables. Your
understanding of how Oracle works will be deepened with clear explanations of
many of Oracle's internal data structures and algorithms. You will be alerted to
potential performance problems that are not mentioned in the documentation.
And you will expand your repertoire of tuning solutions and troubleshooting
techniques by learning how to use numerous hidden parameters and other
undocumented features.
Warnings
The kind of Oracle internals information I've included in this book is not readily
available to customers. Because I have never been an Oracle insider, the material
in this book has had to be compiled the hard way. I began by studying the
structure and contents of the X$ tables, and poring over trace files. I then
formulated hypotheses and tested them. Because of this approach, it is likely that
some of my conclusions about how things work are wrong, and that some of my
suggestions are misguided, or applicable only under limited conditions. So, the
onus is on you to test everything for yourself. If you find any errors, please email
me so that they can be corrected (see "Comments and Questions").
You should also note that this book goes boldly where Oracle Support fears to tread.
I explain and at times recommend the use of various undocumented features that I
find essential to advanced performance tuning. However, Oracle has chosen to leave
those same features undocumented—presumably with valid reasons. So please don't
expect Oracle to assist you in their use. Try them by all means, but if you have a
problem, quit. Don't bother Oracle Support about it.
Finally, please note that this book is oriented towards Oracle8i, release 8.1.
Although most of the material is applicable to earlier releases as well, some of it is
not. In particular, there have been major changes in Oracle Parallel Server in
both the 8.0 and 8.1 releases, and a number of the parameters have been hidden
in release 8.1.
Audience for This Book
This book is intended for Oracle database administrators (DBAs) and developers
who need to understand Oracle performance in detail. Although the information
is advanced, the presentation is easy to follow. Anyone who is familiar with the
basics of the Oracle architecture and has an aptitude for performance tuning will
be able to appreciate everything in this book. However, seasoned veterans will no
doubt appreciate it the most.
About the APT Scripts
This book makes a number of references to APT scripts. APT stands for Advanced
Performance Tuning. It is merely my personal toolkit of Oracle performance
tuning scripts. The scripts referred to in this book can be obtained from O'Reilly's
web site or from my own (see "Comments and Questions"). APT is not a
commercial product, and I do not warrant that the scripts are error-free. But you
are free to use them, or glean from them what you may.

from:internet