This second-edition reference book is recommended to anyone who works with the Oracle Database. It is a very useful guide, not only for Oracle developers and Oracle professionals, but also for system and database administrators, application developers, and performance analysts. Readers will learn how to answer questions about application software, such as how good it is, how fast it is, how many users it can support, how to assess response time, and what its limits are. Issues related to performance, performance testing, and optimizing Oracle software applications are also discussed, analyzed, and richly illustrated.

Regarding the book’s structure, it consists of 16 chapters grouped into four parts, plus an appendix. A brief introduction provides the reader with information about the structure of the book, the Oracle versions covered, the book’s online resources, and the basic elements that led to the emergence of a second edition. A novelty of the book is that it addresses the latest versions of Oracle: Oracle Database 11g Release 2 and Oracle Database 12c Release 1.

In the first part of the book, the author presents with great accuracy the key elements related to software performance. Chapter 1 is dedicated to performance testing as part of software engineering, highlighting the elements of designing for performance and identifying performance problems. I took notes on the advantages and disadvantages of instrumentation and profiling analysis (p. 17), two methods for gathering data about performance problems. Some basic terms are introduced in chapter 2; key concepts are explained in rich discussions and examples using flow diagrams and structured query language (SQL) statements, procedural language (PL)/SQL, Oracle Call Interface (OCI), JDBC, and PHP code.

The issue of identifying the main cause of a performance problem is treated in Part 2, which consists of chapters 3 through 5. The author has opted for a careful analysis of two situations: when the subject matter can be reproduced (chapter 3) and when the problem cannot be reproduced (chapters 4 and 5). In the second situation, the author proposes real-time analysis, waiting for the reappearance of the problem (chapter 4), or statistical analysis based on the historical data stored (chapter 5). In chapter 3, methods for enabling and disabling SQL Trace, manually or automatically, using command-line tools and graphical user interfaces, are discussed.

For real-time analysis of irreproducible problems, the author presents and analyzes a suggestive roadmap in chapter 4 (p. 104). A number of alternative technical solutions are offered in the context of whether or not the reader has access to certain licensed software, such as the Diagnostics Pack and the Tuning Pack. Subjects exemplified by SQL statements with graphical representations of the results are also presented: time model statistics, wait events and wait classes, system and session statistics, active session history analysis, and SQL statements statistics.

Chapter 5 discusses and analyzes methods that allow for the postmortem analysis of irreproducible problems based on two Oracle repositories: Automatic Workload Repository (AWR) and Statspack. Appropriate solutions are provided for analysis with and without the Diagnostics Pack.

The query optimizer--this term refers to the cost-based optimizer (CBO) method only; the rule-based optimizer (RBO) is no longer used, starting with Oracle Database 10g--is introduced in Part 3. In chapters 6 through 10, the reader is carefully guided to understand the characteristics of this type of optimization, which uses statistical information from the data dictionary. The query optimizer configuration is presented in nine steps in chapter 9. In chapter 10, the author makes a number of very well-argued remarks on the five execution plans offered by Oracle. Parameters and the statistics of the optimizer have a major impact on performance. Interpreting execution plans and recognizing inefficient execution plans will help users make good decisions when it comes to optimizing performance problems.

The query optimizer evaluates each type of SQL and generates the best execution plan. Sometimes manual optimization is required. SQL optimization techniques and the impact of their use are presented in chapter 11, the first chapter of Part 4. This part, composed of six chapters, addresses many important issues related to the optimization process: Oracle parsing (chapter 12), optimizing data access and optimizing joins (chapters 13 to 15), and optimizing the physical design (chapter 16). In a very useful way for readers, the author presents the most important concepts of Oracle optimization, along with SQL statements and SQL code examples, with graphical representations of the results and a series of flow diagrams.

This updated book identifies, evaluates, and optimizes Oracle performance problems. Readers of either edition will discover these things.

More reviews about this item: Amazon