Troubleshooting Oracle Performance 2nd ed. Edition

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)* 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. As such, there is a good chance that this review will exceed the length limit imposed by Amazon - see my Oracle blog for the full 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, ash_top_objects.sql, and ash_top_plsql.sql Pg 136 (1/9/15: now downloadable)-- search_space.sql Pg 169 (11/6/14: now downloadable)-- incremental_stats.sql Pg 255 (8/22/14: now downloadable)-- copy_table_stats.sql Pg 256 (8/22/14: now downloadable)-- optimizer_index_cost_adj.sql Pg 288 (8/22/14: now downloadable)-- display_statspack.sql Pg 306 (11/6/14: now downloadable)-- dynamic_in_conditions.sql Pg 499 (11/6/14: now downloadable)-- fbi_cs.sql Pg 506 (11/6/14: now downloadable)-- reserve_index.sql should be reverse_index.sql Pg 671 (8/22/14: confirmed)* 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. (8/22/14: confirmed)* 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.* 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) (8/22/14: confirmed)* 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) (8/22/14: confirmed)* 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). (8/22/14: After discussion with the book author, determined that accessing V$METRIC, V$METRICGROUP, and V$METRICNAME does not require a Diagnostic Pack license)* 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. (8/22/14: confirmed)* 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. (8/22/14: confirmed)* 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) (8/22/14: confirmed)* 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 (8/22/14: confirmed)* 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) (8/22/14: confirmed)* 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 (8/22/14: confirmed)* 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.txt). (8/22/14: After discussion with the book author, "dynamic" implicitly implies execution in PL/SQL (rather than ad hoc SQL statements that might be submitted from client-side tools), so the statement in the book is correct, and does match the output of the author's script. The author's script was attempting to demonstrate how executing SQL in a PL/SQL block could cause a difference in the parsing and execution of the submitted SQL statement)---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

This is my second book bought for Oracle performance, following Cary Millsap's Optimizing Oracle Performance. These two books may be all I'll ever need on this subject. Millsap wrote a preface to this book.Anytime I see a technical book come out with a second or subsequent edition, that's usually a good sign. This one is in its second edition.The index is terrible. Spend the extra $5 or $10 to download the PDF from the publisher. Then grab the source code from the author's web site. You won't be able to follow the book without the code. Having both the PDF and the code makes a paper copy almost irrelevant, but the quality of the printing is good, except for the index.Antognini's knowledge of the cost based optimizer makes me think he was involved in developing it.If you're searching for books on Oracle tuning, you may overlook this one, because it doesn't have the word "tuning" in the title. Get this one. The third edition needs to be named "Oracle Performance and Tuning", for SEO, and to follow in the footsteps of Adrian Cockcroft.

What an amazing book. If you are an oracle professional or you are doing anything related to oracle databases, this is a must read. Christian provides a very detailed coverage of the topic of oracle database engine, internals, optimizer extremely well. This should be kept in any oracle technologist's book shelf. I do not want to go into details on the topics covered, Charles Hooper's review does that job well. Infact Charles' review gives a good overview of the book if you have not read it (or) a good recap if you are done reading the book.

Excellent and very complete reference of the different ways of tuning Oracle applications. As opposed to general SQL tuning books, this one covers different components and strategies like choosing the right data types, data model strategies, manipulation of DBMS_STATS package and other things that are frequently but incorrectly labelled as "instance tuning". Christian Antognini deserves special kudos for chapters about tuning without the diagnostic package. It is the greatest description of tuning mechanisms in Oracle RDBMS available on the market. I love the book.

Very detailed and up-to-date. Lots of information and helpful tips!

Christian worked so hard to write this book so ALL of us DBA's can havea much easier time at work. Christian levels with your level of expertise in making sure that the concepts are understood well andprovides sufficient detail and guidelines so you can do something useful with them at your workplace.Thanks, Chris, for providing this very useful book to the Oracle tuning community of DBAs!

If you work on Oracle Performance issues, this book belongs in your library. -mwf What a great opportunity to join IOUG to get a discount code and buy this directly from Apress!

Worst

Good book

I am always sceptical about 5 stars reviews, but this book really is excellent. It explained a lot of things I observed over the years, and now they actually make sense:)

Der 2008 erschienenen ersten Auflage von Christian Antogninis englischsprachigem Buch Troubleshooting Oracle Performance habe ich vor zwei Jahren in meiner Amazon-Bewertung 5 Sterne gegeben – und das scheint mir auch immer noch angemessen, stellt mich aber vor das Problem, wie ich die zweite Ausgabe in dieser Skala bewerten soll, denn die ist aus meiner Sicht noch mal deutlich besser als ihr Vorgänger. Der Grund dafür ist, dass der Autor für diese zweite Auflage nicht nur marginale Korrekturen und Aktualisierungen hinsichtlich der inzwischen verfügbaren Oracle-Releases (inklusive 12.1) durchgeführt, sondern gleich mehrere neue Kapitel ergänzt hat – insbesondere im ersten Teil des Buches, in dem er eine Taxonomie möglicher Performance-Problemfälle liefert (Analysis of Reproducible Problems, Real-Time Analysis of Irreproducible Problems, Postmortem Analysis of Irreproducible Problems) und jeweils erläutert, wie mit den Situationen umzugehen ist. Ein weiteres neues Kapitel beschäftigt sich mit dem Optimizer und insbesondere mit den Transformationen, die dieser verwenden kann, um ein auszuführendes SQL-Statement intern in etwas semantisch Äquivalentes umzuformen, mit dem er besser zurecht kommt. Viele dieser Transformationen sind mehr oder minder gut bekannt, aber ich kenne keine vergleichbare Übersicht zum Thema und wüsste auch nicht, dass sie anderswo so klar und verständlich erläutert wären. Ein weiteres wichtiges Kapitel ist das zehnte, das sich mit der Interpretation von Ausführungsplänen beschäftigt, und das ebenfalls gegenüber der ersten Auflage noch einmal gründlich überarbeitet wurde – und bereits diese ältere Fassung war meiner Einschätzung nach die bis dahin beste Erläuterung zur Interpretation des Execution Plans.Darüber hinaus liefert der Band umfassende Erläuterungen zur syntaktischen Optimierung von SQL Queries, zum Einsatz von Indizes, Materialized Views, Parallel Operations, zum Plan Management, zur Optimierung des physikalischen Designs, zur sinnvollen Verwendung von System- und Objektstatistiken etc. Um es kürzer zu machen: mir fällt kein wichtiger Bereich der Performance-Optimierung für Oracle-SQL-Operationen ein, zu dem nicht die wichtigsten Grundlagen und maßgeblichen Details aufgeführt werden würden. Sehr nützlich ist auch, dass die Struktur der Kapitel dafür sorgt, dass zu jedem Feature nicht nur die Funktion, sondern auch seine Einsatzmöglichkeiten und seine Einschränkungen aufgeführt werden.Insgesamt handelt es sich aus meiner Sicht um ein herausragendes Buch, das ich gerne jedem Oracle-Nutzer empfehle, der sich ernsthaft mit Performance-Fragestellungen beschäftigen möchte.

Excellent Book

If you want to learn oracle optimization this is your book.