Finding underperforming SQL statements with EXPLAIN

In the SQL Outline view, you can view EXPLAIN data that shows you statistics about the access plans for the SQL statements in your Java application. You can spot the underperforming statements that, when revised, can improve the performance of the application.

Before you begin

EXPLAIN is a monitoring tool in Visual Explain that produces information about the estimated cost of executing an SQL SELECT, INSERT, UPDATE, or DELETE statement.

Ensure the following project and application requirements are met:
  • At least one Java project in your workspace must have support for pureQuery enabled. Your application must have either of these characteristics:
    • The application must contain at least one of the following objects:
      • A call to one of the methods in the pureQuery Data interface
      • An interface that declares at least one annotated method that uses the pureQuery API
      • A pureQueryXML file that contains captured SQL statements
    • The application must use the Java Persistence API or Hibernate, an object-relational mapping library for Java.
  • Your project must be associated with one of the following databases:
    • DB2® for Linux, UNIX, and Windows
    • DB2 for z/OS®
    • Informix® Dynamic Server
Note: You cannot run EXPLAIN for SQL statement if the SQL statement contains variables that are declared in your application. The statement can contain data server related variables such as DB2 host variables.

About this task

Running EXPLAIN for SQL statements can be time consuming. Instead of manually selecting SQL statements and populating their EXPLAIN content, you can run background EXPLAIN.

Procedure

To find underperforming SQL statements with EXPLAIN:

What to do next

To sort the results, click Show tree view or table view to view the results in a table. Then, click the head of the column by which you want to sort.

If the EXPLAIN data make it clear that you should modify an SQL statement so that it takes less time to run, you can tune the query. This feature is available only for the following databases:
  • DB2 for Linux, UNIX, and Windows
  • DB2 for z/OS

Feedback