Tuning single SQL statements

You can improve the performance of SQL statements in applications that access DB2® for Linux, UNIX, and Windows and DB2 for z/OS®.

Before you begin

About this task

For an overview of InfoSphere® Optim™ Query Workload Tuner, see Overview of IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1.

Although there is no predetermined order in which to use the advisors, analysis tools, and reports for tuning an SQL statement, there is a general order on which you can base your own tuning scenarios.

Procedure

In most cases, to tune an SQL statement, you can follow these steps.

  1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
  2. If a license for tuning is activated on the connected data server, tune the SQL statement by following these steps:
    1. Format and annotate the problem statement to make it easier to read and comprehend. The annotations show relevant statistics that can help you understand what information the DB2 optimizer is using when generating the access plan. See Formatting SQL statements.
    2. Follow either or both of these steps:
    3. Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer always has the most current and the most needed statistics. See Generating and acting on recommendations for collecting statistics for database objects that are in the access path for an SQL statement.
    4. Run the Index Advisor and follow its recommendations to ensure that the right indexes exist to help avoid unnecessary table scans. See Generating and acting on recommendations for indexes for improving the performance of single SQL statements.
    5. Run the Query Advisor and follow its recommendations to find and revise structures in the statement that are likely to cause the DB2 optimizer to choose a suboptimal access path. See Generating and acting on recommendations for revising SQL statements for better performance.
    6. Run the Access Path Advisor to discover whether the DB2 optimizer is using a suboptimal access path to process the statement. Then, follow the advisor's recommendations. See Generating and acting on recommendations for changing the access paths for SQL statements.
    7. If the DB2 optimizer continues to use a suboptimal access path, create, validate, and deploy a plan hint (for DB2 for z/OS) or optimization profile (DB2 for Linux, UNIX, and Windows) that gives the optimizer explicit instructions for choosing an access path.
  3. If no license for tuning is activated on the connected data server, tune the SQL statement by following these steps:
    Note: If you are using IBM® Data Studio, no license is activated on the data server.
    1. Format the SQL statement to make it easier to read and comprehend. Seeing a formatted version of the SQL statement can lead to insights that are not as apparent with the unformatted version. See Formatting SQL statements.
    2. Analyze the access plan graph for the SQL statement to find out how DB2 accesses data in the objects that the statement references. See Generating visual representations of access plans.
    3. Run the Statistics Advisor and follow its recommendations to ensure that the DB2 optimizer has the most current and most important statistics. See Generating and acting on recommendations for collecting statistics for database objects that are in the access path for an SQL statement.

Feedback