Si vous utilisez Oracle, vous utilisez Perl ! Oracle Remote Diagnostic Agent (aka RDA), l’agent Enterprise Manager, ou l’outil de clone (aka clone.pl) utilisent en effet le langage de script inclus avec le logiciel Oracle et portable sur de nombreux OS. Facile, Perl permet en particulier de manipuler des jeux de données ou de travailler avec des expressions régulières en quelques lignes…

Dans cet article, je vous propose d’utiliser les bibliothèques adéquates pour créer des graphiques à partir de données stockées en base. Et voilà une autre alternative à Java, Excel, APEX, Google Chart ou encore Python pour représenter des données souvent incompréhensibles sans quelques graphiques.

Des bibliothèques utiles

Un des intérêts de Perl réside dans la communauté très active et dans le nombre incroyables de bibliothèques. Pour ce qui suit, installez DBD::Oracle, GD:Graph et Data::Pivot, comme ci-dessous; commencez par installer un client 11g et par configurer les variables d’environnement correspondantes avant de lancer les installations comme ci-dessous, si vous disposez d’un accès Internet:

sudo perl -MCPAN -e shell 

CPAN> install GD::Graph 

CPAN> install DBD::Oracle

CPAN> install Data::Pivot

Une fois les bibliothèques installées, vous pouvez simplement accéder à la documentation associées avec perldoc comme ci-dessous :

perldoc DBD::Oracle

Un exemple simple

Vous ne manquez surement pas d’idées pour créer des graphiques. Pour illustrer la combinaison des 3 bibliothèques ci-dessus, voici un exemple qui utilise le contenu d’Oracle AWR pour suivre l’évolution de l’espace occupé dans vos tablespaces.

Notez bien que pour exécuter la requête ci-dessous, vous devez avoir une licence d’Oracle Diagnostic Pack

Si vous venez de créer votre base de données et qu’il n’y a pas véritablement d’historique dans AWR, vous pouvez commencer par lancer le script ci-dessous qui va faire évoluer sur un intervalle de quelques clichés l’espace occupé dans vos tablespaces :

create table X(text varchar2(4000)) tablespace users;

begin
for i in 1..10 loop
   for j in 1..10240 loop
      insert into X values (rpad('X',1024, 'X'));
   end loop;
   commit;
   dbms_lock.sleep(10);
   dbms_workload_repository.create_snapshot;
end loop;
end;
/
drop table X purge;
exec dbms_workload_repository.create_snapshot

La requête suivante extrait les données d’AWR qui correspondent à notre futur graphe:

select vts.tsname, 
       to_char(s.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') timestamp,
       ts.TABLESPACE_USEDSIZE
  from dba_hist_tbspc_space_usage ts,
       dba_hist_snapshot s,
       dba_hist_tablespace_stat vts
 where s.snap_id=ts.snap_id
   and s.dbid=ts.dbid
   and s.snap_id=vts.snap_id
   and s.dbid=vts.dbid
   and ts.tablespace_id=vts.ts#(+)
 order by 1, 3;

Le programme Perl

Vous imaginez le principe? Voici le programme Perl qui crée le graphique correspondant à la requête. En substance, ce programme:

• Se connecte à la base de données en « / as sysdba » et exécute la-dite requête
• pivote les données selon l’axe X
• Utilise GD::Graph pour créer un graphe cumulé
• Enregistre un fichier .PNG correspondant au graphe

Le script complet est disponible ci-dessous:

#!/usr/bin/perl
use strict;
use DBI;
use DBD::Oracle qw(:ora_session_modes);
use Data::Pivot;
use GD::Graph::area;
#use Data::Dumper;

#
# Etape 1. Connexion à la base de données et exécution de la requête
#
my $dbh = DBI->connect('DBI:Oracle:', '', '', { ora_session_mode => ORA_SYSDBA } )
   or die "Impossible de se connecter à la base de données: " . DBI->errstr;

my $query=q{
select vts.tsname,
       to_char(s.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') timestamp,
       ts.TABLESPACE_USEDSIZE
  from dba_hist_tbspc_space_usage ts,
       dba_hist_snapshot s,
       dba_hist_tablespace_stat vts
 where s.snap_id=ts.snap_id
   and s.dbid=ts.dbid
   and s.snap_id=vts.snap_id
   and s.dbid=vts.dbid
   and ts.tablespace_id=vts.ts#(+)
 order by 1, 3
};

my $sth = $dbh->prepare($query)
   or die "Impossible de préparer la commande: " . $dbh->errstr;

$sth->execute();

my @dset=$sth->fetchall_arrayref ;

#
# Etape 2. Pivot de la table de données pour afficher 
#          Y=Tablespace
#          X=Time
#
my @xlabel = ('Tablespace', 'Time', '');
my @newdset = pivot( table        => @dset,
                     headings     => @xlabel,
                     pivot_column => 1,
                     layout       => 'vertical',
                     row_sum      => undef,
                     row_titles   => 0,
                     format       => '%5.2f',
                   );

shift (@xlabel);

my @ylabel = ();
for my $i ( 0 .. $#newdset ) {
   push @ylabel, $newdset[$i][0];
   shift (@{$newdset[$i]});
}

unshift (@newdset,@xlabel);

# 
# Etape 2'. Utilisation de Data::Dumper pour afficher le contenu
#           des tableaux
#
#print Dumper @newdset;
#print Dumper @ylabel;
#print Dumper @xlabel;

#
# Etape 3. Création du fichier graphique .PNG à partir des tableaux
#
my $mygraph = GD::Graph::area->new(600, 400);
$mygraph->set(
    x_label     => 'Time',
    y_label     => 'Used Blocks',
    title       => 'Space Usage Trend',
    # Draw datasets in 'solid', 'dashed' and 'dotted-dashed' lines
    line_types  => [1],
    x_label_skip => 1,
    x_labels_vertical => 1,
    # Set the thickness of line
    line_width  => 1,
    cumulate    => 1,
    # Set colors for datasets
    # borderclrs       => ['lred'],
    # dclrs       => ['lred'],
) or warn $mygraph->error;

$mygraph->set_legend_font(GD::gdMediumBoldFont);

$mygraph->set_legend(@ylabel);

my $data = GD::Graph::Data->new(@newdset)
    or die GD::Graph::Data->error;

my $myimage = $mygraph->plot($data) or die $mygraph->error;

#
# Etape 4. Enregistrement du fichier correspondant
#
open(IMG, ">mygraphic.png") or die $!;
binmode IMG;
print IMG $myimage->png;
close IMG;

Le résultat

Le résultat est un graphique que vous n’aurez aucun mal à personaliser.

Conclusion

Vous pourrez facilement enrichir cet exemple avec des paramètres, Apache, CGI, GD::Graph3d, GD::Graph::Map et bien d’autres idées… Maintenant, faites-vous plaisir!