Book Review: Troubleshooting Oracle Performance (Part 2)

November 7, 2011

Most Thorough, Yet Compact Performance Tuning Book 9i-11g
http://www.amazon.com/Troubleshooting-Oracle-Performance-Christian-Antognini/dp/1590599179

(Back to the Previous Post in the Series)

I originally reviewed the “Troubleshooting Oracle Performance” book a bit over three years ago, having pre-ordered the book prior to its publication.  The original review is quite limited in depth compared to some of my more recent Oracle Database book reviews.  I recently decided to purchase the companion PDF file from Apress, as well as to re-read the book so that I could provide a much more detailed book review. 

Since the time when I wrote my original review of this book I have formally reviewed at least three other books that are Oracle Database performance specific, reviewed a handful of other books that contain Oracle Database performance sections, and briefly viewed and wrote comments about a couple of other performance related books.  The “Troubleshooting Oracle Performance” book effectively sets the standard by which all other Oracle Database performance books are measured.  The depth of coverage, accuracy of contents, and signal to noise ratio are unparalleled in the Oracle Database performance book category.

There are several factors that separate this book from the other 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 various solutions.  Very few potential problems were overlooked.  Some of the other books on the market only describe the potential benefits of implementing a feature, without discussing limitations or unintended side-effects.
• For nearly every feature described in the book, the book lists the licensing and version requirements (sometimes to a specific point release such as 9.2.0.4, 9.2.0.5, 10.2.0.3, 10.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 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, and often contains 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 downloadable scripts on the Apress website appear to be corrupt (this corruption appears to affect more than just the scripts for this book).  Updated versions of the scripts are available for download 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.  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.
• In the acknowledgments section at the beginning of the book the author mentioned that his English writing ability is poor and that “I should really try to improve my English skills someday.”  In the book the only hint that English is not the author’s primary language is the repeated use of the phrase “up to” when describing features that exist in one Oracle Database release version or another.  The author’s use of “up to” should be interpreted as “through” (including the specified end-point) rather than as “prior to” (before the specified end-point).  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.

The “Troubleshooting Oracle Performance” book covers Oracle releases through 9.2.0.8, 10.1.0.5, 10.2.0.4, and 11.1.0.6.  For the most part the information provided in the book applies to Oracle Database 11.1 and above, with exceptions noted for earlier release versions.  The author’s website effectively extends the book’s contents to cover Oracle Database 10.2.0.5, 11.1.0.7, and 11.2.0.x.  It is recommended that the Oracle sample schemas are installed in a test database so that the reader is able to experiment with all of the sample scripts provided with the book.  The book appears to be mostly directed at DBAs, however sections of the book are appropriate for developers.

This review is a bit long (roughly 18 typewritten pages), and might not completely appear on Amazon (see my Oracle blog if the review does not appear in full).  As such I will begin the detail portion of the review with the problems/corrections to the book that I have identified (see the author’s website for the complete list of errata), describe some of the foundation knowledge/tips found in the book, and then list various data dictionary views/tables, Oracle Database parameters, SQL hints, built-in functions, execution plan elements, and Oracle error messages that are described in the book (many of these items cannot be located in the index at the back of the book, so a page number reference is provided).

—

Comments, Corrections, and Problems:

• The descriptions of both the IS_BIND_AWARE and IS_SHAREABLE columns of V$SQL include the phrase “If set to N, the cursor is obsolete, and it will no longer be used.”  It appears that this phrase was accidentally added to the description of the IS_BIND_AWARE column. (pages 27-28)
• The book states, “Remember, execution plans are stored only when the cursors are closed, and the wait events are stored only if they occurred while the tracing of wait events was enabled.”  Technically, this behavior changed with the release of Oracle Database 11.1.  The PLAN_STAT parameter of the DBMS_SESSION.SESSION_TRACE_ENABLE function, and the PLAN_STAT parameter of the various DBMS_MONITOR functions default to a value of FIRST_EXECUTION.  The default behavior in 11.1 and later is to write out the execution plans to the trace file after the first execution (before the cursor is closed), however that parameter may be changed to ALL_EXECUTIONS (plan is written to the trace file after each execution) or NEVER (do not output the execution plan). (page 82)
• The book states, “Notice how the number of waits, 941, exactly matches the number of physical writes of the operation HASH GROUP BY provided earlier in the row source operations.”  The statement in the book is correct, but as written it might be slightly confusing.  This statement probably could have been clarified slightly, repeating what was stated earlier about the cumulative nature of the statistics for the parent and child operations.  The reader would then more easily understand that the pw=1649 value associated with the “TABLE ACCESS FULL SALES” operation must be subtracted from the pw=2590 value found on the “HASH GROUP BY” operation to arrive at the number 941 mentioned in the book. (page 86)
• The book states, “As of Oracle Database 10g, the rule-based optimizer is no longer supported and, therefore, will not be covered here.”  This sentence, as written, could be misinterpreted.  The rule based optimizer still exists in the latest release of Oracle Database, but its use is deprecated, and therefore the use of the rule based optimizer is no longer supported by Oracle Corp., even though it still exists for backward compatibility purposes.  Page 174 of the book also states that the rule based optimizer has been desupported. (page 108)
• The script that compares the time required to read from table BIG_TABLE using different values for the DB_FILE_MULTIBLOCK_READ_COUNT parameter is subject to at least three potential problems that could throw off the accuracy of the results: 1. Portions of the table may remain cached in the buffer cache between each execution (a warning about this potential issue is provided in the book immediately after the script). 2. The query optimizer may decide to use serial direct path reads (“direct path read” wait event), rather than the commonly expected “db file scattered read” type accesses for the full table scan.  Testing seems to indicate that the number of blocks read in a single serial direct path read is related to the DB_FILE_MULTIBLOCK_READ_COUNT value – the maximum number of blocks seems to be the largest power of two that is less than or equal to the DB_FILE_MULTIBLOCK_READ_COUNT value (this might explain the stair-stepped diagram that is included in the book).  Serial direct path reads where introduced in Oracle Database 11.1 as a potential replacement for Oracle buffer cache buffered reads when parallel query was not implemented; that change in behavior was apparently not documented prior to the publication of this book. 3. What unrelated blocks are in the buffer cache at the time that the test started might be important. (page 178)
• That book states, “Unfortunately, no feature is provided by the package dbms_xplan to query it [the stats$sql_plan repository table].”  There is a way to use DBMS_XPLAN.DISPLAY to display an execution plan that was captured in the PERFSTAT.STATS$SQL_PLAN table, but the syntax is a little awkward. (page 204)
• The book demonstrates that it is possible to add comment text to a hint block without affecting the hint in that hint block.  Ideally, the book would have mentioned that there are risks that hints will be ignored by the optimizer when adding regular comments to hint blocks, especially when the comments are added in front of the hints. (page 254)
• “8;” is missing from the first EXPLAIN PLAN FOR SELECT statement. (page 260)
• A test case (access_structures_1000.sql) is provided that uses partitioning without first mentioning that the partitioning option may only be purchased for the Enterprise Edition.  This is one of the very few instances where the licensing requirements for a feature are not mentioned in the book when the feature is introduced.  The licensing requirements are described two pages later. (page 348)
• Considering the depth of explanation found in the rest of the book, the book should have mentioned that “ALTER TABLE t SHINK SPACE” is only valid if table t is in an ASSM tablespace. (page 351)
• The book references the clustering_factor.sql script, but that script does not exist in the chapter 9 script library.  A clustering_factor.sql script does exist in the chapter 4 script library, but the table definition differs from what is shown in chapter 9.  This does not appear to be a significant problem because the essential portions of the script appear in the book. (page 375)
• The book states, “Note: Full table scans, full partition scans, and fast full index scans executed in parallel use direct reads and, therefore, bypass the buffer cache.”  This was a correct statement at the time the book was printed.  However, Oracle Database 11.2 introduced in-memory parallel execution.  The book author briefly mentions this feature in one of his blog articles. (page 500)
• The book states that direct path insert does not work with INSERT statements containing a VALUES clause.  Since the time when the book was published, the author created a blog article that demonstrates how to use direct path insert for SQL statements that have a VALUES clause.  According to the blog article, the behavior changed in Oracle Database 11.1 and again in 11.2. (page 513)

The actual errors found in the book are minor in comparison to the amount of information covered by the book.

—

Foundation Knowledge, and Miscellaneous Tips:

• A quote from the book, one of the reasons why application performance is important: “The studies showed a one-to-one decrease in user think time and error rates as system transaction rates increased. This was attributed to a user’s loss of attention because of longer wait times.” (page 3)
• The basic equation that determines the time required for a response from the database server: “response time = service time + wait time” (page 4)
• Description of service level agreements, and a description of a method to design and test code to meet service level agreements. (pages 5-8)
• A quote that sets the tone for the rest of the book: “So if you do not want to troubleshoot nonexistent or irrelevant problems (compulsive tuning disorder), it is essential to understand what the problems are from a business perspective—even if more subtle work is required.” (page 11)
• Describing the selectivity and cardinality statistics: “selectivity is a value between 0 and 1 representing the fraction of rows filtered by an operation.” “cardinality = selectivity * num_rows”. (page 13)
• The description of each step in the cursor life cycle is explained: open, parse, define output variables, bind input variables, execute, fetch, close. (page 15)
• Description of each step in parsing: include VPD predicates; check syntax, semantics, access rights; store parent in library cache; logical optimization; physical optimization; store child in the library cache. (page 18)
• Advantages and disadvantages of bind variables (pages 22-23)
• Causes of new child cursors when using bind variables: maximum size of the bind variable increases, execution environment changes, adaptive (extended) cursor sharing. (pages 23, 27)
• Bind variable peeking was introduced in Oracle 9i. (page 25)
• A caution about using bind variables when histograms are present: “On the other hand, bind variables should be avoided at all costs whenever histograms provide important information to the query optimizer.” (page 30)
• Various profiling applications mentioned: PerformaSure, JProbe; load-testing framework: the Grinder.
• Sample TKPROF output, along with the original 10046 extended SQL trace file.
• Debugging event numbers are listed in the file $ORACLE_HOME/rdbms/mesg/oraus.msg – but that file is not distributed on all platforms. (page 63)
• Structure of 10046 trace files (pages 73-76)
• Using TRCSESS to process 10046 trace files. (page 76-77)
• Using TKPROF to process 10046 trace files. (page 78-90)
• Unlike other books, this book provides a warning about using the EXPLAIN parameter of TKPROF, “In any case, even if all the previous conditions are met, as the execution plans generated by EXPLAIN PLAN do not necessarily match the real ones (the reasons will be explained in Chapter 6), it is not advisable to specify the argument explain.” (page 79)
• Interesting comment about the SORT parameter for TKPROF – it does not perform a multi-level sort, “When you specify a comma-separated list of values [for the SORT parameter], TKPROF sums the value of the options passed as arguments. This occurs even if they are incompatible with each other.” (page 81)
• Using TVD$XTAT – a trace file parser developed by the book author. (pages 90-100)
• Discussion of the I/O cost model, CPU cost model, noworkload statistics, and workload statistics. (page 111)
• A possible explanation why some developers want table columns to appear in a specified order in a table’s definition: “The estimated CPU cost to access a column depends on its position in the table.” “cpu_cost = column_position*20” (page 117)
• Calculated Cost of an operation is approximately IO_COST + CPU_COST  / (CPUSPEED * SREADTIME * 1000). (page 118)
• When using noworkload statistics, SREADTIME is calculated as IOSEEKTIM + (DB_BLOCK_SIZE / IOTFRSPEED). (page 119)
• When SREADTIM, MREADTIM, or MBRC are not available in SYS.AUX_STATS$, the optimizer falls back to noworkload statistics. (page 119)
• An ASSOCIATE STATISTICS SQL statement may be used to associate statistics with columns, functions, packages, types, domain indexes, and index types. (page 120)
• The ENDPOINT_VALUE of the USER_TAB_HISTOGRAMS view only includes the first 6 bytes of character columns – the book did not mention the ENDPOINT_ACTUAL_VALUE column. (pages 126, 129)
• Extended statistics for column groups work only with equality predicates because of the hashing function that is applied to the column group values. (page 132)
• An index is always balanced because the same number of branch blocks are present between the root block and all of the leaf blocks. (page 133)
• As of Oracle Database 10.1 the default value of DBMS_STATS’ CASCADE parameter is DBMS_STATS.AUTO_CASCADE, which allows the database engine to decide when to collect index statistics when the table’s statistics are collected. (page 140)
• Very through description of the DBMS_STATS package.
• Regarding when Oracle determines to automatically collect object statistics, the book states: “By default, a table is considered stale when more than 10 percent of the rows change. This is a good default value. As of Oracle Database 11g, this can be changed if necessary.” (page 163)
• When describing historical object statistics, the book states: “As of Oracle Database 10g, whenever system statistics or object statistics are gathered through the package dbms_stats, instead of simply overwriting current statistics with the new statistics, the current statistics are saved in other data dictionary tables that keep a history of all changes occurring within a retention period.” (page 164)
• Interesting flowchart diagram that describes a process of setting initialization parameters and statistics. (page 171)
• Factors that might cause fewer blocks to be read than is specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter: “Segment headers are read with single-block reads. Physical reads never span several extents. Blocks already in the buffer cache, except for direct reads, are not reread from the I/O subsystem.” (page 175)
• Regarding behavior changes related to specific initialization parameters from one Oracle Database release to another, the book states: “When workload system statistics are available, the I/O cost is no longer dependent on the value of the initialization parameter db_file_multiblock_read_count.” (page 177)
• A close approximation for the calculation of the auto-tuned DB_FILE_MULTIBLOCK_READ_COUNT parameter is provided in the book, rather than simply stating that the auto-tuned parameter could be affected “if the number of sessions is extremely large” as is stated in the Oracle Database documentation. (page 178)
• Nice test case that demonstrates dynamic sampling. (pages 180-183)
• For an index access, the Io_cost ≈ blevel + (leaf_blocks +clustering_factor) * selectivity * (optimizer_index_cost_adj/100). (page 184)
• A danger in modifying the OPTIMIZER_INDEX_COST_ADJ parameter to too low of a value is that the optimizer might calculate the same cost for two different indexes due to rounding in the cost estimates – the optimizer will then select the index that sorts first alphabetically.  The book provides a partial test case that demonstrates the problem. (page 185-186)
• The purpose of the OPTIMIZER_INDEX_CACHING  parameter is often misstated in various books and websites.  This book correctly states: The OPTIMIZER_INDEX_CACHING parameter “does not specify how much of each of the indexes is actually cached by the database engine…  Values greater than 0 decrease the cost of index scans performed for in-list iterators and in the inner loop of nested loop joins. Because of this, it is used to push the utilization of these operations.” (page 186)
• Formula showing how the OPTIMIZER_INDEX_CACHING parameter is applied to costing calculations (page 186).
• Defining a non-mergeable view: “When the view contains grouping functions in the SELECT clause, set operators, or a hierarchical query, the query optimizer is not able to use view merging. Such a view is called a nonmergeable view.” (page 188)
• Test case showing why the OPTIMIZER_SECURE_VIEW_MERGING parameter defaults to TRUE, when it is sensible to set that parameter to FALSE for performance reasons, and the privileges that may be assigned to a user so that the user is not subject to the negative performance effects caused by having a value of TRUE set for this parameter. (pages 187-189)
• In Oracle Database 9.2, automatic PGA management did not work with a shared server configuration, but it does work with a shared server configuration starting in Oracle Database 10.1. (page 190)
• Oracle Database 10.1 and lower artificially limit the amount of memory that a session can allocate when automatic PGA management is enabled (for example 100MB for serial operations), and overcoming that limit requires the modification of hidden initialization parameters.  Oracle Database 10.2 and higher remove this artificial limit, allowing PGA allocation to increase as the amount of memory increases.  While not stated in the book, setting the OPTIMIZER_FEATURES_ENABLE parameter value to that of an earlier release, 10.1.0.4 for example, causes the PGA allocation to be limited, just as was the case prior to Oracle Database 10.2. (page 190)
• As of Oracle Database 10.1, the default value for the PGA_AGGREGATE_TARGET is 20% of the SGA size. (page 190)
• The PLAN_TABLE exists by default starting in Oracle Database 10.1 as a global temporary table.  The utlxplan.sql script only needs to be run in Oracle Database versions prior to 10.1 – this fact was missed by a couple of recently released books that still indicate that the utlxplan.sql script must be executed to create the PLAN_TABLE. (page 197)
• Detailed explanation why EXPLAIN PLAN FOR with a SQL statement using bind variables might generate an incorrect execution plan: no bind peeking, all bind variables are handled as VARCHAR2. (pages 198-199)
• Demonstration of a method to simulate DBMS_XPLAN.DISPLAY_CURSOR on Oracle Database 9.2. (page 202)
• The book states that understanding 10053 trace files is not as easy task, that the trace file is only generated when there is a hard parse.  The book provides three references to other sources to help the reader understand 10053 trace file contents. (page 205)
• The book describes setting event 10132 to cause Oracle Database to write out the SQL statement, execution plan, and initialization parameters that affects the optimizer on each hard parse. (page 206)
• In an execution plan, the Used-Tmp  and Max-Tmp columns are indicated in KB, so the column values must be multiplied by 1024 so that the unit of measure is consistent with the other memory related columns. (page 210)
• Detailed walk through of the order of operations in execution plans (pages 224, 227, 229, 231, 232, 235, 237-239, 240, 241, 363, 401, 421, 437, 453-454, 501)
• The book describes when a feature requires an Enterprise Edition license (such as SQL plan baselines), Enterprise Edition with the Tuning Pack (such as SQL profiles), and the Oracle Database release version that first supported the feature.
• The book correctly states about SQL hints: “Simply put, hints are directives added to SQL statements to influence the query optimizer’s decisions. In other words, it is something that impels toward an action, not merely suggesting one.” (page 252)
• Default prefixes for query block names: CRI$ – CREATE INDEX statements; DEL$ – DELETE statements; INS$ – INSERT statements; MISC$ – Miscellaneous SQL statements like LOCK TABLE; MRG$ – MERGE statements; SEL$ – SELECT statements; SET$ – Set operators like UNION and MINUS; UPD$ – UPDATE statements (page 258)
• Table 7-3 contains a cross-reference between certain initialization parameters and equivalent hints that affect a single SQL statement. (pages 262-263)
• The text of SQL statements is normalized so that it is case-insensitive and white-space insensitive when SQL profiles are created.  This normalization allows the SQL profile to work even if changes in capitalization and white-space result in a different SQL_ID for the SQL statement (and can be set to normalize changes in constants on Oracle Database 11g). (page 271)
• The book states about SQL Profiles: “Simply put, a SQL profile stores a set of hints representing the adjustments to be performed by the query optimizer.” (page 275)
• When both a stored outline and SQL profile exist for the same SQL statement, the stored outline will be used rather than the SQL profile. (page 279)
• Procedure for editing a stored outline. (pages 286-288)
• Interesting three part test case that demonstrates the execution time difference for a parse intensive SQL statement that is repeatedly parsed with different literal values, with bind variables when cursors are closed, and with bind variables when cursors are held open. (pages 317-324)
• While other books advocate the use of non-default values for the CURSOR_SHARING parameter, this book provides the following warning, “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. Therefore, my advice is to carefully test applications when cursor sharing is enabled.”  The book does not mention that the CURSOR_SHARING value of SIMILAR is deprecated as of Oracle Database 11.1 (see Metalink (MOS) Doc ID: 1169017.1), however, this book was likely written long before that parameter value was officially deprecated. (page 325)
• The book correctly states the default value for the SESSION_CACHED_CURSORS parameter is dependent on the Oracle Database release version, while the Oracle Database documentation for 10.2 incorrectly states the default value, and other books simply pick one of the previously used default values (0, 20, or 50) when discussing the parameter. (page 327)
• The book correctly states that there is a change introduced in Oracle Database 9.2.0.5 that changed the default number of cached cursors from being dependent on the OPEN_CURSORS parameter to being dependent on the SESSION_CACHED_CURSORS parameter.  The book probably should have stressed that this change in behavior is only related to PL/SQL cursors. (page 331)
• Code demonstrations are provided in standard SQL, PL/SQL, C, and Java.
• Non-aggregated access paths that use 5 or fewer logical I/Os per row returned are reasonable; non-aggregated access paths that use between 5 and 15 logical I/Os per row returned are probably reasonable; non-aggregated access paths that use more than 15 to 20 logical I/Os per row returned are probably inefficient.  Read consistency and row prefetching (array fetch size, the ARRAYSIZE system variable in SQL*Plus) can distort these suggested targets. (pages 341, 343, 376-378)
• For a simple execution plan with a single full table scan, if the array fetch size is set to 1, reading each row will increment the number of consistent gets for the session by 1.  If the array fetch size is set to a value larger than the maximum number of rows in the table’s blocks, the number of consistent gets will be approximately the same as the number of blocks in the table. (page 343)
• In Oracle Database 11.1 and above, it is possible to use a virtual column as a partition key. (page 358)
• Characteristics of bitmap indexes: cannot be scanned in descending order (SORT ORDER BY operation is required), bitmap indexes always store NULL values (b*tree indexes do not store NULL values when all column values are NULL). (page 371)  The clustering factor of bitmap indexes is always set to the number of keys in the index. (page 375)  Cannot be used to enforce primary/unique key constraints, do not support row-level locking, space management problems due to updates in releases prior to 10.1, supports efficient index combine operations, supports star transformation. (page 378)  A single bitmap index entry might reference thousands of rows – modifying one of those rows may cause concurrency problems for other sessions that need to modify one of the other rows referenced by the same bitmap index entry; bitmap indexes generally work better than b*tree indexes with low cardinality data, but that does not mean that they work efficiently for extremely low cardinality data (unless bitmap combine operations significantly reduce the number of rowids that are used to fetch table rows). (page 379)
• As of Oracle Database 10.1 it is possible to specify table columns in index hints, which effectively forces the optimizer to select an index that references the specified column, ex: /*+ index_asc(t (t.id)) */.  (page 385)
• The book states about index-organized tables: “A row in an index-organized table is not referenced by a physical rowid. Instead, it is referenced by a logical rowid. This kind of rowid is composed of two parts: first, a guess referencing the block that contains the row (key) at the time it is inserted, and second, the value of the primary key.” Use ALTER INDEX i UPDATE BLOCK REFERENCES;  to update the guess references. (page 404)
• Changing an index definition from unique to non-unique could result in the structure of an execution plan changing, even though the index is still used, apparently to help take advantage of block prefetching. (page 423)
• Join elimination is introduced in Oracle Database 10.2, which allows the query optimizer to eliminate a join to a table, which then removes the linked table from the execution plan.  This is possible if no columns are selected from the linked table, and a validated foreign key links to the table from the other tables specified in the SQL statement. (page 448)
• The star transformation is a cost-based transformation, even when a STAR_TRANSFORMATION hint is specified in the SQL statement. (page 456)
• When materialized views are created, the ORDER BY clause is only respected during the initial materialized view creation because the ORDER BY clause is not included in the definition that is stored in the data dictionary. (page 461)
• Automatic query rewrite that allows the optimizer to take advantage of materialized views is a cost based decision that can be controlled by the REWRITE and NOREWRITE hints.  The materialized view must be altered to ENABLE QUERY REWRITE, and the QUERY_REWRITE_ENABLED parameter must be set to TRUE. (pages 462-463)
• The book states: “Full-text-match and partial-text-match query rewrites can be applied very quickly… In contrast, general query rewrite is much more powerful. The downside is that the overhead of applying it is much higher.” (page 466)
• Extensive coverage of materialized views, and improving the performance of those materialized views.  The book contains a warning (page 481) not to use ANSI join syntax with materialized views. (pages 459-481)
• The various results caches are described. (pages 481-489)
• Result cache limitations: “Queries that reference nondeterministic functions, sequences, and temporary tables are not cached.  Queries that violate read consistency are not cached. For example, the result set created by a session with outstanding transactions on the referenced tables cannot be cached.  Queries that reference data dictionary views are not cached.” (page 485)
• Various details of parallel processing (query, DML, and DDL) are described. (pages 489-513)
• “Parallel DML statements are disabled by default (be careful, this is the opposite of parallel queries).”  Use a command such as “ALTER SESSION ENABLE PARALLEL DML” or “ALTER SESSION FORCE PARALLEL DML PARALLEL 4” to specify the default degree of DML parallelism. (page 503)
• The book states: “Parallel DDL statements are enabled by default.” (page 505)
• Disabling parallel execution at the session level does not disable parallel execution for recursive SQL statements. (page 509)
• Parallel execution should not be enabled unless there is sufficient CPU/memory/IO bandwidth available and the query requires at least a minute to execute.  “It is important to stress that if these two conditions are not met, the performance could decrease instead of increase.” (page 509)
• Regarding the various parallel related hints, the book states: “It is very important to understand that the hints parallel and parallel_index do not force the query optimizer to use parallel processing. Instead, they override the degree of parallelism defined at the table or index level.” (page 509)
• The book describes direct path insert. (pages 513-517)
• Regarding the reduction of redo generation, the book states: “Even if minimal logging is not used, a database running in noarchivelog mode doesn’t generate redo for direct-path inserts.” (page 516)
• Enabling array fetching (row prefetching) in PL/SQL (pages 518-519), OCI (pages 519-520), JDBC (pages 520-521), ODP.NET (page 521), SQL*Plus (page 521)
• Enabling array INSERT in PL/SQL (pages 523-524), OCI (page 524), JDBC (page 524), ODP.NET (page 524-525)
• The book provides a test case that demonstrates performance differences caused by the relative position of columns in a table’s definition.  The book states: “So whenever a row has more than a few columns, a column near the beginning of the row might be located much faster than a column near the end of the row.” (page 528)
• Selecting the appropriate datatype for columns. (pages 529-535)
• Row chaining and row migration: row migration happens when there is not enough free space in a block for a row, so the entire row (leaving behind forwarding information) is moved to another block; row chaining happens when a row contains more than 255 columns, or where a row exceeds the full storage capacity of a block. (pages 535-538)
• Block contention (pages 539-546)
• Data compression (pages 546-548)

—

Data Dictionary Views/Tables:

• V$SQLAREA (page 18)
• V$SQL (pages 18, 27)
• V$SQL_SHARED_CURSOR (page 21)
• V$SQL_BIND_METADATA (page 23)
• V$SQL_CS_STATISTICS, V$SQL_CS_SELECTIVITY, V$SQL_CS_HISTOGRAM (page 29)
• V$SYS_TIME_MODEL, V$SESS_TIME_MODEL (page 39)
• V$SESSION (pages 45, 501, 503)
• DBA_ENABLED_TRACES (page 67)
• V$PARAMETER (page 70)
• V$BGPROCESS, V$DIAG_INFO, V$DISPATCHER, V$SHARED_SERVER, V$PX_PROCESS (page 71)
• V$PROCESS (page 73)
• SYS.AUX_STATS$ (page 112)
• V$FILESTAT (page 114)
• USER_JOBS (pages 115, 474)
• USER_SCHEDULER_JOBS (page 115)
• USER_TAB_STATISTICS, USER_TABLES, USER_TAB_STATISTICS, USER_TAB_PARTITIONS, USER_TAB_STATISTICS, USER_TAB_SUBPARTITIONS, USER_TAB_COL_STATISTICS, USER_TAB_HISTOGRAMS, USER_PART_COL_STATISTICS, USER_PART_HISTOGRAMS, USER_SUBPART_COL_STATISTICS, USER_SUBPART_HISTOGRAMS, USER_IND_STATISTICS, USER_INDEXES, USER_IND_STATISTICS, USER_IND_PARTITIONS, USER_IND_STATISTICS, USER_IND_SUBPARTITIONS (page 120)
• USER_STAT_EXTENSIONS, USER_TAB_COLS (page 132)
• SYS.USER$, SYS.REGISTRY$, SYS.REGISTRY$SCHEMAS (page 138)
• ALL_TAB_MODIFICATIONS, DBA_TAB_MODIFICATIONS, USER_TAB_MODIFICATIONS (page 140)
• SYS.COL_USAGE$ (page 145)
• SYS.COL$, SYS.OBJ$, SYS.USER$ (page 146)
• SYS.OPTSTAT_HIST_CONTROL$ (page 147)
• DBA_TAB_STAT_PREFS (page 150)
• USER_TAB_PENDING_STATS, USER_IND_PENDING_STATS (page 151)
• USER_COL_PENDING_STATS, USER_TAB_HISTGRM_PENDING_STATS, DBA_SCHEDULER_JOBS, DBA_SCHEDULER_PROGRAMS, DBA_SCHEDULER_WINGROUP_MEMBERS, DBA_SCHEDULER_WINDOWS (page 152)
• DBA_AUTOTASK_TASK, DBA_AUTOTASK_WINDOW_CLIENTS (page 154)
• DBA_TAB_STATS_HISTORY, USER_TAB_STATS_HISTORY (page 165)
• DBA_OPTSTAT_OPERATIONS  (page 167)
• V$PARAMETER_VALID_VALUES (page 174)
• V$PGASTAT (page 191)
• PLAN_TABLE (page 197, 532)
• V$SQL_PLAN, V$SQL_PLAN_STATISTICS (page 199)
• V$SQL_WORKAREA, V$SQL_PLAN_STATISTICS_ALL (page 200)
• DBA_HIST_SQL_PLAN (page 203)
• STATS$SQL_PLAN (page 204)
• DBA_HIST_SQLTEXT, V$DATABASE (page 219)
• V$SQL_HINT (page 256)
• V$SYS_OPTIMIZER_ENV, V$SES_OPTIMIZER_ENV (page 263)
• V$SQL_OPTIMIZER_ENV (page 264)
• DBA_SQL_PROFILES (page 271)
• SYS.SQLPROF$ATTR (page 275)
• SYS.SQLPROF$ (page 276)
• USER_OUTLINES, USER_OUTLINE_HINTS (page 282)
• OUTLN.OL$,OUTLN.OL$HINTS,OUTLN.OL$NODES (page 284)
• DBA_SQL_PLAN_BASELINES (page 296)
• SYS.SQLOBJ$DATA, SYS.SQLOBJ$ (page 298)
• DBA_SQL_MANAGEMENT_CONFIG (page 303)
• V$SESSTAT, V$STATNAME (page 320)
• USER_TAB_PARTITIONS (pages 353, 355, 366)
• USER_TAB_SUBPARTITIONS (page 366)
• USER_TAB_COLUMNS (page 396)
• INDEX_STATS (page 399)
• V$SQL_WORKAREA_ACTIVE (page 438)
• USER_MVIEWS (page 471)
• ALL_SUMDELTA, USER_MVIEW_LOGS (page 475)
• MV_CAPABILITIES_TABLE (pages 476, 480)
• V$RESULT_CACHE_OBJECTS, V$SGASTAT, V$RESULT_CACHE_DEPENDENCY, V$RESULT_CACHE_MEMORY, V$RESULT_CACHE_STATISTICS (page 484)
• V$PX_PROCESS_SYSSTAT (page 495)
• V$SYSSTAT (page 500)
• V$PQ_TQSTAT (page 511)
• DBA_TABLES (page 538)
• V$WAITSTAT (page 539)
• DBA_EXTENTS (page 542)

—

Initialization Parameters:

• OPTIMIZER_MODE (pages 20, 173)
• SQL_TRACE (page 63)
• TIMED_STATISTICS, STATISTICS_LEVEL, MAX_DUMP_FILE_SIZE, USER_DUMP_DEST, BACKGROUND_DUMP_DEST (page 70)
• DIAGNOSTIC_DEST (page 71)
• TRACEFILE_IDENTIFIER, _TRACE_FILES_PUBLIC (page 73)
• _OPTIMIZER_COST_MODEL (page 111)
• OPTIMIZER_USE_PENDING_STATISTICS (page 151)
• OPTIMIZER_FEATURES_ENABLE (pages 174, 179)
• DB_FILE_MULTIBLOCK_READ_COUNT, DB_BLOCK_SIZE (page 175)
• OPTIMIZER_DYNAMIC_SAMPLING (page 179)
• OPTIMIZER_INDEX_COST_ADJ (pages 111, 183)
• OPTIMIZER_INDEX_CACHING (pages 111, 186)
• OPTIMIZER_SECURE_VIEW_MERGING (page 187)
• WORKAREA_SIZE_POLICY (page 189)
• PGA_AGGREGATE_TARGET, MEMORY_TARGET, MEMORY_MAX_TARGET (page 190)
• SORT_AREA_SIZE (page 191)
• SORT_AREA_RETAINED_SIZE, HASH_AREA_SIZE (page 192)
• BITMAP_MERGE_AREA_SIZE (page 193)
• SQLTUNE_CATEGORY (page 273)
• CREATE_STORED_OUTLINES (281)
• USE_STORED_OUTLINES (page 284)
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES (page 292)
• OPTIMIZER_USE_SQL_PLAN_BASELINES (page 301)
• OPEN_CURSORS (page 322)
• CURSOR_SHARING (page 325)
• SESSION_CACHED_CURSORS (page 327)
• QUERY_REWRITE_ENABLED (pages 391, 463)
• QUERY_REWRITE_INTEGRITY (pages 391, 469)
• NLS_COMP, NLS_SORT (page 392)
• _B_TREE_BITMAP_PLANS (page 402)
• STAR_TRANSFORMATION_ENABLED (page 452)
• RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MODE (pages 484, 485)
• RESULT_CACHE_MAX_RESULT, RESULT_CACHE_REMOTE_EXPIRATION (page 485)
• CLIENT_RESULT_CACHE_SIZE, CLIENT_RESULT_CACHE_LAG (PAGE 487)
• OCI_RESULT_CACHE_MAX_SIZE, OCI_RESULT_CACHE_MAX_RSET_SIZE, OCI_RESULT_CACHE_MAX_RSET_ROWS (in the client’s sqlnet.ora file, page 488)
• PARALLEL_MIN_SERVERS (page 494)
• PARALLEL_MAX_SERVERS, CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, PARALLEL_EXECUTION_MESSAGE_SIZE (page 495)
• PARALLEL_THREADS_PER_CPU, PARALLEL_MIN_PERCENT (page 497)
• PARALLEL_ADAPTIVE_MULTI_USER (page 498)
• PLSQL_OPTIMIZE_LEVEL (page 518)
• NLS_NUMERIC_CHARACTERS (page 530)

—

SQL Hints:

• NO_CPU_COSTING (page 111)
• OPTIMIZER_FEATURES_ENABLE (page 175)
• DYNAMIC_SAMPLING (page 179)
• GATHER_PLAN_STATISTICS (page 199)
• FULL (page 254)
• QB_NAME (page 257)
• CURSOR_SHARING, OPT_PARAM (page 262)
• ALL_ROWS, FIRST_ROWS(n), RULE (page 263)
• RESULT_CACHE (pages 263, 482)
• NO_RESULT_CACHE (pages 263, 484)
• IGNORE_OPTIM_EMBEDDED_HINTS (page 276)
• OPT_ESTIMATE (page 277)
• TABLE_STATS, COLUMN_STATS, INDEX_STATS (page 278)
• CURSOR_SHARING_EXACT (page 325)
• INDEX (pages 369, 419)
• INDEX_FFS, INDEX_ASC, INDEX_DESC (page 370)
• INDEX_SS (page 397)
• INDEX_SS_ASC, INDEX_SS_DESC (page 398)
• INDEX_COMBINE (page 400, 402)
• LEADING, USE_NL, FULL, ORDERED (page 419)
• NLJ_BATCHING, NO_NLJ_BATCHING (page 423)
• USE_MERGE (page 425)
• USE_HASH (page 435)
• INDEX_JOIN (page 439)
• SWAP_JOIN_INPUTS (page 440)
• PQ_DISTRIBUTE (page 445)
• NO_OUTER_JOIN_TO_INNER (page 449)
• NO_UNNEST (page 450)
• STAR_TRANSFORMATION, NO_STAR_TRANSFORMATION (page 456)
• REWRITE, NO_REWRITE (page 462)
• PARALLEL, NO_PARALLEL, NOPARALLEL, PARALLEL_INDEX, NOPARALLEL_INDEX (page 497)
• APPEND (page 513)

—

Built-In Functions/Procedures:

• USERENV (page 45)
• SYS_CONTEXT (pages 46, 268)
• DBMS_SESSION.SET_SQL_TRACE, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (page 63)
• DBMS_SYSTEM.SET_EV (note: DBMS_SYSTEM package function by default can only be executed by the SYS user, page 64)
• DBMS_SUPPORT.START_TRACE_IN_SESSION (page 65)
• DBMS_MONITOR.SESSION_TRACE_ENABLE, DBMS_MONITOR.SESSION_TRACE_DISABLE (page 66)
• DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE, DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE (page 67)
• DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE, DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (page 68)
• DBMS_MONITOR.DATABASE_TRACE_ENABLE, DBMS_MONITOR.DATABASE_TRACE_DISABLE (page 69)
• DBMS_STATS.DELETE_SYSTEM_STATS, DBMS_STATS.SET_SYSTEM_STATS (page 117)
• UTL_RAW.CAST_TO_NUMBER, UTL_RAW.CAST_TO_BINARY_DOUBLE, UTL_RAW.CAST_TO_BINARY_FLOAT, UTL_RAW.CAST_TO_BINARY_INTEGER, UTL_RAW.CAST_TO_NVARCHAR2, UTL_RAW.CAST_TO_RAW, UTL_RAW.CAST_TO_VARCHAR2, DBMS_STATS.CONVERT_RAW_VALUE, DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR, DBMS_STATS.CONVERT_RAW_VALUE_ROWID (page 123)
• NTILE (page 127)
• DBMS_STATS.CREATE_EXTENDED_STATS (page 131)
• SYS_OP_COMBINED_HASH (page 132)
• DBMS_ROWID.ROWID_BLOCK_NUMBER, DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO (page 135)
• DBMS_STATS.SET_PARAM (page 147)
• DBMS_STATS.SET_GLOBAL_PREFS, DBMS_STATS.SET_DATABASE_PREFS, DBMS_STATS.SET_SCHEMA_PREFS, DBMS_STATS.SET_TABLE_PREFS (page 148)
• DBMS_STATS.GET_PREFS (page 149)
• DBMS_SCHEDULER.ENABLE, DBMS_SCHEDULER.DISABLE (page 153)
• DBMS_AUTO_TASK_ADMIN.ENABLE, DBMS_AUTO_TASK_ADMIN.DISABLE, DBMS_STATS.LOCK_SCHEMA_STATS (page 155)
• DBMS_STATS.UNLOCK_SCHEMA_STATS (page 156)
• DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB, DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY, DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING (page 160)
• DBMS_STATS.GET_STATS_HISTORY_RETENTION, DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (page 164)
• DBMS_STATS.PURGE_STATS (page 165)
• DBMS_STATS.RESTORE_SCHEMA_STATS, DBMS_STATS.CREATE_STAT_TABLE, DBMS_STATS.DROP_STAT_TABLE (page 166)
• DBMS_UTILITY.GET_TIME (page 178)
• DBMS_XPLAN.DISPLAY, TABLE (page 197)
• DBMS_XPLAN.DISPLAY_CURSOR (pages 201, 217)
• DBMS_XPLAN.DISPLAY_AWR (pages 204, 219)
• DBMS_XPLAN.DISPLAY(page 213)
• DBMS_OUTLN.CREATE_OUTLINE (page 282)
• DBMS_OUTLN.UPDATE_BY_CAT (page 283)
• DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES, DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE (page 287)
• DBMS_OUTLN.DROP_BY_CAT (page 289)
• DBMS_OUTLN.CLEAR_USED (page 290)
• DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (page 294)
• DBMS_SPM.LOAD_PLANS_FROM_SQLSET, DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (page 297)
• EXTRACTVALUE, XMLSEQUENCE, XMLTYPE (page 298)
• DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (page 299)
• DBMS_SPM.ALTER_SQL_PLAN_BASELINE (page 300)
• DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, DBMS_SPM.UNPACK_STGTAB_BASELINE (page 301)
• DBMS_SPM.DROP_SQL_PLAN_BASELINE, DBMS_SPM.CONFIGURE (page 303)
• DBMS_MVIEW.EXPLAIN_REWRITE (page 470)
• DBMS_MVIEW.EXPLAIN_MVIEW (page 471)
• DBMS_MVIEW.REFRESH, DBMS_MVIEW.REFRESH_ALL_MVIEWS, DBMS_MVIEW.REFRESH_DEPENDENT (page 473)
• DBMS_REFRESH.REFRESH (page 474)
• DBMS_RESULT_CACHE.BYPASS , DBMS_RESULT_CACHE.FLUSH , DBMS_RESULT_CACHE.INVALIDATE , DBMS_RESULT_CACHE.INVALIDATE_OBJECT, DBMS_RESULT_CACHE.MEMORY_REPORT, DBMS_RESULT_CACHE.STATUS (page 485)
• VSIZE (page 533)

—

Packages:

• DBMS_SQL (page 17, 332)
• DBMS_SESSION (page 45)
• DBMS_APPLICATION_INFO (page 46)
• DBMS_PROFILER (page 100)
• DBMS_STATS (pages 109, 136)
• DBMS_XPLAN (page 208)
• DBMS_SQLTUNE (pages 267-279)

—

Execution Plans:

• TABLE ACCESS BY INDEX ROWID operation (page 25)
• Common columns found in an execution plan (pages 209-210)
• Query Block Name/Object Alias, Predicate Information (page 211)
• Column Projection, Note (page 212)
• Format parameters for DBMS_XPLAN (pages 213-214, 218)
• Permissions to display execution plans (197, 217, 219)
• Basic rules for parent and child operations in execution plans (pages 221-222)
• COUNT STOPKEY operation (pages 224-225)
• FILTER operation (pages 225, 229)
• UNION-ALL operation (page 226)
• NESTED LOOPS operation (page 228)
• UPDATE operation (page 232)
• CONNECT BY WITH FILTERING operation (page 233)
• Identifying wrong estimated cardinality problems (page 241)
• Displaying SQL Plan Baselines (page 297)
• TABLE ACCESS FULL operation (page 350)
• PARTITION RANGE SINGLE operation (pages 354, 355)
• PARTITION RANGE ITERATOR (page 356)
• PARTITION RANGE INLIST (page 357)
• PARTITION RANGE ALL (page 358)
• PARTITION RANGE EMPTY, PARTITION RANGE OR (page 359)
• PARTITION RANGE ITERATOR, PARTITION RANGE SUBQUERY (page 361)
• PART JOIN FILTER CREATE, PARTITION RANGE JOIN-FILTER (page 362)
• PARTITION RANGE MULTI-COLUMN (page 364)
• PARTITION LIST SINGLE (page 367)
• INDEX FULL SCAN (page 369)
• INDEX FAST FULL SCAN (page 370)
• INDEX FULL SCAN DESCENDING, SORT AGGREGATE, BITMAP CONVERSION TO ROWIDS, BITMAP INDEX FULL SCAN (page 371)
• BITMAP CONVERSION TO ROWIDS, BITMAP INDEX FAST FULL SCAN (page 372)
• TABLE ACCESS BY USER ROWID (page 373)
• INLIST ITERATOR (page 374)
• TABLE ACCESS BY INDEX ROWID (page 376)
• INDEX UNIQUE SCAN (page 381)
• INDEX RANGE SCAN, INDEX RANGE SCAN DESCENDING (page 382)
• BITMAP INDEX SINGLE VALUE, BITMAP CONVERSION TO ROWIDS (page 383)
• BITMAP INDEX RANGE SCAN (page 386)
• INDEX FULL SCAN (MIN/MAX), INDEX RANGE SCAN (MIN/MAX) (page 389)
• NLSSORT (page 394)
• INDEX SKIP SCAN (page 397)
• INDEX SKIP SCAN DESCENDING (page 398)
• BITMAP AND, BITMAP OR (page 400)
• BITMAP MINUS (page 401)
• BITMAP CONVERSION FROM ROWIDS (page 402)
• TABLE ACCESS BY GLOBAL INDEX ROWID, TABLE ACCESS BY LOCAL INDEX ROWID (page 406)
• TABLE ACCESS HASH (page 407)
• CONCATENATION (page 408)
• MERGE JOIN, SORT JOIN (page 425)
• 0Mem column, Used-Mem column (page 429)
• 1Mem column, Used-Tmp column (pages 433-434)
• INDEX$_JOIN$ (page 439)
• HASH JOIN RIGHT OUTER (page 440)
• PARTITION HASH ALL, PX PARTITION HASH ALL (page 445)
• PX SEND QC (RANDOM) (pages 445, 492; page 493 for methods other than QC RANDOM)
• HASH JOIN BUFFERED, PX SEND PARTITION (KEY) (page 447)
• PX BLOCK ITERATOR (pages 447, 491)
• HASH JOIN SEMI (page 450)
• BUFFER SORT, BITMAP MERGE, BITMAP KEY ITERATION (page 453)
• BITMAP CONVERSION TO ROWIDS (page 454)
• TEMP TABLE TRANSFORMATION, LOAD AS SELECT (page 455)
• MAT_VIEW REWRITE ACCESS FULL (page 463)
• RESULT CACHE (page 483)
• TQ column (page 491)
• IN-OUT column (pages 491, 493)
• PX COORDINATOR (page 494)

—

Oracle Error Numbers:

• ORA-13859: action cannot be specified without the module specification (page 67)
• ORA-00922: missing or invalid option (page 80)
• ORA-20005: object statistics are locked (pages 142, 156)
• ORA-01013: user requested cancel of current operation (page 153)
• ORA-38029: object statistics are locked (page 157)
• ORA-03113: TNS:end-of-file on communication channel (page 175)
• ORA-01039: insufficient privileges on underlying objects of the view (page 196)
• ORA-00931: missing identifier (page 283)
• ORA-18009: one or more outline system tables do not exist (page 286)
• ORA-07445, ORA-00600 (page 452)
• ORA-23538: cannot explicitly refresh a NEVER REFRESH materialized view (page 472)
• ORA-12026: invalid filter column detected (page 481)
• ORA-12827: insufficient parallel query slaves available (page 497)
• ORA-12838: cannot read/modify an object after modifying it in parallel (pages 513, 516)
• ORA-26040: data block was loaded using the NOLOGGING option (page 514)
• ORA-01722: invalid number (page 530)