Book Review: Troubleshooting Oracle Performance, Second Edition

July 15, 2014 (Modified August 21, 2014, August 22, 2014, November 6, 2014, January 9, 2015)

Extensively Researched with Detailed Analysis Covering a Broad Range of Oracle Database Performance Topics, Providing Insight that is Not Available from Other Sources
http://www.amazon.com/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/143025758X/

I pre-ordered this book in December 2013, having previously read the first edition of this book twice.  Once the printed copy of the second edition book arrived, I ordered the PDF companion for the book from Apress.  While the first edition of the book covered Oracle Database 9.2.0.1 through 11.1.0.6, the second edition targets versions 10.2.0.1 through 12.1.0.1+, the author successfully scrubbed the book of all information that is not relevant to the targeted Oracle versions.  Despite the removed obsolete content and the new page formatting that places approximately 17% more content per page, the page count for the second edition of the book grew by roughly 130 pages.

Some of the improvements in the second edition, when compared to the first edition of this book:

• Extended explanation of the different definitions of the term cardinality.  Pg 19
• Second edition of the book added a half page definition of the term cursor.  Pg 21
• The description of V$SQL_CS_HISTOGRAM was omitted from the first edition of this book, and is now included.  Pgs 37-39
• The Instrumentation section that was found in chapter 3 of the first edition is now relocated into chapter 2.  Pgs 42-48
• A new section was added in this edition of the book that is intended to guide the reader in attacking performance problems using different procedures, based on whether or not the problem is reproducible.  Chapters 3 and 4
• A new roadmap flow chart was added to the second edition, showing how to begin the performance tuning process.  Pg 104
• Page 204 of the first edition of the book stated that it was not possible to retrieve a Statspack captured execution plan using DBMS_XPLAN – that statement was incorrect.  Page 306 of the second edition contains a corrected statement:  “Statspack stores execution plans in the stats$sql_plan repository table when a level equal to or greater than 6 is used for taking the snapshots. Even though no specific function is provided by the dbms_xplan package to query that repository table, it’s possible to take advantage of the display function to show the execution plans it contains.”
• The second edition of the book includes a new SQL optimization techniques chapter – the book seems to be making a more dedicated effort to help the reader understand the decision process that determines when to use the various techniques to attack performance issues – explaining the decision tree for performance tuning.  Chapter 11, SQL Optimization Techniques, is a good example of the enhancements made to the second edition.

Over the last several years I have read (and reviewed) a number of Oracle Database performance related books, including the freely available Performance Tuning Guide that is part of the official Oracle Database documentation.  None of the books, including the official Performance Tuning Guide (at least three errors identified in the first 100 pages of the 12.1.0.1 version), is completely free of errors (wrong, omitted, or obsolete information).  However, this book sets the technical content accuracy bar extremely high for books that cover a broad-range of Oracle performance related topics.

As was the case for the first edition of this book, there are several factors that separate this book from the other broad-ranging Oracle Database performance books on the market:

• For every feature that is described to help solve a problem, as many as possible of the benefits are listed, and an equal amount of attention is paid to the potentially wide-ranging problem areas of the various solutions.  Very few potential problems were overlooked in this book.  Some of the other books on the market only describe the potential benefits of implementing a feature, without discussing limitations or unintended side-effects.  One such example is the discussion of the CURSOR_SHARING parameter in two different books.  On page 434 the “Troubleshooting Oracle Performance” book the following warning is provided “Cursor sharing has a reputation for not being very stable. This is because, over the years, plenty of bugs related to it have been found and fixed… my advice is to carefully review Oracle Support note 94036.1…”  This quote is in contrast to the following quotes from pages 191 and 484 of the book “Oracle Database 12c Performance Tuning Recipes”, “Although there are some concerns about the safety of setting the CURSOR_SHARING parameter to FORCE, we haven’t seen any real issues with using this setting.”  “There are really no issues with setting the cursor_sharing parameter to a nondefault value, except minor drawbacks such as the nonsupport for star transformations, for example.”  (Reference) (Reference 2)
• For nearly every feature described in the book, the book lists the licensing and version requirements (sometimes to a specific point release such as 10.2.0.3, 10.2.0.4, 11.2.0.4) that are required so that the reader is able to take advantage of the feature – these requirements are often listed early in the description of the feature (the monitoring/tuning discussion in chapters four and five contain several good examples).  The book commonly describes how to accomplish a task in the current Oracle Database release, as well as older releases, if the approach differs.  Some of the other books on the market inter-mix features and behaviors in various Oracle Database releases, without clearly distinguishing what will and what will not be available in the reader’s environment.
• While many strong statements are made about Oracle Database in the book, there is no “hand waiving”, and there are very few inaccurate statements.  The book uses a “demonstrate and test in your environment” approach from cover to cover.  The downloadable scripts library is extensive with roughly 280 scripts and trace files, and those scripts often contain more performance information than what is presented in the book.  It is thus recommended to view the scripts and experiment with those scripts while the book is read.  The scripts are currently downloadable only from the author’s website.  In contrast, other books seem to take the approach of “trust me, I have performed this task 1,000 times and never had a problem” rather than the “demonstrate and test in your environment” approach as was used in this book.
• Information in this book is densely packaged, without unnecessarily repeating information, and without giving the impression that sections of the book are a paraphrase of some other set of articles, a paraphrase of chapters in the official Oracle documentation, or a reprint of a page that was originally copyright in 1997.  Additionally, the information is well organized into a logical progression of topics, rather than each section of the book appearing as an island of unrelated information.
• The well-placed graphics throughout the book support the contents of the book, rather than distract from the information that is described.
• The book makes extensive use of forward and backward references to other sections in the book, as well as suggestions to review specific Oracle support documents and other books.  Some of the other books handle each chapter as an information silo, never (or rarely) mentioning specific content found elsewhere in the book.
• In the acknowledgments section at the beginning of the previous book edition the author mentioned that his English writing ability is poor and that “I should really try to improve my English skills someday.”  While the English wording in the first edition of the book was easily understood, I took issue with the author’s repeated use of the phrase “up to” when describing features that exist in one Oracle Database release version or another.  The second edition of the book fixes that one issue that I pointed out, typically replacing the text with “up to and including”, and overall the technical grammar in the second edition of the book is among the best that I have seen in a couple years.  It appears that the author exercised great care when presenting his information on each page.  In contrast, some of the other Oracle Database book authors seem to be more concerned with slamming something onto the page so that something else that is more interesting could be introduced, in the process introducing sentences that can best be described as non-sense.
• Almost without exception the issues that were identified as wrong, misleading, or incomplete in the first edition of the book were corrected in the second edition.  Unfortunately, the same cannot be said about other books that survived to see a second or third edition.

The second edition of “Troubleshooting Oracle Performance” is of value to Oracle Database administrators, programmers, and Oracle performance tuning specialists.  Chapter one of this book should be required reading for all people intending to be developers, regardless if the person intends to build advanced Oracle Database solutions or just simple Microsoft Access solutions.  One of my favor quotes from the book is found on page three, “Performance is not merely optional, though; it is a key property of an application.”  Ideally, this book should be read after reading the “Expert Oracle Database Architecture” book (or the Concepts Guide found in the Oracle Database documentation library), and before advancing to books such as “Cost-Based Oracle Fundamentals” or “Oracle Core: Essential Internals for DBAs and Developers”.

The full review of this book is quite long, currently covering the first 12 chapters (447 pages) of the book.  Over the next couple of weeks I will finish the remaining book chapters and post an updated review.  The index at the back of most Apress books seems to be limited in value, so I have tried to include a useful index as part of this review.

—

Foundation Knowledge, and Miscellaneous Tips:

• The ten most common design problems: no formal logical database design; using generic tables (entity-attribute-value or XML); failing to use constraints, failing to implement physical design (partitioning, bitmap indexes, index organized tables, function-based indexes, etc); selecting the wrong data type for table columns (using a VARCHAR2 column to store dates); incorrect bind variable usage; failure to use RDBMS specific advanced features; avoiding PL/SQL when extensive data manipulation is required within a single database; excessive commits; non-persistent database connections.  Pgs 8-11
• To avoid compulsive tuning disorder, there are three sources for identifying actual performance problems: user reported unsatisfactory performance, system monitoring reports time outs or unusual load, response time monitoring indicates performance that is outside of the parameters specified by the service level agreement.  Pg 11
• Cardinality is the number of rows returned by an operation (estimated number of rows in an execution plan).  Cardinality = selectivity *num_rows  Pg 19
• “A cursor is a handle to a private SQL area with an associated shared SQL area.”  Pg 21
• Life cycle of a cursor is explained with a diagram.  Pgs 21-23
• Good explanation of why hard parses and even soft parses should be minimized as much as possible.  Pg 26
• Even though the OPTIMIZER_ENV_HASH_VALUE column value in V$SQL is different for a given SQL statement when the FIRST_ROWS, FIRST_ROWS_1, or FIRST_ROWS_1000 optimizer modes are used, that difference in the OPTIMIZER_ENV_HASH_VALUE column does not prevent a specific child cursor from being shared among sessions with those different optimizer modes.  “This fact leads to the potential problem that even though the execution environment is different, the SQL engine doesn’t distinguish that difference. As a result, a child cursor might be incorrectly shared.”  Pg 27 (Reference)
• Example of using Oracle’s built-in XML processing to convert the REASON column found in V$SQL_SHARED_CURSOR into three separate regular Oracle columns.  Pgs 27-28
• Benefits and disadvantages of using bind variables.  Pgs 29-31, 32-39
• Adaptive cursor sharing (bind-aware cursor sharing) was introduced in Oracle 11.1.  The IS_BIND_SENSITIVE, IS_BIND_AWARE, and IS_SHAREABLE columns of V$SQL indicate if a specific child cursor was affected (created or made obsolete) by adaptive cursor sharing.  Pg 34
• Bind aware cursors require the query optimizer to perform an estimation of the selectivity of predicates on each execution.   Pg 37
• Definition of different types of database file reads and writes.  Pg 40
• Basic definition of Exadata and the goals of Exadata smart scans.  Pg 41
• The database engine allows dynamically setting the following attributes for a session: client identifier, client information, module name, and action name.  Pg 45
• Example of setting the client identifier information using PL/SQL, OCI, JDBC, ODP.NET, and PHP.  Pgs 46-48
• 10046 trace levels 0, 1, 4, 8, 16, 32, and 64 are described.  Pg 55
• See $ORACLE_HOME/rdbms/mesg/oraus.msg for a list of all debugging event numbers – not available on all operating system platforms.  Pg 56
• Using DBMS_PROFILER  requires the CREATE privilege on the PL/SQL code.  DBMS_HPROF just requires execute on DBMS_HPROF.  Pg 96
• Very good description of the performance related columns in V$SESSION.  Pgs 115-116
• The MMNL backgroup process collects active session history data once a second.  Pg 117
• Real-time monitoring is available starting in version 11.1, and requires that the CONTROL_MANAGEMENT_PACK_ACCESS parameter to be set to diagnostic + tuning.  Pg 127
• Real-time monitoring is enabled for SQL statements only if the executions require at least 5 seconds, if the SQL statement is executed using parallel processing, or if the MONITOR hint is specified in the SQL statement.  Pg 127
• The author’s system_activity.sql script file produces output that is similar to the data contained in a Diagnostic Pack chart, without requiring a Diagnostic Pack license.  Pg 143
• The author’s time_model.sql script samples the V$SYS_TIME_MODEL dynamic performance view and outputs results that show the parent, child, and grandchild relationship between the various statistics.  Pg 144
• Use an interval of 20 to 30 minutes for the Statspack or AWR sample period to limit the distortion effects of the reported averages (important problems may be hidden if the sample period covers many hours of time).  Pg 152
• AWR dictionary views have a DBA_HIST or CDB_HIST (12.1 multitenant environment) prefix.  Pg 152
• While the procedure for using Statspack is no longer described in the documentation, the spdoc.txt file in the $ORACLE_HOME/rdbms/admin directory describes how to install, configure, and manage Statspack.  Pg 156
• Statspack data can be captured at levels 0, 5, 6, 7, or 10 (see the book for an explanation of what is captured at each level).  Pg 157
• The book provides an example of automating the collection of Statspack snaps, and automatically purging old Statspack snaps after 35 days.  Pg 159
• The book describes in detail the various inputs that are provided to the query optimizer, including: system statistics, object statistics, constraints, physical design, stored outlines/SQL profiles/SQL plan baselines, execution environment/initialization parameters/client side environment variables, bind variable values/data types, dynamic sampling, and cardinality feedback.  The Oracle Database version, edition (Standard or Enterprise), and installed patches also potentially affect the plans generated by the query optimizer.  Pgs 170-172
• Prior to version 11.1 the ANSI full outer join syntax was automatically translated into Oracle syntax utilizing a UNION ALL.  Pg 187
• Access to the DBMS_STATS package is granted to public, but the GATHER_SYSTEM_STATISTICS role (automatically granted to DBA role) is required to change the system statistics in the data dictionary.  Pg 192
• Bug 9842771 causes the SREADTIM and MREADTIM statistics to be incorrectly calculated when gathering system statistics on Oracle Database 11.2.0.1 and 11.2.0.2 unless patch 9842771 is installed.  Pg 197
• The calculated CPU cost to access a specific table column is computed as the column position multiplied by 20.  Pg 203 (Reference)
• When the mreadtim system statistic is null (has not been computed) or is smaller than the sreadtim system statistic, a formula is used to calculate the mreadtim static value when execution plans are generated.  When the sreadtim system statistic is 0 or not computed, a formula is used to derive a sreadtim statistic value when execution plans are generated.  If the MBRC system statistic is not set (or set to 0), the NOWORKLOAD system statistics are used.  See page 204 for the formulas.
• The maximum number of buckets for histograms increased from 254 to 2048 in Oracle Database 12.1.  pg 213
• Script to show tracked column usage that is used by DBMS_STATS.  Note that USER should be replaced with the schema name that contains the specified table.  Pg 242
• When object statistics are collected using the default NO_INVALIDATE parameter value of DBMS_STATS.AUTO_INVALIDATE, cursors that depend on the object for which statistics were collected will be marked as invalidated after a random time period that is up to five hours (as determined by the value of the _OPTIMIZER_INVALIDATION_PERIOD  parameter; SQL statements using parallel execution will be immediately invalidated).  Pg 244
• “Unfortunately, not all new features are disabled by this [OPTIMIZER_FEATURES_ENABLE] initialization parameter. For example, if you set it to 10.2.0.4 in version 11.2, you won’t get exactly the 10.2.0.4 query optimizer.”  Pg 277
• “When the [memory utilization specified by the PGA_AGGREGATE_LIMIT] limit is reached, the database engine terminates calls or even kills sessions. To choose the session to deal with, the database engine doesn’t consider the maximum PGA utilization [for each session]. Instead, the database engine considers the session using the highest amount of untunable memory.”  Pg 296
• EXPLAIN PLAN defines all bind variables as VARCHAR2, which may lead to unintended/unexpected data type conversion problems in the generated execution plan.  EXPLAIN PLAN also does not take advantage of bind variable peeking, further limiting EXPLAIN PLAN’s ability to accurately generate an execution plan for a previously executed SQL statement.  Unfortunately, there are times when EXPLAIN PLAN shows the correct predicate information, while the typically more reliable DBMS_XPLAN.DISPLAY_CURSOR, V$SQL_PLAN view, and V$SQL_PLAN_STATISTICS_ALL  view show incorrect predicate information for one or more lines in the execution plan.  Pgs 302-303, 336, 339, 346, 348
• To have the query optimizer generate a 10053 trace whenever a specific SQL statement is hard parsed, execute the following command, replacing 9s5u1k3vshsw4 with the correct SQL_ID value: ALTER SYSTEM SET events ‘trace[rdbms.SQL_Optimizer.*][sql:9s5u1k3vshsw4]’  pg 308
• Description of the columns found in most execution plans.  Pgs 312-313
• Description of the undocumented ADVANCED format parameter value for DBMS_XPLAN.  Pg 316
• Adaptive execution plans, where the query optimizer in Oracle Database 12.1 is able to postpone some execution plan decisions (such as selecting a nested loops join vs. a hash join), requires the Enterprise Edition of Oracle Database.  Pg 349
• The IS_RESOLVED_ADAPTIVE_PLAN column of V$SQL indicates whether or not an execution plan takes advantage of adaptive execution (use +ADAPTIVE in the format parameter of the DBMS_XPLAN call to see the adaptive portion of the execution plan).  Pg 351
• Rather than just suggesting to the reader to add an index to avoid an unnecessary full table scan, the book includes the following important note: “For instance, if you add an index like in the previous example, you have to consider that the index will slow down the execution of every INSERT and DELETE statement on the indexed table as well as every UPDATE statement that modifies the indexed columns.”  Pg 361
• “Simply put, hints are directives added to SQL statements to influence the query optimizer’s decisions. In other words, a hint is something that impels toward an action, rather than merely suggests one.”  Pg 363
• “However, mixing comments and hints don’t always work. For example, a comment added before a hint invalidates it.”  This warning is an actual threat to intentionally included hints, and this warning was not included in the first edition of the book.  Pg 366
• The default query block names assigned by the optimizer are: CRI$ CREATE INDEX statements, DEL$ DELETE statements, INS$ INSERT statements, MISC$ Miscellaneous SQL statements like LOCK TABLE, MRC$ MERGE statements, SEL$ SELECT statements, SET$ Set operators like UNION and MINUS, UPD$ UPDATE statements.  Use the QB_NAME hint to specify a different, non-default query block name for use with various hints.  Pg 369
• “One of the most common mistakes made in the utilization of hints is related to table aliases. The rule is that when a table is referenced in a hint, the alias should be used instead of the table name, whenever the table has an alias.”  Pg 371
• Cross reference between several initialization parameter values and the equivalent hint syntax.  Pg 373
• A demonstration of creating a hacked stored outline for a SQL statement (use as a last resort when it is not possible to create a suitable outline using other techniques such as exp/imp or initialization parameter changes).  Pgs 381-387
• SQL profiles, a feature of the Enterprise Edition with the Tuning Pack and the Diagnostic Pack options, are applied even when the upper/lowercase letters and/or the white space differs, and if the FORCE_MATCH parameter is set to true, a SQL profile may be applied even if the literals (constants) in a SQL statement differ.   While SQL profiles allow text normalization, stored outlines and SQL plan management do not support the same degree of text normalization.  Pgs 390, 394, 402
• SQL plan management, which requires an Enterprise Edition license, could be considered an enhanced version of stored outlines.  Pg 402
• “Inappropriate hints occur frequently in practice as the reason for inefficient execution plans. Being able to override them with the technique you’ve seen in this section [SQL plan baseline execution plan replacement (stored outlines are also capable of removing embedded hints using the techniques shown on pages 381-387)] is extremely useful.”  Pg 408
• “What causes long parse times? Commonly, they are caused by the query optimizer evaluating too many different execution plans. In addition, it can happen because of recursive queries executed on behalf of dynamic sampling.”  Pg 433
• “The values provided by the parse count (total) and session cursor cache hits statistics are subject to several bugs.”  Details are provided on pages 437-438

—

Suggestions, Problems, and Errors:

• The following scripts are currently missing from the script library:
  — session_info.sql  Pg 45 (in the script library as session_attributes.sql per the book author).
  — ash_top_files.sql  Pg 136  (Edit: Jan 9, 2015, now downloadable here)
  — ash_top_objects.sql  Pg 136  (Edit: Jan 9, 2015, now downloadable here)
  — ash_top_plsql.sql  Pg 136  (Edit: Jan 9, 2015, now downloadable here)
  — search_space.sql  Pg 169  (Edit: Nov 6, 2014, now downloadable here)
  — incremental_stats.sql  Pg 255  (Edit: Aug 22, 2014, now downloadable here)
  — copy_table_stats.sql  Pg 256  (Edit: Aug 22, 2014, now downloadable here)
  — optimizer_index_cost_adj.sql  Pg 288  (Edit: Aug 22, 2014, now downloadable here)
  — display_statspack.sql  Pg 306  (Edit: Nov 6, 2014, now downloadable here)
  — dynamic_in_conditions.sql  Pg 499  (Edit: Nov 6, 2014, now downloadable here)
  — fbi_cs.sql  Pg 506  (Edit: Nov 6, 2014, now downloadable here)
  — reserve_index.sql should be reverse_index.sql  Pg 671 (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• Page 19 states that “selectivity is a value between 0 and 1 representing the fraction of rows filtered by an operation.”  I understand the intention of this statement, and the examples that follow the statement further clarify the author’s statement.  However, the “filtered” word in the statement seems to suggest that selectivity represents the fraction of the rows removed by an operation, rather than the rows that survived the filter at an operation.  This is just a minor wording problem that might cause the reader a little confusion when reading the book.  The author has addressed this issue in his errata list for the book.  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• Page 24, figure 2-3 has two entries for “Store parent cursor in library cache” – the second entry should show “Store child cursor in the library cache”, just as it is shown in figure 2-2 of the first edition of the book.  The author has addressed this issue in his errata list for the book.  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• Page 62 states, “The ALTER SESSION privilege required to execute the previous trigger can’t be granted through a role. Instead, it has to be granted directly to the user executing the trigger.”  I believe that the session executing the AFTER LOGON trigger, by default, would not need the ALTER SESSION privilege if the user creating the AFTER LOGON trigger had the ALTER SESSION privilege because the trigger is created by default with Definer’s Rights (Reference)  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• Page 72 states, “disk is the number of blocks read with physical reads. Be careful—this isn’t the number of physical I/O operations. If this value is larger than the number of logical reads (disk > query + current), it means that blocks spilled into the temporary tablespace.”  While the statement is correct, and supported by the test case output, it might be a good idea to also mention that prefetching (index or table) or buffer warm up could be another possible cause of the DISK statistic value exceeding the value of the QUERY statistic value (especially after the database is bounced or the buffer cache is flushed).  The PHYSICAL READS CACHE PREFETCH and PHYSICAL READS PREFETCH WARMUP statistics might be useful for monitoring this type of access.  (Reference)
• Page 73 states, “In addition to information about the first execution, version 11.2.0.2 and higher also provides the average and maximum number of rows returned over all executions. The number of executions itself is provided by the Number of plan statistics captured value.”  It appears that the word “executions” should have been “execution plans”.  (Reference)  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• The book made the transition from views that require no additional cost licensing to views that require a Diagnostic Pack license on pages 114 and 115, without providing the reader a warning about the licensing requirements (such a warning is typically present in the book).  (Edit: August 21, 2014: I have discussed this bullet item with the book author.  Pages 114 and 115 describe the use of V$METRIC, V$METRICGROUP, and V$METRICNAME.  The author provided this link, which clearly describes which features, views, and scripts require a Diagnostic Pack license to access.  V$METRIC, V$METRICGROUP, and V$METRICNAME are not among the views that are listed, and it is possible to access data from V$METRIC when the CONTROL_MANAGEMENT_PACK_ACCESS parameter is set to NONE.  Some of the Oracle documentation (link) (link) (Oracle Support Doc ID 748642.1) indicate that “V$METRIC displays the most recent statistic values for the complete set of metrics captured by the AWR infrastructure.” and/or that V$METRIC is one of the Automatic Workload Repository views.  The book did not transition to views that require a Diagnostic Pack license on pages 114 and 115.)
• There are a couple of minor typos in the book that do not affect the accuracy of statements made by the book.  For example, “… prevent it from happenning again” on page 149.  Most of these typos are easily missed when reading the book.  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• The book states on page 215, “This is especially true for multibyte character sets where each character might take up to three bytes.”  Per the Oracle Database globalization documentation, some charactersets, such as UTF-8 (AL32UTF8) may require up to four bytes per character.  The author has addressed this issue in his errata list for the book.
• The book states on page 221, “For this reason, as of version 12.1, top frequency histograms and hybrid histograms replace height-balanced histograms.”  It appears based on the Oracle documentation that height-balanced histograms are not replaced if the histograms are created before the upgrade.  Additionally, if the ESTIMATE_PERCENT parameter is specified in the DBMS_STATS call, a height-balanced histogram will be created if the number of distinct values exceeds the number of buckets.  (Reference).  Page 239 makes a clarifying statement, “Also note that some features (top frequency histograms, hybrid histograms, and incremental statistics) only work when dbms_stats.auto_sample_size is specified [for the ESTIMATE_PERCENT parameter].”  “Work” may be a poor wording choice, “generated” may be a better choice of wording.  (Reference Oracle 12c Histogram Test)  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• The book states on page 282 about dynamic sampling level 11: “The query optimizer decides when and how to use dynamic sampling.  This level is available as of version 12.1 only.”  Oracle Database 11.2.0.4 also adds support for dynamic sampling level 11. (Reference)  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• When describing the output of DBMS_XPLAN, the book states, “Reads: The number of physical reads performed during the execution.”  The book should have clarified that the unit of measure for the Buffers, Reads, and Writes statistics is blocks.  Pg 313  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• The book states, “Syntactical errors in hints don’t raise errors. If the parser doesn’t manage to parse them, they’re simply considered real comments.”  That statement is correct for all hints except the oddly behaving IGNORE_ROW_ON_DUPKEY_INDEX hint, which will raise an “ORA-38917: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation” error, the CHANGE_DUPKEY_ERROR_INDEX hint which will raise an “ORA-38916: CHANGE_DUPKEY_ERROR_INDEX hint disallowed for this operation” error, and the RETRY_ON_ROW_CHANGE hint which will raise an “ORA-38918: RETRY_ON_ROW_CHANGE hint disallowed for this operation” error  if the hints are specified incorrectly.  Pg 365 (a similar comment is made at the top of page 371). (Reference) (Reference 2)  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• The book states, “The aim of using a prepared statement is to share a single cursor for all SQL statements and, consequently, to avoid unnecessary hard parses by turning them into soft parses.”  This statement should be clarified to point out that the aim is to share a single cursor for all _similar_ SQL statements (those that would have differed only by a literal/constant if bind variables were not used).  Pg 428  (Edit: Aug 22, 2014, author now lists this item in the book’s errata page)
• The book states, “Cursor sharing doesn’t replace literal values contained in static SQL statements executed through PL/SQL. For dynamic SQL statements, the replacement takes place only when literals aren’t mixed with bind variables. This isn’t a bug; it’s a design decision.”  This statement about dynamic SQL statements, at least for Oracle Database 11.2.0.2 and 12.1.0.1 (and possibly 10.2.0.2) is no longer true.  The author’s cursor_sharing_mix.sql script does shows literal value replacement when bind variables are also used for SQL statements executed outside PL/SQL.  Pg 434  (Reference Oracle Cursor Sharing Test).  (Edit: August 21, 2014: I have discussed this bullet item with the book author.  The quote from page 434 is describing the behavior of cursor sharing only as it relates to SQL statements executed within PL/SQL code.  I misinterpreted “dynamic SQL statements” to include ad-hoc SQL statements that are submitted by client-side applications (including SQL*Plus) that are built on-demand as a result of user input.  In technical terms, Dynamic implicitly implies that the SQL statements are executed within PL/SQL code.  The test script results do match what is printed in the book.  The author’s script demonstrated (specifically about the CURSOR_SHARING parameter, but likely applies to other functionality as well) that SQL executed directly may be handled differently when executed within PL/SQL code.)

—

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

• ALL_TAB_MODIFICATIONS  Pg 237
• AUX_STATS$  (SYS schema)  Pgs 193, 196
• CDB_ENABLED_TRACES  Pgs 58, 59, 60
• CDB_HIST_SQL_PLAN  Pg 305
• CDB_OPTSTAT_OPERATIONS  Pg 269
• CDB_SQL_PLAN_BASELINES  Pg 409
• CDB_SQL_PROFILES  Pgs 393, 399
• CDB_TAB_MODIFICATIONS  Pg 237
• COL$ (SYS schema)  Pg 242
• COL_USAGE$ (SYS schema)  Pg 242
• DBA_ADVISOR_EXECUTIONS  Pg 413
• DBA_ADVISOR_PARAMETERS  Pg 413
• DBA_AUTOTASK_TASK  Pg 259
• DBA_AUTOTASK_WINDOW_CLIENTS  Pg 260
• DBA_ENABLED_TRACES  Pgs 58, 59, 60
• DBA_HIST_ACTIVE_SESS_HISTORY  Pg 420
• DBA_HIST_BASELINE  Pgs 155, 156
• DBA_HIST_COLORED_SQL  Pg 153
• DBA_HIST_SNAPSHOT  Pg 154
• DBA_HIST_SQL_PLAN  Pgs 305, 324
• DBA_HIST_SQLTEXT  Pg 324
• DBA_HIST_WR_CONTROL  Pg 153
• DBA_OPTSTAT_OPERATION_TASKS  Pg 200
• DBA_OPTSTAT_OPERATIONS  Pgs 200, 201, 269, 270
• DBA_SCHEDULER_JOBS  Pgs 257-258
• DBA_SCHEDULER_PROGRAMS  Pgs 258, 259
• DBA_SCHEDULER_WINDOWS  Pgs 258, 260
• DBA_SCHEDULER_WINGROUP_MEMBERS  Pg 258
• DBA_SQL_MANAGEMENT_CONFIG  Pgs 417, 418
• DBA_SQL_PLAN_BASELINES  Pgs 408, 409
• DBA_SQL_PLAN_DIR_OBJECTS  Pg 230
• DBA_SQL_PLAN_DIRECTIVES  Pg 230
• DBA_SQL_PROFILES  Pgs 393, 399
• DBA_TAB_MODIFICATIONS  Pg 237
• DBA_TAB_STAT_PREFS  Pg 248
• DBA_TAB_STATS_HISTORY  Pg 261
• DBA_USERS  Pgs 235, 242
• GV$INSTANCE  Pgs 60, 63
• OBJ$ (SYS schema)  Pg 242
• OL$ (OUTLN schema)  Pgs 381, 383
• OL$HINTS (OUTLN schema)  Pgs 381, 383-384
• OL$NODES (OUTLN schema)  Pg 381
• OPTSTAT_HIST_CONTROL$ (SYS schema)  Pgs 246, 248
• PLAN_TABLE  Pgs 203, 215, 216, 219-223, 225, 300
• REGISTRY$ (SYS schema)  Pg 235
• REGISTRY$SCHEMAS (SYS schema)  Pg 235
• SQLOBJ$ (SYS schema)  Pgs 399, 410
• SQLOBJ$DATA (SYS schema)  Pgs 399, 410
• SQLPROF$ (SYS schema)  Pg 399
• SQLPROF$ATTR (SYS schema)  Pg 399
• STATS$LEVEL_DESCRIPTION  Pg 157
• STATS$SQL_PLAN  Pg 306
• STATS$STATSPACK_PARAMETER  Pgs 157, 158
• USER_COL_PENDING_STATS  Pg 251
• USER_IND_PENDING_STATS  Pg 251
• USER_IND_STATISTICS  Pgs 209, 231-232, 251
• USER_INDEXES  Pgs 354, 486, 487, 488, 519, 520, 523
• USER_OBJECTS  Pg 300
• USER_OUTLINE_HINTS pg 377
• USER_OUTLINES  Pgs 377, 378, 379, 380
• USER_PART_COL_STATISTICS  Pg 209
• USER_PART_HISTOGRAMS  Pg 209
• USER_SCHEDULER_JOBS  Pg 197
• USER_STAT_EXTENSIONS  Pg 226
• USER_SUBPART_COL_STATISTICS  Pgs 209, 253
• USER_SUBPART_HISTOGRAMS  Pg 209
• USER_TAB_COL_STATISTICS  Pgs 209, 211-213, 251, 252, 355
• USER_TAB_COLS  Pg 227
• USER_TAB_HISTGRM_PENDING_STATS  Pg 251
• USER_TAB_HISTOGRAMS  Pgs 209, 214-215, 218-220, 222, 224-225, 251
• USER_TAB_MODIFICATIONS  Pg 237
• USER_TAB_PENDING_STATS  Pg 251
• USER_TAB_STATISTICS  Pgs 209, 210-211, 215, 249, 251, 255, 264
• USER_TAB_STATS_HISTORY  Pg 261
• USER_TABLES  Pg 240
• V$ACTIVE_SESSION_HISTORY  Pgs 119-121, 132, 138, 420, 424
• V$BGPROCESS  Pg 63
• V$CON_SYS_TIME_MODEL  Pgs 108, 419
• V$CON_SYSSTAT  Pg 113
• V$CON_SYSTEM_EVENT  Pg 111
• V$CON_SYSTEM_WAIT_CLASS  Pg 110
• V$DATABASE  Pg 324
• V$DIAG_INFO  Pgs 63, 64
• V$DISPATCHER  Pg 63
• V$EVENT_HISTOGRAM  Pg 112
• V$EVENT_NAME  Pg 109, 163
• V$FILESTAT  Pg 195
• V$FIXED_TABLE  Pg 233
• V$METRIC  Pg 115, 142
• V$METRIC_HISTORY  Pgs 115, 131
• V$METRICGROUP  Pg 114
• V$METRICNAME  Pg 114
• V$MYSTAT  Pgs 114, 280
• V$OSSTAT  Pgs 106, 131, 161
• V$PARAMETER  Pgs 62, 296
• V$PARAMETER_VALID_VALUES  Pg 278
• V$PGASTAT  Pg 294-295
• V$PROCESS  Pgs 64, 65
• V$PX_PROCESS  Pg 63
• V$SES_OPTIMIZER_ENV  Pg 374
• V$SESS_TIME_MODEL  Pgs 107, 108, 110, 111, 144, 145, 419
• V$SESSION  Pgs  45, 57, 61, 63, 64, 115-116, 117, 119, 145, 146, 304, 322, 390
• V$SESSION_BLOCKERS  Pg 116
• V$SESSION_EVENT  Pgs 111, 145
• V$SESSION_WAIT  Pg 116
• V$SESSION_WAIT_CLASS  Pg 110
• V$SESSTAT  Pgs 113, 114, 145, 430, 437
• V$SGASTAT  Pg 118
• V$SHARED_SERVER  Pg 63
• V$SQL  Pgs 25, 26, 126, 147, 304, 322, 378, 380, 386, 409
• V$SQL_BIND_METADATA  Pg 31
• V$SQL_CS_HISTOGRAM  Pgs 36, 37
• V$SQL_CS_SELECTIVITY  Pgs 36, 37
• V$SQL_CS_STATISTICS  Pg 36
• V$SQL_HINT  Pgs 367, 376, 403
• V$SQL_MONITOR  Pg 127
• V$SQL_OPTIMIZER_ENV  Pg 374
• V$SQL_PLAN  Pgs 303, 304, 305, 322, 336, 339,346, 348, 351
• V$SQL_PLAN_STATISTICS  Pg 303
• V$SQL_PLAN_STATISTICS_ALL  Pgs 303, 304, 320, 321, 322, 336, 339, 346, 348
• V$SQL_SHARED_CURSOR  Pgs 27, 29, 31
• V$SQL_WORKAREA  Pg 303
• V$SQLAREA  Pgs 25,26, 126, 147, 277, 424
• V$SQLSTATS  Pgs 126, 147, 424
• V$STATNAME  Pgs 113, 280, 430, 437
• V$SYS_OPTIMIZER_ENV  Pg 374
• V$SYS_TIME_MODEL  Pgs 108, 144, 419
• V$SYSAUX_OCCUPANTS  Pg 153
• V$SYSMETRIC  Pg 115
• V$SYSMETRIC_HISTORY  Pg 115
• V$SYSSTAT  Pg 113
• V$SYSTEM_EVENT  Pgs 111, 112
• V$SYSTEM_WAIT_CLASS  Pgs 110, 143
• WRI$_OPTSTAT_AUX_HISTORY  Pg 199
• X$KSUSE  Pg 257

Initialization Parameters:

• _CONVERT_SET_TO_JOIN  Pg 189
• _OPTIMIZER_IGNORE_HINTS  Pg 372
• _SQLMON_MAX_PLAN pg 127
• _SQLMON_MAX_PLANLINES  Pg 127
• _TRACE_FILES_PUBLIC  Pg 64
• BACKGROUND_DUMP_DEST  Pgs 63, 259
• BITMAP_MERGE_AREA_SIZE  Pgs 292, 297
• CONTROL_MANAGEMENT_PACK_ACCESS  Pgs 103, 117, 127, 402
• CPU_COUNT  Pg 131
• CREATE_STORED_OUTLINES  Pg 377
• CURRENT_SCHEMA  Pg 320
• CURSOR_SHARING  Pgs 373, 434-436
• DB_BLOCK_SIZE  Pgs 278, 281
• DB_CACHE_SIZE  Pg 281
• DB_FILE_MULTIBLOCK_READ_COUNT  Pgs 195, 198, 274, 278-281
• DB_NAME  Pg 63
• DIAGNOSTIC_DEST  Pg 63
• HASH_AREA_SIZE  Pgs 292, 297
• INSTANCE_NAME  Pg 63
• JOB_QUEUE_PROCESSES  Pg 243, 261
• MAX_DUMP_FILE_SIZE  Pgs 61, 62
• MEMORY_MAX_TARGET  Pg 292
• MEMORY_TARGET  Pg 292
• NLS_SORT  Pg 28
• OPEN_CURSORS  Pgs 432, 433, 437
• OPTIMIZER_ADAPTIVE_FEATURES  Pgs 230, 351
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY  Pg 351
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES  Pgs 405, 407, 417
• OPTIMIZER_DYNAMIC_SAMPLING  Pgs 281-286, 373
• OPTIMIZER_FEATURES_ENABLE  Pgs 202, 213, 277-278, 282, 373
• OPTIMIZER_INDEX_CACHING  Pgs 191, 274, 289, 373
• OPTIMIZER_INDEX_COST_ADJ  Pgs 191, 274, 286-289, 373
• OPTIMIZER_MODE  Pgs 26, 274, 276-277, 373
• OPTIMIZER_SECURE_VIEW_MERGING  Pgs 176, 289-291, 373
• OPTIMIZER_USE_PENDING_STATISTICS  Pgs 250, 373
• OPTIMIZER_USE_SQL_PLAN_BASELINES  Pg 415
• PGA_AGGREGATE_LIMIT  Pgs 274, 293
• PGA_AGGREGATE_TARGET  Pgs 274, 292, 295
• PROCESSES  Pg 295
• RESULT_CACHE_MODE  Pg 373
• SERVICE_NAMES  Pg 59
• SESSION_CACHED_CURSORS  Pgs 436, 437, 440
• SESSIONS  Pg 281
• SORT_AREA_RETAINED_SIZE  Pgs 292, 296-297
• SORT_AREA_SIZE  Pgs 292, 296
• SQLTUNE_CATEGORY  Pgs 395, 402
• STAR_TRANSFORMATION_ENABLED  Pg 373
• STATISTICS_LEVEL  Pgs 62, 106, 152, 199, 238, 261, 303, 322
• TIMED_STATISTICS  Pgs 61, 62, 74, 105-106
• TRACEFILE_IDENTIFIER  Pg 65
• USE_PRIVATE_OUTLINES  Pg 384
• USE_STORED_OUTLINES  Pgs 379, 385
• USER_DUMP_DEST  Pg 63
• WORKAREA_SIZE_POLICY  Pgs 274, 292

SQL Hints:

• ALL_ROWS  Pgs 277, 373, 399
• APPEND  Pg 367
• APPEND_VALUES  Pg 367
• BIND_AWARE  Pgs 38, 367
• CACHE  Pg 367
• CHANGE_DUPKEY_ERROR_INDEX  Pg 367
• CLUSTER  Pg 366
• COLUMN_STATS  Pg 401
• CURSOR_SHARING_EXACT  Pgs 366, 373, 434
• DRIVING_SITE  Pg 367
• DYNAMIC_SAMPLING  Pgs 283, 366, 367, 373
• DYNAMIC_SAMPLING_EST_CDN  Pg 283
• ELIMINATE_JOIN  Pg 366
• EXPAND_TABLE  Pg 366
• FACT  Pg 366
• FIRST_ROWS  Pg 373
• FIRST_ROWS(n)  Pgs 277, 366, 373
• FULL  Pgs 365, 366, 367, 368, 369, 371, 383, 406, 407, 408
• GATHER_OPTIMIZER_STATISTICS  Pg 367
• GATHER_PLAN_STATISTICS  Pgs 303, 320, 322, 366, 376, 403
• HASH  Pg 366
• IGNORE_OPTIM_EMBEDDED_HINTS  Pg 399
• IGNORE_ROW_ON_DUPKEY_INDEX  Pg 367
• INDEX  Pgs 366, 371, 407
• INDEX_ASC  Pg 366
• INDEX_COMBINE  Pg 366
• INDEX_DESC  Pg 366
• INDEX_FFS  Pg 366
• INDEX_JOIN  Pg 366
• INDEX_SS  Pg 366
• INDEX_SS_ASC  Pg 366
• INDEX_SS_DESC  Pg 366
• INDEX_STATS  Pg 401
• INLINE  Pgs 356, 376, 403
• LEADING  Pg 366
• MATERIALIZE  Pgs 367, 376, 403
• MERGE  Pg 366
• MODEL_MIN_ANALYSIS  Pg 367
• MONITOR  Pgs 127, 367
• NATIVE_FULL_OUTER_JOIN  Pg 366
• NLJ_BATCHING  Pg 366
• NO_APPEND  Pg 367
• NO_BIND_AWARE  Pg 367
• NO_CACHE  Pg 367
• NO_CPU_COSTING – disables the CPU cost model, reverting to the I/O cost model.  Pg 191
• NO_ELIMINATE_JOIN  Pg 366
• NO_EXPAND  Pg 366
• NO_EXPAND_TABLE  Pg 366
• NO_FACT  Pg 366
• NO_GATHER_OPTIMIZER_STATISTICS  Pg 367
• NO_INDEX  Pg 366
• NO_INDEX_FFS  Pg 366
• NO_INDEX_SS_ASC  Pg 366
• NO_INDEX_SS_DESC  Pg 366
• NO_MERGE  Pg 366
• NO_MONITOR  Pgs 127, 367
• NO_NATIVE_FULL_OUTER_JOIN  Pg 366
• NO_NLJ_BATCHING  Pg 366
• NO_OUTER_JOIN_TO_INNER  Pg 366
• NO_PARALLEL  Pg 367
• NO_PARALLEL_INDEX  Pg 367
• NO_PQ_CONCURRENT_UNION  Pg 367
• NO_PQ_SKEW  Pg 367
• NO_PUSH_PRED  Pg 366
• NO_PUSH_SUBQ  Pg 366
• NO_PX_JOIN_FILTER  Pg 367
• NO_QUERY_TRANSFORMATION  Pg 366
• NO_RESULT_CACHE  Pgs 367, 373
• NO_REWRITE  Pg 366
• NO_STAR_TRANSFORMATION  Pg 366
• NO_STATEMENT_QUEUING  Pg 367
• NO_SWAP_JOIN_INPUTS  Pg 366
• NO_UNNEST  Pg 366
• NO_USE_CUBE  Pg 366
• NO_USE_HASH  Pg 366
• NO_USE_MERGE  Pg 366
• NO_USE_NL  Pg 366
• NO_XML_QUERY_REWRITE  Pg 366
• NO_XMLINDEX_REWRITE  Pg 366
• OPT_ESTIMATE  Pg 400
• OPT_PARAM  Pgs 250, 366, 373
• OPTIMIZER_FEATURES_ENABLE  Pgs 278, 366, 373, 399
• ORDERED  Pg 366
• OUTER_JOIN_TO_INNER  Pg 366
• PARALLEL  Pg 367
• PARALLEL_INDEX  Pg 367
• PQ_CONCURRENT_UNION  Pg 367
• PQ_DISTRIBUTE  Pg 367
• PQ_FILTER  Pg 367
• PQ_SKEW  Pg 367
• PUSH_PRED  Pg 366
• PUSH_SUBQ  Pg 366
• PX_JOIN_FILTER  Pg 367
• QB_NAME  Pgs 367, 368
• RESULT_CACHE  Pgs 367, 373
• RETRY_ON_ROW_CHANGE  Pg 367
• REWRITE  Pg 366
• RULE  Pg 398
• SET_TO_JOIN – used to enable the set (INTERSECT or MINUS) transformation to standard join syntax.  Pg 189
• STAR_TRANSFORMATION  Pg 366
• STATEMENT_QUEUING  Pg 367
• SWAP_JOIN_INPUTS  Pg 366
• TABLE_STATS  Pg 401
• UNNEST  Pg 366
• USE_CONCAT  Pg 366
• USE_CUBE  Pg 366
• USE_HASH  Pg 366
• USE_MERGE  Pg 366
• USE_MERGE_CARTESIAN  Pg 366
• USE_NL  Pg 366
• USE_NL_WITH_INDEX  Pg 366

Events:

• 10046  Pgs 52-62
• 10053  Pgs 307-309
• 10132  Pgs 309-310, 371

Privileges:

• ADMINISTER SQL MANAGEMENT  Pg 397
• ADMINISTER SQL MANAGEMENT OBJECT  Pg 417
• ADVISOR  Pg 397
• ALTER ANY OUTLINE  Pg 385
• ALTER ANY SQL PROFILE  Pg 397
• ALTER SESSION  Pgs 61, 62
• ANALYZE ANY  Pgs 200, 245, 248, 251, 261, 263
• ANALYZE ANY DICTIONARY  Pgs 200, 245, 248, 261
• CREATE ANY  Pg 96
• CREATE ANY OUTLINE  Pg 385
• CREATE ANY SQL PROFILE  Pg 397
• CREATE JOB  Pg 244
• CREATE TABLE  Pg 69
• DROP ANY OUTLINE  Pg 385
• DROP ANY SQL PROFILE  Pg 397
• EXECUTE ANY PROCEDURE  Pg 385
• MANAGE ANY QUEUE  Pg 244
• MANAGE SCHEDULER  Pg 244
• MERGE ANY VIEW  Pg 291
• SELECT ANY DICTIONARY  Pgs 235, 246

Roles:

• DBA  Pgs 57, 192, 244, 291, 385, 417
• EXECUTE_CATALOG_ROLE  Pg 385
• GATHER_SYSTEM_STATISTICS  Pg 192
• HPROF_PROFILE (custom role)  Pg 101
• SELECT_CATALOG_ROLE  Pgs 322, 324
• SQL_TRACE (custom role)  Pg 61

Statistics:

• background elapsed time  Pg 107
• bytes received via SQL*Net from client  Pg 430
• bytes sent via SQL*Net to client  Pgs 430-431
• DB CPU  Pgs 108, 110, 111, 162, 163
• DB time  Pgs 55, 107, 108, 109, 111, 120, 122, 126, 127, 132, 134, 137, 140, 162, 163, 165, 166, 419, 420, 424
• hard parse elapsed time  Pg 107
• parse count (total)  Pgs 437, 438
• parse time elapsed  Pgs 107, 420
• session cursor cache count  Pg 437
• session cursor cache hits  Pgs 437, 438
• sql execute elapsed time  Pg 165

Wait Events:

• DB FILE SCATTERED READ  Pgs 75, 83
• DB FILE SEQUENTIAL READ  Pgs 75, 83, 112, 163
• DIRECT PATH READ TEMP  Pgs 75, 82
• DIRECT PATH WRITE TEMP  Pgs 75, 82
• latch free  Pg 436
• library cache: mutex X  Pg 436
• SQL*NET MESSAGE FROM CLIENT  Pgs 76, 111, 421-422, 423, 430, 432, 435
• SQL*NET MESSAGE TO CLIENT  Pgs 76, 80, 430

Built-In Functions/Procedures:

• ANALYZE  Pgs 207, 264
• CAST  Pgs 284, 321
• DBMS_APPLICATION_INFO.SET_ACTION  Pg 46
• DBMS_APPLICATION_INFO.SET_CLIENT_INFO  Pg 46
• DBMS_APPLICATION_INFO.SET_MODULE  Pgs 46, 66
• DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER  Pg 413
• DBMS_AUTO_TASK_ADMIN.DISABLE  Pg 260
• DBMS_AUTO_TASK_ADMIN.ENABLE  Pg 260
• DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE  Pg 58
• DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE  Pg 58
• DBMS_MONITOR.DATABASE_TRACE_DISABLE  Pg 60
• DBMS_MONITOR.DATABASE_TRACE_ENABLE  Pg 60
• DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE  Pg 59
• DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE  Pg 59
• DBMS_MONITOR.SESSION_TRACE_DISABLE  Pg 58
• DBMS_MONITOR.SESSION_TRACE_ENABLE  Pg 57
• DBMS_OUTLN.CLEAR_USED  Pg 380
• DBMS_OUTLN.CREATE_OUTLINE  Pg 378
• DBMS_OUTLN.DROP_BY_CAT  Pg 385
• DBMS_OUTLN.UPDATE_BY_CAT  Pg 379
• DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE  Pg 384
• DBMS_OUTPUT.PUT  Pg 238
• DBMS_OUTPUT.PUT_LINE  Pgs 238, 245
• DBMS_RANDOM.NORMAL  Pg 209
• DBMS_RANDOM.STRING  Pgs 209, 284
• DBMS_RANDOM.VALUE  Pg 284
• DBMS_SCHEDULER.CREATE_JOB  Pg 159
• DBMS_SCHEDULER.DISABLE  Pg 259
• DBMS_SCHEDULER.ENABLE  Pg 259
• DBMS_SESSION.IS_ROLE_ENABLED  Pgs 61, 101
• DBMS_SESSION.RESET_PACKAGE  Pg 295
• DBMS_SESSION.SESSION_TRACE_DISABLE  Pg 61
• DBMS_SESSION.SESSION_TRACE_ENABLE  Pg 61
• DBMS_SESSION.SET_IDENTIFIER  Pg 46
• DBMS_SPM.ALTER_SQL_PLAN_BASELINE  Pgs 414-415
• DBMS_SPM.CONFIGURE  Pgs 417, 418
• DBMS_SPM.CREATE_STGTAB_BASELINE  Pgs 415, 416
• DBMS_SPM.DROP_SQL_PLAN_BASELINE  Pgs 408, 416
• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE  Pgs 411-412
• DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE  Pgs 406, 408
• DBMS_SPM.LOAD_PLANS_FROM_SQLSET  Pg 409
• DBMS_SPM.PACK_STGTAB_BASELINE  Pgs 415, 416
• DBMS_SPM.REPORT_AUTO_EVOLVE_TASK  Pg 413-414
• DBMS_SPM.UNPACK_STGTAB_BASELINE  Pgs 415, 416
• DBMS_SQLDIAG.DUMP_TRACE (no extra cost license required, dumps 10053 trace from library cache)  Pg 308
• DBMS_SQLTUNE.ACCEPT_SQL_PROFILE  Pgs 392-393
• DBMS_SQLTUNE.ALTER_SQL_PROFILE  Pgs 393-394
• DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF  Pgs 396, 397
• DBMS_SQLTUNE.CREATE_TUNING_TASK  Pg 390
• DBMS_SQLTUNE.DROP_SQL_PROFILE  Pg 397
• DBMS_SQLTUNE.DROP_TUNING_TASK  Pg 392
• DBMS_SQLTUNE.EXECUTE_TUNING_TASK  Pg 390
• DBMS_SQLTUNE.IMPORT_SQL_PROFILE  Pg 401
• DBMS_SQLTUNE.PACK_STGTAB_SQLPROF  Pgs 396, 397
• DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF  Pgs 396, 397
• DBMS_SQLTUNE.REPORT_SQL_DETAIL  Pg 141
• DBMS_SQLTUNE.REPORT_SQL_MONITOR  Pgs 127-130
• DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST  Pg 127
• DBMS_SQLTUNE.REPORT_TUNING_TASK  Pg 390
• DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF  Pgs 396, 397
• DBMS_STATS. GATHER_FIXED_OBJECTS_STATS  Pg 233
• DBMS_STATS. UPGRADE_STAT_TABLE  Pg 245
• DBMS_STATS.ALTER_STATS_HISTORY_RETENTION  Pgs 199-200, 261
• DBMS_STATS.CONVERT_RAW_VALUE  Pg 212
• DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR  Pg 212
• DBMS_STATS.CONVERT_RAW_VALUE_ROWID  Pg 212
• DBMS_STATS.COPY_TABLE_STATS  Pg 256-257
• DBMS_STATS.CREATE_EXTENDED_STATS  Pgs 226, 229
• DBMS_STATS.CREATE_STAT_TABLE  Pgs 200, 244
• DBMS_STATS.DELETE_COLUMN_STATS  Pg 267, 268
• DBMS_STATS.DELETE_DATABASE_PREFS  Pg 248
• DBMS_STATS.DELETE_DATABASE_STATS  Pg 267
• DBMS_STATS.DELETE_DICTIONARY_STATS  Pg 267
• DBMS_STATS.DELETE_FIXED_OBJECTS_STATS  Pg 267
• DBMS_STATS.DELETE_INDEX_STATS  Pg 267
• DBMS_STATS.DELETE_PENDING_STATS  Pg 251
• DBMS_STATS.DELETE_SCHEMA_PREFS  Pg 248
• DBMS_STATS.DELETE_SCHEMA_STATS  Pg 267
• DBMS_STATS.DELETE_SYSTEM_STATS  Pgs 192, 198, 201
• DBMS_STATS.DELETE_TABLE_PREFS  Pg 248
• DBMS_STATS.DELETE_TABLE_STATS  Pgs 252, 267
• DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY  Pg 266
• DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING  Pg 267
• DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB  Pg 266
• DBMS_STATS.DROP_EXTENDED_STATS  Pg 227
• DBMS_STATS.DROP_STAT_TABLE  Pgs 200, 245
• DBMS_STATS.EXPORT_SYSTEM_STATS  Pg 192
• DBMS_STATS.GATHER_DATABASE_STATS  Pgs 233, 239
• DBMS_STATS.GATHER_DICTIONARY_STATS  Pg 233
• DBMS_STATS.GATHER_INDEX_STATS  Pg 233
• DBMS_STATS.GATHER_SCHEMA_STATS  Pgs 233, 238, 240, 263
• DBMS_STATS.GATHER_SYSTEM_STATS  Pgs 192, 194-197, 200, 201, 206
• DBMS_STATS.GATHER_TABLE_STATS  Pgs 233, 249, 250, 252, 255, 263, 285
• DBMS_STATS.GET_PARAM  Pgs 245, 246, 247
• DBMS_STATS.GET_PREFS  Pg 247
• DBMS_STATS.GET_STATS_HISTORY_RETENTION  Pgs 199, 261
• DBMS_STATS.GET_SYSTEM_STATS  Pg 192
• DBMS_STATS.IMPORT_SYSTEM_STATS  Pg 192
• DBMS_STATS.LOCK_SCHEMA_STATS  Pg 262
• DBMS_STATS.LOCK_TABLE_STATS  Pg 262
• DBMS_STATS.OBJECTTAB  Pg 239
• DBMS_STATS.PUBLISH_PENDING_STATS  Pgs 250-251
• DBMS_STATS.PURGE_STATS  Pgs 199-200, 261
• DBMS_STATS.REPORT_COL_USAGE  (note: use SET LONG 100000 in SQL*Plus before executing query)  Pgs 228, 242-243
• DBMS_STATS.REPORT_SINGLE_STATS_OPERATION  Pg 201
• DBMS_STATS.RESET_COL_USAGE  Pg 243
• DBMS_STATS.RESET_GLOBAL_PREF_DEFAULTS  Pg 248
• DBMS_STATS.RESET_PARAM_DEFAULTS  Pg 246
• DBMS_STATS.RESTORE_DATABASE_STATS  Pg 262
• DBMS_STATS.RESTORE_DICTIONARY_STATS  Pg 262
• DBMS_STATS.RESTORE_FIXED_OBJECT_STATS  Pg 262
• DBMS_STATS.RESTORE_SCHEMA_STATS  Pg 262
• DBMS_STATS.RESTORE_SYSTEM_STATS  Pgs 192, 199-200
• DBMS_STATS.RESTORE_TABLE_STATS  Pg 262
• DBMS_STATS.SEED_COL_USAGE  Pg 228
• DBMS_STATS.SET_DATABASE_PREFS  Pg 246, 247
• DBMS_STATS.SET_GLOBAL_PREFS  Pg 246
• DBMS_STATS.SET_PARAM  Pgs 245, 246
• DBMS_STATS.SET_SCHEMA_PREFS  Pg 247
• DBMS_STATS.SET_SYSTEM_STATS  Pg 192, 198
• DBMS_STATS.SET_TABLE_PREFS  Pgs 247, 250, 251, 254, 256
• DBMS_STATS.UNLOCK_SCHEMA_STATS  Pg 262
• DBMS_STATS.UNLOCK_TABLE_STATS  Pg 262
• DBMS_UTILITY.GET_TIME  Pg 280
• DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL  Pg 153
• DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE  Pgs 154-155
• DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT  Pg 154
• DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE  Pg 156
• DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE  Pg 156
• DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS  Pg 153
• DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL  Pg 15
• DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE  Pg 155
• DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRIC  Pg 155
• DBMS_XPLAN.DISPLAY  Pgs 301, 302, 306, 316-321, 349, 350, 368, 370, 371, 380, 382, 384, 395
• DBMS_XPLAN.DISPLAY_AWR  Pgs 305-306, 311, 323-325
• DBMS_XPLAN.DISPLAY_CURSOR  Pgs 249, 304-305, 311, 322-323, 336, 352, 353, 406, 407, 408
• DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE  Pgs 409, 410, 411
• EXPLAIN PLAN  Pgs 299-302, 320, 336, 339, 346, 348, 349, 368, 370, 371, 380, 382, 384, 395
• EXTRACT  Pgs 399, 410
• EXTRACTVALUE  Pgs 399, 410
• LAG  Pgs 203, 214, 224
• LATERAL  Pg 180
• LNNVL  Pgs 175, 346, 347, 502
• NTILE  Pg 217
• NULLIF  Pgs 354, 419, 424
• PERFSTAT.STATSPACK.CLEAR_BASELINE  Pg 160
• PERFSTAT.STATSPACK.MAKE_BASELINE  Pg 160
• PERFSTAT.STATSPACK.SNAP  Pg 158
• PGA_PKG.ALLOCATE  Pg 295
• RATIO_TO_REPORT  Pgs 92, 111, 112, 120, 221, 223
• STATSPACK.MODIFY_STATSPACK_PARAMETER  Pg 158
• STATSPACK.PURGE  Pg 159
• SYS_CONTEXT, SYS_CONTEXT(‘USERENV’,’SID’) , SYS_CONTEXT(‘USERENV’,’SESSION_USER’)  Pgs 45, 61, 64, 113, 321, 372, 390
• SYS_OP_COMBINED_HASH  Pg 227
• TABLE  Pgs 155, 249, 301, 302, 304, 305, 317-321, 323, 324, 349, 350, 352, 368, 370, 371, 380, 382, 384, 395, 399, 406-411, 625, 639, 669
• UNPIVOT  Pg 158
• UTL_RAW.CAST_TO_BINARY_DOUBLE  Pg 212
• UTL_RAW.CAST_TO_BINARY_FLOAT  Pg 212
• UTL_RAW.CAST_TO_BINARY_INTEGER  Pg 212
• UTL_RAW.CAST_TO_NUMBER  Pg 212
• UTL_RAW.CAST_TO_NVARCHAR2  Pg 212
• UTL_RAW.CAST_TO_RAW  Pg 212
• UTL_RAW.CAST_TO_VARCHAR2  Pg 212
• XMLSEQUENCE  Pgs 399, 410
• XMLTABLE  Pgs 201, 270
• XMLTYPE  Pgs 399, 410

Packages:

• DBMS_HPROF profiler  Pgs 89-96
• DBMS_JOB  Pg 257
• DBMS_PROFILER profiler  Pgs 89, 96-101
• DBMS_SCHEDULER  Pg 257
• DBMS_SQL_MONITOR  Pg 131
• DBMS_SQLTUNE  Pgs 389-402
• DBMS_STATS  Pgs 191-201, 207, 233-271
• DBMS_XPLAN  Pgs 311-325

Oracle Provided Scripts:

• ashrpt.sql  Pg 124
• ashrpti.sql  Pg 124
• awrddrpt.sql  Pg 161
• awrrpt.sql  Pg 160
• awrsqrpt.sql  Pg 306
• coe_xfr_sql_profile.sql  Pg 401
• dbmshptab.sql  Pg 90
• profrep.sql  Pg 100
• proftab.sql  Pg 97
• spauto.sql  Pg 159
• spcreate.sql  Pg 157
• sppurge.sql  Pg 159
• spreport.sql  Pg 161
• sprepsql.sql  Pgs 166, 306
• utlxplan.sql  Pg 300
• utlxmv.sql  Pg 587
• utlxrw.sql  Pg 586

Utilities:

• /proc/loadavg command on Linux.  Pg 131
• exp  Pg 381
• grep  Pg 422
• PLSHPROF (processes DBMS_HPROF output)  Pgs 89, 94
• snapper.sql (utility script by Tanel Põder)  Pgs 145-147
• SQL Developer  Pgs 95-96, 100
• TKPROF  Pgs 67-79, 421-422, 426
• TRCSESS  Pg 67
• TVD$XTAT  Pgs 67, 79-88, 422-423, 426-427

Execution Plans:

Automatic Query Transformations:

• Count transformation – transforms COUNT(column) to COUNT(*) when a NOT NULL constraint is present on the column.  Pg 174
• Common sub-expression elimination – removes unnecessary/duplicate predicates from the WHERE clause.  Pgs 174-175
• OR expansion – transforms SQL statements with OR clauses into multiple SQL blocks with UNION ALL specified between the SQL blocks. Pg 175
• Simple view merging – merges query blocks using heuristics, used when the query blocks do not contain aggregations, set operators, hierarchical queries, MODEL clauses, or scalar subqueries (SELECT statement in the column list of a query).  Pgs 176-177
• Complex view merging  – merges query blocks that contain aggregations using a cost-based approach, but does not work with hierarchical queries or queries containing GROUPING SETS, ROLLUP, PIVOT, or MODEL clauses.  Pgs 176-177
• Select list pruning – removing selected columns from non-top-level query blocks that are not referenced by parent query blocks.  Pgs 177-178
• Filter push down – pushing column restrictions from the WHERE clause of a parent query block into a child query block.  Pgs 178-179
• Join predicate push down – push join predicates from a parent query block into an unmergeable child query block.  Pgs 179-180
• Predicate move around – applying filter restrictions in one child query block (inline view) to another child query block due to join conditions that are specified between the parent and child query blocks.  Pg 180
• Distinct placement – applies a DISTINCT filter to a child table before joining to a parent table, when the DISTINCT clause is specified for a SQL statement.  Pg 181
• Distinct elimination – removes a specified DISTINCT filter when all columns of the primary key, a unique key, or the ROWID are selected from a table.  Pg 181
• Group by placement – applies an aggregation function to a child table before joining that table to a parent table – a COUNT aggregation specified in a SQL statement is transformed into a COUNT aggregation of the child table before the join, and a SUM aggregation after the join.  Pgs 181-182
• Order-by elimination – removes unnecessary ORDER BY clauses using heuristics from subqueries, inline views, and joined standard views.  Pg 182
• Subquery unnesting – injects semi-join, anti-join, and scalar subqueries into the FROM clause; helps to eliminate the need to execute a subquery once for every row returned by the containing query block.  Pg 183
• Subquery coalescing – combines equivalent semi-join and anti-join subqueries into a single query block.  Pgs 183-184
• Subquery removal using window functions – replaces subqueries containing aggregate functions with analytic functions and standard joins.  Pgs 184-185
• Join elimination – eliminates the parent table from a view when a declared foreign key is present between the tables, and no columns are selected from the parent table in the query block that selects from the view.  Pg 185
• Join factorization – avoids repeatedly accessing one of the row sources by rewriting UNION ALL type queries so that predicates are applied to a second table as well as the UNION ALL operation before joining to the other row source.  Pgs 185-186
• Outer join to inner join – when possible, converts left/right joins to equijoins, possibly due to other predicates that conflict with the left/right join syntax.  Pg 186
• Full outer join to outer join, UNION ALL, and antijoin.  Pg 187
• Table expansion – using an index scan on a partitioned table when the index is unusable for some table partitions.
• Set to join conversion – converts INTERSECT or MINUS type queries to standard join queries, must specify the SET_TO_JOIN hint.  Pg 189
• Star transformation.  Pg 189, chapter 14
• Query rewrite with materialized views – accesses data stored in a materialized view, rather than the base tables, even though the materialized view is not referenced in the SQL statement (Enterprise Edition feature).  Pg 189, chapter 15

Execution Plan Operations:

• AND-EQUAL  Pg 331
• BITMAP AND  Pg 331
• BITMAP KEY ITERATION  Pgs 333, 334
• BITMAP MINUS  Pg 331
• BITMAP OR  Pg 331
• CONCATENATION  Pg 331
• CONNECT BY PUMP  Pg 339
• CONNECT BY WITH FILTERING  Pgs 333, 338-341
• CONNECT BY WITHOUT FILTERING  Pg 331
• COUNT STOPKEY  Pg 330
• FILTER  Pgs 330, 333, 335-337
• HASH JOIN  Pg 331
• HASH JOIN ANTI  Pg 362
• HASH JOIN ANTI NA  Pgs 347, 362
• INDEX RANGE SCAN  Pg 327
• INLIST ITERATOR  Pg 331
• INTERSECTION  Pg 331
• MERGE JOIN  Pgs 331, 332, 333
• MINUS  Pgs 331, 362
• MULTI-TABLE INSERT  Pg 331
• NESTED LOOPS  Pgs 333, 334-335
• PARTITION RANGE ITERATOR  Pg 331
• RECURSIVE WITH PUMP  Pg 342
• SELECT  Pg 327
• SORT ORDER BY  Pg 327
• SORT ORDER BY STOPKEY  Pg 330
• SQL MODEL  Pg 331
• STATISTICS COLLECTOR  Pgs 350, 351, 352
• TABLE ACCESS BY INDEX ROWID  Pg 327
• TEMP TABLE TRANSFORMATION  Pg 331
• UNION-ALL  Pgs 331, 332, 333
• UNION ALL (RECURSIVE WITH) BREADTH FIRST, DEPTH FIRST  Pgs 333, 341
• UPDATE  Pgs 333, 337-338

Latches:

•  CACHE BUFFERS CHAINS  Pgs 666, 668
• LIBRARY CACHE  Pgs 25, 436
• SHARED POOL  Pg 25

Oracle Error Messages:

• Error: cannot fetch last explain plan from PLAN_TABLE.  Pg 320
• ORA-00600: Internal error code, arguments.  Pg 569
• ORA-00904: Invalid identifier.  Pg 537
• ORA-00931: Missing identifier.  Pg 379
• ORA-00942: Table or view does not exist.  Pg 569
• ORA-01013: User requested cancel of current operation.  Pg 259
• ORA-01017: invalid username/password; logon denied.  Pg 69
• ORA-01039: Insufficient privileges on underlying objects of the view.  Pg 300
• ORA-01408: Such column list already indexed.  Pg 524
• ORA-01422: Exact fetch returns more than requested number of rows.  Pg 322
• ORA-01722: Invalid number error.  Pg 657
• ORA-02097: Parameter cannot be modified because specified value is invalid.  Pg 295
• ORA-03113: End-of-file on communication channel.  Pg 296
• ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT.  Pg 296
• ORA-07445: Exception encountered: core dump [exception].  Pg 569
• ORA-12026: Invalid filter column detected.  Pg 596
• ORA-12827: Insufficient parallel query slaves available error.  Pgs 624, 625
• ORA-12838: Cannot read/modify an object after modifying it in parallel.  Pgs 640, 644
• ORA-12839: Cannot modify an object in parallel after modifying it.  Pg 640
• ORA-12850: Could not allocate slaves on all specified instances.  Pg 614
• ORA-13541: System moving window baseline size greater than retention.  Pg 156
• ORA-13717: Tuning Package License is needed for using this feature.  Pg 402
• ORA-13859: Action cannot be specified without the module specification.  Pg 59
• ORA-20005: Object statistics are locked.  Pgs 238, 263
• ORA-23538: Cannot explicitly refresh a NEVER REFRESH materialized view.  Pg 588
• ORA-26040: Data block was loaded using the NOLOGGING option.  Pg 642
• ORA-32313: REFRESH FAST of <mview> unsupported after PMOPs.  Pg 594
• ORA-38029: Object statistics are locked.  Pg 264
• ORA-48913: Writing into trace file failed, file size limit [] reached.  Pg 62

Blog articles that reference the “Troubleshooting Oracle Performance, Second Edition” book:

FIRST_ROWS_n Optimizer Mode – What is Wrong with this Statement?