This book excerpt will focus on real-world techniques for improving the speed of SQL queries with a focus on the new Oracle10g features. The topics will include the new Oracle parameters that affect SQL performance, the use of hints to change SQL execution plans, rewriting SQL queries in more efficient forms and the use of advanced techniques such as Materialized Views, replacing SQL with PL/SQL, the new automated CBO statistics collection and using the new Oracle10g CPU costing approach.
This is an excerpt from the bestselling book Oracle Tuning: The Definitive Reference by Alexey Danchenkov and Donald Burleson, technical editor Mladen Gogala.
Understanding Oracle SQL tuning
Before relational databases were introduced, database queries required knowledge of the internal structures and developers needed to build in the tuning as a part of writing the database query. However, the SQL standard imposed a declarative solution to database queries where the database optimizer determines important data access methods such as what indexes to use and the optimal sequence to join multiple tables together.
Today, it is not enough for a developer to write an SQL statement that provides the correct answer. SQL is declarative, so there are many ways to formulate a query, each with identical results but with far different execution times.
Oracle SQL tuning is a phenomenally complex subject, and entire books have been devoted to the nuances of Oracle SQL tuning, most notably the Kimberly Floss book Oracle SQL & CBO Internals by Rampant TechPress. This chapter provides a review the following areas of SQL tuning:
- The goals of SQL tuning
- Simplifying complex SQL
- SQL Optimization instance parameters
- Statistics and SQL optimization
- Oracle10g and CBO statistics
- Oracle tuning with hints
- Oracle10g SQL profiles
- AWR and SQL tuning
- ADDM and SQL tuning
The first three sections will be an overview of general Oracle10g tuning concepts, so that the basic tools and techniques for tuning SQL optimization are clearly introduced. The focus will then shift to an exploration of the new Oracle10g SQL Profiles, and will eventually delve into the internals of AWR and explore how the SQLTuning and SQLAccess advisor use time-series metadata.
Optimizing Oracle SQL execution
The key to success with the Oracle Cost-based Optimizer (CBO) is stability, and ensuring success with the CBO involves the consideration of several important infrastructure issues.
- Ensure static execution plans: Whenever an object is re-analyzed, the execution plan for thousands of SQL statements may be changed. Most successful Oracle sites will choose to lock down their SQL execution plans by carefully controlling CBO statistics, using stored outlines (optimizer plan stability), adding detailed hints to their SQL, or by using Oracle10g SQL Profiles. Again, there are exceptions to this rule such as LIMS databases, and for these databases, the DBA will choose to use dynamic sampling and allow the SQL execution plans to change as the data changes.
- Reanalyze statistics only when necessary: One of the most common mistakes made by Oracle DBAs is to frequently re-analyze the schema. The sole purpose of doing that is to change the execution plans for its SQL, and if it isn't broken, don't fix it. If the DBA is satisfied with current SQL performance, re-analyzing a schema could cause significant performance problems and undo the tuning efforts of the development staff. In practice, very few shops are sufficiently dynamic to require periodic schema re-analysis.
- Pre-tune the SQL before deploying: Many Oracle systems developers assume that their sole goal is to write SQL statements that deliver the correct data from Oracle. In reality, writing the SQL is only half their job and successful Oracle sites require all developers to ensure that their SQL accesses Oracle in an optimal fashion. Many DBAs will export their production CBO statistics into their test databases so that their developers can see how their SQL will execute when it is placed into the production system. DBAs and staff should be trained to use the AUTOTRACE and TKPROF utilities and to interpret SQL execution results.
- Manage schema statistics: All Oracle DBAs should carefully manage the CBO statistics to ensure that the CBO works the same in their test and production environments. A savvy DBA knows how to collect high-quality statistics and migrate their production statistics into their test environments. This approach ensures that all SQL migrating into production has the same execution plan as it did in the test database.
- Tune the overall system first: The CBO parameters are very powerful because a single parameter change could improve the performance of thousands of SQL statements. Changes to critical CBO parameters such as optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching should be done before tuning individual SQL statements. This reduces the number of suboptimal statements that require manual tuning.
Prior to Oracle10g, it was an important job of the Oracle DBA to properly gather and distribute statistics for the CBO. The goal of the DBA was to keep the most accurate production statistics for the current processing. In some cases, there may be more than one set of optimal statistics.
For example, the best statistics for OLTP processing may not be the best statistics for the data warehouse processing that occurs each evening. In this case, the DBA will keep two sets of statistics and import them into the schema when processing modes change.
The following section provides a quick, simple review of the goals of SQL tuning.
Goals of SQL tuning
There are many approaches to SQL tuning and this paper describes a fast, holistic method of SQL tuning where we optimize the SGA, the all-important optimizer parameters, and adjust CBO statistics, all based on current system load. Once the "best" overall optimization is achieved, we drill-down into the specific cases of sub-optimal SQL, and change their execution plans with SQL profiles, specialized CBO stats or hints.
Despite the inherent complexity of tuning SQL, there are general guidelines that every Oracle DBA follows in order to improve the overall performance of their Oracle systems. The goals of SQL tuning are simple:
- Replace unnecessary large-table full-table scans with index scans.
- Cache small-table full table scans.
- Verify optimal index usage.
- Verify optimal JOIN techniques.
- Tune complex subqueries to remove redundant access.
These goals may seem deceptively simple, but these tasks comprise 90% of SQL tuning. They do not require a thorough understanding of the internals of Oracle SQL. This venture will begin with an overview of the Oracle SQL optimizers.
Of course, the SQL can be tuned to one's heart's content, but if the optimizer is not fed with the correct statistics, the optimizer may not make the correct decisions. Before tuning, it is important to ensure that statistics are available and that they are current.
The following section will provide a closer look at the goals listed above as well as how they simplify SQL tuning.
Click here to read the rest of this chapter.