Your one-stop resource for all Oracle SQL and PL/SQL tuning and optimization!
Optimizing SQL code is the #1 factor in improving Oracle database performance, yet most guides to Oracle tuning virtually ignore SQL. Oracle SQL High Performance Tuning, Second Edition zeroes in on SQL and PL/SQL, demonstrating how to achieve performance gains of 100% or more in many applications. Expert Oracle developer Guy Harrison gives Oracle developers and DBAs a single source for guidance on every aspect of Oracle 8/8i SQL and PL/SQL tuning, with specific recommendations, detailed case studies, and real benchmarks. Coverage includes:
Get all the performance and scalability you're paying for without investing in new hardware! Whether you're running OLAP or OLTP applications, Guy Harrison's Oracle SQL High Performance Tuning, Second Edition shows you how, step-by-step!
I. INTRODUCTION TO SQL TUNING.1. Introduction to SQL Tuning.
Introduction. Why Tune SQL? The Place of SQL Tuning in the Overall Tuning Process. When Should SQL be Tuned? The Tuning Process. Constructing a Tuning Environment. Tuning SQL. Summary.2. SQL Tuning Quick Start.
Introduction. Top Tips and Hints for SQL Tuning. SQL Tuning Quick Tips.3. Review of SQL.
Introduction. The History of SQL. Types of SQL Statements. Query Operations: The SELECT Statement. Data Manipulation and Transaction Control. Summary.
II. SQL TUNING THEORY.4. SQL Processing Internals.
Introduction. Overview of SQL Processing. Creating and Parsing Cursors. The Cursor_Sharing Option in Oracle. Executing SQL. Types of Table Accesses. Joins. Sorting and Grouping. Modifying Data. Summary.5. The Optimizer.
Introduction. Overview of Optimization. Collecting Optimizer Statistics. Using Plan Stability. Using Hints. Using Hints to Change the Access Path. Using Hints to Change the Join Order. Errors in Hint Specifications. Summary.6. Indexing and Clustering.
Introduction. B*-tree Indexes. Clustering. Bitmap Indexes. Index Organized Tables. Summary.7. Tracing and Explaining SQL.
Introduction. Explain Plan. Using Explain Plan. SQL Trace. Overview of the SQL Tracing Process. Preparing to Use SQL Trace. Enabling SQL Trace Within a Session. Enabling SQL Trace from Another Session. Finding the Trace File. Using Tkprof. Tkprof Sort Options. Other Tkprof Options. Troubleshooting SQL_TRACE and Tkprof. Interpreting Tkprof Output. Tkprof Execution Statistics. Tkprof Execution Plans. Tuning SQL with Tkprof Output. Tkprof Rules, OK? The SQL*PLUS AUTOTRACE Option. Requirements for Using AUTOTRACE. Initiating AUTOTRACE. AUTOTRACE Output. AUTOTRACE Compared to Tkprof. Summary.
III. SQL TUNING IN PRACTICE.8. Tuning Table Access.
Introduction. When to Use a Full-Table Scan. How the Optimizer Chooses Between Indexes _and Full-Table Scan. Column Histograms. Avoiding "Accidental" Table Scans. Not Equals Conditions. Searching For Nulls. Searching for Values That are NOT NULL. Creating Indexes on NULLable Columns. Searching for a Selective Value in an Unselective Column. Unintentionally Disabling an Index With a Function. Functional Indexes. Choosing the Best Indexing Strategy. How Many Distinct Values are Too Many For a Bitmap Index? Deciding To Use an Index Organized Table. Optimizing B*-tree Index Lookups. Types of Index Retrievals. Using Concatenated Indexes. Index Merges. Searching for Ranges. Optimizing Bitmap Index Access. Optimizing Hash Clusters. Optimizing Index Organized Tables. Optimizing Full Table Scans. Optimizing for the First Row. Summary.9. Tuning Joins and Subqueries.
Introduction. Choosing the Best Join Method. Optimizing the Join. Special Joins. Subqueries. Semijoins. Antijoins. Summary.10. Sorts, Aggregates, and SET Operations.
Introduction. Sort Operations. Aggregate Operations. SET Operations. Summary.11. Parallel SQL.
Introduction. Understanding Parallel SQL. Using Parallel Query. Tuning Parallel Query. Examples of Parallel Queries. Other Parallel Operations. Summary.12. Optimizing DML.
Introduction. General Optimizations. Optimizing INSERTS. Optimizing Transactions. Parallel DML. Summary.13. VLDB and Warehousing.
Introduction. Partitioning. Materialized Views and Snapshots. Summary.14. Using and Tuning PL/SQL.
Introduction. Performance Characteristics of PL/SQL. Using PL/SQL in Place of Standard SQL. Optimizing PL/SQL. Dynamic SQL and PL/SQL. Using the PL/SQL Profiler. Summary.15. Using and Tuning Oracle Java.
Introduction. Java as an Alternative to PL/SQL. Computationally Intensive Routines. Database-Intensive Routines. Java or PL/SQL? Using Bind Variables in JDBC. Cursor Handling. Avoid Autocommit. Using Array Processing (JDBC 1). Using Array Processing (JDBC 2). Using Oracle Datatypes. Bind Variables. Cursor Management. Array Processing. JDBC or SQJ or PL/SQL? Summary.16. Oracle Object Types.
Introduction. Object Type Alternatives to Master-Detail Tables. Performance Comparisons for Object Types. Summary.17. Miscellaneous Topics.
Introduction. Optimizing Views. Distributed SQL. Distributed Joins. Sequences. Using DECODE. Optimizing Data Definition Language. Tuning Access to the V$ Tables. Summary.
IV. BEYOND SQL TUNING.18. Application Design Issues.
Introduction. Building Tuning into the Design Process. Establishing an Efficient Physical Model. Application Design. Summary.19. Oracle Server Design.
Introduction. A Review of the Oracle Architecture. Sizing the Host Computer. Essential Considerations for Database Configuration. Optimizing Database I/O. Sizing the SGA. Multithreaded Servers. Parallel Query Processes. Summary.20. Oracle Server Tuning.
Introduction. Evaluating Operating System Performance. Recognizing Oracle Database Bottlenecks. Identifying Resource Intensive SQL. Summary.Appendix A: Reference.
This book is about tuning Oracle databases and applications with an emphasis on the tuning of SQL statements. Tuning SQL is not the only way to tune an application: The design of an application will often dictate its performance limits, and tuning the physical layout of an Oracle database can be critical to reaching those limits. However, tuning SQL is usually the most cost-effective way of improving the performance of an existing application, while other measuressuch as changing database parameters or altering disk layoutswill usually be ineffective unless the application's SQL is properly tuned.
It is common for the performance of an Oracle application to appear to be acceptable during development only to degrade abruptly when the application encounters production data volumes or transaction rates. While this may result from a number of causes, inefficient SQL that fails to maintain good performance as data volumes increase is a major factor.
Poorly performing SQL arises in applications for a number of reasons. Although SQL is a relatively easy language to learn, its nonprocedural nature tends to obscure performance-related issues. As a result, its much harder to write efficient SQL than it is to write functionally correct SQL. Additionally, there seems to be insufficient awareness of the need to monitor carefully and tune SQL performance, and the tools and techniques needed to tune SQL are not sufficiently well known.
Another factor that has increased the significance of well-tuned SQL is the emergence of data warehouses or On-Line Analytical Processing (OLAP) systems. These databases are often extremely large and are subject to a great deal of ad hoc query activity. If the SQL that supports these queries is inefficient, then queries may take hours or even days to complete or may fail to complete at all.
When Oracle applications start to underperform, it's typical for performance experts to be called in to perform benchmark tests or tune the Oracle database engine. For the most part, they will tune the operating system, change Oracle configuration parameters, reconfigure input/output (I/O), disks and so on. At the end of the process, you can (if you are lucky) expect a 10 to 20 % improvement in performance.
During these tuning exercises it is usually that apparent the SQL contained within the application is the most important factor in determining performance. If the SQL can be tuned, then performance increases of 100 percent or more are not uncommon. But there is a dilemma: By the time performance problems are recognized, it is often difficult to make changes to the production SQL. Furthermore, performance experts usually don't have the application knowledge required to understand and tune the SQL, while the developers don't have the necessary understanding of SQL performance tuning.
It follows that the best way to improve substantially the performance of most Oracle applications is to improve the efficiency of the application SQL. To make this happen, developers needed to acquire SQL tuning skills together with a commitment to tuning.
The objective of this book is to provide SQL programmers with the theory and practice of SQL tuning together with hints and guidelines for optimizing specific SQL statement types. We'll see how to diagnose and correct problems with existing SQL and briefly explore performance issues beyond SQL tuning, such as application design and server tuning. By following the guidelines in this book, SQL programmers should be able to write SQL that will perform well both in development and in production and will be able to detect and correct inefficiencies in existing SQL. The result will be SQL that performs to its peak potential.
With the Oracle server documentation set consisting of more than a dozen manualsincluding a tuning guideand a number of independent Oracle tuning texts on the market, is there really a need for this book?
There is a need, and the basis for this need lies in two fundamental imperfections in all alternative tuning guides: They are aimed almost exclusively at database administrators (DBAs), and they gloss over the processes of tuning SQL statements. There is a need for a book that is aimed not at the administrators of the Oracle databases, but at those writing the access routines (that is, the SQL) for the database, such as application developers, users of data warehouses, and others whose work involves writing high-performance SQL.
Additionally, while tuning the database engine can help poorly performing applications, nothing can match improving the efficiency of SQL for getting massive performance improvements. Unfortunately, most tuning texts spend most of their time focusing on database and I/O subsystem tuning.
This is not a book for Oracle DBAs, although DBAs should find many things of interest here. Rather, this is a book for anyone who needs to write SQL that has a performance requirement.
People who need to write high-performance SQL are as follows:
Few people read a book of this type from beginning to end. Depending on your background, you may wish to skip sections that review database theory and jump right into the details of SQL tuning.
However, apart from the "Review of SQL" and the "Beyond SQL Tuning" sections, most readers should attempt to read or at least review the majority of this book.
The book has the following major sections:
This section contains a review of the importance of SQL tuning, an overview of the tuning process and a review of SQL. The chapters in Part I are as follows:
Chapters in Part II introduce a number of important topics, such as the role of the query optimizers, indexing and hashing concepts, SQL parsing, basic data retrieval strategies, and tools for explaining and tuning SQL execution. Although Part II is heavy on theory, its difficult to tune SQL successfully without at least a broad understanding of these topics. All readers are therefore encouraged to read this section.
The chapters in Part II are as follows:
Chapters in Part III contain tuning guidelines for specific SQL statement types and circumstances. While it will be useful to read Part III from start to finish, it may also be used as a reference. You may wish to consult the relevant portions of this section as appropriate tuning requirements arise. Chapters in Part III are as follows:
At the beginning of the application life cycle, effective database and application design can define the constraints that will ultimately determine the limits on your SQL's performance. For a well-designed application with tuned SQL, the configuration of your databasedisk layouts, SGA configuration, etc.may be the key to getting further gains in performance. Chapters in Part IV discuss these "beyond SQL" issues:
The appendices contain details of configuring client programs and the Oracle server for specific circumstances, a reference guide, and a guide to further reading and other resources.
Whenever possible, any SQL tuning principle in this book will be illustrated with an example SQL statement. Usually, these SQL statements will be based on the sample database shown in Figure P-1. This database is not intended to illustrate good or bad data modeling principles but to be a basis for illustrating a wide range of SQL statements. You can find an export of one of the variations of this database at the book's website.
The sample database implements a simple and familiar business schema containing Customers, Employees, Products, and Sales. In addition, the database contains the results from an imaginary marketing survey in the Subjects and Scores table.
Many different physical implementations of this logical schema were implemented during the development of the book. For instance, the Sales table was subjected to a variety of partitioning schemes, the Customer table was represented in one example as an Index-Organized table, while Subject and Score data were represented in a variety of ways including nested tables, Varrays, and object tables.
Many of the examples contained in this book are accompanied with a graphical illustration of the performance gains that can be achieved by various optimizations. These performance measurements were collected on a range of computer hardware, ranging from a high-end UNIX host to a Pentium laptop. Performance measurements are shown in either elapsed times or logical database I/Os ("block reads"), whichever was most appropriate to the optimization being performed.
Since writing the first edition of this book, I have had the pleasure to work with Quest Software in the development of software that assists in the tuning of SQL and Oracle databases. In particular, the SQLab Xpert SQL tuning tool implements many of the tuning principles outlined in this book. I have also been heavily involved in the development of Quest's Spotlight on Oracle product, which implements the database tuning philosophies outlined in the final chapters of this book. Because I'm so familiar with Quest products and believe that they make substantial contributions to SQL tuning, I often refer to them in the text of the book. However, be warned that I'm a Quest employee and hardly unbiased when it comes to Quest products.