Tracing and profiling DBI

Tracing

Sometimes you want to see the actual queries that DBI is generating. This can be easily achieved by using trace static method. When running a normal Perl script you can also set environment variable DBI_TRACE to the same value.

trace can accept several values which control verbosity and output logging. In the following example we are going to set trace to 2.

DBI->trace(2);

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

my $firstname = 'John';
my $sth       = $dbh->prepare(
    'SELECT firstname, lastname FROM users WHERE firstname = ?');
$sth->execute($firstname);

Profiling

The easiest way to profile DBI is to set $dbh->{Profile} attribute or DBI_PROFILE environment variable.

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});
$dbh->{Profile} = 2;

my $firstname = 'John';
my $sth       = $dbh->prepare(
    'SELECT firstname, lastname FROM users WHERE firstname = ?');
$sth->execute($firstname);

Profiling itself can be very sophisticated. Check out DBI::Profile for more information on this topic.