WITH - Advanced SQL Engine - Teradata Database

VERBOSE

Capture verbose EXPLAIN text.

STATSUSAGE

Capture Optimizer statistics usage and recommendation information.

DETAILED

Capture summary statistics details for all of the database objects referenced in the query plan for the request, in addition to capturing Optimizer statistics usage and recommendation information. When you specify DETAILED, the request captures the following statistics details for all the database objects referenced in the query plan:

• StatTimeStamp
• Version
• OrigVersion
• NumColumns
• NumBValues
• NumEHIntervals
• NumHistoryRecords
• NumNulls
• NumAllNulls
• NumAMPs
• NumPNullDistinctVals
• PNullHighModeFreq
• AvgAmpRPV
• HighModeFreq
• NumDistinctVals
• NumRows
• CPUUsage
• IOUsage

Example: Using the Default TDQCD Database to Capture and Store a Query Plan in XML Format

This request specifies all of the supported options and uses the default TDQCD database to store the query plan data.

     BEGIN QUERY CAPTURE AS workload wl_2;
     SELECT *
     FROM employee
     WHERE emp_code > 2;

An END QUERY CAPTURE request ends the BEGIN QUERY CAPTURE mode.

     END QUERY CAPTURE;

Example: Capturing a Mix of DML and DDL Requests in XML Format Using BEGIN QUERY CAPTURE

You have created the following employee table in the user_1 database.

     CREATE TABLE user_1.employee (
       emp_code  INTEGER,
       emp_name  VARCHAR(30),
       dept_code INTEGER,
       salary    FLOAT);

Assume that employee is populated with 1 million rows and that you collect statistics on the dept_code column using the following request.

     COLLECT STATS COLUMN (dept_code) ON employee;

The following BEGIN QUERY CAPTURE request specifies the VERBOSE and DETAILED STATSUSAGE options and captures query plan data in the user_1_QCD query capture database.

     BEGIN QUERY CAPTURE
     WITH VERBOSE, DETAILED STATSUSAGE
     INTO user_1_QCD
     AS WORKLOAD wl_1;

This BEGIN QUERY CAPTURE request captures the Object, SQL, Step Details, Verbose EXPLAIN text, statistics details, and information on the statistics used and statistics recommendations for all the following requests.

     SELECT *
     FROM employee
     WHERE emp_code > 2;
     DELETE FROM employee
     WHERE emp_code = 10;
     UPDATE employee
     SET dept_code = 11
     WHERE emp_code = 11;
     CREATE TABLE dept (
       dept_code INTEGER,
       dept_name VARCHAR(30),
       manager   VARCHAR(30));
     INSERT DEPT(11, 'HR', 'JOHN');

An END QUERY CAPTURE request terminates the BEGIN QUERY CAPTURE mode.

     END QUERY CAPTURE;    /* ends the BEGIN QUERY CAPTURE mode */

The following SELECT request returns the query IDs of all the requests that were captured in the QUERY CAPTURE session.

     SELECT DISTINCT ID
     FROM user_1_QCD.Xmlqcd
     WHERE WorkLoadName = 'wl_1';