Teradata Performance Tuning Docx 636n58

Teradata Performance Tuning - Basic Tips Performance tuning thumb rules. Here are very basic steps which are used to PT any given query in given environment . As a prerequiste , make sure - has proper select rights and actual profile settings - Enough space available to run and test the queries 1. Run explain plan (pressing F6 or “EXPLAIN sel * …”,) Then see for potential information like - No or low confidence - Product s conditions - By way of an all row scan - FTS - Translate Also check for - Distinct or group by keywords in SQL query - In/ not in keywords and check for the list of values generated for the same APPROACHES A. In case of product scenarios,check for - Proper usage of alias - ing on matching columns - Usage of keywords - like specifying type of s (ex. inner or outer ) - use union in case of "OR” scenarios - Ensure statistics are collected on columns and this is especially important if the columns you are ing on are not unique. B. collects stats - Run command "diagnostic help stats on for the session" - Gather information on columns on which stats has to be collected - Collect stats on suggestions columns - Also check for stats missing on PI, SI or columns used in s - "help stats . - Make sure stats are re-collected when at-least 10% of data changes - remove unwanted stats or stat which hardly improves performance of the queries - Collect stats on columns instead of indexes since index dropped will drop stats as well!! - collect stats on index having multiple columns, this might be helpful when these columns are used in conditions - Check if stats are re-created for tables whose structures have some changes c. Full table scan scenarios - Try to avoid FTS scenarios as, it might take very long time to access all the data in every amp in the system - Make sure SI is defined on the columns which are used as part of s or Alternate access path. - Collect stats on SI columns else there are chances where optimizer might go for FTS even when SI is defined on that particular column 2. If intermediate tables are used to store results, make sure that - It has same PI of source and destination table 3. Tune to get the optimizer to on the Primary Index of the largest table, when possible, to ensure that the large table is not redistributed on AMPS 4. For large list of values, avoid using IN /NOT IN in SQLs. Write large list values to a temporary table and use this table in the query

5. Make sure when to use exists/not exists condition since they ignore unknown comparisons (ex. NULL value in the column results in unknown) . Hence this leads to inconsistent results 6. Inner Vs Outer s Check which works efficiently in given scenarios.Some examples are - Outer s can be used in case of large table ing with small tables (like fact table ing with Dimension table based on reference column) - Inner s can be used when we get actual data and no extra data is loaded into spool for processing Please note for outer conditions: 1. Filter condition for inner table should be present in "ON" condition 2. Filter condition for outer table should be present in "WHERE" condition

COLLECTION OF STATISTICS USING SAMPLE Here is the step by step process on how to collect statistics using SAMPLE

SAMPLE statistics is collected using Random amp sampling and it is recommended to use when we dont have stats collected on index or set of columns.

As a preparation step we check whether table is suitable for SAMPLE STATISTICS collection using following query /* suggested to use when data skew is less and also when more rows are there in table than number of amps*/ SEL TAB1.A AS TABLECOUNT, TAB2.B AS AMPCOUNT, CASE WHEN TABLECOUNT > AMPCOUNT THEN ' RANDOM AMP SAMPLING CAN BE SUGGESTED' ELSE 'RANDOM AMP SAMPLING NOT NEEDED' END FROM (SEL COUNT (*) AS A FROM TABLENAME) TAB1, (SEL HASHAMP () +1 AS B) TAB2;

Below is step by step process on Collecting statistics using SAMPLING

1. Check if Stats are already collected on the column of table for which Random AMP sampling is to be considered using HELP STATISTICS ON . ;

If YES, then this situation is tricky and do you still want to try out SAMPLING or look for other recommendations is up-to you..

2.

If NO then, check if column is highly skewed using following Query.

SELECT HASHAMP (HASHBUCKET (HASHROW ( )))

,

COUNT (*) FROM . GROUP BY 1; If you see that Data is equally distributed among all the amps (Variance of +-5 % is accepted),

If there is large amount of DATASKEW in one AMP, then SAMPLING is not a good option

If you don’t find data skew on any particular AMP then,

3.

Run sample statistics on column of particular table as follows. COLLECT STATICSTICS ON . COLUMN ( ) USING SAMPLE;

4. Check the performance of query after running sample STATS, also note the time taken collecting sample stats.

5. If not satisfied with performance, try to run full statistics on columns and measure performance and time taken to collect full stats

Decide which is the best option “FULL STATS or SAMPLE“considering factors like

6. o o o o o o

Performance, Time taken for statistics collection on scenarios, Table size, Data skew, Frequency of table being loaded How many times this table would be used in your environment.

Advantages of Collecting Stats using Sample 1.

Only a sample of table rows is scanned. Default being 2%.

It is based on random amp sampling estimate of total rows. If you want to override the default value for particular session then use, DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=n" ON FOR SESSION;

for

2. It uses less U and I/O resources compared to full statistics hence saving considerable amount of time and resources.

It is not recommended to used for 1.

Columns which are not indexed

2.

Indexes which has lot of duplicates or non unique combinations

3.

for small tables like dimension /key tables

4.

for tables that have greater data skew.

Please note that Sample statistics cannot be collected on 1.

Global temporary tables

2.

indexes

Comments and Suggestions are welcome!!! Posted by Vinay SHet at 2:46 PM No comments: Links to this post Email ThisBlogThis!Share to TwitterShare to Facebook Labels: Performance tuning, statistics

Friday, December 23, 2011

Performance Tunings tips : Inlist vs Between There are lot of questions running around guys who do tuning . Sometimes they do suggest use of temporary tables instead of using large in list values, as the optimizer would go for value in XXX or Value in XXY or Value in XXZ to generate a explain plain. What if the column compared against large in-list values was part of any index say PI,SI,JI.... ? Sometimes it so happens that even after using a temp table with list of values you would still get same performance issue, why so? Did you ever consider to use "between" clause to check if the query performed better ?? Did you... I would say give it a try to see if this would be much better option compared to standard "temp table " against the in list

Say for example: SELECT customer_number, customer_name FROM customer WHERE customer_number in (1000, 1001, 1002, 1003, 1004); is much less efficient than:

SELECT customer_number, customer_name FROM customer WHERE customer_number BETWEEN 1000 and 1004

We are assuming that an index on customer_number, the Query Optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause. Check for explain to compare difference, if still the same... refresh/collect stats on index column it would help. If still you would find some kind of issue try to find out skewness of column using following query and try to rectify the issue. Sel hashamp(hashbucket(hashrow(customer_number))), count(*) from Customer group by 1;

Over all i would say trying the query with between would be of great help. But query tuning is such a complex thing you will never get what you want unless you understand the data ;-) Posted by Vinay SHet at 11:58 AM 3 comments: Links to this post Email ThisBlogThis!Share to TwitterShare to Facebook Labels: Performance tuning

Tuesday, April 5, 2011

Peformance tuning Tips : Considerations If you are working on writing queries, working on performance or helping in betterment of performance. You will have to take sometime in going through this topic. It is all to do about s which is most important concern in Teradata.

If some light is given to following suggestions, any related issues can be taken care off...

Tip 1: ing on PI/NUPI/ Non PI columns

We should make sure is happening on columns composed of UPI/NUPI. But why??

Whenever we two tables on common columns, the smart optimizer will try to take data from both the data into a common spool space and them to get results. But getting data from both the tables into common spool has overhead.

What if I ed a very large table with small table? Should small table be redistributed or large table?

Should small table be duplicated across all the AMPs? Should both the tables be redistributed across all the AMPs??

Here is some basic thumb rules on ing columns on Index, so ing happens faster.

Case 1 - P.I = P.I s There is no redistribution of data over amp's. Since amp local s happen as data are present in same AMP and need not be re-distributed. These types of s on unique primary index are very fast.

Case 2 - P.I = Non PI column s -Data from second table will be re-distributed on all amps since s are happening on PI vs. NUPI column. Ideal scenario is when small table is redistributed to be ed with large table records on same amp -Data in small table is duplicated to Every AMP where it is ed locally with large table

Case 3 - No Index = Non PI column s Data from both the tables are redistributed on all AMPs. This is one of the longest processing queries , Care should be taken to see that stats are collected on these columns

Tip 2: The columns part of must be of the same data type (CHAR, INTEGER,). But why?!?

When trying to columns from two tables, optimizer makes sure that datatype is same or else it will translate the column in driving table to match that of derived table.

Say for example TABLE employee deptno (char) TABLE dept deptno (integer)

If I am ing employee table with Dept on employee.deptno(char) = dept.deptno(Integer), optimizer will convert character column to Integer resulting in translation . What would happen if employee table had 100 million records and every time deptno would have to undergo Translation. So we have to make sure to avoid such scenarios since translation is a cost factor and might need time and system resources.

Make sure you are ing columns that have same data types to avoid translation!!!!

Tip 3 : Do not use functions like SUBSTR, COALESCE , CASE ... on the indices used as part of . Why?!?

It is not recommended not to use functions such as SUBSTR, COALESCE, CASE and others since they add up to cost factor resulting in performance issue. Optimizer will not be able to read stats on those columns which have functions as it is busy converting functions. This might result in Product , spool out issues and optimizer will not be able to take decisions since no stats/demographics are available on column. It might assume column to have 100 values instead of 1 million values and might redistribute on wrong assumption directly impacting performance.

Tip 4 : use NOT NULL where ever possible!

What?!! Did someone say Not Null?? .. Yes, we have to make sure to use NOT null for columns which are declared as NULLABLE in TABLE definition. Reason being that all the Null values might get sorted to one poor AMP resulting in infamous " NO SPOOL SPACE " Error as that AMP cannot accommodate any more Null values. SO to use NOT NULL in ing so that table SKEW can be avoid .

Since V2R5 , teradata automatically adds the condition « IS NOT NULL » to the query. Still it is better to ensure NOT NULL columns are not included as part of the

Posted by Vinay SHet at 1:54 PM 1 comment: Links to this post Email ThisBlogThis!Share to TwitterShare to Facebook Labels: Performance tuning

Monday, March 21, 2011

Locking table for access?

Locking table for access?

We would have come across this statement in many queries which are run in sensitive environments like PROD, UAT. They can be used with views or sometimes just for querying purpose.

I wanted to discuss how important this statement would be in real-time /active data warehouses where lot of s will be striking queries on same database at the time.

create view Employee.view_employ_withLock as locking table Employee.Dept_emp for access select * from

Employee.Dept_emp ;

By using locking table for access, we make sure that normal "access" lock is applied on table which is required to fetch results. By doing so ,

- There is no waiting for other locks to release since access lock can be applied on table which has read/write lock applied to it - This will cause the query to execute even when some lock is applied , but accessing data using this lock might not be consistent as it might result in dirty read due to concurrent write on the same table.

It is always suggested to use “locking table for access" which since they will not block the other s from applying read/write lock on the table. Posted by Vinay SHet at 8:08 AM No comments: Links to this post Email ThisBlogThis!Share to TwitterShare to Facebook Labels: Performance tuning, tips

Wednesday, December 8, 2010

Peformance tuning Tips : LIKE Clause While tuning queries in Teradata , We take care of major performance issues but ignore small cases which might still cause the query to perform badly.

I wanted to mention about one such case of LIKE clause , which many people good at performance tuning miss it assuming like patterns does not harm the performance . But in reality this is not so!!

If LIKE is used in a WHERE clause, it is better to try to use one or more leading character in the clause, if at all possible.

eg; LIKE '%STRING%' will be processed differently compared to LIKE 'STRING%'

If a leading character 'STRING%' is used in the begining of like clause , the the Qptimizer makes use of an index to perform on query thereby increasig the performance

But if the leading character' in '%STRING%' is a wildcard(say '%') , then the Optimizer will not be able to use an index, and a full table scan (FTS ) must be run, which reduces performance and takes more time.

Hence it is suggested to go for '%STRING%' only if STRING is a part of entire pattern say 'SUBSTRING' Posted by Vinay SHet at 7:57 AM No comments: Links to this post Email ThisBlogThis!Share to TwitterShare to Facebook Labels: Performance tuning

Tuesday, November 16, 2010

Teradata Performance Tuning - Basic Tips Performance tuning thumb rules.

Here are very basic steps which are used to PT any given query in given environment . As a prerequiste , make sure - has proper select rights and actual profile settings - Enough space available to run and test the queries

1. Run explain plan (pressing F6 or “EXPLAIN sel * …”,) Then see for potential information like - No or low confidence

- Product s conditions - By way of an all row scan - FTS - Translate

Also check for - Distinct or group by keywords in SQL query - In/ not in keywords and check for the list of values generated for the same

APPROACHES

A. In case of product scenarios,check for - Proper usage of alias - ing on matching columns - Usage of keywords - like specifying type of s (ex. inner or outer ) - use union in case of "OR” scenarios - Ensure statistics are collected on columns and this is especially important if the columns you are ing on are not unique.

B. collects stats - Run command "diagnostic help stats on for the session" - Gather information on columns on which stats has to be collected - Collect stats on suggestions columns - Also check for stats missing on PI, SI or columns used in s - "help stats . - Make sure stats are re-collected when at-least 10% of data changes - remove unwanted stats or stat which hardly improves performance of the queries - Collect stats on columns instead of indexes since index dropped will drop stats as well!! - collect stats on index having multiple columns, this might be helpful when these columns are used in conditions - Check if stats are re-created for tables whose structures have some changes

c. Full table scan scenarios - Try to avoid FTS scenarios as, it might take very long time to access all the data in every amp in the system - Make sure SI is defined on the columns which are used as part of s or Alternate access path. - Collect stats on SI columns else there are chances where optimizer might go for FTS even when SI is defined on that particular column

2. If intermediate tables are used to store results, make sure that - It has same PI of source and destination table

3. Tune to get the optimizer to on the Primary Index of the largest table, when possible, to ensure that the large table is not redistributed on AMPS

4. For large list of values, avoid using IN /NOT IN in SQLs. Write large list values to a temporary table and use this table in the query

5. Make sure when to use exists/not exists condition since they ignore unknown comparisons (ex. NULL value in the column results in unknown) . Hence this leads to inconsistent results

6. Inner Vs Outer s Check which works efficiently in given scenarios.Some examples are - Outer s can be used in case of large table ing with small tables (like fact table ing with Dimension table based on reference column) - Inner s can be used when we get actual data and no extra data is loaded into spool for processing Please note for outer conditions: 1. Filter condition for inner table should be present in "ON" condition 2. Filter condition for outer table should be present in "WHERE" condition

This article is prepared to understand the key points required to be considered while analyzing/improving the Teradata ETL performance. These points generally refer to the periodic activities or the housekeeping activities that should be done periodically to avoid the database performance implications. Key Points: 1. Stat Collection 2. Pack Disk 3. Data Skew Analysis 4. Lock monitoring (Locking Logger) 5. Session tuning (DBS Control parameters) 1). Stat Collection : Statistics on the loaded tables are very important from the performance perspective. Statistics helps optimizer to generate the accurate & faster query plans. An old statistics in warehouse can lead to wrong query plans which may take time in query processing. Hence it is much required to refresh the statistics periodically at least in the warehousing environment. We can identify the tables which are frequently accessed & modified with inserts, updates, deletes etc. It is recommended to refresh the stats after every 10% of data change. We can collect the statistics at column level or at index level. Syntax:

Collect statistics on column (column_name 1,.., column_name n); OR Collect statistics on index (column_name 1,.., column_name n); 2). Pack Disk : Pack disk is an utility that free up the cylinder space on the database, this utility must be run periodically as in the warehouse environment large amount of data inserts, updates are happening which causes the physical memory to disorder due to frequent data manipulation. Pack disk utility allows us to restructure & physically reorder the data, free up space same as defragmentation. Teradata also run mini CYLPACKs automatically, if cylinder space goes below the prescribed limit. Cylinder space is required for the merge operation while the data Insert, Deletes Updates etc. To run a pack disk we use Ferret utility provided by Teradata can be run through Teradata Manager Tool or through telnet on node session.

The set of commands that starts packdisk utility are given below one can create a kron job to schedule the same & run it periodically.

Commands to run pack defrag & packdisk utilities : ~ferret defrag Y packdisk fsp=8 Y 3). Skew Analysis : Primary index of a table in Teradata is responsible for the data distribution on all the AMPs. Proper data distribution is required for the parallel processing in the system. As Teradata system follows shared nothing architecture, all the AMPs works in parallel. If data is evenly distributed amongst the AMPs then the amount of the work done by every AMP would be equal & time required for particular job would obviously be lesser. In contrast to this if only one/two AMPs are flooded with the data i.e. data skew then while running that job the two AMPs would be working & others will be idle. In this case we won’t be utilizing the parallel processing power of the system. To avoid such data skew need to analyze the primary index of the tables in Teradata database over the period of time it might happen that data is getting accumulate at the few AMPs, which can have a adverse effect on the ETL as well as the system performance. To analyze the data distribution for the table we can use the inbuilt HASH functions provided by the Teradata. To check the data distribution for a table one can use a query:

SELECT HASHAMP (HASHBUCKET (HASHROW (Column 1,.., column n))) AS AMP_NUM, count(*) From Table_Name Group by 1; This query will provide the distribution of records on each AMP

we can also analyze the probable PIs with this query which will predict the data distribution on the AMPs 4). Lock monitoring : Locking Logger is an utility that enables us to monitor the locking on the tables. Using this utility we can create a table that has the entries for the locks which have been applied to the tables while processing. This utility allows us to analyze the regular ETL process, jobs being blocked at particular time when there is no one to monitor the locking. By analyzing such locking situations we can modify the jobs & avoid the waiting period due to such situations. To apply this locking loggers First, we need to enable locking logger via the DBS console window or the cnsterm subsystem. The setting does not take effect until the database is restarted.

LockLogger - This Field defines the system default for the locking logger. This allows the DBA to log the delays caused by database locks to help in identifying lock conflicts. To enable this feature set the field to TRUE. To disable the feature set the field to FALSE. After a database restart with the LockLogger flag set to true, the Locking Logger will begin to accumulate lock information into a circular memory buffer of 64KB. Depending on how frequently the system encounters lock contention, this buffer will wrap, but it will usually span a several day period. Following a period of lock contention, to analyze the lock activity, you need to run the dumplocklog utility which moves the data from the memory buffer to a database table where it can be accessed. 5). Session Tuning: Session tuning is done for the running the load utilities in parallel this requires to analyze some DBScontrol parameters & tune the same to provide the best parallel processing of the load utilities. There are two parameters MaxLoadAWT & MaxLoadTasks that enables the parallel job management a short note on the same: The MaxLoadAWT internal field serves two purposes: 1) Enabling a higher limit for the MaxLoadTasks field beyond the default limit of 15. 2) Specifying the AMP Worker Task (AWT) limit for concurrent FastLoad and MultiLoad jobs when a higher limit is enabled.

In effect, this field allows more FastLoad, MultiLoad, and FastExport utilities running concurrently while controlling AWT usage and preventing excessive consumption and possible AWT exhaustion. 

The default value is zero: When MaxLoadAWT is zero, concurrency limit operates in the same manner as prior to V2R6.1 MaxLoadTasks specifies the concurrency limit for all three utilities: FastLoad, MultiLoad, and FastExport. The valid range for MaxLoadTasks is from 0 to 15.



When MaxLoadAWT is non-zero (higher limit enabled): It specifies the maximum number of AWTs that can be used by FastLoads and MultiLoads. Maximum allowable value is 60% of the total AWTs. The valid range for MaxLoadTasks is from 0 to 30.



A new FastLoad/MultiLoad job is allowed to start only if BOTH MaxLoadTasks AND MaxLoadAWT limits are not reached. Therefore, jobs may be rejected before MaxLoadTasks limit is exceeded. MaxLoadTasks specifies the concurrency limit for the combination of only two utilities: FastLoad and MultiLoad. FastExport is managed differently; FastExport is no longer controlled by the MaxLoadTasks field.



A FastExport job is only rejected if the total number of active utility jobs is 60. At least 30 FastExport jobs can run at any time. A FastExport job may be able to run even when FastLoad and MultiLoad jobs are rejected. When a Teradata Dynamic Workload Manager (TDWM) utility throttle rule is enabled, the MaxLoadAWT field is overridden. TDWM will use the highest allowable value which is 60% of total AWTs. Update to MaxLoadAWT becomes effective after the DBS control record has been written. No DBS restart is required. Note that when the total number of AWTs (specified by the internal field MaxAMPWorkerTasks) has been modified but a DBS restart has not occurred, then there may be a discrepancy between the actual number of AWTs and the DBS control record. The system may internally reduce the effective value of MaxLoadAWTs to prevent AWT exhaustion. 

AWT Usage of Load Utilities: All load/unload utilities require and consume AWTs at different rates depending on the execution phase: FastLoad: – Phase 1 (Loading): 3 AWT’s – Phase 2 (End Loading): 1 AWT’s MultiLoad*: – Acquisition Phase (and before): 2 AWT’s. Application Phase (and after): 1 AWT’s FastExport: – All.

This description is for the single target table case which is the most common. The above explained parameters can be analyzed & tuned accordingly to achieve the expected performance on the Teradata system. Also need to have some maintenance/ House keeping activities in place to avoid the performance implications due to some physical data parameters like data skew, less cylinder space etc.

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 2. 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: a. U consumption b. Parallel Efficiency / Hot amp percentage c. Spool usage 3. Help the Parser:

Since the architecture has been made to be intelligent, we have to give it some respect . 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: d. Incorrect stats are worse than not collecting stats, so make sure your stats are not stale(old) e. 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 4. 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. 5. 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