Fabien Celaia

DBMS_STATS.GATHER_SYSTEM_STATS n'est pas conçu pour jouer les statistiques des tables des schémas SYS et SYSTEM mais pour renseigner l'optimiseur sur les performances réelles de votre système.

Par défaut, Oracle relève des métriques pour son optimiseur basé sur les coûts CPU au démarrage de l'instance, donc sans charge (noworkload).

Cela se fait, pour une collecte de 20 minutes à jouer en pleine charge, via l'ordre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

exec dbms_stats.gather_system_stats(gathering_mode => 'interval', interval => 30)

Avant d’exécuter cela, il convient de relever les valeurs actuelles via

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select pname param, pval1 valeur 
from sys.aux_stats$
where sname='SYSSTATS_MAIN' ;

Sur une base où il n'y a que les valeurs en mode noworkload, l'affichage ressemble à cela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
PARAM                              VALEUR
------------------------------ ----------
CPUSPEEDNW                     1273.890005 
IOSEEKTIM                              10 
IOTFRSPEED                           4096 
SREADTIM                                  
MREADTIM                                  
CPUSPEED                                  
MBRC                                      
MAXTHR                                    
SLAVETHR                                  

 9 lignes sélectionnées

Notez le CPUSPEEDNW (NW pour no workload), et le CPUSEED à NULL.

Il convient donc de jouer la transaction DBMS_STATS.GATHER_SYSTEM_STATS en mode pleine charge afin qu'il obtienne de meilleures informations.
Et il convient de le rejouer à chaque fois que les ressources de la machine contenant la base changent... vous aurez bien compris qu'en environnement virtuel où les ressources sont modifiées à la volée, cette paramétrisation revêt une importance toute particulière !

... et là, ô miracle, les valeurs réelles apparaissent... et elles peuvent sensiblement différer des valeurs NW.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
PARAM                              VALEUR
------------------------------ ----------
CPUSPEED                             1350 -- en millions, nombre d'opérations par seconde que le CPU est en mesure d'effectuer en pleine charge
CPUSPEEDNW                       1606.268 -- en millions, nombre d'opérations par seconde que le CPU est en mesure d'effectuer sans charge (qui correspond généralement à la cadence CPU) 
IOSEEKTIM                           5.927 -- temps de recherche E/S en ms
IOTFRSPEED                       36432.71 -- temps de transfert E/S en ms
MAXTHR                             450000 -- débit maximum des E/S
MBRC                                   10 -- nombre moyen de blocs lus lors d'une lecture multiblocs
MREADTIM                                3 -- vitesse en ms de lecture d'un multibloc
SLAVETHR                                  
SREADTIM                                4 -- vitesse en ms de lecture d'un seul bloc

Charger ces données n'est pas anodin ! Cela va fortement influencer le comportement de votre optimiseur... et, partant, un certain nombre de plans d'exécution...