Book Review: Oracle Database 11g Performance Tuning Recipes (Part 2)

October 6, 2011

Hammering a Square Peg into a Round Hole: Fine Edges are Lost, Gaps in Detail
http://www.amazon.com/Oracle-Database-Performance-Tuning-Recipes/dp/1430236620

(Back to the Previous Post in the Series)

In an effort for my review to be fair, I have completed the review for the second half of the “Oracle Database 11g Performance Tuning Recipes” book (omitting the pages for chapters 11 and 12).  The total review is now 35.5 typewritten pages in length.  Since this is the longest review that I have written for a book, I decided to leave the second half of this book review open for reader comments.

The following is from a comment that I attached to the original review on Amazon in response to a comment that was attached to my review:

That said, there is still a chance that the format could have worked for performance tuning if:

1. The authors focused on Oracle Database 11.1 and 11.2
2. The authors focused on performance tuning, rather than telling the reader, for instance, how to select all of the rows from a table (that is for the SQL Recipes book to explain)
3. The authors, two of whom co-authored the book “RMAN Recipes for Oracle Database 11g”, stayed true to the recipe format that was used in that RMAN book. The RMAN book typically had a lengthy introduction to almost every chapter that more or less set the stage for the recipes in that chapter, providing the background knowledge and theory needed to understand the chapters (reference pages 313-318, the introduction for chapter 11 – viewable through Google books).
4. The authors of this book are no doubt talented, and I think that if the authors had an extra six to eight months of reading, reviewing, and reworking the recipes, the authors probably would have corrected most of the instances of logic errors, weak development problems, and off-topic nature of the recipes that I mentioned in the book review.
5. The authors spent some time discussing how a recipe could fail (maybe the reader did not buy that extra cost license, maybe the tip is valid only for versions of Oracle Database before 10.2, etc.).

For other people who read my review, I need to clarify that I am NOT recommending against buying this book – the book will just need to be used differently than originally designed. If you buy the book, after reading a recipe, perform a search of the documentation and various blogs, and most importantly test the material, to see if you are able to confirm or refute what is in the book. There is a good chance that such an approach will result in a better understanding of the topic than would have been the case if the recipes contained no errors AND were more fully developed. As had happened in my blog articles that referenced the alpha copy of this book, the topics in the book may be used as conversation starters to venture far further into the details of Oracle Database performance tuning.

The second half of the review follows, in the same format as the review for the first half of the book.

—

Data Dictionary Views:

• V$SORT_USAGE (page 218)
• V$HM_FINDING, V$HM_RECOMMENDATION (page 240)
• V$SESSION_LONGOPS (page 310)
• V$SQLSTATS (page 312)
• V$SQL_MONITOR (page 313)
• V$SQL_PLAN_MONITOR (page 316)
• DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT (page 319)
• V$DIAG_INFO (page 328)
• GV$PX_PROCESS (page 345)
• DBA_ENABLED_TRACES (page 353)
• V$BGPROCESS (page 363)
• DBA_AUTOTASK_CLIENT (page 449)
• DBA_OPTSTAT_OPERATIONS, DBA_TAB_MODIFICATIONS (page 450)
• SYS.AUX_STATS$ (page 464)
• SYS.COL_GROUP_USAGE$ (page 485)
• V$RESULT_CACHE_OBJECTS (page 510)
• V$PQ_TQSTAT (page 532)
• V$PQ_SYSSTAT (page 549)

Parameters:

• MAX_DUMP_FILE_SIZE, TIMED_STATISTICS (page 328)
• STATISTICS_LEVEL, DIAGNOSTIC_DEST, USER_DUMP_DEST (page 329)
• TRACEFILE_IDENTIFIER (page 332)
• _PX_TRACE (page 346)
• SQL_TRACE (page 353)
• LOG_ARCHIVE_TRACE (page 365)
• OPTIMIZER_MODE (page 447)
• OPTIMIZER_DYNAMIC_SAMPLING (page 459)
• DB_FILE_MULTIBLOCK_READ_COUNT (page 466)
• OPTIMIZER_USE_PENDING_STATISTICS (page 467)
• OPTIMIZER_INDEX_COST_ADJ (page 468)
• OPTIMIZER_FEATURES_ENABLE (page 470)
• PARALLEL_DEGREE_POLICY (page 531)
• PARALLEL_MIN_TIME_THRESHOLD, PARALLEL_MAX_SERVERS (page 544)
• PARALLEL_MIN_SERVERS, PARALLEL_SERVERS_TARGET (page 545)

Error Numbers:

• ORA-01652: unable to extend temp segment (page 219)
• ORA-03297: file contains used data beyond requested RESIZE value (page 221)
• ORA-01427: single-row subquery returns more than one row (page 264)
• ORA-01790: expression must have same datatype as corresponding expression (page 274)
• ORA-00932: inconsistent datatypes: expected NUMBER got CHAR (page 280)
• ORA-00060: Deadlock detected (page 362)

Hints:

• MONITOR, NOMONITOR (page 314)
• PARALLEL (page 344)
• OPTIMIZER_FEATURES_ENABLE (page 471)
• FULL (page 493)
• INDEX (page 494)
• NO_INDEX (page 495)
• ORDERED, LEADING (page 497)
• USE_NL (page 498)
• USE_HASH, USE_MERGE (page 499)
• OPTIMIZER_FEATURES_ENABLE (page 502)
• FIRST_ROWS (page 503)
• ALL_ROWS (page 504)
• APPEND, APPEND_VALUES (page 505)
• NOAPPEND (page 506)
• RESULT_CACHE (page 509)
• DRIVING_SITE (page 513)
• GATHER_PLAN_STATISTICS (page 518)
• REWRITE (page 519)
• STAR_TRANSFORMATION (page 521)
• PARALLEL, PARALLEL_INDEX (page 526)

 Comments, Corrections, and Problems:

• Recipe 7-5 seems to have borrowed the two SQL statements from a consulting website’s page.  The line breaks and column aliases match, however the table aliases were changed.  The first query is found under the heading “Sort Space Usage by Statement”, and the second query is found under the “Sort Space Usage by Session” heading. (pages 218-219)
• Recipe 7-8 on page 227 states, “While the database can automatically resolve deadlocks between sessions (by killing one of the sessions holding a needed lock), when there’s a latch or pin on…”  When a deadlock happens, the SQL statement that is executed by one of the sessions is automatically rolled back – the session itself is not killed. (pages 224-229)
• Recipe 8-1 “Retrieving All Rows from a Table” really does not belong in an Oracle performance tuning book, but instead in a beginning SQL book.  The recipe probably should have discussed some of the potential performance problems related to using “SELECT *” – the book simply stated that it was an alternative to listing every column to retrieve. (pages 254-255)
• Recipe 8-2 “Retrieve a Subset of Rows from a Table” shows how to add a basic WHERE clause to a SQL statement.  Anyone attempting to learn even basic Oracle performance tuning should be well beyond the level of knowledge covered in this recipe, which shares concepts similar to those in recipe 1-1 in the “Oracle SQL Recipes” book. (pages 256-257)
• Recipe 8-3 “Joining Tables with Corresponding Rows” demonstrates how to perform simple equijoins between tables using Oracle SQL syntax and ANSI syntax.  Once again, the concepts in this recipe are appropriate for a beginner level SQL book – concepts discussed are similar to those found in recipe 3-1 in the “Oracle SQL Recipes” book.  It might have been helpful for the book to discuss some of the potential dangers of natural joins. (pages 258-259)
• Recipe 8-4 “Joining Tables When Corresponding Rows May Be Missing” demonstrates outer joins using Oracle syntax and ANSI syntax, and in that regard is similar to recipe 3-3 in the “Oracle SQL Recipes” book.  The full outer join using Oracle syntax has a couple of problems, including the removal of duplicate rows when those duplicate rows are necessary (consider a father and son working at the same place and in the same department).  To fix the solution, change the UNION clause to a UNION ALL, and then in the second WHERE clause (found in the second half of the UNION ALL), add “AND E.MANAGER_ID IS NULL”. (pages 259-262)
• Recipe 8-5 “Constructing Simple Subqueries” describes how to add subqueries to the SELECT, WHERE, and HAVING clauses.  No examples of actual subqueries were provided under the heading of “Multi-Row Subqueries”, just simple variants of IN lists using ANY, SOME, and ALL keywords.  Based on the section heading I would have expected to see a subquery in the WHERE clause that potentially returned more than one row and used the ANY, SOME, or ALL keywords to prevent an ORA-01427 error. (pages 263-267)
• Recipe 8-6 “Constructing Correlated Subqueries” shows how to place a subquery in the WHERE clause that references columns in the parent query’s row sources – another example that might appear in an introductory SQL book).  The “How It Works” section of this query seems to ignore the fact that a correlated query may be automatically transformed by Oracle’s query optimizer into a standard join – the quote on page 268 could very easily be demonstrated as incorrect, “In a correlated subquery, the outer query is executed first, as the inner query needs the data from the outer query in order to be able to process the query and retrieve the results.” (pages 267-269)
• Recipe 8-7 “Comparing Two Tables to Finding Missing Rows” shows how to use the SQL MINUS operator.  Considering that this book describes performance tuning methods, it probably would have been a good idea to show alternate methods for accomplishing the same task that often perform more efficiently than the MINUS operator (such as a left outer join, with a specification that the column on the right side of the join must be NULL). (pages 269-271)
• Recipe 8-8 “Comparing Two Tables to Finding Matching Rows” shows how to use the SQL INTERSECT operator – basic SQL syntax again with no performance tuning side to the description. (page 271)
• Recipe 8-9 “Combining Results from Similar SELECT Statements” demonstrates using the UNION and UNION ALL operators – material that should be associated with a beginning SQL book (pages 271-274)
• Recipe 8-10 “Searching for a Range of Values” demonstrates how to use the SQL BETWEEN clause – another introduction to SQL recipe with no performance tuning twist (although the examples did not use TRUNC on a date column, a couple of the examples seem to rely on implicit varchar to date datatype conversions). (pages 274-276)
• Recipe 8-11 “Handling Null Values” shows how to use the NVL, NVL2, and COALESCE functions as well as the IS NULL and IS NOT NULL clauses.  The material in the recipe is similar to recipe 1-13 in the “Oracle SQL Recipes” book.  Considering that this is a book about performance tuning, the book should have indicated why     the COALESCE function is often preferable to the NVL function. (pages 277-280)
• Recipe 8-12 “Searching for Partial Column Values” shows how to use the SQL LIKE keyword.  There is a bit of a performance related side to this recipe, where the recipe shows applying the TO_CHAR function to an indexed date column prevents the optimizer from using the standard index on that column.  The recipe then shows a demonstration of altering the NLS_DATE_FORMAT to “’yyyy-mm-dd’” and then demonstrates with the help of an execution plan that using “WHERE HIRE_DATE LIKE ‘1995%’” in the SQL statement will allow the index on the date column to be used.  Unfortunately, the execution plan seems to show an index on the employee’s name being used rather than the index on the HIRE_DATE column.  If the recipe had included the “Predicate Information” section of the execution plan it would have shown that a function is applied to the HIRE_DATE column which will prevent the optimizer again from using that index; something like the following would appear in the “Predicate Information” section of the execution plan: “filter(INTERNAL_FUNCTION(“HIRE_DATE”) LIKE ‘1995%’)”  (pages 280-283)
• Recipe 8-13 “Re-using SQL Statements Within the Shared Pool” includes the following quote on page 284, “Essentially, bind variables are called substitution variables, and replace literals within a query.”  Technically, in Oracle Database terminology bind variables are not synonymous with substitution variables. On page 286 the book states about the row source generation stage of parsing, “This is an Oracle program that received the execution plan from the optimization step and generates a query plan.”  It appears that the book is attempting to paraphrase the Oracle Database Concepts Guide which states, “The row source generator is software that receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan, that is usable by the rest of the database. The iterative plan is a binary program that, when executed by the SQL virtual machine, produces the result set.”  Page 286 shows enabling a 10046 trace by changing the SQL_TRACE parameter at the session level – that parameter is deprecated as of Oracle Database 10.2. (pages 284-288)
• Recipe 8-14 “Avoiding Accidental Full Table Scans” states as a method of preventing accidental full table scans when indexes are present on a column, “When constructing a SQL statement, a fundamental rule to try to always observe, if possible, is to avoid using functions on the left side of the comparison operator.”  This statement does not make sense: “TO_CHAR(ORDER_DATE,’YYYY’) = ‘2011’” is equivalent to “’2011’ = TO_CHAR(ORDER_DATE,’YYYY’)”.  The authors probably intended to state, “If possible, in the WHERE clause avoid placing indexed columns in function calls, such as TO_CHAR, unless a function-based index is present for that exact function call and column combination.” (pages 288-290)
• Recipe 8-15 “Creating Efficient Temporary Views” shows how to create inline views, and provides performance warnings about their use.  The authors of the book make several critical mistakes in this recipe, giving the readers the impression that the rows requested by inline views are always materialized into tables, and as such should be avoided whenever possible.  The SQL statement found in the How it Works section uses subquery factoring (WITH blocks) rather than inline views as described in the book.  Page 292 states about the query found in the How it Works section, “In examples such as these, it is generally more efficient at the database level to create tables that hold the data defined by the inline views—in this case, three separate tables. Then, the final query can be extracted from joining the three permanent tables to generate the results.”  The recipe also contains the following cautionary note, “Large inline views can easily consume a large amount of temporary tablespace.”  There is more wrong than right in this recipe.  Oracle Database does not materialized every inline view, creating a temp table such as SYS_TEMP_0FD9D6628_D08144, as it processes the SQL statement.  The optimizer is free to materialize an inline view into a temp table, but quite often it is also free to push predicates into the inline view, and completely rewrite the original SQL statement so that it no longer contains an inline view.  The book’s suggested alternative of storing the intermediate results of inline views in permanent tables, unnecessarily generating redo and undo in the process, is very strange (if we must head in this direction, why not suggest using materialized views or statically defined temporary tables).  The original SQL statement with the inline views is able to take advantage of not only pushed predicates to further restrict the rows requested by the inline view, but also any indexes and partitioning that may be present in the parent tables.  Additionally, when troubleshooting the performance or accuracy of a SQL statement it is helpful to have the full SQL text that generates the resultset in a single SQL statement. (pages 290-292)
• Recipe 8-16 “Avoiding the NOT Clause” attempts to describe “performance drawbacks in using the NOT clause within your SQL statements, as they trigger full table scans.”  The sample SQL statement in the Solution section shows a SQL statement selecting from a single table named EMPLOYEES_BIG with the execution plan showing an estimated 697,000 rows to be returned by a full table scan and a calculated cost of 4,480.  The “improved” version of the query selects from two tables, with the execution plan showing a full table scan of the EMPLOYEES table and a nested loops join to the DEPARTMENTS table’s primary key with an estimate of 33 rows to be returned with an estimated cost of 3.  The authors specifically mentioned the drop in the calculated cost from 4480 to 3 (usually not a good measure of the performance improvement of two queries), but failed to recognize that a full table scan is still being performed, and the name of the table as shown by the execution plan changed from EMPLOYEE_BIG to EMPLOYEES – the execution plan included in the book does not match the associated SQL statement displayed in the book.  The use of the NOT keyword does not specifically prevent the use of an index on a column, as was stated in the book.  The NOT EXISTS example SQL statement will not perform as the authors expect because the NOT EXISTS clause is not a correlated NOT EXISTS clause – the query will return all employees with a COMMISSION_PCT greater than 0 regardless of the employee’s DEPARTMENT_ID value. (pages 293-295)
• Recipe 8-17 “Controlling Transaction Sizes”, while an interesting discussion on the topic of savepoints, the recipe does not have anything to do with performance tuning. (pages 295-297)
• Recipe 9-4 states on page 308, “Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order.”  That statement, while sometimes is correct, is easy to disprove – there are easy to find examples where that statement is not true.  The Oracle Database 11.2 Performance Tuning Guide states essentially the same thing, “The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first” – that statement is also occasionally incorrect as can be determined by a 10046 extended SQL trace.  The explained order of the execution plan (ID 3, 5, 2, 4, 1, 0) is incorrect – it should be (ID 3, 2, 5, 4, 1, 0).  The recipe also advises looking at the Cost column, so that it is possible to determine the amount of improvement – such an approach is unreliable.  On page 309 the book states, “By looking at our original explain plan, we determined that the EMP table is larger in size” – the execution plan does not indicate the size of a specific table, only the estimated number of rows that will be returned and the estimated number of bytes that will be returned from the row source (pages 307-310)
• Recipe 9-5 states that, “With a simple query against the V$SESSION_LONGOPS view, you can quickly get an idea of how long the query will execute, and when it will finish” – that statement is mostly true for simple SQL statements with a single table being read by a full table scan.  This view shows estimated remaining completion time of individual (currently executing) operations in an execution plan, and not the estimated time remaining before the SQL statement will finish (pages 310-311).
• In Recipe 9-6, the query of V$SQLSTATS does not necessarily retrieve the top five recently executed SQL statements that performed disk I/O, rather it retrieves the five SQL statements that have accumulated the most disk I/O (possibly through multiple executions) without regard to when the SQL statement executed (the statistics could still be in the V$SQLSTATS even though the SQL statement is no longer in the shared pool). (pages 311-312)
• Recipe 9-7 should have mentioned that using the V$SQL_MONITOR view requires an Enterprise Edition license, a Diagnostics Pack license, and a Tuning Pack license.
• Recipe 9-8 should have mentioned that using the V$SQL_PLAN_MONITOR view requires an Enterprise Edition license, a Diagnostics Pack license, and a Tuning Pack license. (pages 316-318)
• Recipe 9-9 should have mentioned that using the Oracle SQL Performance Analyzer requires a license for the Oracle Real Application Testing option. (pages 321-325)
• Recipe 10-1 states (on page 328) that if the TIMED_STATISTICS parameter is set to FALSE that SQL tracing [extended 10046 SQL trace] is disabled – the TIMED_STATISTICS parameter does not determine whether or not SQL tracing is enabled.
• Recipe 10-5 shows how to interpret a raw 10046 extended SQL trace file.  This recipe is surprisingly short at just over one page, including a short sample trace file.  The recipe really did not provide much information other than describing what (generic, not necessarily specific to the sample 10046 extended SQL trace file) processes take place in the Parse, Execute, and Fetch stages, without providing much information beyond stating that the 15.8ms long wait for ‘Disk file operations I/O’ is not a significant amount of time.  This recipe in the book missed a significant chance to describe one of the most useful tools when troubleshooting performance problems when the problems can be associated with a specific session.  See the book “Secrets of the Oracle Database”, the book “Optimizing Oracle Performance”, or the book “Troubleshooting Oracle Performance” for better examples of extracting useful information from a raw 10046 extended SQL trace file. (pages 334-335)
• Recipe 10-6 states the following, “Note that the TKPROF or other profiling tools show the elapsed times for various phases of query execution, but not the information for locks and latches. If you’re trying to find out if any locks are slowing down a query, look at the raw trace files to see if there are any enqueue waits in the WAIT lines of the raw file.”  I might be misinterpreting these two sentences, but the sentences appear to be incorrect assuming that a level 8 or greater (wait events) extended SQL trace generated by Oracle Database 10.1 (or higher) is passed into TKPROF.  Starting in Oracle Database 10.1 most of the enqueue waits are divided into much more specific wait events that begin with “enq:” (there are 299 wait events in Oracle Database 11.2.0.2 that begin with “enq:”).  The same is true for latches, where the “latch free” wait event is split out into multiple wait events (there are 43 wait events in Oracle Database 11.2.0.2 for latches). Oracle Database 9.2 and earlier did not provide information about the exact type of enqueue or latch wait in TKPROF summaries. (pages 335-336)
• Recipe 10-8 shows how to invoke TKPROF to analyze a 10046 extended SQL trace file.  The TKPROF call used by the book specifies the EXPLAIN option, and provides the following description “If you specified the explain parameter when issuing the tkprof command, you’ll find an execution table showing the execution plan for each SQL statement.”  The book does not explain that the execution plan displayed in the output file may not be the actual execution plan used for the query, much like the execution plan generated by autotrace and explain plan might not be the actual execution plan (this fact was not mentioned in the book either).  The book made no mention that the Row Source Operation information in the output file shows the actual execution plan used by the SQL statement (note that the 11.1.0.6 client may still show the wrong execution plan when analyzing a trace file from 11.1.0.6 or more recent version of Oracle Database).  At the top of page 340 the book states that the unit of measure of the time statistic is in milliseconds (1/1000 of a second).  Prior to Oracle Database 9.1 the unit of measure was centiseconds (1/100 of a second) and as of Oracle Database 9.1 the unit of measure is microseconds (1/1000000 of a second). The book stated that because the “SQL*Net message to client” wait event is an idle wait event, the time reported as waited can be ignored.  The actual wait event displayed in the book’s TKPROF output is “SQL*Net message from client” and the time reported is 462.81 seconds in 5,461 waits with a max wait time of 112.95 seconds.  Even though the “SQL*Net message from client” wait event is typically classified as an idle wait event, at the session level, and especially in a 10046 extended SQL trace that wait event may reveal critical details.  For instance, it probably required roughly 112.95 seconds for the 10046 trace to be ended, so if we subtract the 112.95 seconds, we are left with 349.86 seconds in the wait event, which would indicate that the average wait time for this client (and in theory network) side wait is 0.064 seconds, which is longer than the only other wait reported, a 0.05 second single wait for a block read from disk.  The long average duration of this wait event either means that the network link is very slow (you would expect average wait times in the range of 0.00001 seconds to 0.002 seconds), or that the client computer receiving the data was excessively busy on average. In this recipe, the book also again states that the TKPROF output will not show any enqueue related waits – that is also not true. (pages 337-340)
• Recipe 10-15 shows how to enable and disable a 10046 extended SQL trace for another session using DBMS_MONITOR.SESSION_TRACE_ENABLE and  DBMS_MONITOR.SESSION_TRACE_DISABLE, respectively.  The command provided for disabling the 10046 trace in the other session, “execute dbms_monitor.session_trace_disable();” does not specify the SID and SERIAL# for the other session, thus it defaults to disabling a 10046 trace in the current session.  This problem can be confirmed by checking the SQL_TRACE column of V$SESSION for the session for which the trace was enabled, for example “SELECT SQL_TRACE FROM V$SESSION WHERE SID=4;”.  The book also states, “The trace file will have the suffix mytrace1, the value you set as the trace file identifier,” but that is only true if tracing were enabled for the current session (and not another session as demonstrated) and if the recipe had included a command to set the session level TRACEFILE_IDENTIFIER parameter. (pages 349-350)
• Recipes 10-2, 10-11, and 10-16 seem to share a common ancestry with a blog article.
• Recipe 10-16 demonstrates how to enable 10046 extended SQL traces in other sessions using Oracle Database 11.1 and above syntax.  All of the examples in this recipe use ALTER SESSION SET EVENTS commands rather than ALTER SYSTEM SET EVENTS commands, and are thus ineffective (do not perform as described) for enabling a trace in another session. (pages 351-352)
• Recipe 10-18 shows how to enabled a system-wide 10046 trace using DBMS_MONITOR.DATABASE_TRACE_ENABLE – if I recall correctly, tracing might not be disabled when requested for the background processes, which means that those processes will continue writing to their trace files until their max trace file size is reached, the drive containing the trace files fills, or the database is bounced.  This may be an Oracle release version specific problem.  The recipe probably should have mentioned this potential problem. (page 353)
• Recipe 10-20 shows how to enable a system-wide 10046 trace using “ALTER SYSTEM SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’;”.  The same potential problem is present in this recipe as for recipe 10-18.  (page 356)
• Recipe 10-21 shows how to enable a 10046 trace using SQL*Plus’ ORADEBUG.  The top of page 357 states, “You can set a trace in a running session using the operating system process ID (PID), with the help of the oradebug utility.”  Abbreviating the operating system process ID as PID is likely to cause unnecessary confusion.  The PID column in V$PROCESS is the Oracle process number, while the SPID column holds the operating system process ID. (pages 356-357)
• Recipe 10-22 shows a method for enabling a 10046 extended SQL trace with wait events and bind variables using a DBMS_MONITOR.SESSION_TRACE_ENABLE call in an AFTER LOGON ON DATABASE trigger.  The book states that the user to be traced, user SH in this case, must have the ALTER SESSION privilege before the trigger is created that will enable a 10046 trace for just that user.  Testing will quickly show that the GRANT is unnecessary because of the default setting of definer’s rights associated with the trigger that must be created by a user with SYSDBA permissions – the user to be traced only needs to be granted CONNECT for the trigger to execute and enable a trace for the session. (pages 357-358)
• Recipe 10-23 shows how to enable and read 10053 traces.  This recipe just barely scratches the surface of 10053 trace file analysis.  The book fails to mention that 10053 traces will only be generated when a hard parse is required, and fails to describe most of the keywords found in a typical 10053 trace file – such trace files may be quite confusing when several tables with multiple indexes are analyzed during optimization; 10053 trace file contents are also subject to change from one Oracle Database release version to the next, so the following quote found on page 360 is at best misleading, “Unlike a raw 10046 event trace file, a 10053 event trace file is quite easy (and interesting) to read.”  (pages 358-361)
• Recipe 10-24 shows how to enable a specific trace type when an error, such as deadlock, occurs in a session.  While the contents of this recipe are potentially helpful, the depth of discussion is very limited compared to some blog articles found on the Internet, and the content really is not focused on performance, but rather on troubleshooting. (pages 361-362)
• Recipe 10-25 shows how to enable tracing for Oracle Database background processes – this recipe is very similar to recipe 10-16 except that this recipe uses ALTER SYSTEM rather than ALTER SESSION and specifies a value for PNAME rather than specifying the operating system process number.  The recipe probably should have provided a warning about potential problems that may result from enabling traces for background processes. (pages 362-363)
• Recipe 10-26 opens with the following problem statement, “You want to trace the Oracle listener for diagnostic purposes.”  While this recipe has nothing specific to do with performance tuning, it would have been helpful to answer the question of “Why?” or “What will I see in the listener trace file?”  Several of the recipes in this book fall into this pattern of not describing the “Why?” or “When?” or “How often?” or “Should I check something else first?” (pages 363-365)
• Recipe 10-27 shows how to set the LOG_ARCHIVE_TRACE parameter – this is another recipe that is not directly related to performance tuning.  The book states on page 366, “You can specify any of 15 levels of archive log tracing,” however the book only shows seven levels, none of which match level 15 that was specified in the recipe.  The authors seem to have used the Oracle Database Reference from the official Oracle Database documentation library for the source of the seven event levels listed, but it appears that a mistake was made when level 128 was written – that level means “Track FAL (fetch archived log) server related activities”, while level 512 is “Track LGWR redo shipping network activity” which the book incorrectly attributes to level 128. (pages 365-366)
• (Skipping chapter 11 for now)
• Page 412 incorrectly states that the Enterprise Edition is required to use stored outlines.
• (Skipping chapter 12 for now)
• Recipe 13-2 on page 450 states “Note that in addition to collecting statistics for all schema objects, the auto stats job also gathers dictionary statistics (for the SYS and SYSTEM schemas).”  This statement is incorrect per Metalink (MOS) – the fixed object statistics are not collected automatically.  However, either of the following two commands will accomplish that task:
  EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’,GATHER_FIXED=>TRUE);
  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);  (page 448-450)
• Recipe 13-3 states “By default, the database automatically collects statistics (cascade=true) for all of a table’s indexes.”  The Oracle Database documentation seems to indicate that the default value is DBMS_STATS.AUTO_CASCADE, which does not necessarily mean that statistics will be collected for all indexes. Page 456 states, “Oracle recommends that you set the AUTOSTATS_TARGET preference to the value ORACLE, to ensure that the database collects fresh dictionary statistics”.  Metalink (MOS) Doc ID 725845.1 states that setting the value to ORACLE prevents the automatic statistics collection process from collecting statistics from any schema that does not belong to an Oracle component (SYS, SYSMAN, WMSYS, EXFSYS, etc.), so that is probably not the recommended value.  Additionally, setting that parameter value does not appear to cause the dictionary statistics to be collected. (pages 451-456)
• Recipe 13-6, when discussing the various levels of dynamic sampling, seems to be closely paralleling (or simply rephrasing) the Oracle Database documentation.  The recipe states about level 3 dynamic sampling, “Uses dynamic sampling if the query meets the Level 2 criteria and it has one or more expressions in a WHERE clause predicate.” – the documentation states that either of these conditions must be true, not both.  The same problem is present for the description of Level 4 dynamic sampling – the book states “AND” while the documentation states “OR”.  Interestingly, the book and the documentation both state that level 9 dynamic sampling samples 4086 blocks, while 4096 (a power of 2) seems to be a more likely number (and that 4096 number agrees with other articles). (pages 459-460)
• Recipe 13-9 states the following about the mreadtim system statistic, “mreadtim: The Multiblock Read Time statistic shows the average time (in seconds) for a sequential multiblock read.”  This is the only system statistic for which the unit of measure is provided – the unit of measure is actually milliseconds.  Note that the output of the workload system statistics on page 466 shows a value of 46,605.947 for the MREADTIM value (if that statistic’s unit of measure really is seconds, that is a very slow server – note that these odd statistic values show a bug that is present in 11.2.0.1 and 11.2.0.2) and 51,471.538 for the SREADTIM statistic.  The recipe should have explained why the MREADTIM value in this case is less than the SREADTIM value.  The recipe also should have mentioned whether the system statistics should be collected during a relatively quiet time of system activity or a relatively busy time. (pages 463-466)
• Recipe 13-10 seems to closely mirror section 13.5.1 Pending Statistics of the Oracle Database Performance Tuning Guide.  Page 468 states, “By default the database immediately starts using all statistics it gathers.” – that statement is not true for Oracle Database 10.1 and above, where the _OPTIMIZER_INVALIDATION_PERIOD parameter is set to 18000 (5 hours), permitting already hard parsed SQL statements to not become immediately invalidated for a period up to 5 hours (thus the new statistics may not have any impact for 5 hours). (pages 466-468)
• Recipe 13-11 provides conflicting advice about using the OPTIMIZER_INDEX_COST_ADJ parameter to force the optimizer to use an index.  While the recipe states that the parameter should not be adjusted system-wide, and also states, “Ideally, you should collect workload system statistics and leave the optimizer_index_cost_adj parameter alone,” in between those comments the recipe states, “Use the optimizer_index_cost_adj parameter with confidence, especially in an OLTP environment, where you can experiment with low values such as 5 or 10 for the parameter to force the optimizer to use an index.” and also suggests that the parameter could be adjusted for a single SQL statement without providing an example of how such a change could be made.  Potential examples of changing that parameter for a single SQL statement might include adding the hint /*+ OPT_PARAM(‘OPTIMIZER_INDEX_COST_ADJ’,5) */, or temporarily adjusting the parameter at the session level so that a stored outline might be created.  It is quite surprising that the book did not describe some of the problems that might be caused by adjusting the OPTIMIZER_INDEX_COST_ADJ parameter.  It is also surprising that the book did not suggest using an index hint, or even suggest determining whether or not an index access path is the most appropriate access path for the data.  The book states, “By default, the optimizer assumes that the cost of a multiblock read I/O associated with a full table scan and the single block read cost associated with an indexed read are identical.” – that has not been the case since the introduction of system statistics in Oracle Database 9.0.1.  Also stated in the recipe is the following, “By default, if you set the ALL_ROWS optimizer goal, there’s a built-in preference for full table scans by the optimizer.” – that also is not necessarily true, in the end the decision is completely based on the calculated cost of the various access paths – if the system statistics and object statistics provide an invalid picture of the performance characteristics of the server and the data, then an inappropriate access path may be selected by the query optimizer.  The recipe states that the SREADTIM, MREADTIM, and MBRC system statistics have a bearing on whether or not a full table scan is performed, but the depth of insight does not venture any deeper than that comment. (pages 468-470)
• Recipe 13-12 states that setting the OPTIMIZER_FEATURES_ENABLE parameter to a previous Oracle Database release version will cause the optimizer to behave exactly as it did in the older release version.  This is true to an extent, however, some bug fixes such as the one for the cardinality estimates when ROWNUM is used in the WHERE clause, are not automatically disabled, and when you combine that with changes to statistics collection (such as the system statistics MREADTIM and SREADTIM bug in 11.2.0.1 and 11.2.0.2), the optimizer could very well not behave the same is it did in the earlier release. (pages 470-471)
• Recipe 13-13 uses exactly the same command (down to the schema, table, and column names) as a blog article written by the Oracle Optimizer Group.  (pages 472-473)
• Recipe 13-14 repeats portions of the CURSOR_SHARING information found in recipe 5-20.  Page 473 states, “There’s no need for hard-parsing the new statement because a parsed version already exists in the shared pool.” – that statement is mostly true prior to the introduction of adaptive cursor sharing in Oracle Database 11.1.  The tip on page 474 states, “Oracle recommends using the FORCE setting for the CURSOR_SHARING parameter, in an OLTP environment.” – it would be helpful if the book indicated where Oracle Corporation made this recommendation.  On page 474, the book states, “There are really no issues with setting the cursor_sharing parameter to a non-default value, except minor drawbacks such as the non-support for star transformation, for example.” – see the review comments for recipe 5-20.  (pages 473-476)
• Recipe 13-15 states, “Adaptive cursor sharing is enabled by default, and you can’t disable it.”  Technically, this is not a true statement.  Although not recommended, setting the STATISTICS_LEVEL parameter to BASIC will disable adaptive cursor sharing.  Per Metalink (MOS) Doc ID 11657468.8, adaptive cursor sharing may be disabled by changing _OPTIMIZER_ADAPTIVE_CURSOR_SHARING to a value of FALSE and _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL to a value of NONE, but it can also be disabled without the modification of the hidden initialization parameters by changing the OPTIMIZER_FEATURES_ENABLED parameter as was discussed in recipe 13-12.  Page 481 states, “The database creates a new child cursor (child_number=2) for this query and marks the original cursor (child_cursor=0) as not being bind-aware. Eventually the database will remove this cursor from the shared pool.”  Oracle actually set the child cursor 0’s IS_SHARABLE column value to N without changing the previously assigned BIND_AWARE value.  The book could have been more clear when it assigned column aliases in the query by not aliasing the IS_SHARABLE column as BIND_SHARE.  On page 481 the book states, “Since adaptive cursor sharing works only where literal values are replaced with binds, Oracle encourages you to use the FORCE setting for the cursor_sharing parameter… You must set the cursor_sharing parameter to FORCE for adaptive cursor sharing to work, thus letting the optimizer select the optimal execution plan for different values of the bind variable.”  Those statements are incorrect, adaptive cursor sharing will work just as well with the CURSOR_SHARING parameter set to the default value of EXACT as long as bind variables are used in the SQL statements. (pages 476-482)
• Recipe 13-16 includes SQL statements that are exact copies of SQL statements found in section 13.3.1.7 from the Oracle Database documentation library, with a bit of rewording before and after the SQL statements. (pages 482-483)
• Recipe 13-17 seems to closely parallel section 13.3.1.6 from the Oracle Database documentation library, except that the column order of the COUNTRY_ID and CUST_STATE_PROVINCE were reversed, and the book apparently considers the COUNTRY_ID column as a VARCHAR2 while the documentation considers that column as a number. (pages 483-484)
• Recipe 13-18 seems to be based on a blog article that was written by the Oracle optimizer group. The book seems to have omitted some SQL*Plus formatting commands that are necessary to correctly view the report that is generated by the code in the book (refer to the blog article for the correct SQL*Plus formatting commands). (pages 484-486)
• Recipe 13-19 seems to be based on a blog article that was written by the Oracle optimizer group (information from a second blog article also seems to have been used). (pages 486-487)
• Recipe 13-20 seems to be based on a blog article that was written by the Oracle optimizer group (pages 488-490)
• The introduction for chapter 14 states that there are more than 60 Oracle Database hints (apparently quoting the Oracle Database Performance Tuning Guide documentation).  A query of V$SQL_HINT in Oracle Database 11.2.0.2 shows 269 hints. (page 491)
• Recipe 14-1 states, “For example, there is an INDEX hint to enable the use of an index. There is also a NO_INDEX hint, which disables the use of an index.”  These two statements should have been rewritten.  An INDEX hint forces the optimizer to use a specific index, assuming that is a valid access path for the data retrieval.  A NO_INDEX hint prevents the optimizer from using a specific index or set of indexes for data retrieval. (pages 491-493)
• Recipe 14-2 states, “Note Hints influence the optimizer, but the optimizer may still choose to ignore any hints specified in the query.”  Hints are directives and cannot be ignored by the optimizer.  The only reasons that a hint cannot be used by the optimizer is if: the hint is invalid due to the wrong alias used in the hint; the hint is malformed; the hint is incompatible with another hint; the query was transformed by the optimizer into a form that is incompatible with the hint before the optimizer applied the hint; the hint, if followed, would cause the wrong results to be returned; bugs in Oracle Database cause the hint to be lost; the hint specifies the use of a feature that is explicitly disabled by an initialization parameter; or a comment is added before the hint that includes an Oracle keyword. (pages 493-496)
• Recipe 14-3 shows how to control the join order using the ORDERED and LEADING hints.  The recipe does not discuss some of the potential problem areas that are present with the ORDERED hint that are not present with the LEADING hint.  Additionally, the execution plan that accompanies the LEADING hint example appears to be the wrong execution plan.  While it appears to work without issue, the correct syntax for the LEADING hint does not use commas to separate the row sources that are listed in the hint. (pages 497-498)
• Recipe 14-4 demonstrates how to use the USE_NL, USE_HASH, and USE_MERGE hints.  While it apparently does not affect the result of the hint, the official syntax does not use commas to separate the row source names in the hints, as shown in the book.  On page 499 the book states, “The smaller of the two tables is used by the optimizer to build a hash table on the join key between the two tables.”  “Smaller” is not defined in this case – does that mean the table itself occupies less space on disk, the table contains fewer rows, fewer rows are expected to be returned from the table, the aggregated estimated size of the columns multiplied by the estimated number of rows is smaller, or something else? (pages 498-501)
• Recipe 14-5 shows how to use the OPTIMIZER_FEATURES_ENABLE hint by specifying a value of 10.2 in that hint.  The value 10.2 is invalid for that hint, and in testing on Oracle Database 11.2.0.2 is sufficient to prevent the optimizer from seeing the hints that are listed after the OPTIMIZER_FEATURES_ENABLE hint. (pages 501-502)
• Recipe 14-6 on page 504 shows setting the OPTIMIZER_MODE using an ALTER SYSTEM command to the deprecated value of FIRST_ROWS as part of a test.  A better approach would have been to use an ALTER SESSION command, setting the OPTIMIZER_MODE parameter to FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, or FIRST_ROWS_1000.  Likewise, the recipe shows the use of the FIRST_ROWS hint without a number enclosed in parentheses (the hint syntax per the Oracle Database documentation since the release of Oracle Database 9.2). (page 502-504)
• Recipe 14-7 probably should mention that the APPEND hint has no effect when the table into which the rows are to be inserted has either triggers or foreign key references.  Ideally, the recipe should have provided a warning stating that direct path inserts are unrecoverable. (pages 505-506)
• Recipe 14-8 states, “Tip: Hints in queries that reference a complex view are ignored.”  This is not completely correct – the book is only partially rewording what is written in the Oracle Database documentation, and selectively removing other information.  The Oracle Database documentation states: “Because nonmergeable views are optimized separately from the top-level query, access path and join hints inside the view are preserved. For the same reason, access path hints on the view in the top-level query are ignored. However, join hints on the view in the top-level query are preserved because, in this case, a nonmergeable view is similar to a table.” (pages 506-509)
• Recipe 14-9 describes using the Oracle Database 11.1 (and higher) result cache, as did recipes 3-9 and 3-11.  The performance improvement shown on page 511 and 512, where the execution time decreased from 21.80 seconds to 0.08 seconds by changing the NO_RESULT_CACHE hint to a RESULT_CACHE hint, could have been caused by the effects of buffering previously read blocks from disk, and not by the use of the RESULT_CACHE hint.  The book simply stated that the query was executed once with each hint – to be fair, the query should have been executed twice with each hint so that the NO_RESULT_CACHE hinted query could have benefitted from the buffering of previously read blocks from disk, and the RESULT_CACHE hinted query could have benefitted from the caching of the query result in the result cache. (pages 509-513)
• Recipe 14-11 shows how to use the GATHER_PLAN_STATISTICS hint.  The A-Rows column in the execution plan seems strange in that only ID 0 shows any rows returned by a line in the execution plan.  It is also strange that the Starts column shows a value of 2 for ID 0 while the execution plan shows 0 starts for all of the remaining lines in the execution plan.  It would have been helpful for the book to explain if the execution statistics were caused by an Oracle Database bug, or some sort of an optimization.  My first thought is that the query results cache was used as that would explain the displayed 0 Starts for all except ID 0 as well as the A-Rows column values, however ID 1 should then show a RESULT CACHE operation.  Page 519 contains a confusing statement, “From this, you can determine if the optimizer is optimally executing the SQL, and you can determine if any optimization is needed.” (pages 517-519)
• Recipe 15-1 describes enabling parallelism for queries.  While the recipe describes using PARALLEL and PARALLEL_INDEX hints, the book implies that simply specifying the hint will cause the query to run in parallel, if at all possible to execute the query in parallel.  The determination of whether or not parallel execution is used is based on several factors, including the calculated query cost reduction and the availability of parallel servers – the use of the hints merely over-rides the default degree of parallelism for the objects, but does not force parallelism.  Page 530 of the book states. “Note As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated.  Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.”  Those hints were actually deprecated as of Oracle Database 10.1.  It would have been helpful for the book to mention that parallel operations require the Enterprise Edition of Oracle.  The book also seems to consider that the CPU is the only potential bottleneck for parallel execution, without a mention of the I/O subsystem. (pages 526-530)
• Recipe 15-4 lists restrictions for parallel DML – it appears that the book paraphrased a section of the Oracle Database VLDB and Partitioning Guide, resulting in vague descriptions of some of the limitations.  A better paraphrase example is found in the “Expert Oracle Database Architecture” book on page 633. (pages 533-536)
• Recipe 15-7 describes rebuilding indexes in parallel.  The recipe provides four key reasons for rebuilding an index, including “index that has become fragmented over time.”  The book does not describe what it means for an index to be fragmented and does not describe some of the potential unanticipated side-effects of parallel index rebuilds (such as causing parallel execution when that index is accessed in a query). (pages 539-541)
• Recipe 15-10 states, “One prerequisite of using automatic DOP is to run the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.”  An example of using CALIBRATE_IO is not provided. (pages 543-545)
• Recipe 15-12 includes table 15-6 that lists several views that are related to parallel execution.  The description of the V$PQ_SESSTAT view seems to disagree with the documentation for that view. (pages 548-550)
• Recipe 15-13 states, “If you query the V$SYSTEM_EVENT view, you can get an idea of the parallelism-related waits that have occurred in your database instance.”  The SQL statement that follows that sentence is a query of V$SESSION_EVENT, not V$SYSTEM_EVENT.  The recipe should have mentioned the need to calculate the delta values from that view before deciding whether or not there is a bottleneck in parallel processes.  Three of the four wait events that the book classifies as indicating a potential parallel tuning issue (“PX Deq: Execute Reply”, “PX Deq: Parse Reply”, and “PX Deq: Table Q Normal”) are classified by the official Oracle Database documentation as idle wait events.  It would have been helpful to more completely describe why these idle wait events should not be ignored, keeping in mind that recipe 5-4 in this book states, “You can ignore the idle waits” – possibly the book should have cited a reference. (pages 550-552)

Interesting And/Or Well-Above-Average Bits of Information

• Specifying the TRACEFILE_IDENTIFIER for a session to reduce the time needed to locate the trace file for a session. (page 332)
• Recipe 15-1 provides warnings to not abuse parallel execution: “Parallel SQL must be used with caution, as it is common to overuse, and can cause an overutilization of system resources, which ultimately results in slower rather than faster performance.”