The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
GEPHI is being used to visualize the relations between tables and applications, but here I used it with the data from the lock logger to visualize some relations between blocked and blocking sessions that were happening very often
Here are some visualization:
Which workload is blocking/blocked the most
Which User is blocking/blocked the most
Block Type/Level (edge visualization)
Blocking and blocked sessions…
And here is how I did it...
The TARGET node is defined as the BLOCKED node (BLKEDSESSNO) and the SOURCE node is defined as the BLOCKING node (BLKINGSESSNO) based on the DBAWORK.LOCKLOG table, which is populated from the lock logger.
Several of the answers provided by the charts could be achieve using normal SQL. The charts provide other relations that are difficult to understand by SQL. The SESSIONS table is a volatile table defined to get the desired BLOCKED/BLOCKING sessions to be analyzed: CREATE VOLATILE MULTISET TABLE sessions AS (SELECT a.blkdsessno sessionid, a.begdate, b.databasenamei dbid, c.tvmnamei tid FROM dbawork.locklog a, dbc.dbase b, dbc.tvm c WHERE a.begdate = '2016-12-06' AND a.dbid =b.databaseid AND a.tid = c.tvmid AND b.databasenamei LIKE '%MDM%' GROUP BY 1,2,3,4 ) WITH DATA PRIMARY INDEX (sessionid) ON COMMIT PRESERVE ROWS;
INSERT INTO sessions SELECT a.blkingsessno sessionid, a.begdate, b.databasenamei dbid, c.tvmnamei tid FROM dbawork.locklog a, dbc.dbase b, dbc.tvm c WHERE a.begdate = '2016-12-06' AND a.dbid =b.databaseid AND a.tid = c.tvmid AND a.blkdsessno IN (SELECT sessionid FROM sessions GROUP BY 1) GROUP BY 1,2,3,4;
The following is the select statement used to create the NODE file: SELECT a.sessionid ID, USERNAME, wdname, CASE WHEN queryband IS NOT NULL THEN GETQUERYBANDVALUE(COALESCE(queryband,'none'),0,'ApplicationName' ) END ApplicationName, CASE WHEN ERRORCODE IN (0,3158) THEN 'OK' ELSE 'Failed' END ErrorStatus, MIN(starttime) sessionstarted, MAX(firstresptime) sessionended, MAX(delaytime) maxdelay, AVG(delaytime) avgdelay, COUNT(DISTINCT queryid), SUM(parsercputime) ttlparsertime, MAX(parsercputime) maxparsertime, SUM(AMPCPUTIME) ttlAMPCPUTIME, MAX(AMPCPUTIME) maxAMPCPUTIME, SUM(TotalIOCount) ttlTotalIOCount, MAX(TotalIOCount) maxTotalIOCount
FROM pdcrinfo.dbqlogtbl_hst a, sessions b WHERE logdate = '2016-12-06' AND a.sessionid = b.sessionid GROUP BY 1,2,3,4,5;
The EDGE file was created as follows: SELECT a.blkdsessno (NAMED "TARGET"), a.blkingsessno (NAMED "SOURCE"), a.begdate, a.begtime, a.delay, b.databasenamei dbid, c.tvmnamei tid, a.blkdloghost, a.blkdlevel, a.blkdmode, a.blkingloghost, a.blkinglevel, a.blkingmode, a.processor, a.deadlock, a.multipleblocker, a.stmttype FROM dbawork.locklog a, dbc.dbase b, dbc.tvm c WHERE a.begdate = '2016-12-06' AND a.dbid =b.databaseid AND a.tid = c.tvmid AND a.blkdsessno IN (SELECT sessionid FROM sessions);
With the NODE and EDGE files we can use Gephi to chart several relations between the BLOCKED/BLOCKING sessions.