- Series: Expert's Voice in Oracle
- Paperback: 586 pages
- Publisher: Apress; 1st ed. edition (June 23, 2008)
- Language: English
- ISBN-10: 1590599179
- ISBN-13: 978-1590599174
- Product Dimensions: 7 x 1.4 x 10 inches
- Shipping Weight: 2.5 pounds (View shipping rates and policies)
- Average Customer Review: 9 customer reviews
- Amazon Best Sellers Rank: #2,256,270 in Books (See Top 100 in Books)
Enter your mobile number or email address below and we'll send you a link to download the free Kindle App. Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required.
To get the free app, enter your mobile phone number.
Troubleshooting Oracle Performance (Expert's Voice in Oracle) 1st ed. Edition
Use the Amazon App to scan ISBNs and compare prices.
Customers who viewed this item also viewed
Customers who bought this item also bought
About the Author
Since 1995, Christian Antognini has been focusing on understanding how the Oracle database engine works. His main interests range from logical and physical database design, to the integration of databases with Java applications, the query optimizer, and basically everything else related to performance management and tuning. He is working as a senior consultant and trainer at Trivadis AG in Z rich, Switzerland. If he is not helping one of his customers to get the most out of Oracle, he is somewhere lecturing on optimization or new Oracle database features for developers. He is member of the Trivadis Performance Team and of the OakTable Network).
Top customer reviews
There was a problem filtering reviews right now. Please try again later.
What this book accomplishes, which most other performance tuning books seem to miss, is to indicate which performance tuning features are available in each Oracle release (and which are available at no additional licensing cost) as the various performance tuning approaches are discussed. Not only does the book indicate when a feature would be appropriate, but also potential problems ("Pitfalls and Fallacies") associated with each feature.
The depth of coverage of Oracle 11g R1 features is surprisingly thorough given the short amount of time which that version has been on the market. A little more detail in a couple areas, such as hacking stored outlines, would have been helpful; at the same time, everyone who reads the book might have a different opinion of what needed additional detail, the book could have grown to 2500 or more pages, and likely would have been obsolete by the time it was published.
The Apress Roadmap on the back cover of the book indicates that this book should be read before "Forecasting Oracle Performance", "Expert Oracle Database Architecture", and "Cost-Based Oracle Fundamentals". The "Troubleshooting Oracle Performance" book seems to assume that the Oracle database concepts are well understood. As such, it is probably wise to read "Expert Oracle Database Architecture" first, followed by this book, "Cost-Based Oracle Fundamentals", and finally "Forecasting Oracle Performance".
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 126.96.36.199, 188.8.131.52, 10.2.0.3, 10.2.0.4) that are required so that the reader is able to take advantage of the feature ¨C 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' 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 at "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 184.108.40.206, 10.1.0.5, 10.2.0.4, and 220.127.116.11. 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, 18.104.22.168, 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 ¨C 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 ¨C 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 ¨C 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 ¨C 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 ¨C 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 ¨C 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 ¨C 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$ ¨C CREATE INDEX statements; DEL$ ¨C DELETE statements; INS$ ¨C INSERT statements; MISC$ ¨C Miscellaneous SQL statements like LOCK TABLE; MRG$ ¨C MERGE statements; SEL$ ¨C SELECT statements; SET$ ¨C Set operators like UNION and MINUS; UPD$ ¨C 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 22.214.171.124 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 ¨C 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
tuning specialist most likely would refer this book again and again.
I did not find any new techniques, the same old tkprof, explain plan ... If someone wants a real tuning book which you can put to use, try Oracle Performance Firefighting by Craig Shallahamer .
The recent version of 11g has added lots of new functionality such as database replay, sql access advisor, sucurefiles, flashback data archiving, tablespace encryption, data masking and so much more. IT sometimes feels that version 11g is a totally new database compared to version 10.x.
The great thing about this book which others fail is that it covers all versions of Oracle from version 9i to 11gR1. So even if you are using an older version this book still is extremely valuable to own. It will actually save you money if you are using an older version of Oracle because it can be used as you gradually upgrade to newer versions of Oracle. Another reason to buy it!
This book assumes that you already have a good knowledge of using Oracle and goes right into many different scenarios of using different front-end technologies where performance issues can happen and hot to step-by-step figure out where your problem may be coming from.
The author focuses on tracing database calls, profiling PL/SQL code, how to review workload statistics and object statistics, how to configure your query optimizer, and one of the most useful sections of the book is Chapter 6 where the author focus on many different execution plans. I also liked Chapter 10 where the author goes through all the different types of SQL commands (joins, nested loops, merge joins, hash joins, etc.) and how they can be improved and optimized.
For the size of the book (500+), the author really gives you some great material that can be used for many years of using Oracle. It can be used for the beginner Oracle user (after they get their feet wet) for understanding how to write SQL the optimized way, for the Application Oracle developer and for the DBA as well.
I cant see how this book cannot be in your library. A must buy!
Developers can learn how to Instrument code to help the tuning process.
DBAs can then trace the code and find the bottleneck/waits.
This book bridges the gap between Developers and DBAs and points out indirectly that Performance is a teams effort and solution.