Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 154 155 156 157 158 159 < 160 > 161 162 163 164 165 166 .. 218 >> Next


13 - 4 Teradata RDBMS Database Administration

Chapter 13: Database Analysis

Query Capture Facility (QCF)

SQL Statement

Description

Comments

References

INSERT EXPLAIN INTO QCDdbname querytext WITH STATISTICS

.INSERT EXPLAIN INTO QCDdbname querytext WITH STATISTICS AND DEMOGRAPHICS

Collects the same information as COLLECT STATISTICS (QCF Form)

Stores the data in TableStatistics (the table used by COLLECT STATISTICS) in the specified database for subsequent analysis by the Teradata Index Wizard.

Normally invoked via the client-based Database Query Analysis tools, using the WITH STATISTICS, FOR tablename, and FOR frequency clauses.

Note: You must have the INSERT privilege on all tables in the QCD database to use the INSERT and DUMP forms of EXPLAIN.

"Query Workload and Analysis Statements" in In Teradata RDBMS SQL Reference, Volume 4

"Database Query Analysis" in Teradata RDBMS SQL Reference, Volume 6

Uses an SQL query to colloct exactly the same information as COLLECT DEMOGRAPHICS Stores the information in DataDemographics (the table used by COLLECT

DEMOGRAPHICS) in the specified database.

Stored information is automatically deleted when you delete the relevant query plans.

Compare with COLLECT DEMOGRAPHICS and DUMP EXPLAIN

DUMP EXPLAIN INTO QCDdbname querytext

Performs an EXPLAIN on the specified query

Captures the Optimizer plan information

Returns a script containing a series of INSERT statements that, when executed, will store the captured data in the QCD tables

Use DUMP EXPLAIN instead of INSERT EXPLAIN if you:

Are collecting data from multiple systems and want to update just one QCD database

Want to control the updates to QCD tables (for example, by using TDQM scheduling rules)

"Query Capture Facility" in Teradata RDBMS SQL Reference, Volume 2

"INSERT EXPLAIN" in the chapter titled "Query Workload and Analysis Statements" in Teradata RDBMS SQL Reference, Volume 4 "Database Query Analysis" in Teradata RDBMS SQL Reference, Volume 6

"Scheduling Workloads with Teradata Dynamic Query Manager (TDQM)" on page 15-12

13 - 4

Teradata RDBMS Database Administration Chapter 13: Database Analysis

Query Capture Facility (QCF)

SQL Statement Description Comments References
DIAGNOSTIC 'validate index' In index validation mode, the Parser creates pseudo indexes corresponding to the CREATE INDEX statements in the workload cache and collects the statistics for the COLLECT STATISTICS statement into the QCD tables. Sets the index validation mode for the session and validates the indexes proposed by the Teradata Index Wizard utility. "Query and Workload Analysis Statements" in Teradata RDBMS SQL Reference, Volume 4

13 - 4 Teradata RDBMS Database Administration

Chapter 13: Database Analysis

Teradata Index Wizard

Teradata Index Wizard

The Teradata Index Wizard uses the contents of the tables in QCD to analyze specific workloads and suggest SI assignments that should improve throughput. The recommendations are for secondary indexes only, and may recommend deletion, as well as addition, of certain columns or indexes. You use these recommendations to evaluate potential performance improvements and modify your database accordingly.

Recommendations can include adding or deleting SIs to or from an existing design.

Teradata Index Wizard offers the following benefits:

Simulates candidate secondary indexes without incurring the cost of creation

Validates and implements SI recommendations

Provides automatic "what-if" analysis of user-specified index candidates

Interfaces with the Teradata System Emulation Tool to allow workload analysis on test systems as if the workload had been analyzed on the production system

Interfaces with the Teradata Visual Explain utility to compare query plans in the workloads

Preparing your database for analysis by the Teradata Index Wizard involves the following (for instructional procedures, see "Database Foundations for Teradata Index Wizard" in Teradata RDBMS SQL Reference, Volume 2):

Identifying a set of SQL statements that constitute a workload. Repositories that can be used to identify workload components include the:

Database query logs (see "Tracking Processing Behavior with the Database Query Log (DBQL)" on page 14-1)

QCD database (see "Query Capture Facility (QCF)" on page 13-4 and "Query Capture Facility" in Teradata RDBMS SQL Reference, Volume 2)

If you use QCF, executing the SystemFE.AddWorkload macro to define the new workload

Performing an index analysis on the defined workload (see "INITIATE INDEX ANALYSIS . FOR workloadname ." on page 13-11)

Scheduling application of the index recommendations

Prototyping and validating the recommended indexes (see "DIAGNOSTIC "Validate Index"" on page 13-11)

Creating the validated indexes on the tables of your production system by executing the generated DDL statements. You can use the Teradata Index Wizard tool to submit them immediately, or you can use TDQM to schedule
Previous << 1 .. 154 155 156 157 158 159 < 160 > 161 162 163 164 165 166 .. 218 >> Next