The Truth About Teradata Sessions And Performance
Teradata Sessions explained in a few Sentences
A session is a LOGON of a user on a Teradata system, through which one or more sequential transactions can be executed. The session is terminated by a LOGOFF statement.
For Teradata, multiple sessions are like a number of users (but with the same user credentials). Each of these sessions belongs to the same Teradata user and can execute one or more sequential transactions between LOGON and LOGOFF just like a single session.
The use of multiple sessions makes it possible to run multiple tasks in parallel. The aim is to increase performance.
Not at all. For several sessions (e.g. in a BTEQ script) to increase performance, Rowhash accesses must be made. Rowhash access only occurs if a UPI, NUPI, or USI is used. All other access paths require a table lock, and using multiple sessions does not improve performance.
A BTEQ import script can usually increase performance by using multiple sessions. BTEQ export scripts usually do not achieve any performance advantage by using multiple sessions.
This is difficult to answer and is best determined empirically. What is important is an access path via the Rowhash (UPI, NUPI, USI). Too few sessions do not bring out the maximum performance. Too many sessions do not bring any more performance advantage after a certain point but increase the administration effort.
For Full Table Scans and NUSI accesses.
Example 1: Multiple Sessions which do not improve Performance
As shown in the graph above, all 4 transactions (T1, T2, T3, T4) are active on all AMPs simultaneously. Even if only one row is required, all of these transactions involve all AMPs to look this row up. None of the transactions makes access via a single rowhash. Therefore, there is no performance gain.
Example 2: Multiple Sessions which do improve Performance
In example 2, each transaction is only active on exactly one AMP. Therefore the 4 AMPs can work on different tasks at the same time.
In summary, the use of multiple sessions is only useful if:
- Each transaction uses less than all AMPs
- Ideally, there are enough sessions available to keep all AMPs equally busy with transactions.