News
DBA-Tipp: Frühjahrsputz im Index-Tablespace

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Während Indizes grundsätzlich wichtig für eine performante Ausführung von DML und Queries sind, haben sie gleichzeitig auch Nachteile. Unter anderem verursacht jeder Index durch den impliziten Pflegeaufwand Overhead bei DML-Operationen

  • belegt jeder Index wertvollen Platz in der Datenbank und im Backup
  • verbraucht die Berechnung von Indexstatistiken Zeit und verursacht Last in der Datenbank
  • Ziel sollte es daher sein, nicht mehr Indizes vorzuhalten, als für eine performante Abwicklung der typischen Workload erforderlich sind.

Im Rahmen von Performanceuntersuchungen und Healthchecks in Kundensystemen treffen wir häufig auf Indizes, die vom Grundsatz her nicht erforderlich sind. Zwei häufige Fälle - und Queries zum Auffinden entsprechender „Kandidaten“ - werden in diesem DBA-Tipp vorgestellt.

Fall 1 – vertauschte Spaltenreihenfolge

Fall 1 – vertauschte Spaltenreihenfolge

In Abbildung 1 sehen wir die Indizes NDX_NAME_OWN_OBJECTS und  NDX_OWN_NAME_OBJECTS, die die selben Spalten in unterschiedlicher Reihenfolge indizieren. Hintergrund einer derartigen Indizierung ist wahrscheinlich die falsche Annahme, damit sowohl für die Query

SELECT *
FROM scott_objects
WHERE object_name='EMP'
AND owner='SCOTT'

als auch für

SELECT *
FROM scott_objects
WHERE owner='SCOTT'
AND object_name='EMP'

jeweils auf einen Index zurückgreifen zu können. Tatsächlich reicht einer der beiden Indizes vollkommen aus. Der Optimizer tauscht bei Bedarf die beiden WHERE-Attribute aus, so dass er auch bei (vermeintlich) falscher Attributreihenfolge vom Index Gebrauch machen kann (Abbildung 2).

Abbildung 2

Den hier beschriebenen Fall einer Indizierung des selben Satzes von Spalten in unterschiedlicher Reihenfolge fände man über folgende Query heraus (hier eingeschränkt auf das Schema SCOTT):

WITH column_list AS
  ( SELECT DISTINCT index_owner, index_name,
    listagg(column_name,', ') within GROUP (
  ORDER BY column_name) over (partition BY index_name) cols
  FROM dba_ind_columns
  )
SELECT DISTINCT fst.index_owner,
  fst.index_name,
  snd.index_owner,
  snd.index_name,
  snd_cols.cols
FROM dba_ind_columns fst,
  dba_ind_columns snd,
  column_list fst_cols,
  column_list snd_cols
WHERE fst.table_owner  =snd.table_owner
AND fst.table_name     =snd.table_name
AND fst.index_owner    =fst_cols.index_owner
AND fst.index_name     =fst_cols.index_name
AND snd.index_owner    =snd_cols.index_owner
AND snd.index_name     =snd_cols.index_name
AND fst_cols.cols      =snd_cols.cols
AND fst.index_owner    ='SCOTT'
AND fst.index_name!    =snd.index_name

 

Fall 2 – Index als linksbündiges Spalten-Subset eines anderen Indexes

In der Regel sind auch Indizes unnötig, die ein linksbündiges Subset eines anderen Indexes sind.

Fall 2 – Index als linksbündiges Spalten-Subset eines anderen Indexes

In Abbildung 3 ist der Index NDX_OWN_NAME_OBJECTS ein linksbündiges Subset von NDX_OWN_NAME_STAT_OBJECTS, da er komplett und in identischer Reihenfolge seiner Spalten (beides muss erfüllt sein!) Bestandteil des Indexes  NDX_OWN_NAME_STAT_OBJECTS ist. Grundsätzlich würde der Index  NDX_OWN_NAME_OBJECTS zwar für die Query

SELECT *
FROM scott_objects
WHERE owner='SCOTT'
AND object_name='EMP'

gegenüber NDX_OWN_NAME_STAT_OBJECTS bevorzugt (Abbildung 4), da der Leseaufwand geringer ist (in  NDX_OWN_NAME_STAT_OBJECTS müssten mehrere Leaf-Blocks gelesen werden) ...

Abbildung 4

...allerdings könnte die selbe Query ebenso NDX_OWN_NAME_STAT_OBJECTS nutzen, auch wenn nur ein Teil der Attribute dieses Indexes in der Query angegeben ist (Abbildung 5):

Abbildung 5

Achten Sie aber bitte darauf, dass diese Regel nur für linksbündige Subsets gilt. Auf NDX_NAME_STAT_OBJECT z.B. kann nicht ohne weiteres zugunsten NDX_OWN_NAME_STAT_OBJECTS verzichtet werden. Letzterer stimmt mit ersterem nämlich erst ab der zweiten Spalte überein (Abbildung 6).

Abbildung 6

Das würde bei Abfragen ohne OWNER-Attribut in der WHERE-Klausel dazu führen, dass gar kein Index genutzt oder bestenfalls ein INDEX SKIP SCAN durchgeführt würde, der jedoch erheblich „teurer“ und langsamer als der INDEX RANGE SCAN auf dem ursprünglichen Index wäre (Abbildung 7 vs Abbildung 8).

Abbildung 7

Abbildung 8

Alternativ könnte man auch auf den „breiteren“ Index  NDX_OWN_NAME_STAT_OBJECTS zugunsten des „schmaleren“ NDX_OWN_NAME_OBJECTS verzichten, da der schmalere Index per se kleiner als der breitere ist. Hier sollte man allerdings drei nicht unwesentliche Dinge im Hinterkopf behalten:

  1. Sie können nicht auf Indizes verzichten, die unique- oder primary key Constraints durchsetzen.
  2. Der Verzicht auf eine Spalte, der im verworfenen Index noch enthalten war, erhöht die CPU-Last, da nach dem Indexzugriff jetzt noch ein Filtern in der Tabelle erforderlich wird (Abbildung 9 vs Abbildung 10)

    Abbildung 9

    Abbildung 10

     

  3. Queries, die bislang die angefragten Spalten schon im Index finden konnten, müssen nun neben dem Index auch noch auf die Tabelle zugreifen und werden folglich teurer und langsamer (Abbildung 11 vs Abbildung 12).

    Abbildung 11

    Abbildung 12

Indizes, die linksbündige Subsets anderer Indizes und damit Löschkandidaten im Sinne dieses DBA_Tipps sind, finden Sie mit folgender Query (ebenfalls eingeschränkt auf das Schema SCOTT):

WITH column_list AS
  ( SELECT DISTINCT index_owner,
    index_name,
    listagg(column_name,', ') within GROUP (
  ORDER BY column_position) over (partition BY index_name) cols
  FROM dba_ind_columns
  )
SELECT DISTINCT fst.index_owner superset_owner,
  fst.index_name superset_index,
  fst_cols.cols superset_columns,
  snd.index_owner subset_owner,
  snd.index_name subset_index,
  snd_cols.cols subset_columns
FROM dba_ind_columns fst,
  dba_ind_columns snd,
  column_list fst_cols,
  column_list snd_cols
WHERE fst.table_owner=snd.table_owner
AND fst.table_name   =snd.table_name
AND fst.index_owner  =fst_cols.index_owner
AND fst.index_name   =fst_cols.index_name
AND snd.index_owner  =snd_cols.index_owner
AND snd.index_name   =snd_cols.index_name
AND fst_cols.cols LIKE snd_cols.cols||',_%'
AND fst.index_owner ='SCOTT'
AND fst.index_name! =snd.index_name

Wahrscheinlich ist es Ihnen bereits in diversen Screenshots aufgefallen: Die bessere Alternative zum Löschen der überflüssigen Indizes ist in diesem Fall das Ändern des Indexstatus auf „invisible“. Unsichtbare Indizes werden vom Optimizer nicht in Betracht gezogen, aber im Hintergrund weiter aktuell gehalten. Sollte sich herausstellen, dass Sie mit einer Annahme „schwer daneben gelegen haben“ und einen Index wieder aktivieren müssen, dann muss er in diesem Fall nur „visible“ gesetzt werden und ist sofort wieder verfügbar. Ein gelöschter (oder unusable gesetzter) Index hingegen müsste  erst neu aufgebaut werden. Selbstverständlich sollten die invisible gesetzten Indexes nach angemessener Zeit letztendlich dann doch gelöscht werden, denn nur so bekommen Sie den ganz oben erwähnten Overhead tatsächlich auch los.

Fazit

Indizes haben konträre Auswirkungen auf eine Datenbank. Auf der einen Seite beschleunigen sie DML und Queries, auf der anderen Seite erhöhen Sie den Platzbedarf und DML-Overhead. Daher sollten nicht mehr Indizes als absolut nötig in der Datenbank vorhanden sein. Mit Hilfe der oben erwähnten Queries lassen sich Indizes identifizieren, die Duplikate oder Subsets anderer Indizes sind. Diese Duplikate können tendenziell aus der Datenbank entfernt werden.

Übrigens: Informationen zum Indexmonitoring finden Sie in unserem DBA-Tipp Index-Monitoring – Aufspüren und Droppen von ungenutzten Indizes in Datenbanken. Index Monitoring hilft Ihnen, Indizes aufzuspüren, die zwar existieren, aber tatsächlich gar nicht benutzt werden.