Download (direct link):
Teradata Visual Explain User Guide
isolate those SQL requests that may be candidates for performance tuning
• Use the Teradata Statistics Wizard to find recommendations of tables, columns, and/or indexes on which to collect statistical data
• To capture the data, use either:
- To capture queries in QCD, the SQL INSERT EXPLAIN or DUMP EXPLAIN (with the mandatory INTO QCDdbname) statement, followed by the SQL text
- To log query text in DBQL, the SQL BEGIN QUERY LOGGING WITH SQL statement
• To isolate the qualifying requests:
- DBQL time-limit logging
- Teradata Visual Explain
"Teradata SQL Statements for Query Analysis" on page 13-6 Chapter 14: "Tracking Processing Behavior with the Database Query Log (DBQL)"
"BEGIN QUERY LOGGING" in Teradata RDBMS SQL Reference, Volume 4
Teradata Statistics Wizard User Guide Teradata System Emulation Tool User Guide
identify a workload
use queries from either or both:
• QCD tables
• DBQL logs
Under "Database Foundations for the Teradata Index Wizard" in Teradata RDBMS SQL Reference, Volume 2:
- "Teradata Index Wizard Overview"
- "Workload Identification"
13 - 4 Teradata RDBMS Database Administration
Chapter 13: Database Analysis
Query Capture Facility (QCF)
IF you want to . THEN . For instructions, see .
define and register a workload in a QCD by associating it with queries Note: NCR recommends that you use the suite of QCF macros in system user SystemFE to define, modify, and delete QCD workloads. • Use the SystemFE.AddWorkload macro. Execute the macro using either: - The Teradata Index Wizard (normal procedure) - SQL BEGIN TRANSACTION and END TRANSACTION statements • Use the following QCD tables: - Workload (the workloadID links the workload to the queries) - Workload Queries (associates queries with a given workload) • Teradata RDBMS SystemFE Macros • Under "Database Foundations for the Teradata Index Wizard" in Teradata RDBMS SQL Reference, Volume 2: - "Teradata Index Wizard Overview" - "Workload Definition"
analyze indexes to obtain a set of index recommendations use the Teradata Index Wizard. • "Teradata Index Wizard" on page 13-10 • "Index Analysis" in Teradata RDBMS SQL Reference, Volume 2
validate the indexes recommended by the Teradata Index Wizard • Compare query responses using the existing index and the recommended index against the same workload. • Use TLE to emulate your production environment on a test system and evaluate indexes there. • "Index Validation" in Teradata RDBMS SQL Reference, Volume 2 • "DIAGNOSTIC 'Validate Index'" in the chapter "Query and Workload Analysis Statements" in Teradata RDBMS SQL Reference, Volume 4
Teradata SQL Statements for Query Analysis
The following Teradata SQL statements are associated with QCF, Query Analysis, and the Teradata Index Wizard.
Note: The Query Analysis statements are usually invoked by the Teradata Index Wizard or other client-based Teradata Analyst tools, but you can also submit them from a BTEQ script or interactive session.
Because a default database is not provided, specify in each statement the name of the QCD database you want to update.
A full suite of statements and commands are available. For a complete list, plus statement syntax, usage notes, and examples, see "Query and Workload Analysis Statements" in Teradata RDBMS SQL Reference, Volume 4.
13 - 4
Teradata RDBMS Database AdministrationChapter 13: Database Analysis
Query Capture Facility (QCF)
SQL Statement Description Comments References
COLLECT STATISTICS FOR SAMPLE INTO QCDdbname ... • Samples the specified percentage of rows on each AMP to build the statistics for: - Non-indexed column or column set - Named index - Index column or column set • Computes a statistical synopsis of the data and stores it in the TableStatistics table of the specified database. • Normally invoked via client-based Database Query Analysis tools. • Statistics are used by various Database Query Analysis tools for index analysis and validation. (You can view the data using the STATS module of Teradata Manager.) • The data is not stored in the Data Dictionary or used by the Optimizer. • "Query Capture Facility" in Teradata RDBMS SQL Reference, Volume 2 • "Database Query Analysis" in Teradata RDBMS SQL Reference, Volume 6 • The STATS module in Teradata Manager User Guide
DROP STATISTICS FROM QCDdbname ... Drops sampled statistics on the specified table from TableStatistics in the specified database. Operates against the QCD repository, not the data dictionary tables.
COLLECT DEMOGRAPHICS FOR tablename INTO QCDdbname • For each AMP that is online, collects (on either primary and index data, or primary data only): - Estimated per-AMP row count for the specified table, to the index subtable level - Average row length - Identifier and system-related information • Writes the data to the DataDemographics table for analysis by the Teradata Index Wizard. • Normally invoked via the Teradata Index Wizard or Teradata Visual Explain. • Obtains information directly. • You must explicitly delete the data from DataDemographics (compare with INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS). • "Teradata Index Wizard" on page 13-10 • "Teradata Visual Explain Utility" on page 13-12 • "Database Foundation for the Teradata Index Wizard" in Teradata RDBMS SQL Reference, Volume 2 • "Query Workload and Analysis Statements" in Teradata RDBMS SQL Reference, Volume 4 • "Database Query Analysis" in Teradata RDBMS SQL Reference, Volume 6 • Teradata Index Wizard User Guide • Teradata Visual Explain User Guide