# dbtop800.pl

# quick approach to determine the most scheduled db threads (B.08.00)
# Revision: 2008-11-25
#
# usage: EQ_DBSERVER=localhost:eloqdb perl dbtop800.pl
#
# Notes / limitations:
# - This script does not work with B.07.10 as the list thread format 
#   was changed with B.08.00

# update time
$update = 10;

# command to obtain eloqdb6 thread status
# format: "%4d %c  %-10.10s %8d %8d %-20.20s\n"
$dbctl_cmd = "/opt/eloquence/8.0/bin/dbctl list thread /notitle|";
$dbctl_cmd2 = "/opt/eloquence/8.0/bin/dbctl list session /notitle|";

# default db env
$db_env = $ENV{'EQ_DBSERVER'};
$db_env = 'localhost:eloqdb' unless length($db_env) > 0;

# get terminal esc sequence to clear screen
$clear_screen = qx(clear);


# get initial values

print $clear_screen;
print "dbtop800.pl - $db_env - ".localtime(time)."\n";
print "\nCollecting data ...\n";

# session list

open(IN, $dbctl_cmd2) || die "dbctl2 failed: $!";
while(<IN>) {
  chop;
  $t = trim(substr($_,0,4));
  if($t != 0) {
    $tid = $t;
    $ssn[$tid] = trim(substr($_,5,20));
  }
  else {
    $c = session_info($_);
    # print "t=$tid, c=$c\n";
    $ssn[$tid] .= $c;
  }
}
close(IN) || die "dbctl2 failed: $!";


# thread list

open(IN, $dbctl_cmd) || die "dbctl failed: $!";
while(<IN>) {
  chop;
  next if(index($_, 'file') >= 0);
  $t = trim(substr($_,0,4));
  ## next if($t == 1);
  $s = trim(substr($_,36,8));
  $n = trim(substr($_,54)) || die;
  # print "t=$t, s=$s, n=$n\n";
  $cnt[$t] = $s;
}
close(IN) || die "dbctl failed: $!";


# process updates

while(1)
{
  sleep($update);

  undef(@tmp);
  $active = 0;
  $total = 0;

  open(IN, $dbctl_cmd) || die "dbctl failed: $!";
  while(<IN>) {
    chop;
    next if(index($_, 'file') >= 0);
    $t = trim(substr($_,0,4));
    ## next if($t == 1);
    $s = trim(substr($_,36,8));
    $n = trim(substr($_,54));

    $o = $cnt[$t];
    $d = $s - $o;
    # print "t=$t, s=$s, n=$n, o=$o, d=$d\n";
    if($d > 0) {
       $active++;
       $total += $d;
       push(@tmp, [$t, $d, $n]);
    }
    $cnt[$t] = $s;
  }
  close(IN) || die "dbctl failed: $!";

  print $clear_screen;
  print "dbtop800.pl - $db_env - ".localtime(time)."\n";
  print "Active threads: $active, total SC: $total\n";
  print "TID  SCHED  SC% NAME\n";

  @tmp2 = sort(rsorter @tmp);
  $i = 0;
  foreach(@tmp2) {
    ($t, $d, $n) = @$_;
    $n = $ssn[$t] unless $ssn[$t] == '';
    printf "%4d %6d %3d %s\n", $t, $d, 100*$d/$total+0.5, $n;
    last if(++$i == 20);
  }
}

sub trim {
  my $str = shift;
  for ($str) {
    s/^\s+//;
    s/\s+$//;
  }
  return $str;
}

sub rsorter {
  my ($t1, $d1, $n1) = @$a;
  my ($t2, $d2, $n2) = @$b;
  if($d1 < $d2) {
     return 1;
  }
  if($d1 > $d2) {
     return -1;
  }
  if($t1 < $t2) {
     return 1;
  }
  if($t1 > $t2) {
     return -1;
  }
  return 0;
}

sub session_info {
  my $str = shift;

  if(/uid\{([^\}]*)/) { 
    $u = " uid=".$1; 
  }
  if(/pid\{([^\}]*)/) {
    $p = " pid=".$1;
  }
  if(/pname\{([^\}]*)/) {
    $i = rindex($1, '/');
    $n = " " . substr($1,$i+1,20);
  }
  return $u.$p.$n;
}
