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.

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

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(SELECTa.blkdsessno sessionid,a.begdate,b.databasenamei dbid,c.tvmnamei tidFROM dbawork.locklog a,dbc.dbase b,dbc.tvm cWHERE a.begdate = '2016-12-06'AND a.dbid =b.databaseidAND a.tid = c.tvmidAND b.databasenamei LIKE '%MDM%'GROUP BY 1,2,3,4) WITH DATAPRIMARY INDEX (sessionid)ON COMMIT PRESERVE ROWS;