Databases Reference
In-Depth Information
Use More Indexes. Index other common search columns such
as business keys. Also, use composite key indexes when certain
combinations of criteria are often used together.
Eliminate Sorts. Try to reduce DBMS sorting by having index
keys match the ORDER BY or GROUP BY sequence after EQUALS
predicates.
Explain/Show Plan. Know the estimated execution time of the
SQL. Incorporate SQL tuning into the systemdevelopment life cycle.
Monitor and Tune. Some monitoring tools will identify the
SQL statements that use the most overall resources. But as well
as the single execution overhead identified in the Explain (Show
Plan), it is important to also consider the frequency of execution
of the SQL statements. For example, a SQL statement that
runs for 6 seconds but is called only 10 times per hour uses a
lot fewer resources than another that runs only 60 milliseconds,
but is called 10,000 times per hour—in this case, 1 minute vs. 10
minutes total time. The query it is most important to optimize is
the 60 millisecond query.
Use Optimization Hints Cautiously. Most optimizers work
well most of the time. However, once in a while, they just don't
get it right. It's getting harder to force the optimizer into
choosing a better access path, for example by using different log-
ical expressions with the same truth conditions, or by fudging
catalog statistics. However, most optimizers support some type
of optimization hints. Use them sparingly, but when all else fails,
and the optimizer is being stubborn, use them.
Use Isolation Levels. Specify the appropriate Isolation Level to
minimize locks and lock waits. Isolation levels of Cursor Stability
(CS) or Uncommitted Read (UR) can significantly improve the
throughput compared to more restrictive levels such as Repeat-
able Read (RR). However, keep in mind that a temporal update
usually expands into several physical inserts and updates to the
objects. So make sure that less restrictive isolation levels are
acceptable to the application.
Glossary References
Glossary entries whose definitions form strong inter-
dependencies are grouped together in the following list. The
same glossary entries may be grouped together in different ways
at the end of different chapters, each grouping reflecting the
semantic perspective of each chapter. There will usually be sev-
eral other, and often many other, glossary entries that are not
included in the list, and we recommend that the Glossary be
consulted whenever an unfamiliar term is encountered.
