#!/usr/bin/perl
#
# plotvqueue.pl version 0x1 by Jakub Wartak 2009 http://vnull.pcnet.com.pl/
#
# requirements:
#	a) DBD::Oracle with Oracle client installed (it might be instantclient)
#	b) DBI
#	c) gnuplot with X11 clients
#	d) DISPLAY env set
#
####
#
# waits		Total time that all items in this queue have waited.
#		    Divide by TOTALQ for average wait per item.
#
# queued    Number of items in the queue
#
####
#
## Before using please initialize tables on database (CREATE TABLES):
#
# create table plotvqueue_queue_disp as
# select sysdate ts, d.name, q.queued, q.wait, q.totalq, decode(q.totalq,0, 0, (q.wait/q.totalq)/100) "avg wait"
# from v$queue q, v$dispatcher d where d.paddr=q.paddr;
#
# create table plotvqueue_queue_common as
# select sysdate ts, q.type, q.queued, q.wait, q.totalq, decode(q.totalq,0, 0, (q.wait/q.totalq)/100) "avg wait"
# from v$queue q where q.type='COMMON';
#
## And schedule this for 1440 minutes (24h), e.g. from screen(1):
#
# BEGIN
#     FOR i IN 1..1440 LOOP
#
#         insert into plotvqueue_queue_disp
#         select sysdate ts, d.name, q.queued, q.wait, q.totalq, decode(q.totalq,0, 0, (q.wait/q.totalq)/100) "avg wait"
#         from v$queue q, v$dispatcher d where d.paddr=q.paddr;
#
#         insert into plotvqueue_queue_common
#         select sysdate ts, q.type, q.queued, q.wait, q.totalq, decode(q.totalq,0, 0, (q.wait/q.totalq)/100) "avg wait"
#         from v$queue q where q.type='COMMON';
#
#         commit;
#
#         DBMS_LOCK.SLEEP(60);
#     END LOOP;
# END;
# /


use DBI;
use strict;
use warnings;

my $DB = $ARGV[0];
my $login = "sys";
my $ora_session_mode = 0;
$ora_session_mode = 2 if ($login eq "sys"); # AS SYSDBA DBD::Oracle mode

if(!$DB || $DB eq "") {
	print "usage: plotvqueue.pl <TNS_ENTRY>\n";
	print "\n";
	exit 1;
}

die "Please set DISPLAY enviorniment variable\n" if ! exists $ENV{DISPLAY};

my $pwd = `pwd`; chomp $pwd;
$ENV{TNS_ADMIN} = $pwd if ! exists $ENV{TNS_ADMIN};

print "Please enter password for $login on $DB: ";
my $pass = <STDIN>; chomp $pass;

# establish DB connection
my $dbh = DBI->connect ("dbi:Oracle:$DB", $login, $pass, {ora_session_mode => $ora_session_mode}) or die;

my $qcommon = qq{select to_char(ts, 'dd/mm/yyyy-hh24:mi:ss') human_ts, 
wait-(lag(wait,1) over (order by ts)) waits, queued from plotvqueue_queue_common};
# where ts>sysdate-1};

my $qdisp = qq{select to_char(ts, 'dd/mm/yyyy-hh24:mi:ss') human_ts, 
sum(waits), sum(queued) from (
select ts, wait-(lag(wait,1) over (partition by name order by ts)) waits,
queued from plotvqueue_queue_disp)
group by ts order by ts};
# where ts > sysdate-1;

my $s = $dbh->prepare($qcommon) or die;
print "[*] fetching data for COMMON queue\n";
$s->execute() or die;
open(T1, "> vqueue_common.dat") or die;
while (my @r = $s->fetchrow_array()) {
	next if !$r[1]; # skip empty LAG() sql value
	print T1 $r[0]." ".$r[1]." ".$r[2]."\n";
}
close T1;
$s->finish;

$s = $dbh->prepare($qdisp) or die;
print "[*] fetching data for DISPATCHERS queues\n";
$s->execute() or die;
open(T2, "> vqueue_disp.dat") or die;
while (my @r = $s->fetchrow_array()) {
	next if !$r[1]; # skip empty LAG() sql value
	print T2 $r[0]." ".$r[1]." ".$r[2]."\n";
}
close T2;
$s->finish;

print "[*] running gnuplot\n";
open(COMMON, "| gnuplot") or die;
my $ofh = select COMMON; 
$|=1; 
select $ofh;

print COMMON "set terminal x11\n";
print COMMON "set style data points\n";
print COMMON "set title \"Common V\$Queue\"\n";
print COMMON "set grid x y y2\n";
print COMMON "set xdata time\n";
print COMMON "set timefmt \"%d/%m/%Y-%H:%M:%S\"\n";
print COMMON "set xlabel \"time\"\n";
print COMMON "set ylabel \"no# of waits\"\n";
print COMMON "set y2label \"no# of queued\"\n";
print COMMON "set autoscale y\n";
print COMMON "set autoscale y2\n";
print COMMON "set ytics nomirror\n";
print COMMON "set y2tics nomirror\n";
print COMMON "plot " . 
"'vqueue_common.dat' using 1:2 axis x1y1 title 'absolute COMMON waits', ".
"'vqueue_common.dat' using 1:3 axis x1y2 title 'COMMON queued' ".
"\n";
my $input = <STDIN>;

print COMMON "set title \"DISPATCHERS V\$Queue\"\n";
print COMMON "plot ".
"'vqueue_disp.dat' using 1:2 axis x1y1 title 'absolute DISPATCHERS waits', ".
"'vqueue_disp.dat' using 1:3 axis x1y2 title 'DISPATCHERS queued' ".
"\n";
$input = <STDIN>;

close COMMON;
$dbh->disconnect;

exit 0;