News zu Oracle
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 aufzuräumen. Die Grundsätze und Queries treffen heute noch genauso zu wie damals, also Ärmel hochkrempeln und los geht’s!
Während Indizes grundsätzlich wichtig für eine performante Ausführung von DML und Queries sind, haben sie gleichzeitig auch Nachteile, denn:
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 unserer Kundensysteme 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.
In Abbildung 1 siehst du die Indizes NDX_NAME_OWN_OBJECTS und NDX_OWN_NAME_OBJECTS, welche dieselben 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, sodass er auch bei (vermeintlich) falscher Attributreihenfolge vom Index Gebrauch machen kann (Abbildung 2).
Den hier beschriebenen Fall einer Indizierung desselben Satzes von Spalten in unterschiedlicher Reihenfolge findest du ü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
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 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).
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).
Achte 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).
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 wird. Dieser ist jedoch erheblich „teurer“ und langsamer als der INDEX RANGE SCAN auf dem ursprünglichen Index (siehe Abbildung 7 vs. Abbildung 8).
Alternativ kannst du 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 solltest du allerdings drei nicht unwesentliche Dinge im Hinterkopf behalten:
Indizes, die linksbündige Subsets anderer Indizes und damit Löschkandidaten im Sinne dieses DBA Tipps sind, findest du 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 dir 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 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 angemessener Zeit letztendlich dann doch gelöscht werden, denn nur so bekommst du den ganz oben erwähnten Overhead tatsächlich auch los.
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 findest du in unserem DBA-Tipp » Index-Monitoring – Aufspüren und Droppen von ungenutzten Indizes in Datenbanken. Index Monitoring hilft dir, Indizes aufzuspüren, die zwar existieren, aber tatsächlich gar nicht benutzt werden.
Hier findest du weitere interessante Posts zum Thema Oracle aus unserem News und Insights Bereich.
Share this article