LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon