Quick thought, add an option to "abstract" the SQL using code like
this (copied from an app that does something vaguely similar):
s/^[ ]*\n//mg; # delete blank lines
s/^[ ]*/ /mg; # normalize leading whitespace
s/\b\d+\b/N/g; # convert decimal numbers to N
s/\b0x[0-9A-Fa-f]+\b/N/g; # convert hex numbers to 0xN
s/'.*?'/'S'/g; # convert quoted strings to 'S'
s/".*?"/"S"/g; # convert quoted strings to "S"
# -n=8: turn log_20001231 into log_NNNNNNNN
s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n};
# abbreviate massive "in (...)" statements and similar
s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg;
That'll merge together the data for very similar queries.
(*Especially important for apps that don't use placeholders*)
Tim.
On Sun, Oct 06, 2002 at 12:57:16AM -0400, Sam Tregar wrote:
> Here is the POD for the proposed dbi_prof script. I've put off
> designing the interface for DBI::ProfileData for the moment. It should be
> easier to accomplish once the desired functionality in dbi_prof is decided
> upon.
>
> -sam
>
>
> =head1 NAME
>
> dbi_prof - command-line client for DBI::ProfileData
>
> =head1 SYNOPSIS
>
> See a report of the ten queries with the longest total runtime:
>
> dbi_prof
>
> See the top 10 most frequently run queries in the profile file dbi.prof:
>
> dbi_prof --sort count
>
> See the same report with 15 entries:
>
> dbi_prof --sort count --number 15
>
>
> =head1 DESCRIPTION
>
> This tool is a command-line client for the DBI::ProfileData. It
> allows you to analyze the profile data file produced by
> DBI::ProfileDumper and produce various useful reports.
>
> Throughout this documentation I will refer to individual points in the
> profile data as "queries". This is accurate if you used the
> DBI::Profile Path argument of C<2> or C<[ DBIprofile_Statement ]>.
> Since this is the most common usage I chose to phrase the
> documentation in terms of "queries" rather than resort to vague talk
> of "data points" or "leaves".
>
> =head1 OPTIONS
>
> This program accepts the following options:
>
> =over 4
>
> =item --file dbi.prof
>
> Load this profile file. Defaults to dbi.prof in the current
> directory.
>
> =item --number N
>
> Produce this many items in the report. Defaults to 10. If set to
> "all" then all results are shown.
>
> =item --sort field
>
> Sort results by the given field. The available sort fields are:
>
> =over 4
>
> =item total
>
> Sorts by total time run time across all runs. This is the default
> sort.
>
> =item longest
>
> Sorts by the longest single run.
>
> =item count
>
> Sorts by total number of runs.
>
> =item first
>
> Sorts by the time taken in the first run.
>
> =item shortest
>
> Sorts by the shortest single run.
>
> =item alpha
>
> Sorts alphabetically.
>
> =back
>
> =item --reverse
>
> Reverses the selected sort. For example, to see a report of the
> shortest overall time:
>
> dbi_prof --sort total --reverse
>
> =item --select keyN=value
>
> Consider only items with the specified key matching the given value.
> Keys are numbered from 1. For example, let's say you used a
> DBI::Profile Path of:
>
> [ DBIprofile_Statement, DBIprofile_Methodname ]
>
> And called dbi_prof as in:
>
> dbi_prof --select key2=execute
>
> Your report would only include execute queries, leaving out prepares,
> fetches, etc.
>
> Multiple C<--select> options can be specified as long as each refers to a
> separate key. By default the select expression is matched
> case-insensitively, but this can be changed with the --case-sensitive
> option.
>
> =item --match keyN=regex
>
> Consider only items for which the given key matches the regular
> expression. For example, to only include SELECT queries where key1 is
> the statement:
>
> dbi_prof --match key1='^SELECT'
>
> Multiple C<--match> options can be specified, including multiple matches
> for a single key. By default the match expression is matched
> case-insensitively, but this can be changed with the --case-sensitive
> option.
>
> =item --case-sensitive
>
> Using this option causes --match and --select to work case-sensitively.
> Defaults to off.
>
> =item --version
>
> Print dbi_prof version number.
>
> =head1 SEE ALSO
>
> L<DBI::ProfileDumper|DBI::ProfileDumper>,
> L<DBI::Profile|DBI::Profile>, L<DBI|DBI>.
>
> =cut
>
