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. The session is terminated by a LOGOFF statement.
What are multiple Teradata sessions (e.g. in BTEQ)?
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.
What are the multiple Teradata sessions used for?
The use of multiple sessions makes 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 achieve an increase in performance by using multiple Teradata sessions?
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.
How many Teradata sessions should I use?
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 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 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.