Donnerstag, 8. Januar 2009

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

Kommentare:

Roger Bruhin hat gesagt…

Ich habe soeben den Artikel über die Fragmentierung gelesen. Für mich ist die Sache aber noch nicht fertig. Was muss ich machen, damit mein Index wieder schneller wird, einfach neu erstellen oder gibt es da auch andere Möglichkeiten. Welche Werte sind gut, ab welchem Wert muss ich was unternehmen? Das würde mich sehr intressieren. Danke.

Gruss roger

Ulrike Schwinn hat gesagt…

Hallo Herr Bruhin,
sie sollten den Index bei hoher Fragementierung optimieren (siehe ctx_ddl.optimize_index)
Der Aufruf kann mit unterschiedlichen "Optionen" durchgeführt werden wie FAST, FULL, TOKEN usw). Schauen Sie dazu einfach mal ins Handbuch ( siehe Oracle® Text Reference CTX_DDL Package). Eine Richtzahl anzugeben ist schwer. Falls Sie viel DML haben und häufig synchronisieren werden Sie ohne regelmässige Optimierung auch schnell Fragmentierung sehen (siehe Vergleich von "estimated $I rows if optimal" und "$I rows"). Denken Sie an regelmässige Optimierung und behalten Sie die query-Performance im Auge. Manchmal reicht es ja aus einzelne Tokens oder Token Types zu optimieren.
Mit freundlichen Grüßen
Ulrike Schwinn

Anonym hat gesagt…

Vielen Dank für das schöne Beispiel, um den Grad der Fragmentierung zu ermitteln.
Eine Frage: ich habe beobachtet, dass durch zu starke Parallelisierung beim create index der Index stark fragmentiert.
Beispiel-Schema: 81 GB Daten, 7 GB Index
Create Index:
1000 MB x 6 Prozesse -> estimated row fragmentation: 39 % -> Zeit für den create index: 2h min
500 MB x 15 Prozesse -> estimated row fragmentation: 46 % -> Zeit für den create index: 1h 1min

Vorteil bei der Parallelisierung: der enorme Zeitgewinn.
Nachteil: starke Fragmentierung.

Frage: Gibt es diesbezüglich ein von Oracle empfohlenes Verfahren, um die zur Verfügung stehenenden CPU´s (8 Kerne) beim create index zu nutzen -
ohne jedoch eine zu starke Fragmentierung des Index?

Gruß und Danke
MS

Ulrike Schwinn hat gesagt…

Sie haben das richtig beobachtet: bei Erhöhung der Parallelisierung kann mehr Fragmentierung auftreten. Da hilft nur Erhöhung des verfügbaren Memories und natürlich ein anschliessendes OPTIMIZE_INDEX. Es gibt meines Wissens nur die Empfehlung Parallelisierung zu nutzen und das zur Verfügung stehende Memory zu erhöhen.

Beliebte Postings