#!/usr/bin/perl -w

# Lastanalyse aufgrund der SQL Area
# Author: Uwe Schneider <us@webde-ag.de>
#
# Diese Software ist UNSUPPORTET.
#


use strict;
use FileHandle;
use DBI;
use Getopt::Std;

# $opt_d: Differentielle Analyse, wartet $opt_d Sekunden
# $opt_n: Gib eine fortlaufende Nr. aus
# $opt_s: Gib die Liste der Session-Ids aus
use vars qw($opt_d $opt_n $opt_s);
getopts('d:ns') || usage_exit();

# Sekunden, die das Ding wartet.
my ($wait_time);
$wait_time = $opt_d if ($opt_d);


# Feldseparator bei der Ausgabe
my ($SEP) = "\t";



###############################################################
sub do_select
{
    my ($dbh) = @_;
    
    my ($query) = q{
    SELECT
        u.username
    ,   a.hash_value
    ,   a.address
    ,   sum(a.executions)
    ,   sum(a.disk_reads)
    ,   min(replace(a.SQL_TEXT,chr(13),' '))
    ,   avg(86400 * (sysdate - i.startup_time))
    FROM
        V$SQL a
    ,   dba_users u
    ,   v$instance i
    WHERE 
        a.PARSING_SCHEMA_ID = u.user_id
    AND u.username NOT LIKE 'SYS%'
    GROUP BY 
        u.username
    ,   a.hash_value
    ,   a.address
    } || die;
    
    
    # Alle Session-IDs, die zu einem SQL-Text gehören
    my ($query_sess) = $dbh->prepare(q{
        SELECT
            se.sid
        FROM
            v$open_cursor oc
        ,   v$session se
        WHERE
            se.saddr = oc.saddr AND se.sid = oc.sid
        AND oc.hash_value = ? AND oc.address = ?
        GROUP BY se.sid
        ORDER BY se.sid
    }) || die;
    
    
    my($sth) = $dbh->prepare($query) || die;
    
    my ($n_secs,$puser,$hash,$addr,$n_exec, $n_exec_sec,$n_disk, $n_disk_exec,$sql);    
    my ($lifetime, $lifetime1,$lifetime2);
    my ($sid, $sidlist);

    my ($st);
    
    if ($wait_time)
    {
        $sth->execute() || die;
        while (($puser,$hash,$addr,$n_exec,$n_disk,$sql,$n_secs) =
            $sth->fetchrow_array()) 
        {
            $sql =~ tr/\c@//d;
            $sql =~ tr/\t\r\n\f/    /;
            $st->[0]{$puser}{$hash} = [$n_exec, $n_disk, $sql];
            $lifetime1 = $n_secs;
        }
        $sth->finish();
        sleep($wait_time);
    }
    
    $sth->execute() || die;
    while (($puser,$hash,$addr,$n_exec,$n_disk,$sql,$n_secs) =
        $sth->fetchrow_array()) 
    {
        $sql =~ tr/\c@//d;
        $sql =~ tr/\t\r\n\f/    /;
        $lifetime2 = $n_secs;
        
        # Hole auch die Session-IDs
        if ($opt_s) 
        {
            $sidlist = "";
            $query_sess->execute($hash,$addr) || die;
            while (($sid) = $query_sess->fetchrow_array()) {
                $sidlist .= "$sid,";
            }
            $query_sess->finish();
            chop($sidlist); # Letztes Komma wegwerfen
        }
        $st->[1]{$puser}{$hash} = [$n_exec, $n_disk, $sql, $sidlist];
        
    }
    $sth->finish();

    if ($wait_time) 
    {
        $lifetime = $lifetime2 - $lifetime1;
    }
    else 
    {
        $lifetime = $lifetime2 - 0;
    }

    my $st_delta;
    
    # Anzahl Statements / Sekunde
    my $n_sec_sum = 0;           
     
    # bestimme Delta und gib aus!
    # Reigenfolge: Absteigend nach Anzahl der Executions
    print "User${SEP}";
    print "N${SEP}" if $opt_n;
    print "N Exec${SEP}N Exec/sec${SEP}Diskreads${SEP}Diskreads/Exec.${SEP}";
    print "SIDs${SEP}" if $opt_s;
    print "SQL Text\n";
    foreach $puser (sort keys %{$st->[1]})
    {
        my $st_delta;
        # Loop über alle Statements
        foreach $hash (keys %{$st->[1]{$puser}})
        {
            $st_delta->{$hash}{'n_exec'}      = $st->[1]{$puser}{$hash}[0] - ($st->[0]{$puser}{$hash}[0] || 0);
            $st_delta->{$hash}{'n_exec_secs'} = $st_delta->{$hash}{'n_exec'}/$lifetime;
            $st_delta->{$hash}{'n_diskreads'} = $st->[1]{$puser}{$hash}[1] - ($st->[0]{$puser}{$hash}[1] || 0);
            $st_delta->{$hash}{'n_disk_exec'} = $st_delta->{$hash}{'n_diskreads'} / ($st_delta->{$hash}{'n_exec'} || 1);
            $st_delta->{$hash}{'sql'}         = $st->[1]{$puser}{$hash}[2];
            $st_delta->{$hash}{'sidlist'}     = $st->[1]{$puser}{$hash}[3] if $opt_s;
            
            $n_sec_sum += $st_delta->{$hash}{'n_exec_secs'};
        }
        
        my ($count) = 0;
        # Ausgabeschleife: Absteigend sortiert über Executions
        foreach $hash (sort { 
                            $st_delta->{$b}{'n_exec'} <=>$st_delta->{$a}{'n_exec'} 
                            }
                       keys %{$st_delta})
        {
            # ganz seltene Staements wegwerfen?
            #next if ($st_delta->{$hash}{'n_exec_secs'} < 0.001);
            
            # Alles wegwerfen, was mit dem EXPLAIN PLAN zu tun hat
            next if $st_delta->{$hash}{'sql'} =~ /plan_table/i;
            
            $count++;

            printf "%s${SEP}",$puser;
            printf "$count${SEP}" if ($opt_n);
            printf "%s${SEP}%.3f${SEP}%s${SEP}%.3f${SEP}",
                $st_delta->{$hash}{'n_exec'},
                $st_delta->{$hash}{'n_exec_secs'},
                $st_delta->{$hash}{'n_diskreads'},
                $st_delta->{$hash}{'n_disk_exec'}
                ;
            printf "%s${SEP}",$st_delta->{$hash}{'sidlist'} if $opt_s;
            printf "%s\n",$st_delta->{$hash}{'sql'};
        }
        print "\n\n";
    }
    
    
    printf "Anzahl der Statements/sec: %.2f\n",$n_sec_sum;

}

###############################################################
sub usage_exit
{
    print STDERR <<EOUSAGE;
Aufruf: $0 [Optionen] <Loginstring>
Optionen: -d <n> : Bilde differentielle Last während <n> Sekunden
          -n     : Gib eine fortlaufende Statement-Nummer aus
          -s     : Gib die Sessions aus, die das SQL abgesetzt haben
EOUSAGE
    die;
}
###############################################################


my ($login) = shift || usage_exit();

my ($dbh) = DBI->connect("DBI:Oracle:",$login);
$dbh->{'AutoCommit'} = 0;

$dbh->do(qq(ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'));
unless ($dbh) {    die "Kein Connect zur DB.\n";}

do_select($dbh);

$dbh->disconnect();