Query Capture Facility | Teradata Vantage - 17.10 - Quick Functional Overview of the Query Capture Facility - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

The Query Capture Facility, or QCF, provides a method to capture and store the steps from any query plan in a set of predefined relational tables called the query capture database, or QCD.

You create your QCD databases using the procedures described in Teradata Vantage™ - Database Administration, B035-1093.

QCD Information Source

The principal source of the captured information in QCD is the white tree produced by the Optimizer, the same data structure used to produce EXPLAIN reports (note that the current implementation of QCD does not represent all the information reported by EXPLAIN). The white tree was chosen because it represents the output of the final stage of optimization performed by the Optimizer.

Statistical and other demographic information in the QCD is captured using the following set of SQL statements.
  • COLLECT DEMOGRAPHICS
  • COLLECT STATISTICS (QCD Form)
  • INSERT EXPLAIN WITH STATISTICS

For more information, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

QCD Physical Model

Note the following facts about the physical implementation of QCD databases.
  • All text columns that might contain names of any type are explicitly defined as Unicode to ensure proper handling of any Teradata-supported character set.
  • The table SeqNumber exists to feed the values of the artificial sequential number columns defined for several attributes in QCD.

See Teradata Vantage™ - Database Design, B035-1094 for information about physical capacity planning for your QCDs.

Applications of QCF and QCD

Vantage supports the following applications of QCF and QCD:
  • QCD can store all query plans for customer queries. You can then compare and contrast queries as a function of software release, hardware platform, and hardware configuration.
  • You can generate your own detailed analyses of captured query steps using standard SQL DML statements and third party query management tools by asking such questions as “how many spool files are used by this query,” “did this query plan involve a product join,” or “how many of the steps performed by this query were done in parallel.”