I pre-ordered a paperback copy of this book three months ago from Amazon and also purchased a PDF copy of the book from Apress. It was a long, nearly six year wait since the publication of the author's "Cost-Based Oracle Fundamentals" book, and I am fairly certain that many of those who read the "Cost-Based Oracle Fundamentals" book were looking forward to reading volume two in the anticipated three part series.
The author of this book is a well known Oracle Ace Director who has written at least three books since the year 2000 and contributed to a handful of other books. In addition to writing books, the author has also maintained a technical Oracle Database blog since 2006, and contributed to a number of Oracle focused Internet forums (Usenet, Oracle-L, AskTom, Oracle OTN) dating back to at least 1994. The book's primary technical reviewer is also a well known Oracle Ace Director and Oracle Certified Master who also maintains a technical Oracle Database blog and "living" Oracle reference site with deeply technical articles.
Did the book's contents meet the level of expectations provided by the book's cover and the publisher's description of the book? Shortly before the book arrived, I assumed that the book was targeted at people who might have struggled with the "Cost-Based Oracle Fundamentals" book. The back cover of this book states that the book targets readers with knowledge ranging from beginners to intermediate. I was surprised to find that chapter one lasted all of four pages, and that page seven introduced the reader to the first of many symbolic block dumps. It was at this point that the obvious becomes obvious - this book is intended to take the reader on a journey that is far deeper, more intense, and more densely packaged than pretty much any other Oracle Database book published in the last five or ten years. Reading the book twice might be required for full comprehension of the material, and a third read-through a year or two later might be a welcome reminder of how Oracle Database works under the covers to produce the pretty pictures painted by Enterprise Manager. In short, before reading this book, be certain to understand the Oracle Database concepts, and have a reasonable understanding of Oracle performance troubleshooting (read either the Performance Tuning Guide from the Oracle documentation library or the book "Troubleshooting Oracle Performance").
This book fills a key void in the Performance Tuning Guide from the Oracle documentation library: what is the next step when the Oracle wait interface fails to identify the source of a particular performance problem? There is no recipe for that solution; the solution is to understand what triggers Oracle Database to behave as it does. This book pieces together the under-the-hood understanding through the detailed inter-mixing of many Oracle statistics, performance views, X$ structures, latches, parameters, wait events, and Oracle error messages.
While there are a handful of problems/errors in the book, the vast majority of those problems are simple word substitutions or keystroke errors (for example, putting in an extra underscore or removing an underscore from an Oracle keyword on one page, while correctly specifying the keyword elsewhere in the book) that are fairly easy to identify and work around. The author devoted a section of his blog to quickly address potential errors found in the book, and to expand the book's contents as additional information becomes available.
In short, if you need to drill into Oracle Database performance problems beyond what is provided by the Oracle wait interface, this is the key book that glues together the bits and pieces of information that Oracle Database exposes (and selectively hides).
Comments on the Book's Contents:
* The test scripts used in the book show evidence that those scripts were often run on different Oracle Database versions, and the differences found in the output from those versions are often described in the scripts.
* While mostly avoiding Oracle features that require additional cost licenses, features that require an extra cost license, such as partitioning, state that an extra cost license is required.
* Description of symbolic block dump: starting byte position (follows @ sign), lock byte (lb:). (page 7)
* Description of change vector dump: operation code (KDO Op code), update row piece (URP), block address being updated (bdba:), segment header block (hdba:), interested transaction list (itli:), table number (tabn:), row number in the block (slot:), number of columns in the table (ncol:), number of columns updated (nnew:), increase in the row length (size:). (page 8)
* Description of ACID (pages 11-13)
* Due to an optimization (private redo and in-memory undo) in 10g, a session only needs to obtain the public redo allocation latch once per transaction, rather than once per change. (page 15)
* Points out an error in the Oracle Documentation, and in some books about LGWR writing a commit record to the online redo log. (page 28)
* Plenty of forward and backward references in the book.
* Undo block dump: transaction ID (xid:), block renewed incarnation number (seq:) (pages 32-33)
* A single undo block may contain undo records from multiple transactions, but only from a single active transaction. (page 34)
* Data block dump: interested transaction list index (Itl), transaction ID of a transaction that modified the block in the format of undo segment.undo slot.undo sequence number (Xid), undo record address in the format of absolute block address.block sequence number.record in the block (Uba), bit flag indicating state of the transaction (Flag), rows locked by the transaction (Lck), commit SCN or space available if the transaction committed (Scn/Fsc), cleanout SCN (csc:), last change SCN (scn:), number of times the block has changed at the SCN (seq:), row locked by transaction number (lb:) (page 37-38)
* For an index, the initrans parameter only applies to leaf blocks. (page 38)
* Helpful scripts (snap_9_buffer, snap_9_kcbsw, snap_11_kcbsw, snap_myst, snap_rollstat, snap_stat) in chapter 3's script library that create packages used for calculating the delta values of various statistics from Oracle's various performance views. The scripts often describe previously achieved results from Oracle Database versions ranging from 8.1.7.4 through 11.2.0.2. The script libraries for chapters 2, 6, and 7 include packages for monitoring the delta values of statistics from additional performance views.
* Parallel query execution, serial direct path read scans, and accesses to read-only tablespaces can result in repeated delayed block cleanout related work. In the cases of parallel query execution and serial direct path read scans, the cleaned out version of the block is not copied from the PGA to the SGA as a "dirty" block. (page 50)
* The spin and sleep approach to acquiring latches changed in recent Oracle Database releases. Rather than sleeping progressively longer times after each spin when attempting to acquire a latch, the process simply goes to sleep and waits for the process holding the latch to notify the process at the top of the list that is waiting for the latch. (page 72)
* Decoding TX lock ID1 and ID2 values into undo segment, slot, and wrap number. (page 77)
* The book tries to be specific regarding changes made in Oracle database versions, such as the change in 9.2.0.5 when the SESSION_CACHED_CURSORS parameter started controlling the number of PL/SQL cursors that were automatically held open, rather than the OPEN_CURSORS parameter. (page 89)
* The book states: "There is one particularly interesting difference between latches and mutexes: latches are held by processes, while mutexes are held by sessions..." (page 91)
* Default block sizes other than powers of 2 (12KB, 5KB, 4.5KB, etc.) are possible, but may be viewed as unsupported by Oracle support. The book makes a good case for using (only) 8KB block sizes, providing an exception for a 4KB block size as a secondary choice on some Linux platforms. (page 98)
* The book frequently addresses topics that are incorrectly described in other resources. For example, referencing the touch count of blocks to determine which block is the source of latch contention. (page 104)
* Recently discovered potential ACID durability problem described in detail. (page 129)
* Describes problems related to measuring LGWR performance through examination of LOG FILE SYNC wait event durations. (page 134)
* One of the threats of newer hard drives with larger sector sizes (4 KB rather than 512 bytes) is that redo wastage will increase. (page 136)
* The book mentions setting event 10120 to trigger relative file numbers to differ from absolute file numbers when new datafiles are created. Some information on the Internet incorrectly describes this event number as disabling index fast full scans. An interesting side-effect of experimenting with this event is that the database can contain multiple datafiles with the same relative file number (in different tablespaces), even when there are few datafiles in the database. (page 143)
* Oracle checkpoints described: Instance recovery checkpoint, Media recovery checkpoint, Thread checkpoint, Interval checkpoint, Tablespace checkpoint, PQ tablespace checkpoint, Close database checkpoint, Incremental checkpoint, Local database checkpoint, Global database checkpoint, Object reuse checkpoint, Object checkpoint, RBR checkpoint, Multiple object checkpoint (pages 148-149)
* Serial direct path read in 11.1 and later will perform a PQ tablespace checkpoint before the direct path read begins.
Read more ›
