#!perl #ossw: Oracle Session Stat Watcher (single node version). Displays sessions whose given statistic value #has incremented by a given amount since the last sampling #Yong Huang, 2013,2014, http://yong321.freeshell.org/freeware/ossw.html #On Windows, if you have Oracle installed, try including Perl's path, e.g., maybe one of #set path=%path%;C:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread #set path=%path%;C:\oracle\product\11.2.0\client\perl\bin #If that doesn't work, install ActiveState Perl from www.activestate.com, run `ppm install DBI'. #And delete or comment out all lines in the next paragraph. #On UNIX/Linux, change the #! line to use perl under $ORACLE_HOME/perl/bin, and run #export LD_LIBRARY_PATH=$ORACLE_HOME/lib32 #below 11g #export LD_LIBRARY_PATH=$ORACLE_HOME/lib #64-bit 11g or 12c #and change paths below BEGIN { $ENV{ORACLE_HOME}='/u01/app/oracle/product/12.1.0/db'; #$ENV{PERL5LIB}="$ENV{ORACLE_HOME}/perl/lib/5.10.0:$ENV{ORACLE_HOME}/perl/lib/site_perl/5.10.0"; #below 12c $ENV{PERL5LIB}="$ENV{ORACLE_HOME}/perl/lib/5.14.1:$ENV{ORACLE_HOME}/perl/lib/site_perl/5.14.1"; #12c } $defusr = "perfstat"; #Default user, must have create session priv and select_catalog_role. $defpass = "perfstat"; ##### No need to modify below this line. But hacking is welcome. ##### $| = 1; use Getopt::Std; getopts('hs:u:p:d:vn:S:i:'); if (defined $opt_h or !defined $opt_s or !defined $opt_S) { print "Usage: ossw [-h] -S Statname_or_# -s TNSAlias [-u User -p Password] [-d Delay] [-v] [-n LowerLimit] [-i IncrementBy] -h: Help -S: (required) Statistic name (must exactly match an entry in v\$statname or v\$systat case-sensitively) or number (as in v\$statname, v\$sysstat, v\$mystat, or v\$sesstat) -s: (required) Connect identifier -u: Alternate user instead of $defusr to login as -p: Alternate user's password -d: Number of seconds delay between calls (default 5) -v: Show sessions even if their stat values are not changing (output marked 'NoChange!') -n: Only show sessions whose stat values are greater than this number -i: Only show sessions whose stat values have incremented by at least this number since last sampling (default 1) Common usage examples: ossw -S \"parse count (failures)\" -s mydb -u yong -p yongpassword ossw -S 585 -s dbconn -v -i 3 Output is like: Output template: 1234-33:5:1:user-appserver where 1234 is SID, 33 serial#, 5 parse failures so far, 1 increment of parse failure over last sampling, DB user from client appserver *** Oracle Session Statistic Watcher for statistic #585 \"parse count (failures)\" ***\n"; exit; } $opt_d = 5 if !defined $opt_d; $opt_n = 0 if !defined $opt_n; $opt_i = 1 if !defined $opt_i; use DBI; if (defined $opt_u) { die "Alternate user and password must be defined together!" if !defined $opt_p; $dbh = DBI->connect("dbi:Oracle:$opt_s", $opt_u, $opt_p, {RaiseError=>1}); } else { $dbh = DBI->connect("dbi:Oracle:$opt_s", $defusr, $defpass, {RaiseError=>1}); } die "Cannot do $dbh->connect: $DBI::errstr!" if !defined $dbh; $SIG{INT} = sub {exit}; #must be set to handle ^C and must be after DBI->connect if ($opt_S =~ /\d+/) { $statnum = $opt_S; $sql = "select name from v\$statname where statistic#=$statnum"; $sth = $dbh->prepare($sql); $sth->execute(); $statname = $sth->fetchrow_array; #only used in header display die "Statistic# may be incorrect!" unless $statname; } else { $statname = $opt_S; #only used in header display $sql = "select statistic# from v\$statname where name='".$opt_S."'"; $sth = $dbh->prepare($sql); $sth->execute(); $statnum = $sth->fetchrow_array; die "Statistic name not found! It must exactly match case-sensitively." unless $statnum; } print "Output template: 1234-33:5:1:HR-v3besextm001 where 1234 is SID, 33 serial#, 5 so far for the tracked statistic, 1 increment since last sampling, user HR on machine v3besextm001\n *** Oracle Session Statistic Watcher for statistic \#$statnum \"$statname\" ***\n"; #feel free to add more columns to the last column (currently username-machine) $sql = "select a.sid||'-'||a.serial\#, b.value, a.username||'-'||a.machine from v\$session a, v\$sesstat b where b.statistic\#=$statnum and b.value>$opt_n and a.sid=b.sid order by value"; #feel free to change to other columns, i.e. SID, username-machine $sth = $dbh->prepare($sql); while (1) { $sth->execute(); while (@row = $sth->fetchrow_array) { #@row[0]: SID-serial#; [1]: v$sesstat.value; [2]: v$session.username-.machine. #SID-serial# combo, not inst_id and SID alone, is the key to the hash because #SID may be the same for different sessions. #print join("\t", @row), " **********\n"; #debug $sidsr=$row[0]; $sidval=$row[1]; $sidinfo=$row[2]; if (defined $HoA{$sidsr}[0]) #It's undefined in first iteration. { $dltval{$sidsr} = $sidval - $HoA{$sidsr}[0]; #Delta sesstat value if ($dltval{$sidsr} >= $opt_i) { print "$sidsr:$sidval:$dltval{$sidsr}:$sidinfo\n"; #e.g.: 1234-33:5:1:HR-v3besextm001 } elsif (defined $opt_v) #print their values even if they don't change between samplings { print "NoChange! $sidsr:$sidval:0:$sidinfo\n"; } } $HoA{$sidsr} = [ ($sidval, $sidinfo) ]; #$sidsr is key to this hash of array $allsidsr{$sidsr} = 1; #create this hash for all SID-serial#'s } foreach (keys %HoA) { delete $HoA{$_} if ! exists $allsidsr{$sidsr}; #This SID-serial# has disappeared. } #print "\n" if defined $opt_v; #Append one more \n for clarity and reminds of verbose mode print "\n"; sleep $opt_d; }