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)




The Transforming Face of the Oracle Support Site

23 12 2011

December 23, 2011

A day or two ago I saw an announcement that the HTML (non-Flash) version of the Oracle Support site was to be phased out in January 2012.  It seems like the last time I tried to use that site, the search functionality did not quite work for Oracle Database products; I gave up on the HTML version of the site and went back to the Flash-based version of the site. 

The performance of the Flash-based version of Metalink (I usually navigate to the site by entering metalink.oracle.com into the web browser’s address bar) My Oracle Support has improved significantly since the initial launch, and it appears that once the website is loaded, it is a bit more stable now.  I do not own any i products (iPhone, iPad, iToy) which do not support Flash, so the Flash-based version of the Oracle support site works OK on most of my devices (Windows 7 64 bit with IE 9, Windows 7 32 bit with IE 8, Windows XP 32 bit, Motorola Xoom, Amazon BlackBerry Playbook, etc.) as long as Flash is supported in the web browser.  Now the shocker, the Flash-based version of the support site is also planned to be phased out, based on Metalink (MOS) Doc ID 1385682.1.

Turning the other cheek, or doing an about-face?  The support document mentions that ADF Faces will be used – the site will be built using Oracle Application Development Framework.  I hate to say that this is a case of Oracle eating its own dog food – maybe there is a better way to state that the support site will be using their own technology rather than the technology of a third party?  I wonder if the Oracle OTN forums are also being redesigned to use Oracle technology, rather than that of a third party?  On a related note, it appears that the OTN forum site has fixed the Internal Server Error problem.

Anyone found any additional information about the change to the support site?  Any experience with ADF Faces?





Idle Thoughts – SSD, Redo Logs, and Sector Size

18 12 2011

December 18, 2011

It seems that I have been quite busy lately with computer related tasks that are not associated with Oracle Database, making it difficult to slow down and focus on items that have a foundation in logic.  Today I have had some spare time to dig further into the recently released “Oracle Core” book.  A Note on page 123 (obviously a logical page number) gave me a moment to pause.  Quoting a small portion of the Note:

“Recently disk manufacturers have started to produce disks with a 4KB sector size… From 11.2 Oracle lets the DBA choose which is the lesser of two evils by allowing you to specify the block size for the log file as 512 bytes, 1KB, or 4KB.” 

Do you see it yet, the reason to pause?

-

-

-

I am typing this blog article on a laptop that is a bit over a year old with two 256GB Crucial SSD drives in a RAID 0 arrangement.  Even though the laptop supports 3Gb/s transfer speeds from the internal drives rather than the more recent 6Gb/s transfer speeds, the drive arrangement is very quick (quick and potentially subject to a very bad data loss if one drive in the RAID 0 array fails), hitting a speed of 539 MB per second in a parallel full table scan.

I believe that the Crucial SSD drives have a 4 KB sector size, much like many other SSD drives.  It seems that some of Intel’s higher-end single layer drives use a 16 KB sector size (4000h = 16,384, see page 17 for the X25 series and page 22 for the 510 series).  I recall from reading the Oracle Database documentation the following statement:

“Unlike the database block size, which can be between 2K and 32K, redo log files always default to a block size that is equal to the physical sector size of the disk. Historically, this has typically been 512 bytes (512B).

Some newer high-capacity disk drives offer 4K byte (4K) sector sizes for both increased ECC capability and improved format efficiency. Most Oracle Database platforms are able to detect this larger sector size. The database then automatically creates redo log files with a 4K block size on those disks.” 

Do you see it yet, the reason to pause?

-

-

-

I recently read an interesting article titled De-Confusing SSD (for Oracle Databases) that inspired several interesting comments.  I think that the following two statements in the article contributed to the comment count:

SSD’s base memory unit is a cell, which holds 1 bit in SLC and 2 bits in MLC. Cells are organized in pages (usually 4k) and pages are organized in blocks (512K). Data can be read and written in pages, but is always deleted in blocks. This will become really important in a moment.

* Placing redo logs on SSD is not recommended. Exadata now has a “Smart Flash Logging” feature that uses redo logs on SSD. Note that it uses redo logs *also* on SSD. This feature allows Oracle to write redo in parallel to a file on SSD and a file on the magnetic disk, and finish the operation when one of the calls is successful.

Do you see it yet, the reason to pause?

-

-

-

So, the “Oracle Core” book states that as of Oracle Database 11.2 the DBA is able to select a 512 byte, 1024 byte, or 4096 byte (4 KB) block size for the redo log files.  Idle thinking… I have Oracle Database 11.2.0.2 installed on this laptop for educational purposes, let’s try executing a simple SQL statement that retrieves the block size for the redo log files:

SELECT
  GROUP#,
  BLOCKSIZE
FROM
  V$LOG
ORDER BY
  GROUP#;

GROUP#  BLOCKSIZE
------ ----------
     1        512
     2        512
     3        512 

So, if the sector size of my Crucial SSD drive is 4 KB, I have a (default) block size for the redo log files that potentially conflicts with the documentation, and if this is not an isolated issue, it might explain (in part) why the De-Confusing SSD (for Oracle Databases) article states that placing redo logs on SSD is not recommended.

Do you see it yet, the reason to pause?

-

-

-

I wonder if in testing redo log performance on SSD drives, if those drives were provided a “fair” test environment, where the BLOCKSIZE of the redo log files was sized appropriately for the write characteristics of SSD drives?  Any thoughts?





Internal Server Error – Contact Your System Administrator

6 12 2011

December 6, 2011

The Oracle OTN forums changed a bit a year or two ago, and in the process I stopped receiving email notifications when new entries were added to discussion threads.  The absence of email notifications was not a significant loss, although at times it is a bit interesting to see how a post in some of the threads changed a week or two after the initial post.  It appears that the OTN staff have corrected the email notification problems, and the speed of the forums seems to have improved significantly since the dramatic performance drop that was caused by an upgrade to the OTN forums a couple of years ago.

An interesting new problem has arrived.  The unbreakable (but free) forums tell me to contact the system administrator after I attempt to log in – the web server claims that the problem is caused by either an internal error or a misconfiguration.  I tried calling the system administrator here, but his phone is busy every time I try to place the call, and remarkably I always get interrupted when I try calling from a different phone.  ;-)  This is the error that I see immediately after logging in:

What is really irritating is that I received three emails today from OTN telling me that the OP has updated an OTN thread that I responded to, but sadly I cannot reach that thread.  After logging into OTN, I can’t even tell the browser to display forums.oracle.com – this is what I see: 

I can make it into Metalink (My Oracle Support) without an issue – I didn’t even need to log in (no password requested):

So, what happens if I click Sign Out in My Oracle Support?  Let’s try and then head back to forums.oracle.com (this seems to work sometimes):

So, the forums work, just as long as you do not care to contribute.  :-)  If we skip the login step, there are a couple of threads in the Community Feedback and Discussion forum about the problems (thread1, thread2).

Let’s log in again… (something comes to mind about the definition of insanity and doing something over and over again):

Out of curiosity, let’s see where forums.oracle.com is pointing:The traceroute is successful (ignore the long ping times – that was caused by other traffic on the Internet connection).

I noted that Google’s 8.8.8.8 DNS server is currently resolving forums.oracle.com also to e4606.b.akamaiedge.net which Google’s DNS server indicates is at IP address 184.25.198.174 (using Google’s DNS server does not change the error message that is displayed in the browser):

Without using Google’s DNS server, forums.oracle.com resolves to 23.1.18.174, as indicated by the trace route output.  I was curious what Wireshark might show when attempting to display forums.oracle.com (while logged in), so I fired it up and then told the browser to refresh the screen twice:

TCP ACKed lost segments…  Would the same problem happen when using Google’s DNS server, which points at IP address 184.25.198.174?  Let’s check:

Not exactly the same, but similar.  I have not spent a lot of time trying to dig through the Wireshark captures, but it is a bit odd that there are still TCP retransmissions (I might need to take a closer look at the Internet connection).

I guess that maybe this blog article drifted a bit.  Anyone else fail to connect 100% of the time, or never have a problem connecting?  I can only imagine the staff changes that probably would take place if one of our internal systems offered less than 95% uptime, much less the 99.99999% uptime that seems to be our internal expectation.  It is important to keep in mind that the OTN forums (even the copyrighted error message in the second screen capture) is a free service offered by Oracle Corporation – the problems will be resolved.





Stored Outlines (Plan Stability) are an Enterprise Edition Feature? A Self-Conflicting Story

2 12 2011

December 2, 2011

An innocent question was asked in an OTN thread regarding the availability of plan stability options in the Standard Edition of Oracle Database.  If we check the documentation for the latest release (11.2.0.x) of Oracle Database, we will find the following statement:

“The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1). Instead, you should use the SQL plan management feature that enables the optimizer to maintain a history of execution plans for a SQL statement.”

OK, so stored outlines are deprecated, even though stored outlines continue to work.  The documentation suggests using SQL Plan Management as a replacement for stored outlines.  That seems somewhat logical, because the Oracle Database documentation for 9.2 includes in its available feature list for the various Oracle Editions:

Plan Stability:

  • Standard Edition: Not available
  • Enterprise Edition: Available
  • Personal Edition: Available

“Allows execution plans for SQL to be stored so that the plan remains consistent throughout schema changes, database reorganizations, and data volume changes.”

The same documentation also suggest checking the V$OPTION view to see which options are enabled for the particular Edition of Oracle Database that is installed.

Based on the information found in the documentation for the older Oracle Database version, using Outlines (Plan Stability) requires the Enterprise Edition of Oracle Database, so it seems as though the quote from the first documentation link offers a good suggestion to use the Enterprise Edition feature of SQL Plan Management in place of stored outlines.  A couple of people have pointed out on this blog various documentation errors, so let’s check with Oracle support (that was a good suggestion offered by one of the responders in the OTN thread).

An easy to find article in My Oracle Support is Metalink (MOS) Doc ID 100911.1, “V$OPTION Fixed Table and Support Releases and Options”.  That document states that an Enterprise Edition license is needed to use stored outlines.  The document mentions Oracle Database 8i, if I recall correctly.

A quick check of the book “Performance Tuning Recipes” finds on page 412 an indication that stored outlines are only supported on the Enterprise Edition of Oracle Database.

Based on the above, the situation does not appear to be too positive for the OP in the OTN thread.  Maybe we should spend some significant time digging through My Oracle Support.  If we are lucky, we might stumble across the following two articles that offer a different opinion:

If we continue searching, we will find an indication in the “Troubleshooting Oracle Performance” book on page 248 a statement that the Standard Edition is sufficient for the use of stored outlines.

We might even take the suggestion of the Oracle Database documentation and check the V$OPTION view, after confirming that we are connected to a Standard Edition 11.2.0.2 database:

SELECT
  *
FROM
  V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SELECT
  PARAMETER,
  VALUE
FROM
  V$OPTION
ORDER BY
  DECODE(PARAMETER,'Plan Stability','1',PARAMETER);

PARAMETER                          VALUE
---------------------------------- -----
Plan Stability                     TRUE    <---------
Active Data Guard                  FALSE
Advanced Compression               FALSE
Advanced replication               FALSE
Application Role                   FALSE
Automatic Storage Management       FALSE
Backup Encryption                  FALSE
Basic Compression                  FALSE
Bit-mapped indexes                 FALSE
Block Change Tracking              FALSE
Block Media Recovery               FALSE
Change Data Capture                FALSE
Coalesce Index                     TRUE
Connection multiplexing            TRUE
Connection pooling                 TRUE
DICOM                              TRUE
Data Mining                        FALSE
Database queuing                   TRUE
Database resource manager          FALSE
Deferred Segment Creation          FALSE
Duplexed backups                   FALSE
Enterprise User Security           FALSE
Export transportable tablespaces   FALSE
Fast-Start Fault Recovery          FALSE
File Mapping                       FALSE
Fine-grained Auditing              FALSE
Fine-grained access control        FALSE
Flashback Data Archive             FALSE
Flashback Database                 FALSE
Flashback Table                    FALSE
Incremental backup and recovery    TRUE
Instead-of triggers                TRUE
Java                               TRUE
Join index                         FALSE
Managed Standby                    FALSE
Materialized view rewrite          FALSE
OLAP                               FALSE
OLAP Window Functions              TRUE
Objects                            TRUE
Online Index Build                 FALSE
Online Redefinition                FALSE
Oracle Data Guard                  FALSE
Oracle Database Vault              FALSE
Oracle Label Security              FALSE
Parallel backup and recovery       FALSE
Parallel execution                 FALSE
Parallel load                      TRUE
Partitioning                       FALSE
Point-in-time tablespace recovery  FALSE
Proxy authentication/authorization TRUE
Real Application Clusters          FALSE
Real Application Testing           FALSE
Result Cache                       FALSE
SQL Plan Management                FALSE
Sample Scan                        TRUE
SecureFiles Encryption             FALSE
Server Flash Cache                 FALSE
Spatial                            FALSE
Streams Capture                    FALSE
Transparent Application Failover   TRUE
Transparent Data Encryption        FALSE
Trial Recovery                     FALSE
Unused Block Compression           FALSE
XStream                            TRUE 

The above shows that Plan Stability is enabled for the Standard Edition, and thus usable with Standard Edition.  The innocent OTN question has thus led to a lot of effort to demonstrate that not only can the Oracle Database documentation be self-conflicting, but so can the My Oracle Support site.

It is interesting to note that AWR collection and the related features in Enterprise Manager are enabled by default in the Standard Edition of Oracle Database 10.1.0.x and 10.2.0.x.  However, just because these AWR related features are enabled by default does not mean that the features may be legally used in the Standard Edition of Oracle Database (or the Enterprise Edition without the additional cost Diagnostics Pack license).  I recall discussions in a couple of OTN threads where posters claimed that AWR related features were “free” with the Standard Edition, because those features were enabled by default – sorry, it does not work that way (if I recall correctly, partitioning is enabled by default in the Enterprise Edition, even though it is an additional cost option). 

In the OTN thread Pierre Forstmann offered the following helpful demonstration test case, which checks another item found in the V$OPTION view that is set to FALSE for the Standard Edition:

SQL> alter index emp_job_ix rebuild online;
alter index emp_job_ix rebuild online
*
ERROR at line 1:
ORA-00439: feature not enabled: Online Index Build 

As such, if stored outlines were not available on the Standard Edition of Oracle Database (just as OLAP Window Functions were not in 8.1.7.4 and Online Index Build are not in 11.2.0.2), using those features should result in an ORA-00439.

A couple of helpful articles that are related to stored outlines (most written by OakTable Network members):
http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/
http://www.oracle-base.com/articles/misc/Outlines.php
http://www.jlcomp.demon.co.uk/outline_hack.html
http://jonathanlewis.wordpress.com/2010/03/11/dropping-outln/
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf

Any other good self-conflicting stories related to Oracle Database?








Follow

Get every new post delivered to your Inbox.

Join 148 other followers