Diagramming access plans with Visual Explain

You can generate a diagram of the current access plan for an SQL or XPATH statement to find out how your data server processes the statement. You can use the information available from the graph to tune your SQL statements for better performance.

Before you begin

Visual Explain in the workbench supports the following data servers:
  • DB2® Version 9.1.5 for Linux, UNIX, and Windows
  • DB2 Version 9.5.1 for Linux, UNIX, and Windows and higher
  • DB2 Version 9.7.1 for Linux, UNIX, and Windows and higher
  • DB2 Version 9.8 for Linux, UNIX, and Windows and higher
  • DB2 UDB for z/OS® Version 8 (Compatibility Mode)
  • DB2 UDB for z/OS Version 8 (New Function Mode)
  • DB2 Version 9.1 for z/OS
  • DB2 Version 10 for z/OS
  • IBM® Informix® Dynamic Server (IDS) Version 11.50
  • IBM Informix Dynamic Server Version 11.70
  • Oracle Database Version 10g Release 2
  • Oracle Database Version 11g Release 1

If you want to create access plan diagrams for DB2 for z/OS, you must configure the DB2 subsystem that you are using: Configuring DB2 for z/OS subsystems for Visual Explain

Restriction: For IBM Informix Dynamic Server, Visual Explain cannot explain SELECT statements that contain parameter markers or host variables.

About this task

You can use Visual Explain to:
  • View the statistics that were used at the time of optimization. You can then compare these statistics to the current catalog statistics to help you determine whether rebinding the package might improve performance.
  • Determine whether or not an index was used to access a table. If an index was not used, Visual Explain can help you determine which columns might benefit from being indexed.
  • Obtain information about each operation in the access plan, including the total estimated cost and number of rows retrieved (cardinality).

Procedure

To generate the diagram of the current access plan for a query:

  1. Optional: Set preferences for how Visual Explain operates and for how it displays diagrams.
  2. Follow one of these steps:
    • In the Data Project Explorer, right-click an SQL statement, SQL stored procedure, or SQL user-defined function, and select Open Visual Explain.
    • In the Data Source Explorer, right-click a view or right-click an SQL stored procedure or SQL user-defined function that contains an INSERT, UPDATE, DELETE, or SELECT statement. Select Open Visual Explain. If the workbench finds more than one SQL statement or XQUERY statement, the workbench uses the first statement.
    • In an SQL, Routine, or Java editor, highlight and right-click the INSERT, UPDATE, DELETE, or SELECT statement, XPATH, or XQUERY statement and select Open Visual Explain.
      Attempts to open Visual Explain from an SQL statement in a Java editor fail if the SQL statement contains variables that are declared in your application. For example, this SQL statement cannot be analyzed by Visual Explain because of the two variables in the predicate:
      select count(*), sum(order.price)
      from order
      where order.date > var_date_1
      and order.date < var_date_2 
      However, after you bind or deploy the application, you can use InfoSphere™ Optim™ Query Tuner or the single-query tuning features in Data Studio to capture the SQL statement from a DB2 package or from the dynamic statement cache and then tune it.
    Note: Visual Explain is disabled or throws an exception if the selected SQL statement or object is not explainable. Only the SQL statements in the following list can be explained by Visual Explain:
    • For DB2 for Linux, UNIX, and Windows: CALL, Compound SQL (Dynamic), DELETE, INSERT, MERGE, REFRESH, SELECT, SELECT INTO, SET INTEGRITY, UPDATE, VALUES, or VALUES INTO.
    • For DB2 for z/OS: SELECT, INSERT, or the searched form of an UPDATE or DELETE statement.
  3. On the first page of the wizard, specify the terminator of the SQL, XPATH, or XQUERY statement that you want to diagram the access plan for.
  4. Optional: On the first page of the wizard, you can also specify settings for various options.
    1. Specify whether you want to store the collected explain data in explain tables. If you choose this option, Visual Explain does not have to collect explain data the next time that you want to diagram the access plan for the same statement.
      Restriction: This option is not available for Oracle data servers.
    2. Specify the directory that you want Visual Explain to use as a working directory.
    3. If IBM Support needs a trace, specify whether to trace the creation of the diagram of the access plan and whether to trace the collection of the explain data.
    4. Specify whether to save your settings as the defaults for all diagrams that you create with Visual Explain. You can change these defaults with the Preferences window.
  5. On the second page of the wizard, set values for the special registers to customize the runtime environment to influence the collection of explain data.

    When Visual Explain runs the statement to gather explain data, it uses the values that you specify.

    Attention: Please be aware of the following information regarding DB2 data servers.
    • For DB2 for z/OS: If you specify different values for CURRENT SCHEMA and CURRENT SQLID, Visual Explain searches for explain tables that are qualified by the value of CURRENT SQLID. If Visual Explain does not find explain tables that are qualified by the value of CURRENT SQLID, Visual Explain attempts to create the explain tables under that value.
    • For DB2 for Linux, UNIX, and Windows: If you change the value of CURRENT SCHEMA to a value that contains special characters, you must delimit the value with single quotation marks.
    • For DB2 for Linux, UNIX, and Windows: Select the Collect column and column group statistics check box if you want Visual Explain to collect detailed statistics about clustered columns and columns that participate in a GROUP BY clause.
  6. Optional: On the second page of the wizard, specify whether to save your settings as the defaults for all diagrams that you create with Visual Explain. You can change these defaults with the Preferences window.
  7. Click Finish to close the wizard and to generate the diagram.

Results

The workbench displays the diagram in the Access Plan Diagram view. In this view, you can navigate through the diagram, view descriptions of the nodes in the diagram, and search for nodes.

Feedback