set-bchr.pl

Oracle optimizing performance things

#!/usr/bin/perl

# $Header: /home/cvs/cvm-book1/set_hit_ratio/set-bchr.pl,v 1.3 2003/05/08 06:37:50 cvm Exp $
# Cary Millsap ([e-mail])
# based upon the innovative work of Connor McDonald and Jonathan Lewis
# Copyright (c) 2003 by Hotsos Enterprises, Ltd. All rights reserved.

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

# fetch command-line options
my %opt = (
    service      => "",
    username     => "/",
    password     => "",
    debug        => 0,
);
GetOptions(
    "service=s"  => \$opt{service},
    "username=s" => \$opt{username},
    "password=s" => \$opt{password},
    "debug"      => \$opt{debug},
);

sub fnum($;$$) {
    # return string representation of numeric value in
    # %.${precision}f format with specified separators
    my ($text, $precision, $separator) = @_;
    $precision = 0   unless defined $precision;
    $separator = "," unless defined $separator;
    $text = reverse sprintf "%.${precision}f", $text;
    $text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1$separator/g;
    return scalar reverse $text;
}

sub stats($) {
    # fetch LIO and PIO stats from the given db handle
    my ($dbh) = @_;
    my $sth = $dbh->prepare(<<'END OF SQL', {ora_check_sql => 0});
select name, value from v$sysstat
where name in ('physical reads', 'db block gets', 'consistent gets')
END OF SQL
    $sth->execute();
    my $r = $sth->fetchall_hashref("NAME");
    my $pio = $r->{'physical reads' }->{VALUE};
    my $lio = $r->{'consistent gets'}->{VALUE} + $r->{'db block gets'}->{VALUE};
    if ($opt{debug}) {
        print "key='$_', val=$r->{$_}->{VALUE}\n" for (keys %$r);
        print "pio=$pio, lio=$lio\n";
    }
    return ($lio, $pio);
}

sub status($$$) {
    # print a status paragraph
    my ($description, $lio, $pio) = @_;
    print "$description\n";
    printf "%15s LIO calls\n", fnum($lio);
    printf "%15s PIO calls\n", fnum($pio);
    printf "%15.9f buffer cache hit ratio\n", ($lio - $pio) / $lio;
    print "\n";
}


# fetch target hit ratio from command line
my $usage = "Usage: $0 [options] target\n\t";
my $target = shift or die $usage;
my $max_target = 0.999_999_999;
unless ($target =~ /\d*\.\d+/ and 0 <= $target and $target <= $max_target) {
    die "target must be a number between 0 and $max_target\n";
}

# connect to Oracle
my %attr = (RaiseError => 0, PrintError => 0, AutoCommit => 0);
my $dbh = DBI->connect(
    "dbi:Oracle:$opt{service}", $opt{username}, $opt{password}, \%attr
);
END {
    # executed upon program exit
    $dbh->disconnect if defined $dbh;
}

# compute and display the baseline statistics
my ($lio0, $pio0) = stats $dbh;
status("Current state", $lio0, $pio0);

# compute and display the amount of waste required to
# "improve" the cache hit ratio by the requested amount
my $waste;
if ($target < ($lio0 - $pio0)/$lio0) {
    die "Your database buffer cache hit ratio already exceeds $target.\n";
} elsif ($target > $max_target) {
    die "Setting your hit ratio to $target will take too long.\n";
} else {
    # following formula is courtesy of Connor McDonald
    $waste = sprintf "%.0f", $pio0/(1 - $target) - $lio0;
}
my ($lio1, $pio1) = ($lio0 + $waste, $pio0);
status("Increasing LIO count by ".fnum($waste)." will yield", $lio1, $pio1);

# inquire whether to actually change the ratio
print <<"EOF";
******************************************************************
                            WARNING
Responding affirmatively to the following prompt will create the
following effects:
1) It will degrade the performance of your database while it runs.
2) It might run a very long time.
3) It will "improve" your system's buffer cache hit ratio.
4) It will prove that a high database buffer cache hit ratio is
   an unreliable indicator of Oracle system performance.
******************************************************************

EOF
print qq(Enter 'y' to "improve" your hit ratio to $target: );
my $response = <>;
exit unless $response =~ /^[Yy]/;
print "\n";

# create a table called DUMMY
my $sth;
$sth = $dbh->prepare(<<'END OF SQL', {ora_check_sql => 0});
drop table dummy
END OF SQL
$sth->execute if $sth;  # ignore errors
$sth = $dbh->prepare(<<'END OF SQL', {ora_check_sql => 0});
create table dummy (n primary key) organization index as
select rownum n from all_objects where rownum <= 200
END OF SQL
$sth->execute;

# disable 9i connect-by features to ensure lots of LIO
# idea is courtesy of Connor McDonald
$sth = $dbh->prepare(<<'END OF SQL', {ora_check_sql => 0});
alter session set _old_connect_by_enabled = true;
END OF SQL
$sth->execute if $sth;  # ignore errors

# perform the requisite number of LIO calls
# following query is courtesy of Jonathan Lewis
$sth = $dbh->prepare(<<'END OF SQL', {ora_check_sql => 0});
select count(*)
from (select n from dummy connect by n > prior n start with n = 1)
where rownum < ?
END OF SQL
my $e0 = gettimeofday;
$sth->execute($waste);
my $e1 = gettimeofday;
my $e = $e1 - $e0;
$sth->finish;
printf "Performed %s LIO calls in %.6f seconds (%s LIO/sec)\n\n",
    fnum($waste), $e, fnum($waste/$e);

# compute and display the final statistics
my ($lio2, $pio2) = stats($dbh);
status("Final state", $lio2, $pio2);

exit;

__END__


=head1 NAME

set-bchr - set your database buffer cache hit ratio to a higher value


=head1 SYNOPSIS

set-bchr
  [--service=I<h>]
  [--username=I<u>]
  [--password=I<p>]
  [--debug=I<d>]
  I<target>


=head1 DESCRIPTION

B<set-bchr> computes your present buffer cache hit ratio (using the
traditionally accepted formula), computes how much wasted workload must be
added to increase your hit ratio to I<target>, and then provides the
option to actually perform the wasted work that will raise the hit ratio
to the desired I<target> value. I<target> must be a decimal number between
0 and .999999999.

Using B<set-bchr> can increase the value of your system's database buffer
cache hit ratio, but IT WILL DEGRADE THE PERFORMANCE OF YOUR SYSTEM WHILE
IT RUNS. The intent of B<set-bchr> is to demonstrate humorously but
unequivocally that the database buffer cache hit ratio is an unreliable
indicator of system performance quality. If you intend to use this program
to trick customers or managers into believing that you are doing a better
job than you really are, then, well, good luck with that.


=head2 Options

=over 4

=item B<--service=>I<h>

The name of the Oracle service to which B<vprof> will connect. The default
value is "" (the empty string), which will cause B<vprof> to connect
using, for example, the default Oracle TNS alias.

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

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

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

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

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

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

=back


=head1 EXAMPLES

Use of B<set-bchr> will resemble something like the following, in which I
used the tool to "improve" my database buffer cache hit ratio to
approximately 0.92:

  $ set-bchr --username=system --password=manager .92  
  Current state
       37,257,059 LIO calls
        3,001,414 PIO calls
      0.919440394 buffer cache hit ratio
  
  Increasing LIO count by 260,616 will yield
       37,517,675 LIO calls
        3,001,414 PIO calls
      0.920000000 buffer cache hit ratio
  
  ******************************************************************
                              WARNING
  Responding affirmatively to the following prompt will create the
  following effects:
  1) It will degrade the performance of your database while it runs.
  2) It might run a very long time.
  3) It will "improve" your system's buffer cache hit ratio.
  4) It will prove that a high database buffer cache hit ratio is
     an unreliable indicator of Oracle system performance.
  ******************************************************************
  
  Enter 'y' to "improve" your hit ratio to .92: y
  
  Performed 260,616 LIO calls in 46.592340 seconds (5,594 LIO/sec)
  
  Final state
       37,259,288 LIO calls
        3,001,414 PIO calls
      0.919445213 buffer cache hit ratio


=head1 AUTHOR

Cary Millsap ([e-mail]), heavily derived from original work
performed by Connor McDonald.


=head1 BUGS

B<set-bchr> doesn't necessarily improve the database buffer cache hit
ratio to exactly the value of I<target>, but it gets very close.

B<set-bchr> computes the Oracle database buffer cache hit ratio using the
traditional formula R = (LIO - PIO) / LIO, where LIO is the sum of the
values of the Oracle 'consistent gets' and 'db block gets' statistics, and
PIO is the value of the Oracle 'physical reads' statistic. The computation
of LIO in this way is itself deeply flawed. See [Lewis (2003)] for
details.


=head1 COPYRIGHT

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