Guidelines for analyzing explain information

The primary use for explain information is the analysis of access paths for query statements. There are a number of ways in which analyzing the explain data can help you to tune your queries and environment.

Consider the following kinds of analysis:
  • Index use
    The proper indexes can significantly benefit performance. Using explain output, you can determine whether the indexes that you have created to help a specific set of queries are being used. Look for index usage in the following areas:
    • Join predicates
    • Local predicates
    • GROUP BY clause
    • ORDER BY clause
    • WHERE XMLEXISTS clause
    • The select list
    You can also use the explain facility to evaluate whether a different index or no index at all might be better. After you create a new index, use the RUNSTATS command to collect statistics for that index, and then recompile your query. Over time, you might notice (through explain data) that a table scan is being used instead of an index scan. This can result from a change in the clustering of the table data. If the index that was previously being used now has a low cluster ratio, you might want to:
    • Reorganize the table to cluster its data according to that index
    • Use the RUNSTATS command to collect statistics for both index and table
    • Recompile the query
    To determine whether reorganizing the table has improved the access plan, examine explain output for the recompiled query.
  • Access type
    Analyze the explain output, and look for data access types that are not usually optimal for the type of application that you are running. For example:
    • Online transaction processing (OLTP) queries

      OLTP applications are prime candidates for index scans with range-delimiting predicates, because they tend to return only a few rows that are qualified by an equality predicate against a key column. If your OLTP queries are using a table scan, you might want to analyze the explain data to determine why an index scan is not being used.

    • Browse-only queries

      The search criteria for a browse type query can be very vague, resulting in a large number of qualifying rows. If users usually look at only a few screens of output data, you might specify that the entire answer set need not be computed before some results are returned. In this case, the goals of the user are different than the basic operating principle of the optimizer, which attempts to minimize resource consumption for the entire query, not just the first few screens of data.

      For example, if the explain output shows that both merge scan join and sort operators were used in the access plan, the entire answer set will be materialized in a temporary table before any rows are returned to the application. In this case, you can attempt to change the access plan by using the OPTIMIZE FOR clause on the SELECT statement. If you specify this option, the optimizer can attempt to choose an access plan that does not produce the entire answer set in a temporary table before returning the first rows to the application.

  • Join methods

    If a query joins two tables, check the type of join being used. Joins that involve more rows, such as those in decision-support queries, usually run faster with a hash join or a merge join. Joins that involve only a few rows, such as those in OLTP queries, typically run faster with nested-loop joins. However, there might be extenuating circumstances in either case-such as the use of local predicates or indexes-that could change how these typical joins work.