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 .. 155 156 157 158 159 160 < 161 > 162 163 164 165 166 167 .. 218 >> Next


13 - 10

Teradata RDBMS Database Administration Chapter 13: Database Analysis

Teradata Index Wizard

them for automatic submission later (see "Index Application" in Teradata RDBMS SQL Reference, Volume 2, and "Scheduling Workloads with Teradata Dynamic Query Manager (TDQM)" on page 15-12)

When using the Teradata Index Wizard, keep the following in mind:

Teradata Index Wizard does not generate index recommendations for any query that defaults to a full table scan.

To ensure that index recommendations produce optimum results when applied to your production system, use TLE to emulate your production environment on a test system and evaluate indexes there (see "Target Level Emulation (TLE)" on page 13-3).

The following SQL statements are associated with Query Analysis and the

Teradata Index Wizard. 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.

Statement Description Comment References
INITIATE INDEX ANALYSIS ... FOR workloadname ... Analyzes a query workload for index opportunities Saves recommended indexes in the IndexRecommendations and IndexColumns tables Normally invoked via the Teradata Index Wizard utility. Use the QCF Query Macros to retrieve the evaluation data. "Database Foundations for the Teradata Index Wizard" in Teradata RDBMS SQL Reference, Volume 2 Teradata Index Wizard User Guide
RESTART INDEX ANALYSIS Restarts a previously halted analysis started by an INITIATE INDEX ANALYSIS statement that specified a checkpoint. "Database Foundations for the Teradata Index Wizard" in Teradata RDBMS SQL Reference, Volume 2 Teradata Index Wizard User Guide
DIAGNOSTIC "Validate Index" Sets the index validation mode for the session Validates the indexes proposed by the Teradata Index Wizard utility. Recommendations may include the SQL DDL statements CREATE/DROP INDEX and COLLECT/ DROP STATISTICS (Optimizer form). Normally invoked via the Teradata Index Wizard utility. Use the SQL interface to simulate indexes; use sampled statistics to gauge the behavior of queries with these indexes. (Statistics used for analysis are taken from the QCD database.) In Teradata RDBMS SQL Reference, Volume 2: - "Index Validation" - "DIAGNOSTIC "Validate Index""

13 - 10 Teradata RDBMS Database Administration

Chapter 13: Database Analysis

Teradata Visual Explain Utility

Teradata Visual Explain Utility

Teradata Visual Explain client-based utility is a powerful interface for application performance analysis and comparison. You can use it to:

Generate a description of the query processing sequence to determine whether an existing SI should be retained

Compare the same query run on differing releases or operating systems

Compare queries that are semantically the same but syntactically different

The results can help you understand changes:

To your Teradata database schema, physical design, and statistics

Over Teradata release boundaries

For more information, see Teradata Visual Explain User Guide. You can also use the Teradata Index Wizard client utility to determine optimum SIs for particular SQL workloads (see "Teradata Index Wizard" on page 13-10).

13 - 12

Teradata RDBMS Database Administration Chapter 13: Database Analysis

Building Baseline Transaction Profiles

Building Baseline Transaction Profiles

Baseline profiles can provide information on typical resource usage by period and by user, on a daily, weekly, or monthly basis.

You can build baseline profiles for:

Single operations (such as FastLoad, full table scans, primary index INSERT SELECTs, select joins)

Multiple, concurrently run transactions

Once defined and stored, baseline profiles can help you:

Compare current to profiled operations on a real-time basis.

Collect data instantaneously for a set interval.

Detect and resolve throughput anomalies.

Types of Performance Metrics

The following types of performance metrics are recommended for profiling. You can gather these metrics from:

ResUsage reports (see "ResUsage Reports" on page D-11)

DBQL (see Chapter 14: "Tracking Processing Behavior with the Database Query Log (DBQL)")

Teradata Visual Explain utility (see "Teradata Visual Explain Utility" on page 13-12)

Metric Description
Elapsed time Time for a job or transaction to run from beginning to end, either in actual seconds, or within a set of specified time intervals, or below a specified time limit.
Resource utilization Percentage of time a resource (CPU, disk, or BYNET) is busy processing a job. For example, for a full-table scan, CPU usage may be 30% busy and disk usage may be 70% busy.
Throughput rate Transaction (total number of transactions in a job divided by job elapsed time) Rows (total number of rows in a table divided by elapsed time of an all-rows transaction) Parallel processing (rows per second, per AMP or PE).
Previous << 1 .. 155 156 157 158 159 160 < 161 > 162 163 164 165 166 167 .. 218 >> Next