Identify the queries or workload to be analyzed for performance tuning. Logging onto
Teradata DBQL is one easy way to selectively capture SQL queries at query run time.
Capture query plans into the Query Capture Database (QCD). For individual queries
or a small set of queries, use the Teradata Visual Explain Launch QCF feature. For
bigger workloads, the Teradata Index Wizard Workload Definition feature can be used
to define the workload and capture query plans for all queries in the workload.
Optionally, export the captured query plans, system cost parameters, statistics, data
demographics, random AMP samples, and object definitions from the production or target
system to a test system using Teradata System Emulation Tool. This step is not necessary,
but enables off loading of the analysis activities from the production to the test
environment.
Find out the statistics and data demographics for the tables involved in the workload
using Teradata Statistics Wizard. Statistics recommended by TSWiz for the tables/columns
in the workload can be applied immediately. At this point, you can optionally recapture
the plans for the workload and compare them using Teradata Visual Explain's query
or bulk compare feature. Doing this allows you to visualize and measure the improvements
achieved by implementing the Teradata Statistics Wizard recommendations.
Perform index analysis for the workload using Teradata Index Wizard. In the case of
analysis on a test system, the index recommendations can be validated back on the
production system using the Validate Recommendations feature. This will simulate the
indexes without actually creating them, and produce a query plan as though the indexes
were present. Validation also collects sampled statistics as part of the process,
thus ensuring up-to-date statistics are available for the proposed indexes.
Analyze the output of the Teradata Index Wizard reports to understand the recommendations'
impact on performance (estimated performance improvement), and cost to implement (in
terms of disk space and creation time). Utilize Teradata Visual Explain to compare
pre- and post-recommendation plans (post recommendation plans are automatically generated
and stored for each query in the workload during Index Validation). Implement the
recommendations on the production system to achieve the performance improvement desired,
or alternatively, use the what-if analysis mode to identify potential performance
improvements of your own proposed secondary indexes.
To learn more about Teradata Index Wizard, see How Index Wizard Works.
How Teradata Index Wizard Works
Index Wizard Features
Teradata Analyst Pack
User DocumentationOnline Help