Teradata Sessions explained in a few Sentences
What is a Teradata session?
A session is a LOGON of a user on a Teradata system, through which one or more sequential transactions can be executed. A LOGOFF statement terminates the session.
What are multiple Teradata sessions (e.g., in BTEQ)?
For Teradata, multiple sessions are like several users (but with the same user credentials). Each session belongs to the same Teradata user and can execute one or more sequential transactions between LOGON and LOGOFF, just like a single session.
What are the multiple Teradata sessions used for?
Multiple sessions make it possible to run multiple tasks in parallel. The aim is to increase performance.
Does the use of Teradata multiple sessions always 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.
When does a BTEQ script typically increase performance by using multiple Teradata sessions?
A BTEQ import script can usually increase performance by using multiple sessions. BTEQ export scripts typically do not achieve any performance advantage by using multiple sessions.
How many Teradata sessions should I use?
This isn’t easy to answer and is best determined empirically. What is essential 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 which access paths do multiple sessions not make sense?
For Full Table Scans and NUSI accesses.
Example 1: Multiple Sessions which do not improve performance
As shown in the graph above, all four 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 precisely one AMP. Therefore the 4 AMPs can work on different tasks at the same time.
In summary, the use of multiple sessions is only helpful if:
- Each transaction uses less than all AMPs
- Ideally, enough sessions are available to keep all AMPs equally busy with transactions.