News zu Oracle

DBA Tipp: Frühjahrsputz im Index-Tablespace

Bei unserem internen “Frühjahrsputz” sind wir auf einen Beitrag von Oracle Certified Master Thilo aus dem Jahr 2020 zu genau diesem Thema gestoßen. Konkret geht es hier um den Frühjahrsputz im Index-Tablespace und warum es sich lohnt, diesen ab und zu mal aufzu­räumen. Die Grundsätze und Queries treffen heute noch genauso zu wie damals, also Ärmel hochkrempeln und los geht’s!

Während Indizes grund­sätzlich wichtig für eine perfor­mante Ausführung von DML und Queries sind, haben sie gleich­zeitig auch Nachteile, denn:

  • Jeder Index verur­sacht durch den impli­ziten Pflegeaufwand Overhead bei DML-Operationen.
  • Jeder Index belegt wertvollen Platz in der Datenbank und im Backup.
  • Die Berechnung von Indexstatistiken verbraucht Zeit und verur­sacht Last in der Datenbank.


Ziel sollte es daher sein, nicht mehr Indizes vorzu­halten, als für eine perfor­mante Abwicklung der typischen Workload erfor­derlich sind.

Im Rahmen von Performanceuntersuchungen und Healthchecks unserer Kundensysteme treffen wir häufig auf Indizes, die vom Grundsatz her nicht erfor­derlich sind. Zwei häufige Fälle – und Queries zum Auffinden entspre­chender „Kandidaten“ – werden in diesem DBA Tipp vorgestellt.

Fall 1 – vertauschte Spaltenreihenfolge

Abbildung 1

In Abbildung 1 siehst du die Indizes NDX_NAME_OWN_OBJECTS und NDX_OWN_NAME_OBJECTS, welche dieselben Spalten in unter­schied­licher Reihenfolge indizieren. Hintergrund einer derar­tigen 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ück­greifen zu können. Tatsächlich reicht einer der beiden Indizes vollkommen aus. Der Optimizer tauscht bei Bedarf die beiden WHERE-Attribute aus, sodass er auch bei (vermeintlich) falscher Attributreihenfolge vom Index Gebrauch machen kann (Abbildung 2).

Abbildung 2

Den hier beschrie­benen Fall einer Indizierung desselben Satzes von Spalten in unter­schied­licher Reihenfolge findest du über folgende Query heraus (hier einge­schrä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 links­bün­diges Subset eines anderen Indexes sind. 
Abbildung 3

In Abbildung 3 ist der Index NDX_OWN_NAME_OBJECTS ein links­bün­diges Subset von NDX_OWN_NAME_STAT_OBJECTS, da er komplett und in identi­scher Reihenfolge seiner Spalten Bestandteil des Indexes NDX_OWN_NAME_STAT_OBJECTS ist (beides muss erfüllt sein). 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 werden (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 dieselbe 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

Achte aber bitte darauf, dass diese Regel nur für links­bü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 besten­falls ein INDEX SKIP SCAN durch­ge­führt wird. Dieser ist jedoch erheblich „teurer“ und langsamer als der INDEX RANGE SCAN auf dem ursprüng­lichen Index (siehe Abbildung 7 vs. Abbildung 8).

Abbildung 7
Abbildung 8

Alternativ kannst du auch auf den „breiteren“ Index NDX_OWN_NAME_STAT_OBJECTS zugunsten des „schma­leren“ NDX_OWN_NAME_OBJECTS verzichten, da der schmalere Index per se kleiner als der breitere ist. Hier solltest du aller­dings drei nicht unwesent­liche Dinge im Hinterkopf behalten:

  1. Du kannst nicht auf Indizes verzichten, die unique- oder primary key Constraints durchsetzen.

  2. Der Verzicht auf eine Spalte, die im verwor­fenen Index noch enthalten war, erhöht die CPU-Last, da nach dem Indexzugriff jetzt noch ein Filtern in der Tabelle erfor­derlich wird (Abbildung 9 vs. 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 9
Abbildung 10
Abbildung 11
Abbildung 12

Indizes, die links­bündige Subsets anderer Indizes und damit Löschkandidaten im Sinne dieses DBA Tipps sind, findest du mit folgender Query (ebenfalls einge­schrä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 dir bereits in diversen Screenshots aufge­fallen: Die bessere Alternative zum Löschen der überflüs­sigen 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 heraus­stellen, dass du mit einer Annahme „schwer daneben gelegen hast“ und einen Index wieder aktivieren musst, 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 angemes­sener Zeit letzt­endlich dann doch gelöscht werden, denn nur so bekommst du den ganz oben erwähnten Overhead tatsächlich auch los.

Fazit

Indizes haben konträre Auswirkungen auf eine Datenbank. Auf der einen Seite beschleu­nigen 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 identi­fi­zieren, die Duplikate oder Subsets anderer Indizes sind. Diese Duplikate können tenden­ziell aus der Datenbank entfernt werden.

Übrigens: Informationen zum Indexmonitoring findest du in unserem DBA-Tipp » Index-Monitoring – Aufspüren und Droppen von ungenutzten Indizes in Datenbanken. Index Monitoring hilft dir, Indizes aufzu­spüren, die zwar existieren, aber tatsächlich gar nicht benutzt werden.

Hier findest du weitere inter­es­sante Posts zum Thema Oracle aus unserem News und Insights Bereich.

icon-arrow_right_medium-violet-blue.svg

Share this article

Facebook 
Twitter 
LinkedIn 
XING 
WhatsApp 
Email