Les problèmes de performance (tuning) en bases de données sont récurrents et redoutés des entreprises et Oracle ne fait pas exception. Imaginez qu’au moment où l’on sort les paies ou les factures en fin de mois, la base de données se met à  » ramer », je vous assure que votre patron ne viendra pas vous féliciter. Ainsi un de nos clients m’apprend un matin que la base de données qui lui sert à la facturation affiche une baisse de performance (elle est lente). Je lui ai demandé s’il a une licence Entreprise Manager, la réponse était évidemment non !

              Bon ! Un petit tour théorique. Sous Oracle, parmi les outils de diagnostic et de tuning on peut trouver :

  • AWR pour Automatic Workload Repository : son utilisation demande une licence
  • ADDM pour Automatic Database Diagnostic Monitor : son utilisation demande également une licence
  • STATSPACK reports : Gratuit
  •  …

Nous allons donc utiliser STASPACK pour trouver d’éventuels goulots d’étranglement, des requêtes couteuses en performance, etc. Pour cela, il faut installer STATSPACK, le configurer, générer des snapsots à intervalle de temps réguliers, générer le rapport, regarder le dit rapport pour détecter par exemple des requêtes couteuses en performances.

STATSPACK n’est pas installé par défaut sur Oracle 11g et 12g, il faudra donc l’installer. Mais avant cela quelques petites vérifications s’imposent :

  • Se connecter à Oracle en tant que SYSDBA et passer les instructions suivantes :

show parameter statistics_level;

show parameter control_management;

dans le meilleur des cas, nous avons ce résultat :

STATISTICS_LEVEL = TYPICAL or ALL or BASIC

  • Si STATISTICS_LEVEL = TYPICAL or ALL

Alors la capture des snapshots est activée.

  • Si STATISTICS_LEVEL = BASIC

Alors la capture des snapshots est désactivée

CONTROL_MANAGEMENT_PACK_ACCESS = { NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING }

Si CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING

On peut alors générer un rapport AWR, ADDM, … c’est le cas dans Entreprise Edition d’Oracle et une licence séparée est nécessaire.

Si CONTROL_MANAGEMENT_PACK_ACCESS = NONE

C’est le cas dans toutes les autres éditions. Les package cités ci-dessus ne sont pas disponible. Seul STATSPACK qui est un outil gratuit peut être utilisé.

Une fois que ces vérifications faites, nous pouvons passer aux étapes suivantes.

  1. Installation de STATSPACK sur la base de développement ORCL :

Il faut au préalable créer un tablespace où seront stockés les snapshots. PERFTBS est le nom le plus communément utilisé.

  1. Création du tablespace PERFTBS : la requête utilisée est la suivante :

CREATE TABLESPACE PERFTBS

       LOGGING

       DATAFILE ‘C:\oracle\STATSPACK\PERFTBS1.ora’ SIZE 120M

       REUSE AUTOEXTEND ON NEXT  1024K MAXSIZE  16383M EXTENT MANAGEMENT LOCAL

       UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT AUTO;

La requête ci-dessus permet de créer un tablespace auto extensible etc. Mais comme la prudence est de mise, il est préférable de créer un tablespace de taille limitée et que l’on peut étendre manuellement uniquement. 

  1. Installation de STATSPACK en exécutant en SYSDBA, le fichier .sql suivant :

Sous l’utilisateur SYSDBA, exécuter le script suivant :

@$ORACLE_HOME\RDBMS\ADMIN\spcreate.sql

Exemple :

@C:\Oracle\Product\11.2.0.4\RDBMS\ADMIN\spcreate.sql

Pendant l’exécution, les informations suivantes seront demandées : il faudra définir le tablespace par défaut, un mot de passe pour l’utilisateur PERFSTAT que le script va créer, le plus simple est de faire comme suit :

Default_tablespace = ‘PERFTBS’

Tempory_tablespace = ‘TEMP’

Perfstat_password = ‘perfstat’

A la fin du processus, ceci s’affiche :

Pour passer les commandes STATSPACK, il faut se connecter avec l’utilisateur PERFSTAT. Ici, le mot de passe est le même : conn perfstat/perfstat

Puis exécution des requêtes suivantes :

exec STATSPACK.MODIFY_STATSPACK_PARAMETER (I_SNAP_LEVEL=>10)

Par défaut le paramètre I_SNAP_LEVEL =>5, il faut le porter à 10 pour avoir un maximum d’information sur les snapshot qui seront capturés. Pour vérifier que cela a été bien exécuté, passer la requête suivante :

SELECT SNAP_LEVEL FROM STATS$STATSPACK_PARAMETER;

1.3.Création du premier SNAPSHOT :

exec STATSPACK.SNAP

Afficher les SNAPSHOTS :

SELECT SNAP_ID, SNAP_LEVEL, to_char(SNAP_TIME,’DD-MM-YYYY HH24:MI:SS’) SNAP_TIME FROM STATS$SNAPSHOT ORDER BY 1;

Attendre au moins 1h pour créer le second SNAPSHOT afin d’avoir les informations. C’est l’activité de la base qui est relevée pendant cet intervalle (ordres SQL, …). Pour que les informations relevées soient exploitables efficacement, cet intervalle de temps doit être prolongé exemple (1 SNAPSHOT par jour pendant une semaine). Le nombre et l’intervalle entre deux SNAPSHOTS est variable selon l’activité de la base.
1.4.Création du 2e SNAPSHOT environ 1h plus tard par exemple :

1.5.Référencer les SNAPSHOT (baselining) :

exec STATSPACK.MAKE_BASELINE(I_BEGIN_SNAP=>1, I_END_SNAP=>2);

SELECT SNAP_ID,SNAP_LEVEL,SNAP_TIME,BASELINE FROM STATS$SNAPSHOT ORDER BY 1;

1.6.Associer les SNAP à l’utilisateur PERFSTAT :
exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>’PERFSTAT’,CASCADE=>TRUE);

1.7.Générer le rapport : pour cela, se connecter avec l’utilisateur PERFSTAT et exécuter le fichier suivant :

@$ORACLE_HOME\RDBMS\ADMIN\spreport.sql

Exemple :

@C:\Oracle\Product\11.2.0.4\RDBMS\ADMIN\spreport.sql

Le rapport va relever l’activité de la base de données entre 2 snapshots capturés avec un intervalle de temps déterminé par le DBA et indiquer leur consommation en ressources. Pour générer ce rapport, nous devons fournir le SNAP_ID du premier snapshot et celui du dernier et le nom que l’on veut donner au rapport.
Le rapport a le nom de « SP_31-32.lst ». Sous Windows server 2012, ce rapport se trouve à l’emplacement « C:\Users\Administrateur »

1.8.Générer le rapport SQL :
Voici à quoi ressemble un rapport STATSPACK :

On peut y distinguer le SID de l’instance (ici orcl) ; le DB_ID (ici 112182136), la date de prise des snapshot et leurs numéros.

Dans ce rapport, chercher la paragraphe « SQL ordered by CPU » puis repérer la requête qui consomme le plus de CPU et relever son numéro de HASH (Hash Value). Ici c’est 1403518453. Puis générer le rapport SQL (SPREPSQL) en exécutant ce fichier avec le user PERFSTAT :

@C:\Oracle\Product\11.2.0.4\RDBMS\ADMIN\ sprepsql.sql

Voici le rapport en question où le texte de la requête a été affiché : une fois la requête couteuse en CPU est connue, nous pouvons l’optimiser pour améliorer les performances de la base.

Article réalisé par Smail G. – DBA – Noury Solutions

Tags