Appendix B. Optimizing Your Database Buffer Cache Hit Ratio
Shortly after I joined Oracle Corporation in 1989, several of my technical mentors taught me that just about the only thing you could tell from looking at a database’s buffer cache hit ratio is that when it’s really high, it’s usually a sign of trouble [Millsap (2001b)]. In the several years that have passed since my first exposure to that lesson, the battle has raged between advocates of using the buffer cache hit ratio as a primary indicator of performance quality and those who believe that hit ratio metrics is too unreliable for such use. It’s not been much of a battle, actually. The evidence that hit ratios are unreliable is overwhelming, and similar ratio fallacies occurring in other industries are well documented (see, for example, [Jain (1991)] and [Goldratt (1992)]).
One of the most compelling (and funniest) proofs that hit
ratios are unreliable is a PL/SQL procedure called choose_a_hit_ratio written by Connor McDonald.
Connor’s procedure lets you increase your database buffer cache hit
ratio to any value that you like between its current value and 99.999
999 9%. How does it work? By adding wasteful workload to your system.
That’s right. You specify what you want your database buffer cache hit
ratio to be, and choose_a_hit_ratio
adds just enough wasteful workload to raise your hit ratio to that
value. What you get in return is proof positive that having a high
database buffer cache hit ratio is no indication that you have an
efficient system. In his original text at http://www.oracledba.co.uk, Connor thanks Jonathan Lewis
for some of the strategy that he used. And I thank Connor for his
letting me use his work in this book.
You can find Connor’s original PL/SQL at http://www.oracledba.co.uk. Example B-1 expresses the same idea in Perl, which enables me to do a little bit more, like prompting and printing timing statistics on the LIO generation. You can download the code as part of the examples for this book, from the O’Reilly catalog page: http://www.oreilly.com/catalog/optoraclep/.
#!/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 ([email protected]) # 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/(ddd)(?=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} " for (keys %$r); print "pio=$pio, lio=$lio "; } return ($lio, $pio); } sub status($$$) { # print a status paragraph my ($description, $lio, $pio) = @_; print "$description "; printf "%15s LIO calls ", fnum($lio); printf "%15s PIO calls ", fnum($pio); printf "%15.9f buffer cache hit ratio ", ($lio - $pio) / $lio; print " "; } # fetch target hit ratio from command line my $usage = "Usage: $0 [options] target "; 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 "; } # 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. "; } elsif ($target > $max_target) { die "Setting your hit ratio to $target will take too long. "; } 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 " "; # 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) ", 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 ([email protected]), 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.