Wednesday, July 7, 2010

Performance tuning #1

I have taken a long break from updating this blog.
To appease my mind, I am going to update a series of posts for Performance tuning, as it is going on in my project work.
When it comes to performance tuning, we cannot stick to a certain set of rules. It varies based on the data you are dealing with.
Although, we can create a baseline and address issues based on scenarios we face on a day to day basis.
Feel free to comment and ask questions. I will try and address them as soon as I can.
  1. Utilizing Teradata’s Parallel Architecture:
If you understand what happens in the background, you will be able to make your query work its best. So, try and run explain plan on your query before executing it and see how the PE(Parsing Engine) has planned to execute it.
Understand the Key-words in Explain plan. I will have to write a more detailed post on this topic. But for now, let us go on with the highlights
  1. Understanding Resource consumption:
Resource that you consume can be directly related to dollars. Be aware and frugal about the resources you use.
Following are the factors you need to know and check from time to time:
  1. CPU consumption
  2. Parallel Efficiency / Hot amp percentage
  3. Spool usage
  1. Help the Parser:
Since the architecture has been made to be intelligent, we have to give it some respect J. You can help the parser understand data you are dealing with, by collecting statistics. But you need to be careful when you do so, due to 2 reasons:
  1. Incorrect stats are worse than not collecting stats, so make sure your stats are not stale(old)
  2. If your dataset changes rapidly in your table, and suppose you are dealing with a lot of data, then collecting stats itself might be resource consuming. So, based on how frequently your table will be accessed, you will have to make the call
  1. Since same SQL can be written in different ways, you will have to know which method is better than which. For eg, creating Volatile table vs Global temp table vs working table. You cannot directly point out which is the best, But I can touch base on the pros and cons and comparison for them.
  2. Take a step back and look at the whole process. Consider how much data you need to keep, how critical is it for your business to get the data soon, how frequently do you need to run your SQL. Most of the times, the ‘big picture’ will give you a lot of answers


  1. Hello Meena,

    Can you please provide example Procedure where we can generate a surrogate key based on 4 input parameters and out parameter being the surrogate key of the Procedure.

  2. i hav so many doubt in teradata(this is my no 9985261714)plese feel free can u call me. this is thirupathi

  3. Hi Meena, Please provide your mail id

  4. Thank you all for the interest! I prefer not to post personal contact details in my blog to avoid spam. Kindly post a comment, I will try to respond to your request as early as possible.
    Apologies if I could not respond to you quickly, I might have forgotten myself @ work :)

  5. Sry for asking this question,
    Do you train, novice ppl in teradata, i have Oracle Db background, looking for ramp up in training. if so, can you kindly email to one.sbabu

  6. Hi meena this is gowri...working as a oracle core DBA..i have interest to learn TERADATA DATABASE..send any document regarding TERADATA to my

  7. WHAT ARE THE Issues ARE YOU FACING while loading the data in MLOAD?

  8. hai mam this is siva i am new to teradata what is difference between PE and optimizer plz send to my mail

  9. i ran one update query i need to update 1000 in between update query i can stop where i can see the upadate records, where is store records plz tell me i have face thes problem

  10. Great article Meena.....One question.....In multiload....we have some limitation for the target table...... one of the limitation cannot use USI in the target table. Why it is so?

  11. @Himanshu...In Multiload you cannot use USI on loading table, as mload has to load data parallely and independently in each amp that is what it is for to load the data as fast as possible,so if we use USI index subtable might be in other amp as compared to your data row that will need a communication between the two amps and it doesn't support that, you can use NUSI in mload. Rather u can drop the secondary indexes before loading and can recreate them after the load is completed.


  12. Hi Meena..
    In teradata how the data flow from source to target?would u plzzz send this answer to my mail

  13. hi madam dis is ram from india...
    i have some doubts in teradata performance tuning and indexes?...
    when ever u r free plz reply me...

  14. Hi meena this is prala....i have interest to learn TERADATA DATABASE..send any document regarding TERADATA to my

  15. Hi Meena ,My side am Santhosh am working as a teradata Developer from last 3 years onwards . Nice blog and good respose also. Keep rocking :).
    From myside i will do my best . My mail id

    Have a nice day.

  16. What are the Key- words need to understand in explain plan and how it helps while taking decisions to improve the performance ? Plz help me with some detailed scenarios.