Troubleshooting Oracle Performance [Hardcover]

Christian Antognini (Author)

Book Description

ISBN-10: 1590599179 | ISBN-13: 978-1590599174 | Publication Date: June 23, 2008 | Edition: 1

A fantastic addition to Apress's ever-growing line of performance optimization titles for Oracle developers and database administrators written by internationally acclaimed experts. This book forges a strong link in the chain from performance problem to performance solution by giving readers a systematic method for solving problems once they've been identified. Christian Antognini's emphasis on planning for performance as an application requirement represent clear and fresh thinking on the problem of database performance. His use of solution roadmaps to provide a repeatable path from identified problem to solution make his book genuinely helpful to readers who want more than a random collection of performance tips. Readers of Jonathan Lewis, Tom Kyte, Craig Shallahamer will welcome the chance to add Christian Antognini to their shelves.

Editorial Reviews

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, the integration of databases with Java applications, to the query optimizer and basically everything else related to performance management and tuning. He is currently working as a senior consultant and trainer at Trivadis AG (http://www.trivadis.com) 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 (http://oaktable.net).

Product Details

• Hardcover: 616 pages
• Publisher: Apress; 1 edition (June 23, 2008)
• Language: English
• ISBN-10: 1590599179
• ISBN-13: 978-1590599174
• Product Dimensions: 9.3 x 7.2 x 1.4 inches
• Shipping Weight: 2.5 pounds (View shipping rates and policies)
• Average Customer Review: 4.6 out of 5 stars  See all reviews (8 customer reviews)
• Amazon Best Sellers Rank: #594,154 in Books (See Top 100 in Books)
  • #94 in Books > Computers & Technology > Databases > Relational Databases

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

15 of 16 people found the following review helpful:

5.0 out of 5 stars Most Thorough, Yet Compact Performance Tuning Book 9i-11g, September 11, 2008

By 

Charles Hooper (Michigan, USA) - See all my reviews
(REAL NAME)   

Amazon Verified Purchase

This review is from: Troubleshooting Oracle Performance (Hardcover)

"Troubleshooting Oracle Performance" is the most thorough, yet physically compact book covering performance tuning with Oracle 9i R2 through 11g R1 on the market. It is quite clear that a great deal of effort was made by the author to carefully verify the tips and test results contained in the book and to organize the material in a logical progression, thus building a bridge between the reader's current understanding to the understanding of complex tuning approaches.

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 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 ¨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 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... Read more ›

5 of 5 people found the following review helpful:

5.0 out of 5 stars A MUST for Developers and DBAs, September 30, 2008

By 

Eric L. Yen (Covia, CA USA) - See all my reviews

This review is from: Troubleshooting Oracle Performance (Hardcover)

BOTH Developers and DBAs can learn from this book. Along the line of "Optimizing Oracle Performance" by Cary Milsap; this books shows by clear examples how to Identify Performance Problems (Chapter 3) and how to solve those problems (the remainder of the book).

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.

2 of 2 people found the following review helpful:

5.0 out of 5 stars Excellent work, December 10, 2008

By 

Martin Decker "Oracle Certified Master" (München, Deutschland) - See all my reviews
(REAL NAME)   

This review is from: Troubleshooting Oracle Performance (Hardcover)

This book is exremely useful for Oracle Administrators and Developers alike. It is easy to understand and contains valuable information for everybody interested in understanding Oracle performance issues. The author demonstrates an excellent method to identify wrong Optimizer estimates that lead to suboptimal execution plans.

The author shares dozens of "best-practices" and advices from his personal consulting experience as well es detailed information which is not available in Oracle documentation.

My personal highlight was the introduction to a sophisticated, yet easy-to-use SQL Trace Analyzer TVD$XTAT which overcomes the limits of tkprof for SQL Response Time Analysis.

This book is a must-have for every Oracle professional.

Martin Decker
ora-solutions.net