The Steps to Oracle SQL Tuning Oracle Tips by Burleson Consulting

Tuning individual SQL statements is one of the most time consuming and challenging areas of Oracle tuning. SQL tuning can also be quite tedious because of the complexity of some SQL statements, and a complex SQL statement can often take many hours to tune.

If your database has thousands of complex SQL statements, this tuning can take many months. At a high level, Oracle SQL tuning involves the following activities:

1. Ensure that all prerequisite tuning has been done on the server, disk, network, instance, and tables.
2. Rank the SQL statements in your library cache to identify the statements that will result in the most benefit from SQL tuning (i.e., those with the highest number of executions).
3. Tune the SQL statement by adding hints, rewriting the query, or adding or changing indexes.
4. Make the SQL execution plan persistent by updating the SQL source code, or by using optimizer plan stability.
5. Repeat steps 2 through 4 until all SQL has been located and tuned.

There is some debate about when SQL tuning should be performed. Some experts believe that SQL tuning can be conducted first, before the server, disk, network, instance, and objects are tuned. In fact, this sequence is tested in the Oracle Certified Professional (OCP) exam. However, you must remember that there are dependencies between a SQL statement and its environment. With regards to the external environment, bottlenecks at the server disk or network level can skew the performance of SQL executions, thereby making it very difficult to tune the statement. The same issue applies to instance tuning. If we have not tuned the instance, resetting Oracle initialization parameters could undo the execution plans for SQL that has already been tuned. For example, changing the value of db_file_multiblock_read_count could change the behavior of the CBO, causing hundreds of SQL statements to change their execution plans.

Goals of SQL Tuning

Oracle SQL tuning is a phenomenally complex subject, and entire books have been devoted to the nuances of Oracle SQL tuning. However there are some general guidelines that every Oracle Remote DBA follows in order to improve the performance of their systems. The goals of SQL tuning are simple:

• Remove unnecessary large-table full-table scans  Unnecessary full-table scans cause a huge amount of unnecessary I/O and can drag down an entire database. The tuning expert first evaluates the SQL in terms of the number of rows returned by the query. If the query returns less than 40 percent of the table rows in a row-resequenced table, or 7 percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan. The most common tuning for unnecessary full-table scans is adding indexes. Standard B-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. The decision about removing a full-table scan should be based on a careful examination of the I/O costs of the index scan versus the costs of the full-table scan, factoring in the multi-block reads and possible parallel execution. In some cases an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL

• Cache small-table full-table scans  In cases where a full-table scan is the fastest access method, the tuning professional should ensure that a dedicated data buffer is available for the rows. In Oracle7, you can issue cache commands, and in Oracle8 and beyond, the small table can be cached by forcing it into the KEEP pool.

alter table xxx cache; -- Oracle7
alter table xxx storage (buffer_pool keep); -- Oracle8

• Verify optimal index usage  This is especially important for improving the speed of queries. Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index. This also includes the use of bitmapped and function-based indexes.

• Verify optimal JOIN techniques  Some queries will perform faster with NESTED LOOP joins, others with HASH joins, and others with MERGE or STAR joins.

• Review Subqueries  Every correlated and non-correlated subquery should be examined to determine if the SQL query could be rewritten as a simple table join.

These goals may seem deceptively simple, but these tasks compose 90 percent of SQL tuning, and they don’t require a through understanding of the internals of Oracle SQL.

Now that you understand the goals of SQL tuning, let’s take a look at the steps of tuning. We will revisit many of these steps in detail in later chapters, but for now you need to understand the basic steps.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.