Archives of the TeradataForum
Message Posted: Tue, 04 Apr 2006 @ 21:33:04 GMT
| Subj: | | Re: TPUMP Blocking on multiple sessions |
| |
| From: | | Bob Hahn |
There are two different types of collisions that can affect TPump or any multi-session update process using multi-statement requests--NUPI dups
and hash synonyms.
TPump serialize helps with NUPI dups--it uses basically a checksum (NOT the hashing algorithm) to cause rows with the same PI value to be sent
on the same session to avoid blocking (and preserve order). This does NOT help with hash synonyms where different PI values have the same
rowhash. It also means that multiple TPumps against the same table require cross-job serialization-sometimes this is natural e.g. different
tpumps handling different stores.
In the worst case, the blocking can be so severe as to deadlock. Options to deal with it include 1) Change the PI 2) use >1 session pack 1
(short blocking delays), 3) Single session max pack (a session won't block itseslf). 4) Parameter Array exploitation in V2R6 w/TTU8.1 TPump
(potential better single session throughput) 5) Mini-batch (populate staging table w/'good' PI then SQL apply via insert/select--trade latency for
throughput). 6) UDFs wrapped in views to transform the bad PI values into a value/datatype that hashes better.
There is good discussion of this from the physical design perspective in the Database Design Manual Chapters 9 and 10 and also in the
Performance Management Manual e.g. Chapter 13 and also SQL Reference (Hash Related functions).
| 
