Collecte de statistiques système

La collecte des statistiques système est malheureusement très souvent oubliée par les administrateurs et pourtant, elle ne le devrait pas car l'optimiseur effectue un calcul des coûts plus adéquat lorsque celles-ci sont disponibles.

Les statistiques Système procure à l'optimiseur des informations près de la réalité sur la façon dont votre système se comporte réellement. Ces informations permettent à l'optimiseur de produire une meilleure déduction entre les temps estimés et réelles pour l'exécution d'une requête.

Les Statistiques Système contiennent les détails qui permettent à l'optimiseur de comparer les coûts entre les entrées / sorties (I/O) et ceux liés aux processeurs (CPU).

Lorsque vous commencer à utiliser les statistiques système, l'optimiseur devient plus sensible au moment de choisir entre une lecture séquentielle (table scan) et l'utilisation d'index, car le coût d'une lecture multiblocs pour un accès séquentiel comportera des informations sur le temps d'exécution. Par exemple, Oracle peut décider qu'une lecture séquentielle d'une table serait plus rapide en se basant uniquement sur la vitesse de lecture de blocs (simple et multi).

Différentes façons de collecte peuvent être effectuées. Personnellement, j'aime bien avoir le contrôle alors, j'opte pour la méthode manuelle.

-- Établir une connexion avec SYS
connect / as sysdba

-- Créer la table de collecte de statistiques
EXEC DBMS_STATS.DELETE_SYSTEM_STATS('stats','stats','SYS');
EXEC DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats','SYSAUX');

-- Démarrer la collecte en mode manuelle
EXEC DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'START', -
stattab => 'stats', -
statid => 'stats', statown => 'SYS');

-- Vérifier si la collecte est activée
column statid format a7
column c1 format a13
column c2 format a16
column c3 format a16
select STATID, C1, C2, C3,to_char(sysdate,'MM-DD-YYYY HH24:MI:SS')
from stats;

-- Arrêt de la collecte
-- ATTENTION: Avant d'arrêter, assurez-vous d'avoir laisser fonctionner la collecte
-- pendant une période significative
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS -
(gathering_mode=>'STOP', -
stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');

-- Prendre une copie des statistiques actuelles
EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','stats_bkp','SYSAUX');
EXEC DBMS_STATS.EXPORT_SYSTEM_STATS -
(stattab => 'stats_bkp', -
statid => 'stats_bkp', -
statown => 'SYS');
select * from sys.stats_bkp;

-- Activer les statistiques
EXEC DBMS_STATS.IMPORT_SYSTEM_STATS -
(stattab => 'stats', -
statid => 'stats', -
statown => 'SYS');

-- Afficher les nouvelles statistiques
select * from sys.aux_stats$;

Et, voilà, c'est assez simple n'est-ce pas !

Je vous conseille fortement d'utiliser les statistiques système. La mise en fonction de ces statistiques doivent être effectuée avec précaution. Pour minimiser les impacts et les surprises, effectuer des essais de performance sur un environnement dédié à des tests et comparer vos résultats.