Investigating access path problems

You can investigate whether a performance problem is caused by the access path for a particular SQL statement.

To investigate access path problems, complete the following investigations:

1. Check the accuracy and completeness of statistics for the objects in the SQL statement.
   Inaccurate statistics often result in inaccurate access path cost estimates. Check the statistics that support your SQL statements before completing any other access path comparisons or investigations by taking any of the following actions:
   
   • Identify missing statistics by using the statistics advisor feature that is available with tools such as Db2 Administration Foundation or similar Db2 Tools for z/OS products. Use the following approach with the statistics advisor:
     1. Run the statistics advisor.
     2. Run any suggested RUNSTATS utility jobs.
     3. Run the statistics advisor again. It might suggest more RUNSTATS utility jobs related to data correlation and skew statistics, as a result of the statistics gathered in the first iteration.
     4. Run the newly suggested RUNSTATS utility jobs.
     5. Measure the performance of the SQL statement again.
   • Call the DSNACCOX stored procedure to discover whether to invoke the REORG or RUNSTATS utilities to maintain the health database objects. For more information, see DSNACCOX stored procedure.
   • Do your own analysis of the statistics. For example:
     1. Check the accuracy of the basic statistics that are needed for all database objects. These statistics describe the size and organization of objects such as tables and indexes. Examples of these statistics include values from the following columns:
        • CARDF
        • NPAGESF
        • NLEAF
        • CLUSTERRATIOF
        • DATATREPEATFACTORF
     2. Check the status of the selectivity statistics for the particular SQL statement. These include correlation and distribution statistics. They are scenario-specific, support specific SQL statements, and are not routinely collected for all database objects. Examples of these statistics include:
        • COLCARDF, LOW2KEY, and HIGH2KEY column values
        • Single and multiple column frequency statistics
        • Single and multiple column histogram statistics
        • Multi-column cardinality statistics (such as KEYCARD and COLGROUP column values)
   
2. Compare the filtering estimate that Db2 uses for access path selection to the actual filtering at run time.
   When the estimated and actual filtering differ, Db2 might choose a poorly performing access path because the cost estimates are inaccurate.
   1. Query the FILTER_FACTOR column of the DSN_PREDICAT_TABLE table to obtain the estimated filter factor for a predicate.
   2. To determine the actual filter factor, issue a query to determine the number of qualified rows. Divide the resulting value by the total number of rows in the table.
      For example, assume that a statement contains a STAT_CD='A' predicate. You might issue the following query to find the number of rows that qualify:

      SELECT COUNT(*)
      FROM T1
      WHERE STAT_CD='A'
      FOR FETCH ONLY WITH UR;

      The resulting count divided by the table cardinality is the actual filter factor.

   You can take the following actions to improve the estimated filter factor when it differs greatly from the actual filter factor at run time:

   • Gather frequency statistics, or histogram statistics, or both.
   • Rewrite predicates to take advantage of frequency and histogram statistics.
   • Use the REOPT(ALWAYS) or REOPT(ONCE) bind options to make the values of the parameter markers and host variables available at bind or prepare time.
3. If you have a history of the performance of the affected application, use the EXPLAIN output to compare the current and previous access paths and costs.
   You can also use the access plan graph feature of IBM Data Server Manager and other related tools to analyze and compare the SQL access paths.
4. Check whether indexes are used, how many matching columns are used, and whether the application used a different access path because an index was dropped.
5. Examine the number and methods of join operations that are used to access the data.