||Donald K. Burleson
SQL tuning in three simple steps
After I was chosen by
Oracle Corporation to write the Oracle Press book “Oracle
high-performance SQL tuning,” many readers are befuddled by the 600+
pages of detailed instructions on tuning Oracle SQL. While this
level of detail is necessary for a large detailed book, the goals of SQL
tuning can be encapsulated into just a few simple points.
Let’s take a
high-level look at the main goals of SQL tuning. If you achieve
these goals, you should have tuned more than 90% of your database:
large-table full-table scans
This is the most
important goal of all SQL tuning because unnecessary large-table
full-table scans consume a high amount of disk I/O that can drag-down
the performance of the whole database. Small-table full table
scans are acceptable, so long as these tables are cached in the KEEP
pool, but the trick is to evaluate the number of rows returned by a
large-table full-table scan to determine if an index range scan will
make the query run faster and with less disk I/O. In general, and
SQL query that accesses more than 40% of the rows in a table will run
faster with a full-table scan, depending on the degree of parallelism
for the table and the setting for db_file_multiblock_read_count.
For those queries that return less than 40% of table rows, you should
always try to add indexes and hints to remove the full-table scan and
force an index range scan.
When multiple indexes
are present on a table, the Oracle SQL optimizer must choose the index
with the highest selectivity to service the query. If you are
using rule-based SQL optimization, the optimizer commonly chooses a
sub-optimal index to service a table access, and you job is to force te
use of the most selective index.
table join techniques
As we know, Oracle
can join tables with a sort-merge join, a nested loop join, a hash join
or a STAR join. It is your job to tune the SQL joins to ensure that
the fastest table join method is being used.
If you like Oracle
tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think
it is right to charge a fortune for books!) and you can buy it right now
at this link: