I. Introduction▲
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.sqlIl 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 : PERFSTATAttention 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 : PERFTBSAprè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 5Lorsque 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 completeIV-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 ;