Book Review: Oracle Core Essential Internals for DBAs and Developers

25 12 2011

December 25, 2011 (Modified December 29, 2011)

Digging Deeply into Oracle Internal Processing when the Oracle Wait Interface is Insufficient
http://www.amazon.com/Oracle-Core-Essential-Internals-Developers/dp/1430239549

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. (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)
  • Detailed ORADEBUG examples in the appendix. (pages 231-238)
  • Detailed list of Oracle Database terms and their definitions. (pages 245-253)

 Suggestions, Problems, and Errors:

  • 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):

  • V$SESSTAT (page 15)
  • V$LATCH (pages 15, 69, 116, 138, 175)
  • V$LATCH_CHILDREN (pages 16, 69, 138, 166, 170)
  • X$KTIFP (in-memory undo pool, undo change vectors) (page 16)
  • V$TRANSACTION (pages 16, 29)
  • X$KCRFSTRAND (private pool, redo change vectors) (pages 16, 125, 237)
  • X$KTIFF (pages 19, 152)
  • DBA_ROLLBACK_SEGS (page 28)
  • V$LOCK (pages 29, 77, 78, 226)
  • X$KTUXE (transaction table information) (page 30)
  • V$BH (page 47)
  • X$KSUSE (base structure for V$SESSION) (page 59)
  • X$KTATL, V$RESOURCE_LIMIT, X$KSQEQ (page 60)
  • X$KSMFSV (pages 60, 236)
  • X$KSQRS (enqueue resources) (pages 60, 77, 78, 79, 88)
  • V$SQL (pages 63, 234)
  • V$LATCH_PARENT (page 69)
  • V$SYSTEM_EVENT (page 70)
  • X$KSUPRLATCH (page 73)
  • V$LATCHHOLDER (pages 73, 133)
  • V$RESOURCES (page 77)
  • X$KSQEQ (generic enqueues) (pages 77, 78)
  • X$KTADM (table/DML locks), X$KDNSSF, X$KTATRFIL, X$KTATRFSL, X$KTATL, X$KTSTUSC, X$KTSTUSG, X$KTSTUSS, X$KSQEQ (page 78)
  • X$KTCXB (transactions) (pages 78, 252)
  • X$KGLLK (library cache lock) (pages 89, 195)
  • V$OPEN_CURSOR (pages 89, 195, 248)
  • V$SGAINFO, V$SGA_DYNAMIC_COMPONENTS, X$KSMGE, X$KSMGV (page 94)
  • X$BH (pages 95, 102, 113, 219, 220)
  • V$BUFFER_POOL (pages 96, 99)
  • V$BUFFER_POOL_STATISTICS (pages 99, 100)
  • V$SGA_RESIZE_OPS, V$MEMORY_RESIZE_OPS (page 100)
  • X$KCBWDS (pages 100, 102, 138, 144, 179, 180)
  • X$KCBBF (page 113)
  • V$SYSSTAT (page 118)
  • V$SESSION (page 126)
  • V$SESSION_WAIT (pages 126, 226)
  • V$LOG, V$LOG_HISTORY (page 139)
  • V$CONTROLFILE_RECORD_SECTION (page 146)
  • X$KCBOQH (kernel cache buffers object queue header), X$KCBOBH (kernel cache buffers object buffer headers) (page 151)
  • OBJ$,TAB$, COL$, IND$, ICOL$, TRIGGER$ (page 162)
  • V$ROWCACHE (dictionary cache) (pages 164, 166, 169, 170, 225, 227)
  • V$ROWCACHE_PARENT (pages 164, 166)
  • X$KQRPD (pages 164, 168, 169)
  • DBA_OBJECTS, ALL_OBJECTS, DBA_DEPENDENCIES (page 165)
  • SYS.BOOTSTRAP$ (pages 165, 233)
  • USER_OBJECTS, USER_DEPENDENCIES, V$ROWCACHE_SUBORDINATE, X$QRST (page 166)
  • X$KQRSD (pages 168, 169)
  • V$SGASTAT (page 169)
  • X$KGHLU (pages 179, 180, 188, 189, 190)
  • X$KSMSP (page 188)
  • V$LIBRARYCACHE (pages 194, 234)
  • X$KGLPN (library cache pin) (page 195)
  • X$KGLOB (pages 196, 234)
  • V$GES_ENQUEUE (page 208, 209)
  • V$DLM_RESS (pages 208, 209)
  • V$RESOURCE, V$RESOURCE_LIMIT, V$SGASTAT (page 209)
  • V$LOCK (pages 209, 233)
  • V$RESOURCE_LIMIT (pages 209, 210)
  • V$SGASTAT (page 209, 210)
  • SEQ$ (pages 223, 224, 225, 226)
  • V$ENQUEUE_STAT (page 225)
  • V$LOCK_TYPE, V$SESSION_EVENT, V$EVENT_NAME (page 226)
  • V$PROCESS, V$BGPROCESS (page 231)
  • SYS.AUD$ (page 232)
  • X$KSMMEM (page 237)

 Parameters (the index at the back of the book misses most of these entries):

  • LOG_PARALLELISM (page 15)
  • TRANSACTIONS, SESSIONS, PROCESSES, CPU_COUNT (page 16)
  • UNDO_RETENTION (page 56)
  • _ENABLE_RELIABLE_LATCH_WAITS (page 72)
  • CPU_COUNT (pages 72, 75, 101)
  • _ENQUEUE_RESOURCES (page 77)
  • SESSION_CACHED_CURSORS (pages 89, 175)
  • OPEN_CURSORS (pages 89, 176)
  • SHARED_POOL_SIZE (page 94)
  • DB_CACHE_SIZE (pages 94, 97)
  • SGA_TARGET, MEMORY_TARGET, PGA_AGGREGATE_TARGET (page 95)
  • DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE (PAGE 97)
  • DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, DB_32K_CACHE_SIZE (page 98)
  • DB_BLOCK_BUFFERS, BUFFER_POOL_KEEP, BUFFER_POOL_RECYCLE (page 99)
  • DB_WRITER_PROCESSES, _DISABLE_CPU_CHECK (page 101)
  • _DB_HOT_BLOCK_TRACKING (page 104)
  • _DB_PERCENT_HOT_DEFAULT (page 108)
  • _DB_BLOCK_MAX_CR_DBA (pages 108, 115)
  • _DB_BLOCK_HASH_BUCKETS (page 111)
  • _BUFFER_BUSY_WAIT_TIMEOUT (page 113)
  • _DB_HANDLES, _DB_HANDLES_CACHED (page 115)
  • DB_FILE_MULTIBLOCK_READ_COUNT (page 117)
  • _SMALL_TABLE_THRESHOLD (page 118)
  • LOG_BUFFER (pages 123, 125)
  • COMMIT_WRITE, COMMIT_LOGGING, COMMIT_WAIT (page 130)
  • TRANSACTIONS (page 136)
  • FAST_START_MTTR_TARGET, FAST_START_IO_TARGET,  _TARGET_RBA_MAX_LAG_PERCENTAGE (page 140)
  • LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT (pages 140, 148)
  • _DB_BLOCK_MAX_SCAN_PCT (page 144)
  • ARCHIVE_LAG_TARGET (page 151)
  • _DEFER_LOG_BOUNDARY_CKPT, _DEFER_LOG_COUNT (page 153)
  • _MORE_ROWCACHE_LATCHES (page 164)
  • CURSOR_SHARING (page 172, 194)
  • SHARED_POOL_RESERVED_SIZE (pages 179, 183)
  • _SHARED_POOL_RESERVED_PCT (pages 179, 183)
  • _SHARED_POOL_RESERVED_MIN_ALLOC (pages 183, 191)
  • CURSOR_SPACE_FOR_TIME (page 195)
  • PARALLEL_MAX_SERVERS (page 212)
  • TRACEFILE_IDENTIFIER (page 232)
  • USE_STORED_OUTLINES (page 236)

 Statistics (the index at the back of the book misses most of these entries):

  • ROLLBACK CHANGES – UNDO RECORDS APPLIED (page 33)
  • PHYSICAL READS FOR FLASHBACK NEW, USER ROLLBACKS, TRANSACTION ROLLBACKS, ROLLBACK CHANGES – UNDO RECORDS APPLIED (page 34)
  • CR BLOCKS CREATED, DATA BLOCKS CONSISTENT READS – UNDO RECORDS APPLIED (page 44)
  • CONSISTENT GETS – EXAMINATION (pages 44, 51, 114)
  • CONSISTENT CHANGES, NO WORK – CONSISTENT READ GETS, CONSISTENT GETS, CR BLOCKS CREATED (page 45)
  • FUSION WRITES, COMMIT CLEANOUTS (page 46)
  • DB BLOCK CHANGES, REDO ENTRIES, COMMIT CLEANOUT FAILURES: BLOCK LOST (page 47)
  • CALLS TO KCMGRS, COMMIT TXN COUNT DURING CLEANOUT, CLEANOUT – NUMBER OF KTUGCT CALLS, DB BLOCK GETS (page 49)
  • REDO SYNCH WRITES (pages 49, 126, 128, 131, 132)
  • TRANSACTION TABLES CONSISTENT READS – UNDO RECORDS APPLIED (pages 52, 55)
  • TRANSACTION TABLES CONSISTENT READ ROLLBACKS (page 55)
  • LATCH FREE (page 70)
  • BUFFER IS PINNED COUNT (pages 114, 115)
  • SWITCH CURRENT TO NEW BUFFER (pages 115, 116, 139)
  • CR BLOCKS CREATED (page 116)
  • TABLE SCANS (SHORT TABLES), TABLE SCANS (LONG TABLES) (page 118)
  • MESSAGES RECEIVED (page 125)
  • REDO SIZE (pages 125, 135, 152)
  • MESSAGES SENT (pages 125, 126)
  • REDO SYNC WRITE TIME, REDO SYNCH TIME (USEC), REDO SYNC LONG WAITS (page 129)
  • REDO BLOCKS WRITTEN (page 131)
  • REDO ENTRIES (pages 131, 152)
  • REDO WASTAGE (pages 131, 135)
  • UNDO CHANGE VECTOR SIZE (page 152)
  • PHYSICAL READS FOR FLASHBACK NEW (page 156)
  • PARSE COUNT (TOTAL) (pages 173, 174, 176, 178)
  • PARSE COUNT (HARD) (pages 174, 176, 178, 194)
  • SESSION CURSOR CACHE HITS (pages 175, 176)
  • CURSOR AUTHENTICATIONS, SESSION CURSOR CACHE COUNT (page 176)
  • GC CURRENT BLOCK PIN TIME, GC CURRENT BLOCK FLUSH TIME (page 218)
  • GC CR BLOCK BUILD TIME, GC CR BLOCK FLUSH TIME (page 219)

 Wait Events (the index at the back of the book misses most of these entries):

  • READ BY OTHER SESSION, BUFFER DEADLOCK (page 113)
  • BUFFER BUSY WAITS (pages 113, 224)
  • DB FILE SEQUENTIAL READ, DB FILE PARALLEL READ, DB FILE SCATTERED READ (page 117)
  • LOG BUFFER SPACE (pages 123, 133)
  • LOG FILE SYNC (pages 125, 126, 132, 134)
  • RDBMS IPC MESSAGE (pages 125, 126)
  • LGWR WAIT FOR REDO COPY (pages 133, 134)
  • LOG FILE PARALLEL WRITE (page 134)
  • CF ENQUEUE (page 146)
  • ENQ: KO – FAST OBJECT CHECKPOINT, ENQ: RO – FAST OBJECT REUSE (page 150)
  • LOG FILE SWITCH (PRIVATE STRAND FLUSH INCOMPLETE) (page 152)
  • ROW CACHE LOCK (pages 169, 227)
  • CURSOR: PIN S WAIT ON X (page 192)
  • LIBRARY CACHE PIN (pages 192, 196)
  • GC CR BLOCK 2-WAY, GC CR BLOCK 3-WAY, GC CURRENT BLOCK 2-WAY, GC CURRENT BLOCK 3-WAY (page 217)
  • GC BUFFER BUSY (pages 224, 227)
  • LATCH: GES RESOURCE HASH LIST (page 227)

 Functions:

  • DBMS_SYSTEM.KSDWRT (write to trace file or alert log) (page 238)
  • DBMS_SYSTEM.SET_EV (page 239)

 Latches:

  • REDO ALLOCATION (pages 14, 126, 132, 134, 136, 152)
  • REDO COPY (page 15)
  • IN MEMORY UNDO LATCH (page 16)
  • CACHE BUFFERS CHAINS (pages 106, 112, 114, 116, 178)
  • CACHE BUFFERS LRU CHAIN (page 116)
  • REDO WRITING (pages 125, 132)
  • CHECKPOINT QUEUE LATCH (page 139, 141)
  • ACTIVE CHECKPOINT QUEUE LATCH (pages 141, 146)
  • LIBRARY CACHE, LIBRARY CACHE LOCK (page 175)
  • SHARED POOL (pages 178, 190)

 Oracle Error Messages:

  • ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction (page 13)
  • ORA-08177: can’t serialize access for this transaction (page 35)
  • ORA-01555: snapshot too old (page 56)
  • ORA-22924: snapshot too old (page 57)
  • ORA-00060: deadlock detected (pages 82-84, 232)
  • WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK (page 169)
  • ORA-01000: maximum open cursors exceeded (page 177)
  • ORA-04031: unable to allocate %n bytes of memory(%s, %s, %s, %s) (pages 183, 190, 191)
  • ORA-03113: end-of-file on communication channel (page 237)

Actions

Information




Follow

Get every new post delivered to your Inbox.

Join 139 other followers

%d bloggers like this: