Performance Tuning


Performance Tuning

Hi ,

Which tool we can use to do performance tuning in Teradata.
As PMON is used to just view the query and to check the status of that query.


Re: Performance Tuning

I am also new to Teradata, but not performance management in general.

Can you elaborate on what you are looking to tune?

You could tune queries, which would involve looking at the access paths, data distrubution, Primary Keys and Primary indexes etc

you could look at tuning the System in general, Memory allocations CPU, number of Amps/PE et al.

There does not seem to be the wealth of information on the internet about Teradata, that does not originate from Teradata, as you would find with Oracle or DB2, and there are only a few books on Teradata on Amazon etc which are old.

I found the following books on Teradatas site to be helpful.

Resource Usage Macros and Tables.
Teradata RDBMS Performance Optimization
Performance management.

They gave me a good start, but I may have been starting from a different position.

Re: Performance Tuning

This is a huge topic that cannot be answered quickly. There are several Orange Books and Teradata classes that will help. Some of the Orange Books include topics on indexes (PPI and non-PPI), Tuning Tactical Queries, and many others. Orange Books can be downloaded from Teradata @ Your Service (contact your site administrator or DBA if you don't have access to T@YS). To tune queries, you need a solid understanding of the basics, including primary and secondary indexes (how to choose and how to use), database/table design, statistics, and joining tables. Spend a lot of time in the Teradata documentation (your DBA or site administrator should be able to help you install this on your computer). I don't have my list of classes that Teradata offers, but there are several that will help to gain enough understanding to tune queries. Last, but not least, use EXPLAIN to see how your queries will be run. Good luck!

Re: Performance Tuning

can any body tell me how PPIs will help in performance?

Re: Performance Tuning

can some one point me the right URL for Teradata @ Your Service?
so that i can download this Orange Book

Re: Performance Tuning

To start with the folllowing article in Teradata Magazine might help

Also refer to Teradata Documentation - performance - SQL and system performace

Re: Performance Tuning

I want to know about the index defintion

Re: Performance Tuning

Very Simple. If you want to start with Perfromance Tuning on SQL you need to check the below

1. Statistics are used

2. Partitions are used

You can find these two by

Use this sql in the session

Diagnsotic helpstats on for session;

Explain ;

This will give you collect stats recommendation.

For parition usage you have to read the explain plan if it is eliminating partitions or not.

Re: Performance Tuning

i have question here related to performance issue,
when we do a joins iam geting a error as skew issue
may i know why iam getng this error
n how to resolve this issue ,
if i find a collect stats on colum which iam using in join is that fine
do i need to collect stats on all the columns which iam using in that query

Re: Performance Tuning

What error are you getting exactly? In general you should collect statistics on columns used in joins and WHERE clauses.