Lob fragmentation user

De wiki.nexiat.fr
Aller à la navigation Aller à la recherche
Fiche express
Type Script SQL*Plus / PL*SQL de diagnostic
Rôle Détection de la fragmentation des segments LOB d'un schéma
Voir aussi Lob dump blob · Lob dump clob · Lob dump nclob

Lob fragmentation user compare, pour chaque colonne LOB du schéma courant, la taille réellement allouée au segment LOB (user_segments.bytes) à la taille effectivement utilisée par les données qu'il contient (somme des DBMS_LOB.GETLENGTH sur toutes les lignes), et en déduit un taux de fragmentation.

Un segment LOB, une fois créé, ne réduit jamais spontanément sa taille allouée : les DELETE et UPDATE répétés sur une colonne LOB libèrent l'espace applicatif mais pas l'espace disque, qui reste réservé pour de futures écritures. Avec le temps, un segment peut ainsi occuper beaucoup plus d'espace que ce qu'il contient réellement — jusqu'à plusieurs dizaines de gigaoctets d'écart dans les cas extrêmes.

Script

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
COLUMN current_user     NEW_VALUE current_user     NOPRINT
SELECT RPAD(instance_name, 17) current_instance, RPAD(USER, 13) current_user FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Rapport   : Fragmentation des LOB du schéma courant                    |
PROMPT | Instance  : &current_instance                                         |
PROMPT | User      : &current_user                                             |
PROMPT +------------------------------------------------------------------------+

SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 180 PAGESIZE 50000 SERVEROUTPUT ON TIMING OFF VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES

DECLARE
    v_actual_length               NUMBER;
    v_allocated_length            NUMBER;
    v_lob_fragmentation_pct       NUMBER;
    v_actual_length_char          VARCHAR2(50);
    v_allocated_length_char       VARCHAR2(50);
    v_statement                   VARCHAR2(2000);
    v_table_column_pad_length     CONSTANT NUMBER := 45;
    v_actual_length_pad_length    CONSTANT NUMBER := 20;
    v_allocated_length_pad_length CONSTANT NUMBER := 20;
    v_fragmentation_pad_length    CONSTANT NUMBER := 15;
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    DBMS_OUTPUT.PUT_LINE(
        RPAD('LOB COLUMN - [OWNER.TABLE.COLUMN]', v_table_column_pad_length)   || ' ' ||
        LPAD('ALLOCATED LOB LENGTH', v_allocated_length_pad_length)            || ' ' ||
        LPAD('ACTUAL LOB LENGTH', v_actual_length_pad_length)                  || ' ' ||
        LPAD('FRAGMENTATION', v_fragmentation_pad_length));
    DBMS_OUTPUT.PUT_LINE(
        RPAD('-', v_table_column_pad_length, '-')     || ' ' ||
        LPAD('-', v_allocated_length_pad_length, '-') || ' ' ||
        LPAD('-', v_actual_length_pad_length, '-')    || ' ' ||
        LPAD('-', v_fragmentation_pad_length, '-'));

    -- Tous les segments LOB du schéma courant
    FOR v_lob_segment IN (
        SELECT USER AS owner, l.table_name, l.column_name
        FROM   user_lobs l JOIN user_segments s
               USING (segment_name, tablespace_name)
        WHERE  l.column_name NOT LIKE '"%'
        ORDER BY 2, 3
    )
    LOOP
        DBMS_OUTPUT.PUT(RPAD(v_lob_segment.owner || '.' || v_lob_segment.table_name || '.' || v_lob_segment.column_name, v_table_column_pad_length));
        DBMS_OUTPUT.PUT(' ');

        -- Taille allouée au segment LOB
        v_statement :=
               'BEGIN '
            || 'SELECT TO_CHAR(a.bytes, ''999,999,999,999,999'') '
            || 'INTO :col_val2 '
            || 'FROM user_segments a JOIN user_lobs b USING (segment_name) '
            || 'WHERE b.table_name  = ''' || v_lob_segment.table_name  || ''' '
            || '  AND b.column_name = ''' || v_lob_segment.column_name || '''; '
            || 'END;';
        EXECUTE IMMEDIATE v_statement USING OUT v_allocated_length_char;
        v_allocated_length_char := REPLACE(v_allocated_length_char, ' ');
        v_allocated_length      := TO_NUMBER(REPLACE(v_allocated_length_char, ','));
        DBMS_OUTPUT.PUT(LPAD(v_allocated_length_char, v_allocated_length_pad_length));
        DBMS_OUTPUT.PUT(' ');

        BEGIN
            -- Taille réellement utilisée par les données
            v_statement :=
                   'BEGIN '
                || 'SELECT TO_CHAR(NVL(SUM(DBMS_LOB.GETLENGTH(' || v_lob_segment.column_name || ')), 0), ''999,999,999,999,999'') '
                || 'INTO :col_val1 '
                || 'FROM ' || v_lob_segment.owner || '.' || v_lob_segment.table_name || '; '
                || 'END;';
            EXECUTE IMMEDIATE v_statement USING OUT v_actual_length_char;
            v_actual_length_char := NVL(REPLACE(v_actual_length_char, ' '), '0');
            v_actual_length      := NVL(TO_NUMBER(REPLACE(v_actual_length_char, ',')), 0);
            DBMS_OUTPUT.PUT(LPAD(v_actual_length_char, v_actual_length_pad_length));
            DBMS_OUTPUT.PUT(' ');

            -- Taux de fragmentation
            IF v_actual_length = 0 THEN
                v_lob_fragmentation_pct := 0;
            ELSE
                v_lob_fragmentation_pct := ROUND((1 - (v_actual_length / v_allocated_length)) * 100, 2);
            END IF;
            DBMS_OUTPUT.PUT(LPAD(v_lob_fragmentation_pct || ' %', v_fragmentation_pad_length));
        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;

        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

Le script d'origine utilisait regexp_replace pour retirer espaces et virgules du format de nombre, sans forcer explicitement la conversion en NUMBER — v_allocated_length restait alimenté par une affectation implicite d'un VARCHAR2. Ici, un TO_NUMBER explicite est ajouté sur les deux variables numériques. Le calcul du taux de fragmentation a aussi été corrigé pour éviter une division par zéro sur les segments vides : quand v_actual_length vaut 0 (table vide), la fragmentation est simplement rapportée à 0% plutôt que de recycler la taille allouée dans le calcul.

Pour réduire l'espace alloué d'un segment LOB fragmenté sans le recréer : 

ALTER TABLE owner.table_name MODIFY LOB (colonne_lob) (SHRINK SPACE);

Cette opération peut être longue (de quelques minutes à plusieurs heures) selon le volume de données à réorganiser ; à planifier en dehors des fenêtres de forte activité.

Voir aussi

Récupérée de "https://wiki.nexiat.fr/index.php?title=Lob_fragmentation_user&oldid=2244"