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
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.
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
A Nested Query
grade = 'A'
A Correlated Subquery
grade = 'A'
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.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.