Cet article décrit comment simuler et analyser la fragmentation des segments dans Oracle en utilisant des tables de test et des outils intégrés.

Création d'une table de test

-- Étape 1 : Définir une table pour les données de test
CREATE TABLE STAFF_DEMO (
    STAFF_ID      NUMBER PRIMARY KEY,
    PRENOM        VARCHAR2(45),
    NOM           VARCHAR2(45),
    COURRIEL      VARCHAR2(90),
    DATE_EMBAUCHE DATE,
    SERVICE_ID    NUMBER,
    SALAIRE       NUMBER
) TABLESPACE USERS; -- Utiliser un tablespace approprié

Insertion de données et simulation de fragmentation

-- Insérer des enregistrements initiaux pour remplir la table
DECLARE
    v_compteur NUMBER := 1;
BEGIN
    WHILE v_compteur <= 8000 LOOP
        INSERT INTO STAFF_DEMO VALUES (
            v_compteur,
            'Prenom_' || v_compteur,
            'Nom_' || v_compteur,
            'email' || v_compteur || '@entreprise.fr',
            SYSDATE - MOD(v_compteur, 2920),
            MOD(v_compteur, 8) + 1,
            2500 + MOD(v_compteur, 18000)
        );
        v_compteur := v_compteur + 1;
    END LOOP;
    COMMIT;
END;
/

-- Supprimer des données pour créer des espaces libres non contigus
DELETE FROM STAFF_DEMO WHERE MOD(STAFF_ID, 3) = 0; -- Environ 33% des données
COMMIT;

-- Insérer de nouveaux enregistrements qui ne remplissent pas tous les espaces
DECLARE
    v_indice NUMBER;
BEGIN
    FOR v_indice IN 8001..9500 LOOP
        INSERT INTO STAFF_DEMO VALUES (
            v_indice,
            'Nouveau_Prenom_' || v_indice,
            'Nouveau_Nom_' || v_indice,
            'nouveau.email' || v_indice || '@entreprise.fr',
            SYSDATE,
            MOD(v_indice, 6) + 1,
            4000 + MOD(v_indice, 12000)
        );
    END LOOP;
    COMMIT;
END;
/

-- Créer un index sur la colonne NOM pour une analyse ultérieure
CREATE INDEX IDX_STAFF_NOM ON STAFF_DEMO (NOM) TABLESPACE USERS;

Utilisation du Segment Advisor pour analyser la fragmentation

-- Configurer l'affichage
SET LINESIZE 300
COL ADVISOR_NAME FORMAT A40
COL TASK_NAME FORMAT A40

-- Lister les tâches existantes pour référence
SELECT task_id, task_name, advisor_name, created, status
FROM user_advisor_tasks;

-- Nettoyer une tâche précédente si elle existe
BEGIN
    DBMS_ADVISOR.DELETE_TASK('analyse_fragmentation_conseil');
    DBMS_OUTPUT.PUT_LINE('Tâche supprimée.');
END;
/
COMMIT;

-- Créer et exécuter une tâche d'analyse de segment manuellement
SET SERVEROUTPUT ON
DECLARE
    v_id_tache NUMBER;
    v_id_objet NUMBER;
    v_nom_tache VARCHAR2(100) := 'analyse_fragmentation_conseil';
    v_desc_tache VARCHAR2(500) := 'Exécution manuelle du Segment Advisor';
BEGIN
    DBMS_ADVISOR.CREATE_TASK(
        advisor_name => 'Segment Advisor',
        task_id => v_id_tache,
        task_name => v_nom_tache,
        task_desc => v_desc_tache
    );

    DBMS_ADVISOR.CREATE_OBJECT(
        task_name => v_nom_tache,
        object_type => 'TABLE',
        attr1 => USER,
        attr2 => 'STAFF_DEMO',
        attr3 => NULL,
        attr4 => NULL,
        attr5 => NULL,
        object_id => v_id_objet
    );

    DBMS_ADVISOR.SET_TASK_PARAMETER(
        task_name => v_nom_tache,
        parameter => 'recommend_all',
        value => 'TRUE'
    );

    DBMS_ADVISOR.EXECUTE_TASK(v_nom_tache);
END;
/

-- Récupérer les résultats de l'analyse
SELECT
    'Nom de tâche     :' || f.task_name || CHR(13) || CHR(10) ||
    'Nom du segment   :' || o.attr2    || CHR(13) || CHR(10) ||
    'Type de segment  :' || o.type     || CHR(13) || CHR(10) ||
    'Nom de partition :' || o.attr3    || CHR(13) || CHR(10) ||
    'Message          :' || f.message  || CHR(13) || CHR(10) ||
    'Informations     :' || f.more_info AS RESULTATS_ADVISOR
FROM dba_advisor_findings f, dba_advisor_objects o
WHERE o.task_id = f.task_id
    AND o.object_id = f.object_id
    AND f.task_name = 'analyse_fragmentation_conseil'
ORDER BY f.task_name;

Analyse détaillée de la fragmentation des index

-- Valider la structure de l'index en ligne
ALTER INDEX IDX_STAFF_NOM VALIDATE STRUCTURE ONLINE;

-- Vérifier les statistiques de fragmentation de l'index
SELECT
    t.name AS nom_index,
    t.height AS hauteur,
    t.pct_used AS pourcentage_utilise,
    t.del_lf_rows_len AS longueur_lignes_supprimees,
    t.lf_rows_len AS longueur_totale_lignes,
    ROUND((t.del_lf_rows_len / NULLIF(t.lf_rows_len, 0)), 4) * 100 || '%' AS taux_fragmentation
FROM index_stats t
WHERE t.name = 'IDX_STAFF_NOM';

Évaluation de l'utilisation de l'espace des segments

-- Utiliser DBMS_SPACE pour analyser l'espace dans la table
SET SERVEROUTPUT ON
DECLARE
    v_blocs_non_formattes NUMBER;
    v_octets_non_formattes NUMBER;
    v_blocs_fs1 NUMBER;
    v_octets_fs1 NUMBER;
    v_blocs_fs2 NUMBER;
    v_octets_fs2 NUMBER;
    v_blocs_fs3 NUMBER;
    v_octets_fs3 NUMBER;
    v_blocs_fs4 NUMBER;
    v_octets_fs4 NUMBER;
    v_blocs_pleins NUMBER;
    v_octets_pleins NUMBER;
BEGIN
    DBMS_SPACE.SPACE_USAGE(
        segment_owner => USER,
        segment_name => 'STAFF_DEMO',
        segment_type => 'TABLE',
        unformatted_blocks => v_blocs_non_formattes,
        unformatted_bytes => v_octets_non_formattes,
        fs1_blocks => v_blocs_fs1,
        fs1_bytes => v_octets_fs1,
        fs2_blocks => v_blocs_fs2,
        fs2_bytes => v_octets_fs2,
        fs3_blocks => v_blocs_fs3,
        fs3_bytes => v_octets_fs3,
        fs4_blocks => v_blocs_fs4,
        fs4_bytes => v_octets_fs4,
        full_blocks => v_blocs_pleins,
        full_bytes => v_octets_pleins
    );

    DBMS_OUTPUT.PUT_LINE('Blocs non formattés : ' || v_blocs_non_formattes);
    DBMS_OUTPUT.PUT_LINE('Blocs FS1 (0-25% libres) : ' || v_blocs_fs1);
    DBMS_OUTPUT.PUT_LINE('Blocs FS2 (25-50% libres) : ' || v_blocs_fs2);
    DBMS_OUTPUT.PUT_LINE('Blocs FS3 (50-75% libres) : ' || v_blocs_fs3);
    DBMS_OUTPUT.PUT_LINE('Blocs FS4 (75-100% libres) : ' || v_blocs_fs4);
    DBMS_OUTPUT.PUT_LINE('Blocs pleins : ' || v_blocs_pleins);
END;
/