Oracle nutzt Index nicht

Computer: Software & Programmierung Datenbanken allgemein
#1

Ich verwende Oracle9i und habe folgendes Statement abgesetzt:

ALTER INDEX … REBUILD COMPUTE STATISTICS PARALLEL;

Und auf die USER_INDEXES angewendet. Leider ist die Ausführung bei einem Index fehlgeschlagen. Vermutlich sind die Rollback Segemente voll gelaufen, jedenfalls ist die DB in einen Zustand stundenlanger Dauerlast geraten und konnte nur durch einen Restart wieder zum laufen gebracht werden.

Der Restart ist erfolgreich, jedoch werden von nun an nicht mehr alle Indizes verwendet. Das fällt besonders bei sehr grossen Tabellen auf, die statt Sekunden nun durchaus eine Stunde benötigen.

In USER_INDEXES sind alle als VALID markiert. Auch die gewünschten Indizes sind dort vorhanden. Aber warum werden sie jetzt nicht mehr verwendet?

Thx, Markus

#2
  • Stats solltes du immer über das dbms_stats Package erstellen lassen
  • Versuche, die Stats nur für die entsprechende Tabelle und ihre Indizies neu zu erstellen :

begin
dbms_stats.gather_table_stats(ownname=> ‚SCHEMA‘,
tabname=> TABELLE’ ,
cascade=> TRUE,
granularity=> ‚ALL‘);
end;

  • Warum lässt du alle Indizies rebuilden ?

Gruss

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#3

Ich verwende Oracle9i und habe folgendes Statement abgesetzt:

ALTER INDEX … REBUILD COMPUTE STATISTICS PARALLEL;

[…]

  • Stats solltes du immer über das dbms_stats Package erstellen
    lassen

Ist das SQL pendant schlechter?

  • Versuche, die Stats nur für die entsprechende Tabelle und
    ihre Indizies neu zu erstellen :

begin
dbms_stats.gather_table_stats(ownname=> ‚SCHEMA‘,
tabname=> TABELLE’ ,
cascade=> TRUE,
granularity=> ‚ALL‘);
end;

Guter Hinweis, den merke ich mir!

  • Warum lässt du alle Indizies rebuilden ?

Vor kurzem hatte ich eine Tabelle, die als Queue benutzt wird. Daten rein, verarbeiten und wieder löschen. Der Index ist dabei entartet, d.h. die gelöschten Rows haben noch immer (nicht genutzte) Blöcke im Index belegt. Ergebnis: obwohl sich nur einige tausend Zeilen in der Tabelle befindet, dauert die Abfrage eine knappe halbe Stunde. Index neu erzeugt und die gleiche Abfrage dauert noch eine Sekunde.

Ausserdem ist mir aufgefallen, dass Indizes nach einem Rebuild deutlich wenbiger Blöcke belegen. Laut user_indexes hat ein Index vor dem Rebuild 55MB belegt, danach nur noch 5MB.

Bei einigen hundert Indizes habe ich deshalb einfach alle rebuilden lassen. Schaden kann es ja nicht, habe ich da noch gedacht :frowning:((

Markus

#4

Ist das SQL pendant schlechter?

  • DBMS_STATS erstellt deutlich mehr Daten als COMPUTE, d.h der Optimizer hat mehr Informationen für deine Abfragen.
  • Für Partitionierte Tabellen ist COMPUTE nur bedingt geeignet

Vor kurzem hatte ich eine Tabelle, die als Queue benutzt wird.
Daten rein, verarbeiten und wieder löschen. Der Index ist
dabei entartet, d.h. die gelöschten Rows haben noch immer
(nicht genutzte) Blöcke im Index belegt. Ergebnis: obwohl sich
nur einige tausend Zeilen in der Tabelle befindet, dauert die
Abfrage eine knappe halbe Stunde. Index neu erzeugt und die
gleiche Abfrage dauert noch eine Sekunde.

Ausserdem ist mir aufgefallen, dass Indizes nach einem Rebuild
deutlich wenbiger Blöcke belegen. Laut user_indexes hat ein
Index vor dem Rebuild 55MB belegt, danach nur noch 5MB.

Bei einigen hundert Indizes habe ich deshalb einfach alle
rebuilden lassen. Schaden kann es ja nicht, habe ich da noch
gedacht :frowning:((

Markus

  • Dazu dieser Artikel bei Tom :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::…

Gruss

#5

Hast du auch eine Idee zu meinem eigentlichen Problem? Die Indizes sind vorhanden und gültig (laut user_indexes) werden aber nicht genutzt. Eine Refrenzdatenbank mit gleichen Datenbestand und erfolgreichem rebuild nutzt die Indizes wie gewünscht.

Ich möchte das Problem gerne eingrenzen, denn mehrere hundert Indizes würden Tage zum Rebuild benötigen.

#6
  • Was sagt TKPROF ? Mach einen Trace einer in Frage kommenden Abfrage und poste das Ergbenis hier

Gruss

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#7
  • Was sagt TKPROF ? Mach einen Trace einer in Frage kommenden
    Abfrage und poste das Ergbenis hier

Den Explain Plan einer Abfrage kann ich gut interpretieren. Nach dem Fehlschlag kommt die Datenbank immer mal wieder in eine Krise. Welches Statement schuld ist, lässt sich nur schwer ermitteln, da die DB in solchen Situationen praktisch nicht mehr reagiert (hoher io wait).
In diesen Situationen kann weder ein TKPROF noch ein execute statspack.snap ausgeführt werden. (Könnte schon, nur bleibt dann nicht die Zeit, bis die Statements zurück kehren)

Ich erzeuge mir in regelmässigen Abständen einen statspack.snap, in der Hoffnung auch mal die Krisensituation dabei zu haben.
Primär suche ich aber noch einer Möglichkeit, inkonsistente Strukturen abzufragen. Also: kann ich ungültige Indizes, Tabellen o.ä. abfragen?
Oder bleibt nur die Möglichkeit, wieder eine Krisensituation abzuwarten?

#8

hi Markus

ist vielleicht im thread untergegangen:

oracle ab 9i nutzt defaultmässig den cost-based-optimizer. der prüft zuerst aufgrund der statistiken, ob und welcher index genutzt werden soll. wenn die statistiken nicht aktuell sind, kann sich da der optimizer schon gröber vertun und indizes dort ignorieren, wo sie sinnvoll sind oder indizes dort nehmen, wo sie die sache langsamer machen.

einen index zu rebuilden heisst im endeffekt ihn neu aufzubauen. ich kann es nun nicht mit bestimmtheit sagen aber ich vermute mal, dass oracle bei einem rebuild die statistiken löscht. der optimizer hat nun keine vernünftigen daten mehr über die selektivität der indizes.

normalerweise geht oracle so vor, dass der index nur dann genommen wird, wenn der optimizer zum schluss kommt, dass aufgrund des index weniger als 7 % der gesammtdatenmenge gelesen werden muss. andernfalls ist ein full-table-scann effizienter.

ich würde also zuerst mal über das dbms_stats-package dein schema neu analysieren lassen, um alle statistiken (tabellen UND indizes) neu zu bauen. danach geht ev. eh wieder alles gleich schnell. in extremfällen musst du extra histogramme erstellen. das ist z.b. notwendig, wenn eine indizierte spalte wenig verschiedene werte hat, die aber meist extrem selektiv sind. beispiel: du speicherst in einer tabelle nicht nur aktuelle sondern auch historische daten. die aktuellen erkennst du an dem wert „active=‚Y‘“. das feld active hat also nur zwei werte: Y und N. eine normale statistik sagt dem optimizer, dass die spalte nur zwei werte hat, der index also 50 % der daten liefert. damit wird der index IMMER ignoriert. ein histogramm sagt dem optimizer aber, dass nur 5 % der daten den wert Y haben, der rest N, der Index also sehr wohl sinnvoll ist. gerade in statusorientierten daten kann ein histogramm manche suchen ganz dramatisch beschleunigen.

ach übrigens: in der user_indexes gibt es eine spalte „last_analyzed“. mach mal ein
select index_name, last_analyzed from user_indexes order by 2;

vermutlich findest du so am schnellsten deine problem-indizes

lg
erwin

#9

Ich erzeuge mir in regelmässigen Abständen einen
statspack.snap, in der Hoffnung auch mal die Krisensituation
dabei zu haben.
Primär suche ich aber noch einer Möglichkeit, inkonsistente
Strukturen abzufragen. Also: kann ich ungültige Indizes,
Tabellen o.ä. abfragen?
Oder bleibt nur die Möglichkeit, wieder eine Krisensituation
abzuwarten?

  • Versuche über die Tabelle user_indexes diejenigen Indizies rauszusuchen, welche schon länger nicht anlaysiert worden sind

  • Versuche mal. für das ganze Schema die Statistiken neu zu berechnen:

DBMS_STATS.gather_schema_stats (ownname => ‚MY_SCHEMA‘,
method_opt => ‚FOR ALL COLUMNS SIZE AUTO‘,
CASCADE => TRUE);

  • Wenn du in etwa weisst, welche Tabellen und ihre Indizies das Problem verursachen, kannst du auch probieren, den Index zu droppen und neu zu erstellen
#10
  • Versuche mal. für das ganze Schema die Statistiken neu zu
    berechnen:

DBMS_STATS.gather_schema_stats (ownname => ‚MY_SCHEMA‘,
method_opt => ‚FOR ALL
COLUMNS SIZE AUTO‘,
CASCADE => TRUE);

Danke, das werde ich mal ausprobieren.

  • Wenn du in etwa weisst, welche Tabellen und ihre Indizies
    das Problem verursachen, kannst du auch probieren, den Index
    zu droppen und neu zu erstellen

Das habe ich auch schon gemacht. Leider dauert die Erzeugung schnell 30 bis 60 Minuten und ist so resourcen-intensiv, dass die DB dann kaum noch reagiert. Da nutzt auch das beste Storage nicht, wenn GB an Daten durch das i/o geschleust werden müssen.
Gibt es eine Möglichkeit die Indexerstellung zu bremsen? Von mir aus kann die Erstellung 12 Stunden dauern, wenn nur die Verfügbarkeit gewähleistet bleibt.

#11

Gibt es eine Möglichkeit die Indexerstellung zu bremsen? Von
mir aus kann die Erstellung 12 Stunden dauern, wenn nur die
Verfügbarkeit gewähleistet bleibt.

  • Hmm, habt ihr den keine definierten Wartungsfenster ? Dies sind typische Aufgaben, welche während solch einem Zeitraum gemacht werden müssen (sollten).
#12

Hi,
wenn ich den Rest des Threads richtig verstanden hab, weißt du nicht, welches Statement die Probleme macht?

Dann weißt du auch nicht, ob es daran liegt, dass Oracle einen Index nicht nutzt. Es gibt ne Menge Situationen, in denen die Verwendung eines Indexes eine schlechte Idee ist. Vielleicht kommt Oracle genau auf diese dummme Idee, da deine Indizes kleiner und damit attraktiver geworden sind.

Produziert die Anwendung auf der Datenbank kein logging, was sie gerade versucht zu tun? Darüber müsste man doch einschränken können was den Hänger verursacht.

Jens