Administration Guide

Chapter 26. SQL Explain Facility

The SQL explain facility is part of the SQL Compiler that can be used to capture information about the environment where the static or dynamic SQL statement is compiled. The information captured allows you to understand the structure and potential execution performance of SQL statements, including:

• Sequence of operations to process the query
• Cost information
• Predicates and selectivity estimates
• Statistics for all objects referenced in the SQL statement at the time of the explain.

This information can help you:

• Understand the execution plan chosen for a query
• Assist in designing application programs
• Determine when an application should be rebound
• Assist in database design.

The following topics are provided:

• Choosing an Explain Tool
• Using the SQL Explain Facility
• Introductory Concepts for Explain
• How Explain Information is Organized
• Obtaining Explain Data
• Guidelines on Using Explain Output
• Visual Explain
• SQL Advise Facility.

The explain output is stored in relational tables and, as an option, in a format which may be graphically displayed using the Visual Explain tool. You should consider using the explain tables to find those queries run against the explain tables that are of interest to you. For more information on the tables used by the explain facility and how to create those tables, see Appendix G, Explain Tables and Definitions.