SQL provides a powerful facility for analyzing the request plans the Optimizer creates for various 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 (see SQL Data Definition Language for more information about these statements) and then analyze the captured data using the Teradata Index Wizard or standard SQL DML statements.
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: it is 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.
- Provide the input for these utilities: Teradata Index Wizard (which recommends index definitions for improved efficiency) and Teradata VE (which presents a graphical view of the flow of data during request execution).