If you are a developer, DBA, or query tuning specialist, it is critical that
you understand the basics of access
paths so that you can precisely tune queries and query workloads before they cause
problems in your production environment.
understanding, coupled with the visualization and tuning advice provided by IBM Optim query tuning solutions, can help make you more
efficient at this task.
This article provides conceptual background on access paths, shows you how to read an access path graph,
and walks through the access path graph to demonstrate critical information regarding access path selection.
concludes with a sample scenario that demonstrates how to use query annotation, a feature in Optim Query Tuner that
helps you with query analysis by providing vital statistical information
directly in the SQL statement.
The first article in this series introduced the concept of access
paths and showed you how to read an access path diagram in Optim Query Tuner.
In this article, a methodology for tuning individual queries is introduced.
Using a sample query, you are shown how you can use Optim Query Tuner to go
through the process. The process includes using query formatting and annotation,
and analysis of the access plan, statistics, predicates, and indexes. The goal
is to ensure that the DB2 optimizer is provided with the information it needs
to make the best performance-based decisions for your DB2 queries, and to provide
you with advice on things you can do to help the DB2 optimizer have more options
for improving access, such as creating the necessary indexes.
The previous articles in this series introduced the concept of access
paths, showed you how to read an access path diagram in Optim Query Tuner and went
through the methodology for tuning individual queries. Here in Part 3, a methodology
for tuning SQL workloads is introduced. Learn how to use
InfoSphere Optim Query Workload Tuner to capture an SQL
workload from different sources, gather statistics and
index analyses, compare access plans, and perform plan lock-down and plan management. The goal
is to ensure that the IBM DB2 optimizer is provided with the information it
needs to make the best performance-based decisions for your DB2 queries and to provide
advice to help the DB2 optimizer improve access, such as collecting necessary
statistics and creating optimal indices.