 |
|
The Goals of SQL Tuning
Oracle Tips by
Burleson
|
Oracle SQL tuning is a
phenomenally complex subject, and we will begin with a high-level
statement of the goals of SQL tuning and get into detail in later
chapters.
The Guidelines for SQL Tuning
The cardinal rules of
SQL tuning are very straightforward:
1.
There exists only one optimal execution plan for any SQL
statement. It is the job of the Oracle DBA to find that execution
plan.
2.
Because SQL comes to the Oracle database from external
programs, the Oracle DBA must continually monitor the library cache
for untuned SQL.
3.
A properly tuned SQL statement will always have the fastest
wall-clock execution time. This is usually associated with the
execution plan that has the least amount of table I/O.
Now that we have
stated the goals, let’s examine some of the specific goals of SQL
tuning. There are some general guidelines that all Oracle DBAs must
follow 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 an ordered table, or 7 percent of the rows in an
un-ordered table, the query can be tuned to use an index in lieu of
the full-table scan. The most common tuning remedy 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 converted to an index scan by
adding an index hint to the SQL statement.
-
Cache small-table full-table scans
full-table scan is the fastest In cases where a 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 to into the KEEP pool.
-
Verify optimal index usage improving the
speed of This is especially important for 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.
These goals may seem deceptively simple, but these tasks
make up 90 percent of SQL tuning, and they don’t require a thorough
understanding of the internals of Oracle SQL. Next let’s look at a
series of tools that we can use to tune SQL statements.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
