8 # Michael G Schwern, 11-1999
10 # Current maintainer: Ivan Kohler <ivan-dbix-profile@420.am>
12 # Copyright (c) 1999,2000 Jeff Lathan, Kerry Clendinning. All rights reserved.
13 # This program is free software; you can redistribute it and/or modify it
14 # under the same terms as Perl itself.
16 # .15 First public release. Bad naming.
17 # .20 Fixed naming problems
18 # .30 Module is now more transparent, thanks to Michael G Schwern
19 # One less "To Do" left!
21 # 1.0 Added ability to trace executes, chosen by an environment variable
22 # Added capability of saving everything to a log file
23 # 1.01 Added sprintf patch, MANIFEST and Changes files, and kludge to get
24 # along with DBIx::ContextualFetch
27 # This package provides an easy way to profile your DBI-based application.
28 # By just "use"ing this module, you will enable counting and measuring
29 # realtime and cpu time for each and every query used in the application.
30 # The times are accumulated by phase: execute vs. fetch, and broken down by
31 # first fetch, subsequent fetch and failed fetch within each of the
32 # fetchrow_array, fetchrow_arrayref, and fetchrow_hashref methods.
33 # More DBI functions will be added in the future.
36 # Add "use DBIx::Profile;" or use "perl -MDBIx::Profile <program>"
37 # Add a call to $dbh->printProfile() before calling disconnect,
38 # or disconnect will dump the information.
41 # Make the printProfile code better
44 ##########################################################################
45 ##########################################################################
49 DBIx::Profile - DBI query profiler
52 Copyright (c) 1999,2000 Jeff Lathan, Kerry Clendinning.
55 This program is free software; you can redistribute it and/or modify it
56 under the same terms as Perl itself.
60 use DBIx::Profile; or "perl -MDBIx::Profile <program>"
64 #or to use with things like like to be the DBI root class themselves:
65 # (Class::DBI / Ima::DBI uses DBIx::ContextualFetch)
66 use DBIx::Profile ( RootClass=>'DBIx::ContextualFetch' );
70 DBIx::Profile is a quick and easy, and mostly transparent, profiler
71 for scripts using DBI. It collects information on the query
72 level, and keeps track of first, failed, normal, and total amounts
73 (count, wall clock, cput time) for each function on the query.
75 NOTE: DBIx::Profile use Time::HiRes to clock the wall time and
76 the old standby times() to clock the cpu time. The cpu time is
79 DBIx::Profile can also trace the execution of queries. It will print
80 a timestamp and the query that was called. This is optional, and
81 occurs only when the environment variable DBIXPROFILETRACE is set
82 to 1. (ex: (bash) export DBIXPROFILETRACE=1).
84 Not all DBI methods are profiled at this time.
85 Except for replacing the existing "use" and "connect" statements,
86 DBIx::Profile allows DBI functions to be called as usual on handles.
88 Prints information to STDERR, prefaced with the pid.
92 1) Add "use DBIx::Profile" or execute "perl -MDBIx::Profile <program>"
93 2) Optional: add $dbh->printProfile (will execute during
96 4) Data output will happen at printProfile or $dbh->disconnect;
101 $dbh->printProfile();
103 Will print out the data collected.
104 If this is not called before disconnect, disconnect will call
108 $profile = $dbh->sprintPRofile();
110 Returns the data collected.
113 $dbh->setLogFile("ProfileOutput.txt");
115 Will save all output to the file.
119 Jeff Lathan, lathan@pobox.com
120 Kerry Clendinning, kerry@deja.com
122 Aaron Lee, aaron@pointx.org
123 Michael G Schwern, schwern@pobox.com
125 Current maintainer: Ivan Kohler <ivan-dbix-profile@420.am>
133 package DBIx::Profile;
136 use vars qw(@ISA $VERSION);
143 # Store DBI's original connect & disconnect then replace it with ours.
145 local $^W = 0; # Redefining a subrouting makes noise.
146 *_DBI_connect = DBI->can('connect');
147 *DBI::connect = \&connect;
151 # Make DBI aware of us.
153 __PACKAGE__->init_rootclass;
155 $DBIx::Profile::DBIXFILE = "";
156 $DBIx::Profile::DBIXFILEHANDLE = "";
157 $DBIx::Profile::DBIXTRACE = 0;
159 if ($ENV{DBIXPROFILETRACE}) {
160 $DBIx::Profile::DBIXTRACE = 1;
164 my( $self, %opt ) = @_;
165 if ( $opt{RootClass} ) {
166 eval "use $opt{RootClass}";
167 #*_DBI_connect = UNIVERSAL::can( $opt{'RootClass'}, 'connect' );
168 #*DBI::connect = \&connect;
169 unshift @DBIx::Profile::ISA, $opt{RootClass};
170 unshift @DBIx::Profile::db::ISA, $opt{RootClass}. '::db';
171 unshift @DBIx::Profile::st::ISA, $opt{RootClass}. '::st';
172 #__PACKAGE__->init_rootclass;
178 my $result = __PACKAGE__->_DBI_connect(@_);
181 # set flag so we know if we have not printed profile data
182 $result->{'private_profile'}->{'printProfileFlag'} = 0;
188 ##########################################################################
189 ##########################################################################
191 package DBIx::Profile::db;
195 @ISA = qw( DBI::db );
198 # insert our "hooks" to grab subsequent calls
204 my $result = $self->SUPER::prepare(@_);
214 # disconnect from the database; if printProfile has not been called, call it.
219 if ( !$self->{'private_profile'}->{'printProfileFlag'}) {
223 return $self->SUPER::disconnect(@_);
230 $DBIx::Profile::DBIXFILE = $logName;
232 open(OUT,">$logName") || die "Could not open file!";
234 $DBIx::Profile::DBIXFILEHANDLE = \*OUT;
241 $self->disconnect(@_);
245 # Print the data collected.
247 # JEFF - The printing and the print code is kinda (er... very) ugly!
250 #like printProfile, except returns the results instead of printing them.
253 $self->printProfile({'sprint'=>1});
264 # Set that we have printed the results
265 $self->{'private_profile'}->{'printProfileFlag'} = 1;
267 # Loop through the queries
268 foreach my $qry (keys %{$self->{'private_profile'}}) {
272 if ( $qry eq "printProfileFlag" ) {
278 # Now loop through the actions (execute, fetchrow, etc)
279 foreach my $name ( sort keys %{$self->{'private_profile'}->{$qry}}) {
280 # Right now, this assumes that we only have wall clock, cpu
281 # and count. Not generic, but what we want NOW
283 if ( $name eq "first" ) {
287 $text .= " $name ---------------------------------------\n";
289 foreach my $type (sort keys %{$self->{'private_profile'}->{$qry}->{$name}}) {
292 my ($count, $time, $ctime);
293 $count = $self->{'private_profile'}->{$qry}->{$name}->{$type}->{'count'};
294 $time = $self->{'private_profile'}->{$qry}->{$name}->{$type}->{'realtime'};
295 $ctime = $self->{'private_profile'}->{$qry}->{$name}->{$type}->{'cputime'};
297 $text .= sprintf " Count : %10d\n",$count;
298 $text .= sprintf " Wall Clock : %10.7f s %10.7f s\n",$time,$time/$count;
299 $text .= sprintf " Cpu Time : %10.7f s %10.7f s\n",$ctime,$ctime/$count;
301 if ($type eq "Total") {
308 $text = "$$ \"" . $qry . "\" Total wall clock time: ". $total ."s \n" . $text;
309 $text = "=================================================================\n" . $text;
311 # In order to sort based on the total time taken for a query "easily"
312 # we are placing the information in a hash with the total time as the key.
313 # Since we could have many queries with the same total, if this exists,
314 # we cat the query string to the total string and use that as the key.
315 # The sort function will do the right thing.
317 if (exists $result{$total} ) {
321 $result{$total} = $text;
325 foreach my $qry (sort stripsort keys %result) {
326 if ( $args->{'sprint'} ) {
327 $results .= $result{$qry} . "\n";
328 } elsif ($DBIx::Profile::DBIXFILE eq "" ) {
329 warn $result{$qry} . "\n";
331 print $DBIx::Profile::DBIXFILEHANDLE $result{$qry} . "\n";
335 return $results if $args->{'sprint'};
340 # Strip off the actual number amount, since the variables may
341 # contain text as well
343 $a =~ m/^(\d+\.\d+)/;
345 $b =~ m/^(\d+\.\d+)/;
348 # Yes, this processes backwards since we want to go decreasing
353 ##########################################################################
354 ##########################################################################
356 package DBIx::Profile::st;
362 # Get some accurancy for wall clock time
363 # Cpu time is still very coarse, but...
365 use Time::HiRes qw ( gettimeofday tv_interval);
367 # Aaron Lee (aaron@pointx.org) provided the majority of
368 # BEGIN block below. It allowed the removal of a lot of duplicate code
369 # and makes the code much much cleaner, and easier to add DBI functionality.
373 # Basic idea for each timing function:
377 # Calculate time diff
379 # Just add more functions in @func_list
381 my @func_list = ('fetchrow_array','fetchrow_arrayref','execute',
386 foreach $func (@func_list){
388 # define subroutine code, incl dynamic name and SUPER:: call
394 my ($time, $ctime, $temp, $x, $y, $z, $type);
398 $time = [gettimeofday];
399 ($ctime, $x ) = times();
401 @result = $self->SUPER::' . "$func" . '(@_);
404 $time = tv_interval ($time, [gettimeofday]);
407 # Checking scalar because we are also interested
408 # in catching empty list
410 if (scalar @result) {
414 $type = "no more rows";
420 $ctime = ($y + $z) - ($x + $ctime);
421 $self->increment($func,$type,$time, $ctime);
426 $time = [gettimeofday];
427 ($ctime, $x ) = times();
429 $result = $self->SUPER::' . "$func" . '(@_);
432 $time = tv_interval ($time, [gettimeofday]);
434 if (defined $result) {
435 if ($result ne "0E0") {
438 $type = "returned 0E0";
443 $type = "no more rows";
449 $ctime = ($y + $z) - ($x + $ctime);
450 $self->increment($func,$type,$time, $ctime);
453 } # end of if (wantarray);
455 } # end of function definition
458 # define $func in current package
466 # fetchrow is just an alias for fetchrow_array, so
469 # Is the return below safe, given the main function above? - JEFF
472 return $self->fetchrow_array(@_);
476 my ($self, $name, $type, $time, $ctime) = @_;
479 my $qry = $self->{'Statement'};
480 $ref = $self->{'private_profile'};
482 # text matching?!? *sigh* - JEFF
483 if ( $name =~ /^execute/ ) {
485 if ( $DBIx::Profile::DBIXTRACE ) {
486 my ($sec, $min, $hour, $mday, $mon);
487 ($sec, $min, $hour, $mday, $mon) = localtime(time);
488 my $text = sprintf("%d-%2d %2d:%2d:%2d", $mon, $mday,$hour,$min,$sec);
489 if ($DBIx::Profile::DBIXFILE eq "" ) {
490 warn "$$ text $name SQL: $qry\n";
492 print $DBIx::Profile::DBIXFILEHANDLE "$$ $text $name SQL: $qry\n";
497 if ( ($name =~ /^fetch/) && ($ref->{'first'} == 1) ) {
502 $ref->{$name}->{$type}->{'count'}++;
503 $ref->{$name}->{$type}->{'realtime'}+= $time;
504 $ref->{$name}->{$type}->{'cputime'}+= $ctime;
506 $ref->{$name}->{"Total"}->{'count'}++;
507 $ref->{$name}->{"Total"}->{'realtime'}+= $time;
508 $ref->{$name}->{"Total"}->{'cputime'}+= $ctime;
512 # initRef is called from Prepare in DBIProfile
514 # Its purpose is to create the DBI's private_profile info
515 # so that we do not lose DBI::errstr in increment() later
519 my $qry = $self->{'Statement'};
521 if (!exists($self->{'private_profile'})) {
522 if (!exists($self->{'Database'}->{'private_profile'}->{$qry})) {
523 $self->{'Database'}->{'private_profile'}->{$qry} = {};
525 $self->{'private_profile'} =
526 $self->{'Database'}->{'private_profile'}->{$qry};