Using GEPHI to analyze lock logger data.

Database
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.
Highlighted
Teradata Employee

Using GEPHI to analyze lock logger data.

 

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

 

pic1.png

Which User is blocking/blocked the most

pic2.png

Block Type/Level (edge visualization)

pic3.png

Blocking and blocked sessions…

 pic4.png

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.