Author: spadkins
Date: Tue Feb 9 08:20:37 2010
New Revision: 13819
Added:
p5ee/trunk/App-Repository/bin/analyze_debug_sql (contents, props changed)
Log:
new
Added: p5ee/trunk/App-Repository/bin/analyze_debug_sql
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/bin/analyze_debug_sql Tue Feb 9 08:20:37 2010
@@ -0,0 +1,165 @@
+#!/usr/bin/perl -w
+
+use strict;
+
+use App::Options (
+ options => [qw()],
+ option => {
+ },
+);
+
+{
+ my ($sql, $collecting_sql, $method, $nrows_label, $nrows, $time);
+ my %stash = (
+ methods => {},
+ templates => {},
+ tags => {},
+ );
+ my $line_num = 0;
+ while (<>) {
+ $line_num++;
+ #chomp;
+ if (/^DEBUG_SQL: ([a-z_-]+)\(\)/) {
+ $method = $1;
+ #print $method, "\n";
+ $sql = "";
+ $collecting_sql = 1;
+ }
+ elsif (/^DEBUG_SQL: (nrows|retval) \[(\d+)\] \(([0-9\.]+) sec\)/) {
+ $nrows_label = $1;
+ $nrows = $2;
+ $time = $3;
+ $collecting_sql = 0;
+ if ($method && $sql) {
+ &accumulate_sql_stats(\%stash, $method, $sql, $nrows, $time);
+ }
+ else {
+ print "WARNING: DEBUG_SQL $nrows_label found without a start:
Line #$line_num\n";
+ }
+ }
+ elsif (/^DEBUG_SQL:/) {
+ # discard
+ }
+ elsif ($collecting_sql) {
+ $sql .= $_;
+ }
+ else {
+ # discard
+ }
+ }
+ &print_sql_stats(\%stash);
+}
+
+sub accumulate_sql_stats {
+ my ($stash, $method, $sql, $nrows, $time) = @_;
+ my ($template_sql, $tag);
+
+ my $templates = $stash->{templates};
+ if (!$templates) {
+ $templates = {};
+ $stash->{templates} = $templates;
+ }
+
+ my $methods = $stash->{methods};
+ if (!$methods) {
+ $methods = {};
+ $stash->{methods} = $methods;
+ }
+
+ my $tags = $stash->{tags};
+ if (!$tags) {
+ $tags = {};
+ $stash->{tags} = $tags;
+ }
+
+ if ($method eq "insert") {
+ $template_sql = $sql;
+ }
+ elsif ($method eq "_do") {
+ #if ($sql =~ /^([a-z_]+)/) {
+ # $method .= ":$1";
+ #}
+ $template_sql = $sql;
+ $template_sql =~ s/^(where| and )(.*) in \(([^()]*)\)$/$1$2 in
(xxx)/gm;
+ $template_sql =~ s/^(where| and )(.*) = (.*)$/$1$2 = xxx/gm;
+ }
+ elsif ($method eq "_get_rows") {
+ $template_sql = $sql;
+ $template_sql =~ s/^(where| and )(.*) in \(([^()]*)\)$/$1$2 in
(xxx)/gm;
+ $template_sql =~ s/^(where| and )(.*) = (.*)$/$1$2 = xxx/gm;
+ # print "SQL=[$sql]\n";
+ # print "TSQL=[$template_sql]\n";
+ }
+ elsif ($method eq "_update") {
+ $template_sql = $sql;
+ $template_sql =~ s/where.*//s;
+ $template_sql =~ s/^(where| and )(.*) in \(([^()]*)\)$/$1$2 in
(xxx)/gm;
+ $template_sql =~ s/^(where| and )(.*) = (.*)$/$1$2 = xxx/gm;
+ }
+ else {
+ print "WARNING: Don't know how to accumulate stats for [$method]\n";
+ }
+
+ $methods->{$method}{count}++;
+
+ $tag = $templates->{$template_sql}{tag};
+ if (!defined $tag) {
+ $methods->{$method}{seq}++;
+ $tag = sprintf("$method-%04d", $methods->{$method}{seq});
+ $templates->{$template_sql}{tag} = $tag;
+ $tags->{$tag}{sql} = $sql;
+ $tags->{$tag}{template_sql} = $template_sql;
+ }
+
+ $tags->{$tag}{nrows} += $nrows;
+ $tags->{$tag}{count} ++;
+ $tags->{$tag}{cumul_time} += $time;
+ if (! defined $tags->{$tag}{min_time} || $time < $tags->{$tag}{min_time}) {
+ $tags->{$tag}{min_time} = $time;
+ }
+ if (! defined $tags->{$tag}{max_time} || $time > $tags->{$tag}{max_time}) {
+ $tags->{$tag}{max_time} = $time;
+ $tags->{$tag}{sql} = $sql;
+ }
+
+ #print "ROWS=[$nrows] TIME=[$time] SQL=[$sql]\n";
+}
+
+sub print_sql_stats {
+ my ($stash, $method, $sql, $nrows, $time) = @_;
+ my $templates = $stash->{templates};
+ my $methods = $stash->{methods};
+ my $tags = $stash->{tags};
+ print
"============================================================================\n";
+ printf("%-16s %9s\n", "METHOD", "COUNT");
+ foreach my $method (sort keys %$methods) {
+ printf("%-16s %9d\n", $method, $methods->{$method}{count});
+ }
+ print
"============================================================================\n";
+ printf("%-16s %9s %9s %9s %9s %9s %9s\n", "TAG", "COUNT", "NROWS",
"CUMULTIME", "AVGTIME", "MINTIME", "MAXTIME");
+ foreach my $tag (sort keys %$tags) {
+ printf("%-16s %9d %9d %9.3f %9.5f %9.5f %9.5f\n",
+ $tag,
+ $tags->{$tag}{count},
+ $tags->{$tag}{nrows},
+ $tags->{$tag}{cumul_time},
+ $tags->{$tag}{cumul_time}/$tags->{$tag}{count},
+ $tags->{$tag}{min_time},
+ $tags->{$tag}{max_time});
+ }
+ foreach my $tag (sort keys %$tags) {
+ print "--
=========================================================================\n";
+ printf("-- %-16s %9s %9s %9s %9s %9s %9s\n", "TAG", "COUNT", "NROWS",
"CUMULTIME", "AVGTIME", "MINTIME", "MAXTIME");
+ printf("-- %-16s %9d %9d %9.3f %9.5f %9.5f %9.5f\n",
+ $tag,
+ $tags->{$tag}{count},
+ $tags->{$tag}{nrows},
+ $tags->{$tag}{cumul_time},
+ $tags->{$tag}{cumul_time}/$tags->{$tag}{count},
+ $tags->{$tag}{min_time},
+ $tags->{$tag}{max_time});
+ print $tags->{$tag}{sql};
+ }
+ #print "ROWS=[$nrows] TIME=[$time] SQL=[$sql]\n";
+}
+
