Fragmentierung feststellen mit INDEX_STATS

Wie fragmentiert ist mein Text Index? Wann soll ich den Index optimieren? Dies sind wichtige Fragestellungen, um eine optimale Zugriffsperformance gewährleisten zu können. Ein nicht optimaler Index zeigt sich in der Fragmentierung der $I-Tabelle z.B. bewirkt durch zu kleine SYNC Intervalle bzw. durch vorhandenen Garbage entstanden durch DELETE bzw. UPDATE Operationen.

Wie kann ich nun den Grad der Fragmentierung monitoren? Das Package CTX_REPORT liefert mit der Prozedur INDEX_STATS eine einfache Möglichlichkeit. Folgender Aufruf demonstriert die Nutzung. Die Hilfstabelle REPORT ist dabei notwendig, um die Inhalte zu speichern.

DROP TABLE ausgabe;
CREATE TABLE ausgabe (resultat CLOB);
 
  declare
    ergebnis clob := null;
  begin
    ctx_report.index_stats(index_name=>'IDX_TEXT',report=>ergebnis,stat_type=>null);
    insert into ausgabe values (ergebnis);
    commit;
    dbms_lob.freetemporary(ergebnis);
  end;
  /
 
set long 32000
set head off
set pagesize 10000
SELECT * FROM ausgabe; 
 ....
-- ein Ausschnitt aus dem Ergebnis
---------------------------------------------------------------------------
                         FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                                398

$I rows:                                                              105
estimated $I rows if optimal:                                         105
estimated row fragmentation:                                          0 %

garbage docids:                                                         0
estimated garbage size:                                                 0

most fragmented tokens:
  WIRTSCHAFT (0:TEXT)                                                 0 %
  WIRD (0:TEXT)                                                       0 %
  WI

Das Ergebnis zeigt, dass weder Garbage noch Fragmentierung des Index vorhanden ist.
Da die Durchführung dieser Prozedur bei grossen Indizes oder Partitionen sehr lange dauern kann, kann es sinnvoll sein, Logging einzuschalten. So ist es möglich den Fortschritt (d.h. die ge-scannten Zeilen der $I Tabelle) der jeweiligen Operation monitoren zu können.
Im nächsten Beispiel wird das ganze Prozedere mit zusätzlichem Logging an einem größeren Index demonstriert. Paralleles Monitoren der LOGGING-Tabelle würde den Stand der gescannten Zeilen anzeigen.

DROP TABLE ausgabe;
CREATE TABLE ausgabe (resultat CLOB);
  declare
    ergebnis clob := null;
  begin
        ctx_output.start_log('index_statistik'); 
    ctx_report.index_stats(index_name=>'TEXT_IND',report=>ergebnis,stat_type=>null);
    insert into ausgabe values (ergebnis);
        ctx_output.end_log; 
    commit;
    dbms_lob.freetemporary(ergebnis);
  end;
  /

Nachdem die Prozedur INDEX_STATS erfolgreich durchgeführt ist, kann man wie eben die ERGEBNIS Tabelle monitoren mit:

spool fragment.lst
set long 32000
set head off
set pagesize 10000
SELECT * FROM ausgabe;
... 
spool off

Im Abschnitt "FRAGMENTATION STATISTICS" ist nun folgende Information zu finden.

---------------------------------------------------------------------------
                         FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                            105,598,019 (100.71 MB)

$I rows:                                                        2,325,672
estimated $I rows if optimal:                                     538,712
estimated row fragmentation:                                         77 %

garbage docids:                                                         0
estimated garbage size:                                                 0

most fragmented tokens:
  126 (0:TEXT)                                                       96 %
  124 (0:TEXT)                                                       96 %
  12