I bought the alpha copy of this book from the Apress site in May 2011 when I heard about the book's pending release, and was able to obtain the final version of the book at the end of August 2011. I have read a couple of the other "Recipe" books from Apress, so I was excited to see how the recipe format could be applied to the task of Oracle Database performance tuning, specifically in versions 11.1 and above.
The authors of this book all have at least 10-15 years of experience with Oracle Database (and likely several years of IT related experience prior to that), are accomplished writers of several books (at least three of the "Recipes" books were co-authored by this book's authors), and at least two of the authors teach at the university level. The technical reviewer of the book is an Oracle Ace with an OCP certificate and also has an Oracle Certified RAC Expert certificate. Apress, based on my past experience, publishes some of the best Oracle Database material on the market. All of the critical recipe elements for a very successful, extremely useful book were initially in place for this book.
So, how well did the book's final contents meet the level of expectations provided by the book's front cover and the publisher's description of the book? Simply put, some of the chapters appeared as though someone tried to hammer a square cut nail into a finely machined and tapped machine-screw hole. The end result shows that the topic of performance tuning Oracle Databases simply does not fit well into a recipe format. The number of technical/logical errors per page in some of the chapters makes for a difficult challenge to find any useful/reliable information in those chapters without spending significant time verifying that the material presented by the book is accurate. Some of the other chapters did provide useful information that was mostly correct, but that information sometimes lacked sufficient depth of the information presented, likely constrained by the recipe format of the book. The topic selection for the book is reasonably good, although in execution the book sometimes drifted far away from performance tuning, into a lesson of what to do if a user cannot connect to the database, how to write basic SQL statements, how to perform basic database administration (such as handling snapshot too old errors), and how to issue operating system commands. Some sections of the book also seemed to be more specific to Oracle Database 9.2 and below than 11.1 and above. In short, there is good material in the book, but it is difficult to determine what is accurate and what is completely wrong.
This review is very long (20+ type-written pages for the first seven chapters in the book), as that level of detail is required to justify the rating attached to this book review. As such, this review will exceed the length limit permitted by Amazon, so see my blog for the full review. I have divided this review into three sections: interesting dictionary views, parameters, functions, hints, and Oracle error messages mentioned in the book; comments, corrections, and problems for the contents of the book; and interesting and/or well-above-average bits of information found in the book.
Data Dictionary Views:
* DATABASE_PROPERTIES (page 4)
* DBA_TABLESPACES (page 6)
* USER_SEGMENTS (page 12)
* USER_EXTENTS (page 13)
* V$DATABASE, DBA_TABLESPACES (page 16)
* V$TRANSACTION (page 18)
* DBA_AUTO_SEGADV_SUMMARY (page 20)
* DBA_ADVISOR_FINDINGS, DBA_ADVISOR_OBJECTS (page 25)
* DBA_INDEXES (page 29)
* DBA_AUDIT_TRAIL, DBA_AUDIT_OBJECT (page 41)
* USER_CONSTRAINTS (page 54)
* USER_INDEXES (page 55)
* USER_IND_EXPRESSIONS (page 66)
* V$OBJECT_USAGE (page 75)
* V$MEMORY_DYNAMIC_COMPONENTS, V$MEMORY_TARGET_ADVICE (page 90)
* V$SQL_WORKAREA_HISTOGRAM (page 94)
* V$RESULT_CACHE_STATISTICS (page 98)
* V$STATISTICS_LEVEL (page 114)
* DBA_HIST_WR_CONTROL (page 116)
* DBA_HIST_BASELINE (page 125)
* DBA_HIST_ACTIVE_SESS_HISTORY, V$ACTIVE_SESSION_HISTORY (page 139)
* V$SYSMETRIC, V$SESSION, V$SESSION_WAIT, V$SESSION_WAIT_HISTORY, V$SESSION_EVENT (page 148)
* V$SYSTEM_EVENT, V$SYSTEM_WAIT_CLASS (page 149)
* V$ACTIVE_SESSION_HISTORY, V$SQLAREA (page 150)
* V$EVENT_NAME, V$SYSTEM_WAIT_CLASS (page 155)
* DBA_EXTENTS (page 160)
* V$LOCK, V$LOCKED_OBJECT (page 164)
* SYS_TIME_MODEL (page 175)
* V$SESSION_WAIT_CLASS, V$EVENT_HISTOGRAM (page 177)
* V$UNDOSTAT (page 212)
* V$TRANSACTION, V$SESSTAT, V$STATNAME (page 214)
* DBA_TEMP_FILES, V$TEMP_SPACE_HEADER (page 217)
* V$OPEN_CURSOR, GV$OPEN_CURSOR (page 222)
Parameters
* UNDO_MANAGEMENT, UNDO_TABLESPACE (page 4)
* AUDIT_TRAIL (page 40)
* _USE_NOSEGMENT_INDEXES (page 54)
* OPTIMIZER_USE_INVISIBLE_INDEXES (page 70)
* PARALLEL_THREADS_PER_CPU (page 78)
* DDL_LOCK_TIMEOUT (page 80)
* SGA_TARGET, PGA_AGGREGATE_TARGET, MEMORY_TARGET, MEMORY_MAX_TARGET (page 83)
* RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT, and RESULT_CACHE_REMOTE_EXPIRATION (page 95)
* RESULT_CACHE_MODE (page 100)
* CLIENT_RESULT_CACHE_SIZE, CLIENT_RESULT_CACHE_LAG (page 103)
* DB_FLASH_CACHE_FILE, DB_FLASH_CACHE_SIZE (page 109)
* LOG_BUFFER (page 111)
* STATISTICS_LEVEL (page 114)
* CONTROL_MANAGEMENT_PACK_ACCESS (page 115)
* COMMIT_WRITE (page 159)
* LOG_BUFFER (page 160)
* CURSOR_SPACE_FOR_TIME (page 179)
* UNDO_TABLESPACE, UNDO_RETENTION (page 211)
* OPEN_CURSORS (page 222)
* SESSION_CACHED_CURSORS (page 223)
Functions:
* DBMS_RESULT_CACHE.STATUS (page 97)
* DBMS_RESULT_CACHE.MEMORY_REPORT (page 98)
Hints:
* APPEND (page 15)
* INDEX (page 70)
* RESULT_CACHE, NO_RESULT_CACHE (page 100)
Error Numbers:
* Chapter 1: ORA-01578: ORACLE data block corrupted, ORA-26040: Data block was loaded using the NOLOGGING option (page 16); ORA-02266: unique/primary keys in table referenced by enabled foreign keys (page 18)
* Chapter 2: ORA-00001: unique constraint violated (page 58), ORA-02270: no matching unique or primary key for this column-list (page 59), ORA-10635: Invalid segment or tablespace type (page 81)
* Chapter 3: ORA-00838: Specified value of MEMORY_TARGET is too small (page 86)
* Chapter 4: ORA-20200: The instance was shutdown between snapshots (page 120), ORA-13541: system moving window baseline size (page 124), ORA-13539: invalid input for modify baseline window size (page 124)
* Chapter 6: ORA-09817: Write to audit file failed (page 187)
* Chapter 7: ORA-0155: snapshot too old (page 211)
---
Comments, Corrections, and Problems:
* In recipe 1-2 the following note appears, "As of Oracle Database 11g R2, the EXTENT MANAGEMENT DICTIONARY clause has been deprecated." I could not locate confirmation in the Oracle Database documentation that that clause was deprecated in 11g R2, but the documentation does state that dictionary managed tablespaces were deprecated in Oracle Database 9i R2, even though Oracle Database 11g R2 apparently still supports (to an extent) dictionary managed tablespaces (and the deprecated DICTIONARY keyword) as long as the system tablespace is not locally managed. This recipe also states that the authors recommend using ASSM tablespaces, without describing the potential negative performance impact of such tablespaces. (pages 5-7)
* In recipe 1-4 the authors state "Specify a separate tablespace for the table and indexes" with the justification that doing so "simplifies administration and maintenance" - it would have been helpful for the authors to provide supporting evidence for this claim (pages 9-12). In recipe 2-2 on page 51, the authors suggests that backup and recovery is easier with separate tablespaces for data and indexes.
* In recipe 1-5 (page 13), an apparently unintentional word substitution: "An extent will not be allocated until the initial record is inserted into a given extent" - the second "extent" in the sentence should probably be replaced with the word "segment". The recipe should mention that deferred segment creation is an Enterprise Edition only feature, and explain the potential problems that restriction might cause. (pages 12-14)
* In recipe 1-8 the authors suggest checking the DBA_ADVISOR_EXECUTIONS, DBA_ADVISOR_FINDINGS, and DBA_ADVISOR_OBJECTS views without providing a warning about the licensing requirements associated with the DBA_ADVISOR views. (pages 19-23)
* Recipe 1-11 demonstrates using several approaches to reduce the number of rows that span more than one data block. It would have been helpful if the book differentiated between chained rows and migrated rows, and discussed why the ANALYZE TABLE LIST CHAINED ROWS command might still report a count greater than 0 in the CHAINED_ROWS table (table has more than 255 columns and must be broken into multiple row pieces, table row is larger than a table block, etc.). Since it is the absolute file number that appears most commonly in the various data dictionary views, it probably would have been a better approach to use the DBMS.ROWID_TO_ABSOLUTE_FNO function rather than the ROWID_RELATIVE_FNO function when decoding the ROWID values. (pages 28-32)
* Recipe 2-5 includes a SQL statement that attempts to identify unindexed foreign key columns. This same SQL statement appears on page 243 of the book "Oracle SQL Recipes: A Problem-Solution Approach". While the SQL statement only works for the current user's schema, and A, B, and C generic aliases are used for table aliases, there are more significant problems with the SQL statement and the recipe as a whole.
Read more ›
