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.

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)

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)

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)

Serial direct path read in 11.1 and later will perform a PQ tablespace checkpoint before the direct path read begins. (page 149)

The process of allowing space at the end of a redo log file for potential redo data in the public and private redo threads is one explanation why archived redo log files become a couple of megabytes smaller than the online redo logs. (page 151)

Four different definitions of the term CURSOR, as related to Oracle Database. (page 162)

Demonstration of the use of bind variables significantly decreasing the number of dictionary cache accesses. (pages 171-172)

The CURSOR_SHARING parameter value of SIMILAR is deprecated as of Oracle Database 11.1 due to the arrival of adaptive cursor sharing. (page 173)

Lengthy discussion of the types of problems that might be intensified when moving a database from a single instance to a multi-instance RAC environment. (pages 202-229)

A potential area for improvement: explain how the author determined that block 0x008009a found in a redo header dump was in fact referencing datafile 2, block 154. The following SQL statement: SELECT TO_CHAR(DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(2,154), ‘XXXXXXX’) DBA FROM DUAL; produces a value of 80009A which confirms that the author is correct. The approach for decoding the block printed in the dump might be covered later in the book. (page 9) (Edit Dec 29, 2011: the author has provided an extended explanation describing how to decode the datafile number and block number in a comment found on his “OC 2 Undo and Redo” blog page)

The book states, “(as can be seen in the dynamic performance view v$latch_holder, which is underpinned by the structure x$ksuprlatch).” There should not be an underscore character in V$LATCHHOLDER, and the X$KSUPRLATCH structure does not seem to exist in 11.2.0.2 (confirmed by a response on the author’s errata page on his blog that the structure name is X$KSUPRLAT). (page 73)

The book states, “… if you query the dynamic performance view v$lock, you are querying the structure defined by the parameter enqueues.” It appears that the author intended to state, “defined by the parameter _ENQUEUE_LOCKS”. (Confirmed by a response on the author’s errata page.) (page 77)

The book states, “…and a column x$ksqlres, which is the address of the resource it’s locking, exposed indirectly through the type, id1, and id2.” The actual column name is KSQLKRES, without the embedded $ character. (Confirmed by a response on the author’s errata page.) (page 78)

Missing word in steps 1 and 2 that describe the sequence of events that lead to the V$LOCK output shown in the book. (Confirmed by a response on the author’s errata page – the missing word is delete.) (page 79)

The book expands the abbreviation ASMM as “automatic system memory management”, while the documentation and most other sources expand this abbreviation as “automatic shared memory management”. (Confirmed by a response on the author’s errata page.) (page 94)

The book states, “If you want to see how memory allocations change with the number of CPUs, you can adjust parameter cpu_count and restart the instance; however, in 11.2 you also need to set parameter _disable_cpu_check to false.” The _DISABLE_CPU_CHECK parameter defaults to FALSE, so the author probably intended to write TRUE. (Confirmed by a response on the author’s errata page.) (page 101)

The book dropped the “s” following the word “get” in the statistic name “CONSISTENT GETS – EXAMINATION”. The statistic name is spelled correctly on pages 44 and 51. (Confirmed by a response on the author’s errata page.) (page 114)

The book states, “If the optimizer thought the table was larger than the 2 percent limit, then the buffers used to read the table were immediately dropped to the end of the LRU list as the blocks were read…” It appears that the author intended to write “runtime engine” (as stated in the previous paragraph) rather “than optimizer”. (Confirmed by a response on the author’s errata page.) (page 118)

The book states, “11.2.0.2 takes this a little further with two new statistics: redo synch write time (usec), which is the time in microseconds…” It appears that the actual statistic name does not contain the word “write”. (Confirmed by a response on the author’s errata page.) (page 129)

The book states: “If we check x$qrst (the X$ structure underlying v$rowcache), we find that it contains an interesting column, as follows”. It appears that the X$ structure is actually X$KQRST – the DESC command that follows the sentence in the book shows the correct X$ structure name. (page 166) (Edit Dec 29, 2011: Confirmed by a response on the author’s “OC 7 Parsing and Optimising” errata blog page)

The book states: “(If you needed an argument why you should select only the columns you need in a query, rather than using select *, the extra cost of accessing dc_histogram_defs should be enough to convince you.)” This sentence immediately follows a sentence that described how adding a second predicate in the WHERE clause referencing a second column would double the number of gets from the dictionary cache; thus it seems that the comment about the threat of “select *” causing more visits to dc_histogram_defs is only significant if those columns are also specified in the WHERE clause. (page 171) (Edit Dec 29, 2011: a response from the author suggested experimenting with the core_dc_activity_01.sql script in the book’s script library; experimentation with that script indicates that the statement in the book is correct)

The book states: “There are probably a number of sites that could benefit from increasing the session_cache_cursor parameter,…” The parameter name is SESSION_CACHED_CURSORS – that parameter is correctly spelled on the previous page and further down the same page. (Confirmed by a response on the author’s errata page.) (page 176)

The book states, “However, you may recall all those latches relating to the library cache that appeared in 10g—like the library cache pin allocation latch…”. That particular latch appears to have been removed in Oracle Database 11.1.0.6, however it is not clear if this section of the book is only describing behavior prior to Oracle Database 11.1. (page 194) (Edit Dec 29, 2011: Confirmed by a response on the author’s “OC 7 Parsing and Optimising” errata blog page, with additional detail provided related to the statement in the book)

The book states, “In 10.2 they introduced code to allow KGL pins to be cached by sessions (hidden parameter _session_kept_cursor_pins) with similar intent…”. The _SESSION_KEPT_CURSOR_PINS hidden parameter does not exist in Oracle Database 11.2.0.2 (10.2.0.x not checked). (page 195) (Edit Dec 29, 2011: Confirmed by a response on the author’s “OC 7 Parsing and Optimising” errata blog page, with additional detail provided related to the statement in the book)

The book states, “However, the parameter cursor_spare_for_time is deprecated in 11g…” The CURSOR_SPACE_FOR_TIME parameter is spelled correctly in the previous sentence. (page 195) (Edit Dec 29, 2011: Confirmed by an errata entry on the author’s “OC 7 Parsing and Optimising” errata blog page)

The book states, “Let’s start with the memory structures—we have v$dlm_ress that is analogous to v$resources — it lists the things that are lockable…” It appears that V$RESOURCE should not have a trailing S. (page 209) (Edit Dec 29, 2011: Confirmed by an errata entry on the author’s “OC 8 RAC and Ruin” errata blog page)

The glossary indicates that the possible granule sizes are one of 4MB, 8MB, 16MB, and 64MB depending on the Oracle Database version and the size of the SGA. The statement in the book is more accurate than what is provided by the Oracle Database documentation for 11.2 which states that the granule size is either 4MB or 16MB depending on the size of the SGA. However, limited testing in Oracle Database 11.2.0.2 indicates that the granule size increases from 64MB to 128MB when the SGA_TARGET parameter is set to 1 byte greater than 32G, and jumps to 256MB when the SGA_TARGET parameter is set to 1 byte greater than 64G. A granule size of 32MB is possible when the SGA_TARGET was set to a value between 8G + 1 byte to 16G. (page 247) (Edit Dec 29, 2011: Confirmed by an errata entry and follow up comments on the author’s “OC Glossary” errata blog page)

Data Dictionary Views/Structures (the index at the back of the book misses most of these entries):

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: