Harrison’s objective is to provide SQL programmers with the theory and practice of SQL tuning and with hints and guidelines for optimizing specific SQL statement types. He explains how to diagnose and correct problems with existing SQL and briefly explores performance issues that go beyond SQL tuning, such as design and server tuning. By following the guidelines in this book, programmers should be able to write SQL that will perform well both in development and in production, and should be able to detect and correct inefficiencies in existing SQL code. The result will be SQL that lives up to its potential.
There is a need for a book like this, aimed not at Oracle database administrators but at the people writing the access routines for the database. These people include application developers, users of data warehouses, and others whose work involves writing high-performance SQL. Database administrators should also find some of this material interesting, however.
Most Oracle developers should read, or at least review, most of the book. It explains the importance of SQL tuning and reviews the basic functionality and concepts of the SQL language. The author explains the mechanisms by which Oracle interprets an SQL statement and retrieves or alters the specified data. He discusses how to build indexes that support efficient queries and how to work with joins. Readers will learn how SQL processing can be traced and interpreted. The part about tuning SQL should be read from beginning to end and used as a reference.
Although this book contains an abundance of SQL tuning techniques, guidelines, and examples, it cannot provide a rule or example for every circumstance. It does present an effective approach to tuning, which will produce well-tuned SQL statements, and offers some suggestions for common situations. To use this information, you will need access to the database structure--table and index definitions, table sizes, relationships between tables, and so on.
Tuning Oracle SQL programs effectively requires an understanding of Oracle’s approach to SQL processing; an understanding of Oracle’s indexing facilities; an ability to use the Oracle SQL tuning tools; an elementary understanding of database and application design principles; and some familiarity with the Oracle server architecture and how bottlenecks in that architecture can affect the performance of SQL. While tuning SQL is not always easy, it can improve the system’s interactive response time and batch throughput, ensure scalability of an operation, and help reduce system load and avoid costly hardware upgrades.
Harrison presents ways of optimizing the retrieval of data from a single table. He then explains ways of improving performance when two or more tables are joined and in the case of SQL operations that require Oracle to order or group data. Next, he considers issues related to the performance of DML statements (INSERT, UPDATE, and DELETE) and shows how PL/SQL can be used to improve the performance of traditional SQL statements and how PL/SQL programs themselves can be tuned. Another way to improve the performance of SQL statements is to use Oracle’s parallel capabilities.
After looking at a variety of tuning principles and techniques, the book offers some real-life examples of tuning Oracle SQL. Improvements, measured in terms of reduced response time or database I/O, range from 80 percent to 99.9 percent.
Although SQL tuning can lead to impressive improvements in application performance, the data model and application design often constrain the performance that can be achieved. Harrison concludes by reviewing the principles of Oracle database design for high-performance systems and exploring methods of monitoring an Oracle database server and detecting bottlenecks and inefficiencies. Mastering the tools and tips presented in this book will enable readers to become expert database developers.
