Some
queries will perform faster with
NESTED LOOP joins,
some with HASH joins, while others
favor sort-merge joins. It is
difficult to predict what join
technique will be fastest a priori,
so many Oracle tuning experts will
test-run the SQL with each different
table join method and time the speed
with the SQL*Plus "set timing on"
command.
These goals may
seem deceptively simple, but these
tasks comprise 90 percent of SQL
tuning, and they do not require a
thorough understanding of the
internals of Oracle SQL. Let's begin
with an overview of the Oracle SQL
optimizers.
Of course, you can
tune the SQL all you want, but if you
do not feed the optimizer with the
correct statistics, the optimizer may
not make the correct decisions.
It is important to ensure that you
have statistics present and that they
are current.
Some believe in
the practice of running statistics by
schedule such as weekly, some believe
in just calculating statistics when
the data changes, still others believe
that you only run statistics to fix a
poor access path, and once things are
good; do not touch them. It is
difficult to say who is correct.
Therefore, the new
features in Oracle 10g that tell you
when statistics are old and need to be
recalculated are extremely helpful.
Gone are the days when statistics were
calculated weekly (or on whatever
schedule), just in case the data
changed. Now we know for sure
one way or the other. Of course,
some will still believe that you
should only calculate new statistics
if you are having a problem, and once
you have decent access paths, leave it
alone.
For complete scripts to access Oracle
joins, see Mike Ault's script collection:
Advanced Oracle Monitoring and Tuning
Scripts
Also, see these good notes on nested loop vs.
hash joins:
and include the URL for the page.
