Dba index schema fragmentation report

Fiche express
Type	Script SQL*Plus de diagnostic (batch)
Domaine	Administration (index)
Voir aussi	Dba index fragmentation · Dba index stats

Dba index schema fragmentation report automatise Dba index fragmentation sur tous les index d'un schéma (ou d'un ensemble de schémas via un motif %), et ne remonte que les index candidats à un rebuild.

Un index est considéré comme candidat au rebuild si :

• les entrées supprimées représentent 20% ou plus des entrées courantes, ou
• la profondeur de l'index (blevel) dépasse 4 niveaux.

Un index est signalé comme candidat possible à une conversion en index bitmap si sa distinctiveness (proportion de valeurs distinctes) dépasse 99% — au-delà, un index B-tree classique apporte peu comparé à un bitmap (à réserver toutefois aux colonnes à faible cardinalité et faible volume de DML concurrent, le cas d'usage typique du bitmap reste l'inverse : peu de valeurs distinctes).

Script

ACCEPT schema CHAR PROMPT 'Schema name (% allowed) : '

SPOOL index_schema_fragmentation_report_&schema..lst

SET SERVEROUTPUT ON
SET ECHO         OFF
SET FEEDBACK     6
SET HEADING      ON
SET LINESIZE     180
SET PAGESIZE     50000
SET VERIFY       OFF

DECLARE
    c_name        INTEGER;
    ignore        INTEGER;
    height        index_stats.height%TYPE        := 0;
    lf_rows       index_stats.lf_rows%TYPE        := 0;
    del_lf_rows   index_stats.del_lf_rows%TYPE     := 0;
    distinct_keys index_stats.distinct_keys%TYPE   := 0;

    CURSOR c_indx IS
        SELECT owner, table_name, index_name
        FROM   dba_indexes
        WHERE      owner LIKE UPPER('&&schema')
               AND owner NOT IN ('SYS', 'SYSTEM');
BEGIN
    dbms_output.enable(1000000);
    dbms_output.put_line('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
    dbms_output.put_line('--------------- --------------------------------------- ----------------- ------ ---------------');

    c_name := DBMS_SQL.OPEN_CURSOR;

    FOR r_indx IN c_indx LOOP
        DBMS_SQL.PARSE(c_name, 'ANALYZE INDEX ' || r_indx.owner || '.' || r_indx.index_name || ' VALIDATE STRUCTURE', DBMS_SQL.NATIVE);
        ignore := DBMS_SQL.EXECUTE(c_name);

        SELECT
              height
            , DECODE(lf_rows, 0, 1, lf_rows)
            , del_lf_rows
            , DECODE(distinct_keys, 0, 1, distinct_keys)
        INTO
              height
            , lf_rows
            , del_lf_rows
            , distinct_keys
        FROM index_stats;

        IF (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) THEN
            dbms_output.put_line(
                   RPAD(r_indx.owner, 16, ' ')
                || RPAD(r_indx.index_name, 40, ' ')
                || LPAD(ROUND((del_lf_rows / lf_rows) * 100, 3), 17, ' ')
                || LPAD(height - 1, 7, ' ')
                || LPAD(ROUND((lf_rows - distinct_keys) * 100 / lf_rows, 3), 16, ' ')
            );
        END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(c_name);
END;
/

SPOOL OFF
PROMPT Report written to index_schema_fragmentation_report_&schema..lst

Colonnes / sortie

• Owner / Index Name — identification de l'index.
• % Deleted Entries — même métrique que Dba index fragmentation (ratio d'entrées supprimées).
• Blevel — profondeur de l'arbre B-tree moins 1 (la vue index_stats.height compte à partir de 1 ; blevel dans dba_indexes compte à partir de 0, d'où le height - 1 dans le script pour aligner les deux conventions).
• Distinctiveness — proportion de clés distinctes parmi les entrées de l'index.

Le rapport est à la fois affiché via DBMS_OUTPUT et spoolé dans un fichier .lst nommé d'après le schéma interrogé. Comme pour Dba index fragmentation, INDEX_STATS n'ayant qu'une ligne à la fois, le script boucle et relit la vue à chaque itération — sur un schéma avec beaucoup d'index, prévoir un temps d'exécution proportionnel (chaque ANALYZE INDEX VALIDATE STRUCTURE parcourt l'index concerné).

Voir aussi

• Dba index fragmentation — la même analyse, index par index, à la demande
• Dba index stats — statistiques détaillées d'un index unique