Remote DBA Plans
Remote DBA Service

Goals of SQL Tuning

Oracle Tips by Burleson Consulting

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 based on the number of rows returned by the query. If the query returns less than 40 percent of the table rows in an ordered 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 vs. the costs of the full table scan, factoring in the multiblock 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 statement.
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 alter table xxx cache. In Oracle8 and beyond, the small table can be cached by forcing it into the KEEP pool.
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.

These goals may seem deceptively simple, but these tasks comprise 90 percent of SQL tuning, and they don't require a thorough understanding of the internals of Oracle SQL. Let's begin with an overview of the Oracle SQL optimizers.

The Problem of Declarative SQL Syntax

With SQL being a declarative language, there are many options to use when writing an SQL query, and each option may have dramatically different performance. This is a big issue and the Oracle Remote DBA must be constantly on the lookout for malformed and convoluted SQL statements.

Let's illustrate this concept with a simple example. Let's assume that we have a student database for a university and we need to know the names of all students who received an A for any class last semester.

This query can be written in three ways, each providing identical results:

A Standard JOIN

L 11-1

SELECT
   *
FROM
   STUDENT,
   REGISTRATION
WHERE
    student.student_id = registration.student_id
AND
    registration.grade = 'A';

A Nested Query

L 11-2

SELECT
   *
FROM
   STUDENT
WHERE
    student_id =
    (SELECT student_id
        FROM REGISTRATION
        WHERE
        grade = 'A'
    );

A Correlated Subquery

L 11-3

SELECT
   *
FROM
   STUDENT
WHERE
    0 <
    (SELECT count(*)
        FROM REGISTRATION
        WHERE
        grade = 'A'
        AND
        student_id = STUDENT.student_id
    );

Each of these queries will return identical results, but with radically different execution plans and different performance. Most experienced Remote DBAs know that the standard JOIN will outperform the other queries, but developers and end users often write convoluted queries to answer a simple question. We will see the shortcomings of convoluted SQL later in this chapter, but now let's take a quick tour of the Oracle SQL optimizers.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.