Statspack

Par Jaouad ZOUAGHI (home)

Nous allons apprendre à installer l'utilitaire gratuit de mesures des performances sous Oracle: Statspack.
Cette documentation est valable pour les environnements 8 à 10g

I. Introduction
II. Différences entre STATSPACK et ULTBSTATS/ UTLESTATS
III. Installation
III-A. Préambule
III-B. Création du tablespace
III-C. Installation
III-D. Désinstallation
IV. Statpack
IV-A. Préambule
IV-B. Comment fonctionne t'il ?
IV-C. Paramétrer la prise de clichés
IV-D. Tâches d'administration
IV-E. Compatibilité, Upgrade et nouveauté en 9i

I. Introduction

En période de charge et lorsque des problèmes interviennent, vous devez effectuer un snapshot toutes les 15 minutes.
Sinon une prise de clichés toutes les 30 minutes est suffisante dans la plupart des systèmes de productions.
Statspack a été introduit avec la version 8.1.6 d'oracle, cependant il est utilisable avec une base 8.0. C'est un outil de mesure des performances qui remplace les scripts UTLBSTATS.SQL et UTLESTATS.SQL sur les anciennes versions d'Oracle.
Avant de procéder à l'installation de cet utilitaire il convient de s'assurer de la bonne valeur d'un paramètre que nous avons déjà eu l'occasion de voir : TIMED_STATISTICS.
En effet ce dernier doit être positionnée à TRUE.
Positionné à true au niveau instance (PFILE, SPFILE ou par le biais de la commande « alter system ou alter session …»), ce paramètre va permettre à Oracle de fournir des informations temporelles.
Les statistiques temporelles sont exprimées en microsecondes.
Dans les versions 9.2 et ultérieures les statistiques sont collectées de manière automatique pour l'instance si STATISTICS_LEVEL est à TYPICALL ou ALL.
Si par contre, sa valeur est BASIC alors dans ce cas là il faut que le paramètre TRUE soit affecté à TIMED_SATISTICS.
Pour modifier la valeur de ce paramètre :

SQL> show parameter TIMED_STATISTICS ; 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     FALSE
SQL> alter system set  TIMED_STATISTICS =TRUE scope =both ;

Système modifié.

SQL> show parameter TIMED_STATISTICS ; 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE
SQL>

Lors du prochain arrêt redémarrage de la base, ce changement de paramétrage sera également appliqué au SPFILE ( Server Parameter File).

II. Différences entre STATSPACK et ULTBSTATS/ UTLESTATS

Statspack enregistre beaucoup plus d'informations et de manière plus complète que la version précédente de mesure des statistiques d'Oracle.

Les ratios sont déjà calculés.
On a la possibilité de stocker les informations dans la base de données.
Identifier les requêtes SQL gourmandes.
Page de résumé des rapports.
Statspack appartient à un schéma : Perfstat.

Statpack s'exécute donc sous le schéma PERFSTAT, il a donc son propre schéma mais également des tables, indexes, séquences et un package.
Statpack doit être installé sur chaque base, avant de procéder à l'installation :

Créer un tablespace qui lui sera propre afin d'éviter l'utilisation de SYSTEM.
Le Tablespace doit de préférence être autoallocate et locally managed.
Il doit faire au moins 100 M.
Définir un tablespace temporaire pour Perfstat.
Dimensionner un Shared pool d'au moins 9 M.

III. Installation

III-A. Préambule

Oracle recommande d'effectuer l'installation uniquement sous SQL*PLUS et non sous Server Manager (pour les versions compatibles ).
Tout les scripts d'installation, paramétrage et utilisation se trouve dans :

$ORACLE_HOME/rdbms/admin/

Et se présentent sous la forme sp*.sql
Sous unix :

$ cd $ORACLE_HOME/rdbms/admin
$ ls -ltr sp*.sql
-rw-r--r--   1 oracle10   oinstall     23329 Jun 28 17:48 spup817.sql
-rw-r--r--   1 oracle10   oinstall      1268 Jun 28 17:48 sprepsql.sql
-rw-r--r--   1 oracle10   oinstall    194764 Jun 28 17:48 sprepins.sql
-rw-r--r--   1 oracle10   oinstall     30655 Jun 28 17:48 spup816.sql
-rw-r--r--   1 oracle10   oinstall      4228 Jun 28 17:48 sptrunc.sql
-rw-r--r--   1 oracle10   oinstall      1284 Jun 28 17:48 spreport.sql
-rw-r--r--   1 oracle10   oinstall      1540 Jun 28 17:48 spdusr.sql
-rw-r--r--   1 oracle10   oinstall      4900 Jun 28 17:48 sppurge.sql
-rw-r--r--   1 oracle10   oinstall      6868 Jun 28 17:48 spdtab.sql
-rw-r--r--   1 oracle10   oinstall    166365 Jun 28 17:48 spcpkg.sql
-rw-r--r--   1 oracle10   oinstall       861 Jun 28 17:48 spcreate.sql
-rw-r--r--   1 oracle10   oinstall     72564 Jun 28 17:48 spctab.sql
-rw-r--r--   1 oracle10   oinstall     14121 Jun 28 17:48 spcusr.sql
-rw-r--r--   1 oracle10   oinstall       758 Jun 28 17:48 spdrop.sql
-rw-r--r--   1 oracle10   oinstall      1771 Jun 28 17:48 spauto.sql
-rw-r--r--   1 oracle10   oinstall     31001 Jun 28 17:48 sprsqins.sql
-rw-r--r--   1 oracle10   oinstall     19129 Jun 28 17:48 spup90.sql
-rw-r--r--   1 oracle10   oinstall      2460 Jun 28 17:48 sprepcon.sql
-rw-r--r--   1 oracle10   oinstall     40277 Jun 28 17:48 spup92.sql

Il existe en plus des scripts traditionnels d'exploitation, des scripts permettant d'assurer des tâches d'administration sur des versions inférieures notamment, notamment lorsque l'on effectue des upgrades: spup817.sql , spup90.sql ( ici nous sommes dans un ORACLE_HOME 10G ).
Commencer par la création du Tablespace :

III-B. Création du tablespace

SQL> CREATE TABLESPACE PERFTBS 
  2      LOGGING 
  3      DATAFILE 'C:\ORACLE\ORADATA\OIDPRD\PERFTBS1.ora' SIZE 120M 
  4      REUSE AUTOEXTEND 
  5      ON NEXT  1024K MAXSIZE  16383M EXTENT MANAGEMENT LOCAL 
  6      UNIFORM SIZE 1024K SEGMENT SPACE MANAGEMENT  AUTO ;

Tablespace créé.

III-C. Installation

L'installation se lance en se connectant à l'instance sous un super utilisateur et en exécutant le script SPCREATE.SQL
Trois scripts sont lancés par SPCREATE :
Spcusr : qui permet la création de l'utilisateur et lui donne les privilèges ainsi que des vues nécessaires.
Spctab : qui crée les tables et indexes.
Spcpkg : qui conclut par la création du package STATPACK.
La première action est de demander un nouveau mot de passe pour cet utilisateur. Il est préférable, dans un souci de commodité, de laisser le mot de passe à PERFSTAT. D'autant plus que les privilèges de cet utilisateur ne permettent pas d'actions dangereuses.

C:\>set local=oidprd

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Me Sep 7 12:06:49 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /as sysdba
Connect.
SQL> start C:\oracle\oid\rdbms\admin\spcreate.sql
... Installing Required Packages

Package cr.


Autorisation de privilges (GRANT) accepte.


Vue cre.


Corps de package cr.


Package cr.


Synonyme cr.


Autorisation de privilges (GRANT) accepte.


Vue cre.


Synonyme cr.


Vue cre.


Synonyme cr.


Vue cre.


Synonyme cr.


Vue cre.


Synonyme cr.


Vue cre.


Synonyme cr.


Vue cre.


Synonyme cr.

... Creating PERFSTAT user ...

Choose the PERFSTAT user's password.

Not specifying a password will result in the installation FAILING

Specify PERFSTAT password
Entrez une valeur pour perfstat_password : PERFSTAT

Attention comme il est indiqué le fait de ne pas lui préciser de PASSWORD fait échouer l'installation.

Procdure PL/SQL termine avec succs.


Utilisateur cr.


Autorisation de privilges (GRANT) accepte.


Autorisation de privilges (GRANT) accepte.


(….) 


Autorisation de privilges (GRANT) accepte.



Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the STATSPACK tables
and indexes.  This will also be the PERFSTAT user's default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for performance data is not supported.


TABLESPACE_NAME                CONTENTS
------------------------------ ---------
CERTIF                         PERMANENT
DRSYS                          PERMANENT
EXAMPLE                        PERMANENT
INDX                           PERMANENT
ODM                            PERMANENT
OLTS_ATTRSTORE                 PERMANENT
OLTS_CT_CN                     PERMANENT
OLTS_CT_DN                     PERMANENT
OLTS_CT_OBJCL                  PERMANENT
OLTS_CT_STORE                  PERMANENT
OLTS_DEFAULT                   PERMANENT

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
OLTS_IND_ATTRSTORE             PERMANENT
OLTS_IND_CT_CN                 PERMANENT
OLTS_IND_CT_DN                 PERMANENT
OLTS_IND_CT_OBJCL              PERMANENT
OLTS_IND_CT_STORE              PERMANENT
OLTS_TEMP                      TEMPORARY
PERFTBS                        PERMANENT
P1TS_ATTRSTORE                 PERMANENT
P1TS_IND_STORE                 PERMANENT
TEMP                           TEMPORARY
TOOLS                          PERMANENT

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS1                       UNDO
USERS                          PERMANENT
XDB                            PERMANENT

25 ligne(s) slectionne(s).


Specify PERFSTAT user's default   tablespace
Entrez une valeur pour default_tablespace : PERFTBS

Après avoir donné tous les privilèges requis, l'installation à besoin de connaître la tablespace par défaut et le temporaire.
Avant de vous demander le TBS notez qu'Oracle effectue un récapitulatif des espaces de tables présents.
Ensuite Oracle lance les deux scripts de création de segments. Ici il n'y a pas besoin d'actions du DBA.
Un message de fin indique que l'installation c'est déroulé sans problème :

Corps de package cr.

Pas d'erreur.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

Comme souvent lors de l'installation d'outil Oracle, des reports d'installation sont crées sous le répertoire courant de lancement de Sql*Plus :
Ils se nomment ainsi :
Spcusr.lis : report sur la création de l'utilisateur.
Spctab.lis : report sur la création des tables et indexes.
Spcpkg.lis : report sur la création du package STATPACK.

III-D. Désinstallation

Pour effectuer une désinstallation propre ou si au cours de l'installation se produit une erreur il suffit de simplement lancé le script SPDROP.sql. Ce script appelle deux autres scripts :
Spdtab.sql qui va supprimer les objets de PERFSTAT.Il produit un fichier de sortie dans le répertoire courant : spdtab.lis qui permet de visualiser le déroulement du script.
Spusr.sql qui supprime le user PERFSTAT.Il produit également un spool : spdusr.lis

IV. Statpack

IV-A. Préambule

La procédure statpack.snap extrait des informations des tables V$ et les enregistre dans les tables du référentiel de Perfstat.

Il existe une séquence STATS$SNAPSHOT_ID qui permet d'identifier de manière unique les reports notamment lors de la production de ces derniers ou lors d'opérations de purge.

IV-B. Comment fonctionne t'il ?

Grâce à la procédure Perstat.statpack.snap on va pouvoir effectuer des clichés permettant donc de prendre différentes mesures et de les stocker en base.
Lorsque l'on a pris au moins deux clichés, nous allons pouvoir formater un report en effectuant une comparaison des valeurs pour les deux périodes.
Pour pouvoir générer un rapport il faut tout simplement exécuter le script SPREPORT comme nous le voyons ici :

                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
XXXXX      XXXX     7851 22 Jun 2005 09:00     5
                                    7852 22 Jun 2005 10:00     5
                                                      (…) 
                                    7869 23 Jun 2005 17:00     5
                                    7870 23 Jun 2005 18:00     5

Lorsque nous exécutons SPREPORT on voit s'afficher à l'écran le nom de l'instance et de la base de données.
Nous voyons également l'ID qui est le numéro unique d'identification et enfin la date de prise de cliché et le niveau du cliché.

Il suffit alors de donner l'ID du report de début et l'ID du report de fin. Ainsi que le nom du report. Cette information est facultative : en effet si nous ne donnons pas de nom spécifique, Oracle nomme toujours ces reports de la manière suivante :
SP_IDDEBUT_IDFIN.LST

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entrez une valeur pour begin_snap : 7851
Begin Snapshot Id specified: 7851

Entrez une valeur pour end_snap : 7852

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp__.  To use this name,
press <return> to continue, otherwise enter an alternative.
Entrez une valeur pour report_name :

Rappelons-nous que les clichés sont basés sur des informations insérés dans des tables.
Pour ce faire on peut retrouver tous les clichés que l'on a prit interrogeant la table Perfstat.stats$snapshot.

SQL> SELECT * FROM perfstat.stats$snapshot ;

IV-C. Paramétrer la prise de clichés

Pour programmer la prise de clichés, on peut programmer une tache planifiée sous CRON et/ou AT en fonction de votre système d'exploitation.
Il existe une autre solution qui est indépendante du système d'exploitation :

Le planificateur de tâche Oracle :DBMS_JOB.
Celui est conseillé pour ce genre de tâches d'administration car il permet ainsi de soulager le système de gestion des tâches planifiées et de ne pas interférer avec les autres tâches d'administration du système ( même si sous UNIX le user Oracle peut avoir sa propre CRONTAB ).
Le package statspack est constitué de quatre procédures et une fonction :
Statspack.modify_statspack_parameter :

procedure MODIFY_STATSPACK_PARAMETER
( i_dbid in number default null
, i_instance_number in number default null
, i_snap_level in number default null
, i_session_id in number default null
, i_ucomment in varchar2 default null
, i_num_sql in number default null
, i_executions_th in number default null
, i_parse_calls_th in number default null
, i_disk_reads_th in number default null
, i_buffer_gets_th in number default null
, i_sharable_mem_th in number default null
, i_version_count_th in number default null
, i_seg_phy_reads_th in number default null
, i_seg_log_reads_th in number default null
, i_seg_buff_busy_th in number default null
, i_seg_rowlock_w_th in number default null
, i_seg_itl_waits_th in number default null
, i_seg_cr_bks_sd_th in number default null
, i_seg_cu_bks_sd_th in number default null
, i_all_init in varchar2 default null
, i_pin_statspack in varchar2 default null
, i_modify_parameter in varchar2 default 'TRUE'
);

Cette fonction permet de modifier les seuils de définition des ordres SQL les plus consommateurs.
Notamment grâce à :
I_executions_th : nombre d'exécutions maximum d'une requête ( INTEGER => 0 défaut 100).
I_disk_read_th : Nombre de lecture Disque par requête ( integer => 0 défaut 1000)
I_parse_call_th : nombre de parse par appel ( integer => 0 défaut 1000).
I_buffer_gets_th : nombre de buffer par requête, ce paramètre indique les requêtes qui peuvent nécessiter une optimisation : en effet il faut surveiller les requêtes qui consomment beaucoup trop de buffer/ nombre de lignes ramenées. (Absence d'index ou mauvaise jointure) ( integer => 0 défaut 10 000).
I_sharable_mem_th : Mémoire consommée par requête ( integer => 0 défaut 1048576 l'unité est l'octet )
On peut également voir les ordres SQL les plus consommateurs en se basant sur la table :

SQL> SELECT * FROM perfstat.stats$sql_summary  ;

La liste des paramètres est visible également par une requête SQL :

SQL> SELECT * FROM perfstat.stats$statspack_parameter ;

Note : Le DBID est celui que l'on peut retrouver dans la vue V$DATABASE.
Statspack. QAM_STATSPACK_PARAMETER:

procedure QAM_STATSPACK_PARAMETER
( i_dbid in number default null
, i_instance_number in number default null
, i_snap_level in number default null
, i_session_id in number default null
, i_ucomment in varchar2 default null
, i_num_sql in number default null
, i_executions_th in number default null
, i_parse_calls_th in number default null
, i_disk_reads_th in number default null
, i_buffer_gets_th in number default null
, i_sharable_mem_th in number default null
, i_version_count_th in number default null
, i_seg_phy_reads_th in number default null
, i_seg_log_reads_th in number default null
, i_seg_buff_busy_th in number default null
, i_seg_rowlock_w_th in number default null
, i_seg_itl_waits_th in number default null
, i_seg_cr_bks_sd_th in number default null
, i_seg_cu_bks_sd_th in number default null
, i_all_init in varchar2 default null
, i_pin_statspack in varchar2 default null
, i_modify_parameter in varchar2 default 'FALSE'
, o_snap_level out number
, o_session_id out number
, o_ucomment out varchar2
, o_num_sql out number
, o_executions_th out number
, o_parse_calls_th out number
, o_disk_reads_th out number
, o_buffer_gets_th out number
, o_sharable_mem_th out number
, o_version_count_th out number
, o_seg_phy_reads_th out number
, o_seg_log_reads_th out number
, o_seg_buff_busy_th out number
, o_seg_rowlock_w_th out number
, o_seg_itl_waits_th out number
, o_seg_cr_bks_sd_th out number
, o_seg_cu_bks_sd_th out number
, o_all_init out varchar2
, o_pin_statspack out varchar2
);

Statspack. SNAP ( permet la prise de clichés) .

procedure SNAP
(i_snap_level in number default null
,i_session_id in number default null
,i_ucomment in varchar2 default null
,i_num_sql in number default null
,i_executions_th in number default null
,i_parse_calls_th in number default null
,i_disk_reads_th in number default null
,i_buffer_gets_th in number default null
,i_sharable_mem_th in number default null
,i_version_count_th in number default null
,i_seg_phy_reads_th in number default null
,i_seg_log_reads_th in number default null
,i_seg_buff_busy_th in number default null
,i_seg_rowlock_w_th in number default null
,i_seg_itl_waits_th in number default null
,i_seg_cr_bks_sd_th in number default null
,i_seg_cu_bks_sd_th in number default null
,i_all_init in varchar2 default null
,i_pin_statspack in varchar2 default null
,i_modify_parameter in varchar2 default 'FALSE'
);

On peut également paramétrer le niveau de finesse de prise des clichés.
Levels >= 0 : Performances générales de la base. Ici nous avons les ratios principaux ( cache, shared_pool, les différents caches … ) et la liste des contentions ( Wait event ) et des statistiques de l'instance ( les latchs, UNDO.. ).
Levels >=5 : Ajout des ordres SQL les plus consommateurs.
Levels >=6 : Ajout des Explain plan
Levels >= 10 : Parent and child childrens ( En règle général il sert au support Oracle ).
Les fonctions SLARTI et la procédure STAT_CHANGES sont secondaires et très peu utilisés. Une recherche sur Métalink ou OTN vous permettra d'avoir une explication précise.

IV-D. Tâches d'administration

Bien entendu comme tout outil STATSPACK réclame une administration qui se consiste en deux points essentiels :
Le premier est de calculer des statistiques sur le schéma PERFSTAT. Et oui cet outil de mesures statistiques a également besoin de stats afin que la génération de report soit le plus rapide possible.

Un exemple :

SQL> conn system
Entrez le mot de passe : ********
Connecté.
SQL> execute dbms_utility.analyze_schema('PERFSTAT','COMPUTE'); 

Procédure PL/SQL terminée avec succès.

SQL>

Et enfin de temps en temps il faut vider les tables du référentiel afin de garantir un bon accès aux données et de ne pas surcharger inutilement le tablespace.
Sppurge supprime les entrées dans les tables :

SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql

    7869     5  23 Jun 2005 17:00:17 db1prod
    7870     5  23 Jun 2005 18:00:52 db1prod


Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 7869
Using 7870 for lower bound.

Enter value for hisnapid: 7870
Using 7870 for upper bound.

Deleting snapshots 7869 - 7870.


Purge of specified Snapshot range complete.  If you wish to ROLLBACK
the purge, it is still possible to do so.  Exitting from SQL*Plus will
automatically commit the purge.

Comme pour Spreport on choisit l'ID de début et L'id de fin.
Il est possible à la fin du script d'effectuer un ROLLBACK
Sptrunc truncate toutes les tables :

 SQL>@ORACLE_HOME/rdbms/admin/sptrunc.sql
 Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.


About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you would like to continue, press <return>


Enter value for return: 
Entered - starting truncate operation

Table truncated.


Table truncated.


(...) 


1028 rows deleted.


43 rows deleted.


Commit complete.


Truncate operation complete

IV-E. Compatibilité, Upgrade et nouveauté en 9i

Il est impossible d'utiliser une version de statspack sur une version inférieure de base de données. Ainsi Les scripts 9i ne fonctionnent qu'avec la 9i.
Cependant il est possible d'utiliser statspack 817 sur une base 9i.
A chaque upgrade il est effectivement conseillé d'upgrader également Statspack ( script d'upgrade fournit avec la version 9i et 10g , par spup817.sql permet d'upgrader un statspack 817 en 9i )
A partir de la 9i STATSPACK supporte les environnements RAC mais il permet également :

D'avoir les temps cumulés.
Affiche l'explan plan et le texte SQL complet pour une hash value ;

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2005 Jaouad ZOUAGHI. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.