Collecte des statistiques d'optimiseur Oracle avec DBMS_STATS
Appels DBMS_STATS courants pour collecter les statistiques de schéma, de base, du dictionnaire, des objets fixes et système, avec le vidage du shared pool.
Les statistiques de l'optimiseur décrivent le volume et la distribution des données des tables, index et colonnes. L'optimiseur basé sur les coûts s'appuie dessus pour choisir les plans d'exécution. Cet article regroupe les appels DBMS_STATS les plus courants pour les collecter.
Statistiques d'un schéma
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ( -
ownname => 'APP_SCHEMA', -
options => 'GATHER', -
estimate_percent => 100, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
cascade => TRUE, -
degree => 5 -
);
| Paramètre | Description |
|---|---|
ownname |
Propriétaire du schéma à analyser |
estimate_percent |
Pourcentage de lignes échantillonnées ; 100 = calcul complet |
method_opt |
Options des colonnes et des histogrammes ; SIZE AUTO laisse Oracle décider |
cascade |
Collecte aussi les statistiques des index |
degree |
Degré de parallélisme |
Pour une collecte avec histogrammes sur plusieurs schémas, on répète l'appel pour chaque propriétaire.
Statistiques de la base de données
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(cascade => TRUE);
END;
/
Statistiques du dictionnaire et des objets fixes
BEGIN
DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
Statistiques système
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');
-- Sur Exadata
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');
Collecte table par table
Cette boucle collecte les statistiques de toutes les tables dont le nom se termine par 300 dans un schéma donné :
SET SERVEROUTPUT ON
BEGIN
FOR cur IN (SELECT table_name FROM all_tables
WHERE owner = 'APP_SCHEMA' AND table_name LIKE '%300')
LOOP
DBMS_OUTPUT.PUT_LINE('Compute stats for APP_SCHEMA.' || cur.table_name);
DBMS_STATS.GATHER_TABLE_STATS('APP_SCHEMA', cur.table_name);
END LOOP;
END;
/
Vider le shared pool
Après un recalcul des statistiques, les curseurs déjà en mémoire peuvent continuer à utiliser les anciens plans. Pour forcer leur recompilation :
ALTER SYSTEM FLUSH SHARED_POOL;
Remarques
- Le vidage du shared pool supprime tous les curseurs en mémoire : à faire de préférence en période de maintenance.
- Après une mise à niveau, il est recommandé de recollecter les statistiques du dictionnaire et des tables.
- Pour le rôle de
METHOD_OPTet des histogrammes, voir le blog de l'optimiseur Oracle : https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work