Suivre les performances de notre base de données Oracle avec : STATSPACK

Introduction

Cette semaine j’ai découvert un utilitaire très sympa : L’utilitaire STATSPACK.

Aujourd’hui la sécurité ou dû moins la surveillance d’une base de données est une tache importante pour un DBA qui est digne de ce statut . En effet, il nous faut veiller à la performance de notre database. L’utilitaire STATSPACK qui est intégré à ORACLE depuis la version 8.1.6 a l’avantage d’être bien conçu et simple d’accès et d’utilisation.

Tout au long de ce billet nous verrons dans un premier temps  , comment installer STATSPACK , puis dans un deuxième temps nous verrons comment il fonctionne et comment faire un rapport de performance puis en dernier lieu nous verrons comment désinstaller STATSPACK.

Installation de STATSPACK

Avant de commencer il est primordial de vérifier que le paramètre timed_statistics est à TRUE, car cela va nous permettre de récolter plus d’informations sur les statistiques.

SQL > show parameter timed_statistic

Si le paramètre n’est pas à true il faut le modifier ainsi :

SQL > alter system set timed_statistics = true scope=both ;

Il faut savoir que statspack doit être installé pour chaque base de données que vous voulez surveiller.

Rendons nous dans le répertoire comportant les scripts de statspack (pour rendre plus simple les commandes) :

cd $ORACLE_HOME/rdbms/admin

Avant de commencer l’installation de statspack,  à proprement dit, on va créer un tablespace statspack et un tablespace temporaire statstemp. En effet lors de l’installation un compte utilisateur PERFSTAT devra être créé et on nous demandera un tablespace et un tablespace temporaire ainsi que le mot de passe pour le nouveau compte.

Toute la partie installation doit se faire avec un utilisateur ayant le privilège sysdba (dans mon cas je vais utiliser le compte system)

Création des tablespaces

$ sqlplus/ nolog

SQL > connect system / as sysdba

Enter password : ********

Connected

SQL > create tablespace statspack

2   datafile ‘/u01/app/oracle/oradata/stats/statspack.dbf’

3   size 300M reuse autoextend on next 50m ;

SQL > create tablespace statstemp

2   tempfile ‘/u01/app/oracle/oradata/stats/statstemp.dbf’

3   size 300M reuse autoextend on next 50m ;

installation statspack avec le script

SQL > @spcreate

Pendant l’exécution du script, il vous sera demander de fournir le mot de passe du compte PERFSTAT, de fournir le tablespace et le tablespace temporaire (sachant qu’une liste des tablespaces vous sera présentée). Le script va utiliser le compte qui vient d’être créé pour se connecter à la base de données et créer les objets requis par statspack.

A l’issue de l’installation on se retrouve connecté avec le compte perfstat (il vous suffit de faire un « select user from dual » pour vérifier).

Bon à savoir : Le compte PERFSTAT reçoit un grand nombre de privilèges sur notre base de données, il serait donc judicieux de sécuriser le compte. (Je rentre pas dans les détails, car je ne serais plus dans le cadre de mon article ^^).

STATSPACK en pratique

Statspack utilise la procédure ‘snap’ du package ‘statspack’ du schéma Perfstat pour effectuer des clichés (snapshots) pour voir les différentes mesures de performance à un instant T.

Lorsqu’on aura au moins deux clichés, il nous sera possible d’extraire un rapport entre un cliché1 et un cliché2 qui va effectuer une comparaison de performance entre les deux périodes.

Avant que je vous montre comment effectuer un cliché et un rapport. Il est bon de savoir qu’il existe 5 niveaux de snapshots (0 , 5 , 6 , 7 et 10 ). Plus le niveau est grand, plus il y aura de collecte d’informations est bien sur le temps de génération du snapshot augmente. Par défaut le niveau est à 5.

• Le niveau 0(Performance générale ) : Statistiques concernant toutes les zones de mémoires, les latchs, pools et évènements.
• Le niveau 5 : informations sur les expression SQL qui sont lourdes et demande beaucoup de ressources +niveau 0.
• Le niveau 6: informations sur les plans d’exécution SQL et leur utilisation + niveau 5.
• Le niveau 7: statistiques sur les segments (lecture logique/physique…) + niveau 6.
• Le niveau 10: informations supplémentaires sur les latchs enfants… + niveau 7.

Modifier le niveau de capture

Supposons que l’on veuille avoir le niveau 10:

exec statspack.snap(i_snap_level => 10, i_modify_parameter => ‘true’);

i_modify_parameter: la valeur true indique que le changement sera appliquer dés le prochain snapshot.

Exécution d’un cliché (en supposant qu’on est toujours dans le répertoire $ORACLE_HOME/rdbms/admin):

SQL> execute STATSPACK.SNAP ;
Procédure PL/SQL terminée avec succès.

Etablir un rapport en se basant sur deux clichés

Assurez vous qu’entre les deux clichés que vous allez choisir, la base de données n’estpas arrêtée.

SQL> @spreport

Oracle  va vous proposer d’entrer l’identifiant de cliché de début et de fin. Une liste des clichés vous sera présentée. Sauf indication de votre part le fichier de report sera nommé sp_IDcliché1_IDcliché2 et sera dans le dossier $ORACLE_HOME/rdbms/admin.

Pour lire le rapport

vim sp_1_2.lst

On pourra à travers ce rapport récolter des informations sur  la santé de notre base de données . Sur l’image ci-dessus,  on peut voir qu’en moyenne ma base de données supporte 0.1 transaction par secondes ( normal étant le seul utilisateur de ma base en plus n’ayant pas forcement travailler entre les deux snapshots le taux est faible… ) On peut aussi voir le taux de lecture et d’écriture physique…

Il et possible d’automatiser les snapshots/rapports , par automatiser j’entends le fait de creer des Jobs/schedules soit avec le système d’exploitation (cron , at…) soit avec le package DBMS_JOB d’Oracle.

Désinstallation de STATSPACK

Comme pour l’installation il vous faut désinstaller statspack avec un utilisateur possédant le privilège sysdba, car statspack inclut des objets privés , des synonymes publics… Un script spdrop.sql vous est fournit pour la désinstallation (dossier $ORACLE_HOME/rdbms/admin.)

Ce script va appeler d’autres script pour supprimer les tables , packages , synonymes et le compte PERFSTAT.

SQL > connect system / as sysdba

Enter password : ********

Connected.

SQL > @spdrop

Conclusion

Nous avons vu tout au long de billet ce qu’est l’utilitaire statspack, comment l’installé et surtout comment l’utilisé. Cet utilitaire peut être très important lorsque l’on rencontre des problèmes de performance ou tout simplement lorsque l’on veut suivre l’état de santé de notre base de données.

Avez vous déjà utilisé STATSPACK ? Si oui faites vous des clichés manuelles ou à l’aide de jobs avec DBMS_JOB/cron/at… ?