The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.
All information submitted is secure.
The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.
Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.
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.