polling.pl

Oracle optimizing performance things

#!/usr/bin/perl

# $Header: /home/cvs/cvm-book1/polling/polling.pl,v 1.6 2003/04/23 03:49:37 cvm Exp $
# Cary Millsap ([e-mail])

use strict;
use warnings;
use DBI;
use DBD::Oracle;
use Getopt::Long;
use Time::HiRes qw(gettimeofday);

my @dbh;     # list of database connection handles
my $dbh;     # "foreground" session database connection handle
my $sth;     # Oracle statement handle

my $hostname = "";
my $username = "/";
my $password = "";
my %attr = (
    RaiseError => 1,
    AutoCommit => 0,
);
my %opt = (
    sessions    => 50,      # number of Oracle sessions
    polls       => 1_000,   # number of polls on the v$ object
    hostname    => "",
    username    => "/",
    password    => "",
    debug       => 0,
);

# Get command line options and arguments.
GetOptions(
    "sessions=i"    => \$opt{sessions},
    "polls=i"       => \$opt{polls},
    "debug"         => \$opt{debug},
    "hostname=s"    => \$opt{hostname},
    "username=s"    => \$opt{username},
    "password=s"    => \$opt{password},
);

# Fill v$session with "background" connections.
for (1 .. $opt{sessions}) {
    push @dbh, DBI->connect("dbi:Oracle:$opt{hostname}", $opt{username}, $opt{password}, \%attr);
    print "." if $opt{debug};
}
print "$opt{sessions} sessions connected\n" if $opt{debug};

# Execute the query to trace.
$dbh = DBI->connect("dbi:Oracle:$opt{hostname}", $opt{username}, $opt{password}, \%attr);
$sth = $dbh->prepare(q(select * from v$session));
my $t0 = gettimeofday;
my ($u0, $s0) = times;
for (1 .. $opt{polls}) {
    $sth->execute();
    $sth->fetchall_arrayref;
}
my ($u1, $s1) = times;
my $t1 = gettimeofday;
$dbh->disconnect;
print "$opt{polls} polls completed\n" if $opt{debug};

# Print test results.
my $ela = $t1 - $t0;
my $usr = $u1 - $u0;
my $sys = $s1 - $s0;
printf "%15s %8d\n", "sessions", $opt{sessions};
printf "%15s %8d\n", "polls", $opt{polls};
printf "%15s %8.3f\n", "elapsed", $ela;
printf "%15s %8.3f\n", "user-mode CPU", $usr;
printf "%15s %8.3f\n", "kernel-mode CPU", $sys;
printf "%15s %8.3f\n", "polls/sec", $opt{polls}/$ela;

# Disconnect "background" connections from Oracle.
for my $c (@dbh) {
    $c->disconnect;
    print "." if $opt{debug};
}
print "$opt{sessions} sessions disconnected\n" if $opt{debug};

__END__

=head1 NAME

polling - test the polling rate of SQL upon V$SESSION


=head1 SYNOPSIS

polling
  [--sessions=I<s>]
  [--polls=I<p>]
  [--hostname=I<h>]
  [--username=I<u>]
  [--password=I<p>]
  [--debug=I<d>]


=head1 DESCRIPTION

B<polling> makes I<s> Oracle connections and then issues I<p> queries of
B<V$SESSION>. It prints performance statistics about the polls, including
the elapsed duration, the user- and kernel-mode CPU consumption, and the
number of polls per second exeucted. The program is useful for
demonstrating the polling capacity of an Oracle system.


=head2 Options

=over 4

=item B<--sessions=>I<s>

The number of Oracle connections that are created before the polling
begins. The default value is 50.

=item B<--polls=>I<p>

The number of queries that sill be executed. The default value is 1,000.

=item B<--hostname=>I<u>

The name of Oracle host. The default value is "" (the empty string).

=item B<--username=>I<u>

The name of the Oracle schema to which B<polling> will connect. The
default value is "/".

=item B<--password=>I<p>

The Oracle password that B<polling> will use to connect. The default value
is "" (the empty string).

=item B<--debug=>I<d>

When set to 1, B<polling> dumps its internal data structures in addition
to its normal output. The default value is 0.

=back


=head1 EXAMPLES

Use of B<polling> will resemble the following example:

  $ perl polling.pl --username=system --password=manager
         sessions       50
            polls     1000
          elapsed   15.734
    user-mode CPU    7.111
  kernel-mode CPU    0.741
        polls/sec   63.557


=head1 AUTHOR

Cary Millsap ([e-mail])


=head1 COPYRIGHT

Copyright (c) 2003 by Hotsos Enterprises, Ltd. All rights reserved.