Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 22 Apr 2004 @ 15:27:05 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Performance impact of Locking Logger
 
From:   Glen Blood

I run it when needed. It doesn't take long to run it for a period of two or three days.

The report is a really long text file without sear4ch capabilities, To make it useful, I exoport it and open it in notepad or word.

As an alternative, I have two views (see below) that were stolen from some of John Carpenter's macros. They make it real easy to figure out what to do. You have to specify the locking logger table to use them.


Glen


     replace view O10782.analyze_locklog as
          select a.begdate as begin_Date,a.begtime as begin_Time,
          b.databasename as Databasename,
          c.tvmname as Tablename,
          d.username as Blocked_Username,  d.sessionno as BLOCKED_SESSION,
          e.username as Blocking_Username,  e.sessionno as BLOCKING_SESSION,
          a.deadlock,a.stmttype, a.delay
          from O10782.locklog a
        , dbc.databases2 b
        , dbc.tables2 c
        , dbc.logonoff d,
          dbc.logonoff e
          where   a.dbid = b.databaseid
          and  a.tid = c.tvmid
          and  a.blkdsessno = d.sessionno
          and  a.blkingsessno = e.sessionno
          and d.event = 'logoff'
         and e.event = 'logoff'
         AND begin_date * 1000000 + begin_time between d.logondate * 1000000 +
     d.logontime  AND d.logdate * 1000000 + d.logtime
             AND begin_date * 1000000 + begin_time between e.logondate *
     1000000 + e.logontime  AND e.logdate * 1000000 + e.logtime;

     replace view o10782.analyze_locklog_no_blocker as
          select a.begdate as begin_Date,a.begtime as begin_Time,
          b.databasename as Databasename,
          c.tvmname as Tablename,
          d.username as Blocked_Username,
          a.deadlock,a.stmttype, a.delay
          from o10782.locklog a
        , dbc.databases2 b
        , dbc.tables2 c
        , dbc.logonoff d
          where   a.dbid = b.databaseid
          and  a.tid = c.tvmid
          and  a.blkdsessno = d.sessionno
          and d.logondate between a.begdate - 1 and a.begdate
          and d.event = 'logon'
          and  a.blkingsessno = 0;


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016