Logo Oracle Deutschland   DBA Community  -  August 2011
Indizes in der Datenbank: Monitoring und Komprimierung (Index Key Compression)
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Welche Indizes mit welchen Eigenschaften stehen den Applikationen in der Datenbank zur Verfügung? Sind die Indizes für den Optimizer überhaupt nutzbar? Wieviel Speicherplatz wird von den Indizes alloziert? Werden die Indizes überhaupt verwendet? Müssen Indizes reorganisiert werden? Diese und weitere Fragen stellen sich automatisch, falls man sich mit dem Tuning von Datenbankapplikationen oder der Speicherplatzoptimierung befasst. Der folgende Tipp soll dabei helfen, diese Fragen mithilfe von Data Dictionary Views zu beantworten. Darüberhinaus werden die beiden Techniken Index Monitoring und Index Key Compression erläutert.

Dictionary Views für Indizes
Zur Beantwortung dieser Fragen stehen eine Reihe von speziellen Data Dictionary Views zur Verfügung. Um die Nutzung zu erleichtern, ist im folgenden Abschnitt eine Auswahl der wichtigsten Views mit kurzer Beschreibung getroffen worden:

  • DBA_INDEXES: gibt allgemeine Informationen (auch Statistiken) über alle verfügbaren Indizes
  • DBA_IND_COLUMNS: beschreibt die zughörigen Spalten der Indizes von allen Tabellen und Clusters
  • DBA_IND_PARTITIONS: gibt für jede Partition die Partition-Level Information mit den entsprechenden Statistiken aus
  • DBA_IND_SUBPARTITIONS: siehe DBA_IND_PARTITIONS für Subpartitionen
  • DBA_IND_STATISTICS: stellt die Optimizer-Statistiken für alle Indizes zur Verfügung
  • DBA_IND_PENDING_STATISTICS: gibt die "pending" Statistik-Informationen für alle Tabellen, Partitionen und Subpartitionen an
  • DBA_IND_EXPRESSIONS: stellt Informationen über die verwendeten Function based Indizes zur Verfügung
  • INDEX_STATS: speichert das aktuelle Ergebnis aus der letzten ANALYZE INDEX ... VALIDATE STRUCTURE Analyse (siehe unten)
  • Views, die mit dem DBA_INDEXTYPE Präfix beginnen, stehen für die speziellen Domain-Indizes zur Verfügung
Ein guter Startpunkt liefert in allen Fällen die View DBA_INDEXES. Fast jedes neue Datenbank-Release und damit verbunden jedes neue Index-Feature führt zu einer Erweiterung dieser View. Zum Beispiel gibt die Spalte SEGMENT_CREATED den Hinweis darauf, ob das Index-Segment schon angelegt worden ist; die Spalte VISIBILITY gibt an, ob der Index vom Optimizer verwendet werden kann. Die Eigenschaft COMPRESSION weist darauf hin, ob der Index in komprimierter Form gespeichert ist (siehe Abschnitt unten).

Folgende Abfrage zeigt ein Beispiel für die Nutzung von DBA_INDEXES: 
SELECT index_name, segement_created, table_name, compression, prefix_length, last_analyzed, visibility  
FROM user_indexes AND owner='US' ORDER BY index_name;
INDEX_NAME         SEG TABLE_NAME         COMPRESS PREFIX_LENGTH LAST_ANAL VISBILITY
------------------ --- ------------------ -------- ------------- --------- ---------
AUX_TEST1          NO  TEST1_AUX          DISABLED               22-FEB-11 VISIBLE

BASIC_LOB_KAT_ORT  YES BASIC_LOB          ENABLED              2 26-JUL-11 VISIBLE

BASIC_LOB_QUE      YES BASIC_LOB          DISABLED               06-JUN-11 INVISIBLE
...
In den folgenden Abschnitten wollen wir uns auf die Themen Index Monitoring und Index Key Compression konzentrieren, da diese Techniken wenig bekannt und teilweise unterschätzt werden.

Index Monitoring
Häufig stellt sich die Frage, ob ein Index überhaupt von den Abfragen genutzt wird - speziell, wenn es darum geht, Speicher für überflüssige Indizes freizugeben. Eine Möglichkeit, dies herauszufinden, besteht darin, Ausführungspläne zu generieren und zu analysieren. Möchte man allerdings eine generelle Aussage treffen ohne eine Statement-Analyse durchzuführen, ist die Technik "Monitoring Index Usage" hilfreich.

Hinweis: Monitoring Index Usage ist in jeder Datenbank Edition enthalten.

Um das Monitoring zu starten, muss bei jedem Index, der überwacht werden soll, das folgende ALTER INDEX Kommando ausgeführt werden. 
ALTER INDEX basic_lob_kat_ort MONITORING USAGE;

SELECT index_name, monitoring, used, start_monitoring, end_monitoring 
FROM v$object_usage;

INDEX_NAME         MON USE START_MONITORING    END_MONITORING
------------------ --- --- ------------------- -------------------
BASIC_LOB_KAT_ORT  YES NO  08/03/2011 14:55:31
Die View V$OBJECT_USAGE gibt Aufschluss darüber, ob der Index in der entsprechenden Zeitperiode - Startpunkt ist START_MONITORING, Endpunkt ist END_MONITORING - genutzt worden ist. Jedes Mal, wenn die Klausel MONITORING USAGE für einen Index angegeben wird, wird die Information in der View V$OBJECT_USAGE zurückgesetzt. Die folgende Abfrage zeigt, dass der Index mittlerweile genutzt worden ist. 
SELECT index_name, monitoring, used, start_monitoring, end_monitoring FROM v$object_usage;

INDEX_NAME         MON USE START_MONITORING    END_MONITORING
------------------ --- --- ------------------- -------------------
BASIC_LOB_KAT_ORT  YES YES 08/03/2011 14:55:31
Das Ausschalten erfolgt mit der Syntax-Klausel NOMONITORING USAGE.

Index-Komprimierung - Index Key Compression
Komprimierung im Index findet seit jeher in den sogenannten Bitmap Indizes statt, die speziell in Warehouse-Anwendungen bei Spalten mit geringer Kardinalität zum Einsatz kommen. Bitmap Indizes speichern eine Bitmap für jeden Index-Schlüssel statt einer Liste von ROWIDS. Jedes Bit in dieser Bitmap adressiert eine ROWID und somit eine Zeile in der Tabelle. Bitmap Indizes benötigen keine Komprimierungsalgorithmen, da diese Art von Indizes einen sehr geringen Speicherbedarf hat. Wegen des speziellen Locking-Verhaltens bietet die Verwendung von Bitmap Indizes besonders bei lesenden Zugriffen Vorteile.

Weniger bekannt ist die Tatsache, dass auch "normale" B*Tree Indizes komprimiert werden können. Im Gegensatz zum Bitmap Index ist kein spezielles Locking erforderlich, so dass diese ohne Einschränkung im OLTP Umfeld für unterschiedliche Anwendungen so z.B. auch im ERP-Bereich eingesetzt werden können. Die sogenannte Index Key Compression ist bereits seit Oracle 9i verfügbar und steht für B*tree Indizes und IOTs (Index Organized Table) zur Verfügung.

Das Prinzip der Index Key Compression beruht dabei auf der Eliminierung von sich wiederholenden Schlüssel-Werten (auch Präfix genannt) eines nonunique single column- oder eines unique multicolumn- Index. Zusammengesetzte Schlüssel (unique multicolumn) werden dabei in einen Präfix- und einen Suffix-Anteil unterteilt, wobei der Suffix-Anteil den eindeutigen Teil des Index-Schlüssels repräsentiert. Wenn sich Schlüsselwerte im Präfix-Teil des Index wiederholen, so werden diese Werte nur einmal gespeichert und vom Suffix referenziert. Bei nonunique single column Schlüssel wird die ROWID Information genutzt, um den Schlüssel eindeutig zu machen. Der Präfix-Anteil wird also durch die sich wiederholenden Werte repräsentiert; der verbleibende Anteil, die ROWIDS, stellen dann den Suffix-Anteil dar. Präfix- und Suffix- Werte befinden sich grundsätzlich im gleichen Block. Diese Speicherung kann zu einer starken Reduzierung der Index Leaf Pages und damit der Anzahl der I/O Operationen bei einem Indexzugriff führen.

Die Komprimierung wird beim Erzeugen des Index (CREATE INDEX) oder mit einem ALTER INDEX REBUILD-Kommando eingestellt. Bei IOTs wird das CREATE TABLE bzw. das ALTER TABLE MOVE Kommando verwendet. 
CREATE INDEX  ON t1(col1, col2, col3, col4) COMPRESS zahl;
-- CREATE INDEX i_t1 ON t1 (col1, col2, col3, col4) COMPRESS 1;
ALTER INDEX  REBUILD [ONLINE] COMPRESS zahl;
Die Klausel COMPRESS zahl gibt dabei die Anzahl der Präfixspalten an. Die Default Präfixlänge für unique Indizes ist die Anzahl der Spalten minus 1, für nonunique Indizes beträgt dieser Wert die Anzahl der Spalten.
Hinweis: Das Feature ist nutzbar ohne zusätzliche Lizenzierung der Advanced Compression Option.

Index Komprimierung lässt sich über die Standard Views wie USER_INDEXES monitoren. 
SELECT index_name, owner, compression, prefix_length
FROM dba_indexes 
WHERE PREFIX_LENGTH is not null AND owner='US';

INDEX_NAME                     OWNER      COMPRESS PREFIX_LENGTH
------------------------------ ---------- -------- -------------
DR$TEXT_IDX$X                  US         ENABLED              2
DR$TESTIDX$X                   US         ENABLED              2
DR$MYTABLE_OTXML$X             US         ENABLED              2
DR$FILTER_TEST_IDX$X           US         ENABLED              2
DR$IDX_KOMMENTARE$X            US         ENABLED              2
CT_COMP                        US         ENABLED              1
Wie findet man nun die optimale Komprimierung und Präfixl$auml;nge? Zur Illustration werden folgende Index-Leaf-Einträge mit den zugehörigen Rowids betrachtet:

ArztZahnarztAAAPvCAAFAAAAFaAAa
RestaurantItalienischAAAPvCAAFAAAAFaAAa
ArztInternistAAAPvCAAFAAAAFaAAl
RestaurantBayerischAAAPvCAAFAAAAFaAAm
RestaurantIndischAAAPvCAAFAAAAFaAAq

Der Index wird nun mit der Klausel COMPRESS 1 gespeichert. Die Schlüsselwerte Restaurant (hier P 0) und Arzt (hier P 1) werden im Leaf-Block genau einmal abgespeichert. Folgende Tabelle zeigt das Ergebnis dieser Speicherung in einer schematische Darstellung.
P 0Restaurant
P 1Arzt

P 0ItalienischAAAPvCAAFAAAAFaAAa
P 0BayerischAAAPvCAAFAAAAFaAAm
P 0IndischAAAPvCAAFAAAAFaAAq
P 1ZahnarztAAAPvCAAFAAAAFaAAa
P 1InternistAAAPvCAAFAAAAFaAAl

Die Güte der Komprimierungsrate kann stark variieren und ist z.B. abhängig von der richtigen Anzahl der komprimierten Spalten, den Werteausprägungen und der Anordnung der Spalten im Index. Falls das Umsortieren der Spalten im Index möglich ist, kann dies zu höheren Komprimierungsraten führen. Um zu beurteilen, ob und welche Indexkomprimierung für die entsprechenden Indexkandidaten sinnvoll ist, kann das Kommando ANALYZE INDEX-Kommando hilfreich sein. 
ANALYZE INDEX basic_lob_kat_ort VALIDATE STRUCTURE;
Bei der Ausführung wird der Index analysiert und das Ergebnis in der dynamischen Tabelle INDEX_STATS eingetragen. Die Spalte OPT_CMPR_COUNT gibt die optimale Key Compression Länge an, OPT_CMPR_PCTSAVE die Speichereinsparung in Prozent. Die folgende Abfrage zeigt das Ergebnis aus der INDEX_STATS-Tabelle nach Anwendung des ANALYZE INDEX Kommandos - angewendet auf einen unkomprimierten Index. 
SELECT name, blocks, br_blks,lf_blks, opt_cmpr_pctsave, opt_cmpr_count 
FROM index_stats;

NAME                   BLOCKS    BR_BLKS    LF_BLKS OPT_CMPR_PCTSAVE OPT_CMPR_COUNT
------------------ ---------- ---------- ---------- ---------------- --------------
BASIC_LOB_KAT_ORT         768          3        643               46              2
Die Komprimierung des Index mit COMPRESS 2 würde eine Einsparung von 46 Prozent des Speicherplatzes bewirken. In einem zweiten Test wird der Index mit COMPRESS 2 aufgebaut. Das Ergebnis in der Tabelle INDEX_STATS zeigt nun, dass es kein weiteres Einsparungspotential gibt. Die Blockanzahl hat sich - wie zu erwarten war - fast halbiert. 
SELECT name, blocks, br_blks,lf_blks, opt_cmpr_pctsave, opt_cmpr_count 
FROM index_stats;

NAME                   BLOCKS    BR_BLKS    LF_BLKS OPT_CMPR_PCTSAVE OPT_CMPR_COUNT
------------------ ---------- ---------- ---------- ---------------- --------------
BASIC_LOB_KAT_ORT         384          1        345                0              2
Die Tabelle INDEX_STATS ist dynamisch und speichert nur den letzten Eintrag des ANALYZE INDEX Kommandos. Um eine Historie zu speichern, sollte man eine Hilfstabelle anlegen und diese mit den entsprechenden Ergebnissen füllen. Ein Nachteil dieser Methode ist, dass das ANALYZE INDEX- Kommando weder ONLINE noch PARALLEL durchführbar ist.

Wichtiger Hinweis: Da bei der Ausführung ein DML Lock erfolgt, können DML-Operationen besonders bei der Verwendung von grossen Indizes stark beeinträchtigt werden.
Ein alternatives Skript zur Indexanalyse liefert die MOS Note 989186.1 (siehen unten). Allerdings wird hier keine Analyse über die optimale Key Compression Länge durchgeführt.

Weitere Informationen
Folgende Tipps, Links, MOS Notes können hilfreich sein: Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite