Alors que l’utilisation d’AWR nécessite le Diagnostic Pack d’EM et donc une Entreprise Edition, Statspack est son pendant gratuit pour effectuer du tuning sur toutes les versions de base de données Oracle depuis la version 9i.
Statspack est simple d’utilisation mais voici quelques instructions qui vous permettront de gagner du temps dans sa mise en œuvre et son exploitation.
Sommaire
1 – Documentation et références
2 – Faire l’état des lieux sur une base inconnue
3 – Installer
4 – Désinstaller
5 – Capturer des snapshots
6 – Sauvegarder les clichés par Datapump Export
7 – Recharger une sauvegarde par Datapump Import
8 – Purger
9 – Obtenir un rapport Statspack
10–Interpréter le rapport
==================================================
1 – Documentation et références
?/rdbms/admin/spdoc.txt MOS note 228913.1 Systemwide Tuning using STATSPACK Reports MOS note 942241.1 STATSPACK Complete Reference DOCUMENTATION Utilities chapitre "Datapump Legacy Mode"
2 – Faire l’état des lieux sur une base inconnue
- Statspack a-t-il été implémenté ?
Connect / as sysdba Select * from all_users where username=’PERFSTAT’ ;
- Combien de snapshots y-a-t-il actuellement en base ?
SELECT count(*) FROM STATS$SNAPSHOT /
Combien de snapshots ventilés par mois ?
SELECT TO_CHAR(snap_time,'YYYY/MM') "AN/MOIS", count(*), MIN(snap_id) begin_snap, MAX(snap_id) end_snap FROM STATS$SNAPSHOT GROUP BY TO_CHAR(snap_time,'YYYY/MM') /
Détail des snaphots ?
SELECT instance_id, snap_id, TO_CHAR(snap_time,'DD/MM/YYYY HH24"h"MI') snap_time, snap_level, baseline FROM STATS$SNAPSHOT ORDER BY snap_time /
- La prise de clichés a-t-elle été automatisée ?
SET LINES 135 SET PAGES 500 COL interval FORMAT A25 COL what FORMAT A25 COL schema_user FORMAT A15 SELECT instance, job, schema_user, interval, broken, what, TO_CHAR(last_date, 'DD/MM/YYYY HH24"h"MI"m"SS') last_date, TO_CHAR(next_date, 'DD/MM/YYYY HH24"h"MI"m"SS') next_date FROM dba_jobs WHERE lower(what) like ‘%statspack.snap%’;
3 – Installer
Cela revient à créer le user PERFSTAT :
CONNECT as sysdba DEFINE default_tablespace='SYSAUX' DEFINE temporary_tablespace='TEMP' DEFINE perfstat_password='perfstat' @?/rdbms/admin/spcreate.sql UNDEFINE perfstat_password
4 – Désinstaller
Cela revient à supprimer le user PERFSTAT
CONNECT as sysdba @?/rdbms/admin/spdrop.sql
5 – Capturer des snapshots
- Capturer un snapshot ponctuellement :
CONNECT perfstat/perfstat VARIABLE snapno NUMBER BEGIN :snapno := STATSPACK.SNAP; END; / PRINT snapno
- Automatiser la capture des snapshots :
Pré-requis : JOB_QUEUE_PROCESSES > 0
Il est important de ne pas se tromper de user à ce niveau-là :
CONNECT perfstat/perfstat @?/rdbms/admin/spauto.sql
Les paramètres de la tâche peuvent être modifiés par le package DBMS_JOB
(fréquence, exec immediate, arrêt).
L’utilisation de DBMS_JOB peut être remplacée par l’utilisation d’outils autres (cron, at…) en faisant appel à la routine « statspack.snap » dans le schéma de PERFSTAT.
- Changer le niveau de capture
0 Performances générales 5 Instructions SQL 6 Plans SQL et utilisation des plans SQL 7 Statistiques au niveau segment 10 Verrous internes parent et enfant
Le niveau de capture par défaut est 5.
Modifier le niveau de collecte pour UNE capture :
Paramètre de la routine statspack : i_snap_level
Exemple : EXECUTE statspack.snap (i_snap_level=>6);
Modifier les VALEURS PAR DEFAUT de collecte (dont le niveau) :
Routine « modify_statspack_parameter » du package statspack.
Les niveaux de capture 5 et + collectent des stats sur SQL qui dépassent l’UN des seuils suivants :
Nb exec 100 Nb disk reads 1,000 Nb parse calls 1,000 Nb buffers gets 10,000 Sharable mem / statement 1Mo Version count 20
Le niveau 6 capture les plans d’exec.
Le niveau 7 capture les stats sur les segments qui dépassent l’UN des seuils suivants :
Logical reads 10,000 Physical reads 1,000 Buffer busy waits 100 Row lock waits 100 ITL waits 100 Global cache consistent reads blocks served 1,000 Global cache current blocks served 1,000
- Arrêter la capture automatique
connect perfstat / xxxxx SELECT job FROM dba_jobs WHERE lower(what) like ‘%statspack.snap%’; execute dbms_job.remove(<indiquer le numéro du job obtenu>);
6 – Sauvegarder les clichés par Datapump Export
expdp perfstat/perfstat parfile=spuexpdp.par
Contenu de spuexpdp.par :
directory=data_pump_dir dumpfile=spuexpdp.dmp logfile=spuexpdp.log schemas=PERFSTAT flashback_time=systimestamp estimate_only=n
7 – Recharger une sauvegarde par Datapump Import
Pour importer il faut que le user existe avant :
Connect as sysdba ?/rdbms/admin/@spcuser.sql
Import
impdp system/DBC112 parfile=spuimpdp.par
contenu de spuimpdp.par :
directory=data_pump_dir dumpfile=spuexpdp.dmp logfile=spuimpdp.log schemas=PERFSTAT
8 – Purger
Cela peut se faire de deux manières :
- Suppression de certains clichés
i_extended_purge =>TRUE nettoie les tables d’ID SQL (attention, plus long)
CONNECT perfstat/perfstat
VARIABLE num_snaps NUMBER
BEGIN
:num_snaps := statspack.purge
( i_begin_snap=>1,
i_end_snap=>2,
i_extended_purge=>TRUE);
END;
/
PRINT num_snaps
- Vidage total des tables
CONNECT prfstat/perfstat @?/rdbms/admin/sptrunc.sql
ATTENTION ! Deux des tables ne peuvent être tronquées en raison des nombreuses FK, elles font donc l’objet d’un DELETE mais s’il y a beaucoup de snapshots cela risque de faire « exploser » les RBS.
Voici donc une modification apportée au script sptrunc.sql :
CONNECT prfstat/perfstat
--delete from STATS$SNAPSHOT;
--delete from STATS$DATABASE_INSTANCE;
SET ECHO ON
SELECT count(*) FROM STATS$SNAPSHOT;
SELECT count(*) FROM STATS$DATABASE_INSTANCE;
SET ECHO OFF
==============================================
DECLARE
nb_lig NUMBER(8);
BEGIN
SELECT count(*) INTO nb_lig FROM STATS$SNAPSHOT;
FOR i IN 1..ROUND(nb_lig/1000)+1
LOOP
DELETE FROM STATS$SNAPSHOT WHERE ROWNUM < 1000;
COMMIT;
END LOOP;
SELECT count(*) INTO nb_lig FROM STATS$DATABASE_INSTANCE;
FOR i IN 1..ROUND(nb_lig/1000)+1
LOOP
DELETE FROM STATS$DATABASE_INSTANCE WHERE ROWNUM < 1000;
COMMIT;
END LOOP;
END;
/
==============================================
SET ECHO ON
SELECT count(*) FROM STATS$SNAPSHOT;
SELECT count(*) FROM STATS$DATABASE_INSTANCE;
SET ECHO OFF
9- Obtenir un rapport Statspack
- Lancer le rapport
CONNECT perfstat/perfstat @?/rdbms/admin/spreport.sql
- Abbréviations utilisées
CONT Contention liée à des blocs CPU Consommation de temps CPU ENQ Mise en file d'attente LC Cache "library" LAT Contention liée à des verrous internes IO Consommation d'E/S MEM Consommation de mémoire PGAM Consommation de mémoire PGA RBS Segment d'annulation UNDO Annulation automatique SP Zone de mémoire partagée
- Changer la configuration du rapport
(top N à prendre en compte : nombre d’ordres SQL, de segments, …)
CONNECT perfstat/perfstat @?/rdbms/admin/sprepcon.sql
- Obtenir un rapport sur les différences entre 2 clichés
CONNECT perfstat/perfstat @?/rdbms/admin/sprepins.sql
10- Interpréter le rapport
Faites appel à Easyteam…ou bien voici, pour vous aider, un rappel de la hiérarchie du Time Model sans laquelle on ne peut interpréter correctement les statistiques.
1 - DB Time 2 - DB CPU 2 - Connection management call elapsed time 2 - Sequence load elapsed time 2 - SQL execute elapsed time 3 - Repeated bind elapsed time 2 - Parse time elapsed 3 - Hard parse elapsed time 4 -Hard parse (sharing criteria) elapsed time 5 -Hard parse (bind mismatch) elapsed time 3 -Failed parse elapse time 4 -Failed parse (out of shared memory) elapsed time 2 -PL/SQL execution elapsed time 2 -Inbound PL/SQL RPC elapsed time 2 -Java execution elapsed time 1 -Background elapsed time 2 -Background CPU time3 -RMAN CPU time (backup / restore)
