Query Capture Database | Teradata Vantage - 17.00 - 17.05 - About the Query Capture Database - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

A query capture database, or QCD, stores the Optimizer query analysis workload data and the statistical and demographic data needed to support query analysis.

The SystemFE DIP creates a default QCD called TDQCD beneath the SystemFE user, but you can also create your own QCD databases, and you can define multiple QCD databases for your systems.

You must be granted the INSERT privilege to use a BEGIN QUERY CAPTURE request to capture query plans in XML format at the session level and write data into TDQCDor any other user-defined QCD.

Vantage determines the size of TDQCD based on the number of system AMPs using the following equation.

TDQCD_size = 50MB + (number_of_system_AMPs x 100 x 1,024 bytes)

You can use the modify_tdqcd.spl SQL stored procedure to modify the size of the TDQCD database based on the number of AMPs in a test system. The definition of modify_tdqcd.spl is as follows.

REPLACE PROCEDURE TDQCD.MODIFY_TDQCD(IN Basesize int, OUT Success int)
BEGIN
DECLARE TotalAmps INTEGER;
DECLARE CurrentSize INTEGER;
DECLARE TargetSize INTEGER;

SELECT COUNT(DISTINCT Vproc) FROM Dbc.DiskSpaceV INTO TotalAmps ;
select ZEROIFNULL(permspace) into :CurrentSize from Dbc.DatabasesV where databasename = 'tdqcd';
SET TargetSize = Basesize + (TotalAmps * 100 * 1024);

IF (CurrentSize < TargetSize) then
CALL dbc.sysexecsql('MODIFY DATABASE TDQCD AS PERM = ' ||:TargetSize || 'BYTES');
END IF;

SET Success = 1;

END;

Use the Control Center feature of the Visual EXPLAIN tool (see Procedure Using the Visual EXPLAIN Utility) or BTEQ using the CreateQCF table (see Procedure Using BTEQ) to create your query capture databases.QCD tables are populated using various methods, including the following:

  • BEGIN QUERY CAPTURE
  • COLLECT DEMOGRAPHICS
  • COLLECT STATISTICS (QCD Form)
  • INSERT EXPLAIN … WITH STATISTICS
  • BEGIN QUERY CAPTURE
  • Invoking workload macros from the client-based Teradata Index Wizard utility.
Because the QCD is often a set of user tables, you can change both their individual structure and the structure of the QCD; however, you should never change any of the QCD structures in any way.

The reason for this constraint is that the SQL statements BEGIN QUERY CAPTURE, DUMP EXPLAIN, INSERT EXPLAIN, INITIATE INDEX ANALYSIS, RESTART INDEX ANALYSIS, COLLECT DEMOGRAPHICS, and COLLECT STATISTICS (QCD Form) all assume the default physical model when they capture the specified query, statistical, and demographic information for insertion into the QCD tables.

The results of changing the structure of the QCD tables are unpredictable.