SQL provides a powerful facility for analyzing the request plans the Optimizer creates for data manipulation statements. This feature, called the Query Capture Facility, permits a DBA to capture access plan information from the Optimizer using the SQL statements COLLECT DEMOGRAPHICS , DUMP EXPLAIN , INSERT EXPLAIN , and BEGIN QUERY CAPTURE and then analyze the captured data using standard SQL DML statements.
INSERT EXPLAIN and DUMP EXPLAIN are supported only on the Block File System on the primary cluster, not on the Object File System.
The captured request plans are stored in a relational database called a Query Capture Database (QCD) that you can create for that purpose. QCD is a user database whose space is drawn from available permanent disk space, not a set of system tables found in a fixed location.
Applications of QCF include:
- Store all request plans for customer requests. You can then compare and contrast requests as a function of software release, hardware platform, and hardware configuration.
- Provide data so that you can generate your own detailed analyses of captured request steps using standard SQL DML statements and third-party request management tools.