ich habe eine ziemlich große Datenbank (Dump hat 15 GB) mit zig Tabellen mit teilweise hundertausenden von Einträgen.
Hinzu kommen auch noch eine Menge Views. Diesen Views haben auch teilweise mehrere hundertausende von Zeilen.
Jetzt wird es etwas kompliziert:
Bei einer dieser Views geht es um die Dauer von Ereignisse. Wenn ein Ereigniss eintritt, wird dieses mit einem Zeitstempel in die Datenbank geschrieben. Diese View erzeugt nun eine Tabelle, mit der man die Dauer zwischen einem Ereigniss und dem nachfolgenden Ereigniss ermitteln kann. Hierzu wird die Tabelle mit den Ereignissen abgefragt und mit dem Befehl “rownum” eine Spalte mit Zeilennummern erzeugt. Diese Abfrage wird “gejoint” mit einer Abfrage der Ereignisstabelle, die mit den Befehl “rownum-1” ebenfalls eine Spalte mit diesmal um eine Zeilennummer versetzte Zeilennummer. Wenn die beiden Zeilennummern gleich sind, hat man praktisch einmal den Beginn des Ereignisses und den Beginn des nachfolgenen Ereignisses (und somit das Ende des vorangegangenen Ereignisses).
Ich sollte noch erwähnen, daß die Tabelle mit den Ereignissen ebenfalls eigentlich eine View ist.
Das Problem:
Ich habe nun eine SQL-Abfrage, die für einen bestimmten Zeitraum die Dauer von diversen Ereignissen liefern soll.
Wenn ich die Abfrage das erste mal ausführe kommt die Dauer “irgendwas” heraus.
Wenn ich dieselbe Abfrage nun wieder ausführe kommt die Dauer “irgendwas anderes” heraus. Dieser Wert kommt auch bei jeder nachfolgenden Abfrage heraus.
Normalerweise sollte ja ein und dieselbe Abfrage auch immer ein und dasselbe Ergebniss liefern.
Es werden während, vor oder nach der Abfrage keine Daten in die DB geschrieben, gelöscht oder geändert.
Dieses Verhalten tritt bei der Oracle 10g sowie 11 auf.
Wenn ich die Datenbank neu starte und die Abfrage wiederhole beginnt das selbe Spielchen wieder von vorne. Scheinbar sogar immer mit den selben Werten.
Die Abweichung der Dauer scheint mit der größe des Zeitraums für die Abfrage zusammenzuhängen.
Woran könnte dieses fehlerhafte Verhalten liegen?
Ist irgend jemandem schon mal so was ähnliches passiert?
Ich vermute, daß es irgendwas mit den Views zu tun hat. Das die Datenbank irgendwelche Tabellen teilweise noch im Speicher behält und das dann der Kniff mit den Zeilennummern irgendwie durcheinander gebracht wird.
Ich möchte nochmal darauf hinweisen, daß hier wirklich große Tabellen abgefragt werden.
Was ist den richtig?
Irgendwas oder irgendwas anderes?
Es gibt auch noch ein Verfahren, dass sich Debugging nennt? Schonmal versucht?
Das klassische Beispiel für die Taktik die ich hier Anwenden würde ist die eines HTML-Parsers.
Die Problemstellung ist, dass man eine große Datei hat und beim parsen eine Exception geworfen wird. Nun geht man hin und nimmt meist mit einem tool automatisiert die Ursprüngliche Datei und löscht Solange darin rum bis die Exception verschwindet. Das Ergebnis daraus ist dann ein aus oft nur wenigen Zeichen bestehender Text der diese Exception produzieren kann, was gegenüber der Suche in einer großen Datei sehr viel besser ist.
Selbiges würde ich hier empfehlen. Also solange Datensätze löschen bis das Fehlverhalten nicht mehr reproduziert wird.
Mit ein wenig Glück und Können bleiben dann evtl. nur noch drei Datensätze übrig anhand derer das Problem offensichtlich wird.
Sind die Ereignistabellen den nach dem Timestamp sortiert, bevor Numrow angewendet wird?
Zudem würde ich auch noch einen Blick ins Oracle Manual zu Numrow werfen und schauen ob einer der dort beschriebenen Fälle auf mein Problem zutrifft
[quote=Unregistered]Es gibt auch noch ein Verfahren, dass sich Debugging nennt? Schonmal versucht?[/quote]SQL Debuggen? das wäre mir neu, aber AFAIR man kann dem CBO ziemlich genau auf die Finger schauen. Genaueres müsste ich aber nochmal bei meinen Kollegen nachfragen…
[quote=hansmueller;80024]Bei einer dieser Views geht es um die Dauer von Ereignisse.[/quote]Die arbeitet doch bestimmt nur auf einer (oder zwei) Tabellen, kannst Du da einen SSCCE draus basteln?
[quote=hansmueller;80024]Hierzu wird die Tabelle mit den Ereignissen abgefragt und mit dem Befehl “rownum” eine Spalte mit Zeilennummern erzeugt. Diese Abfrage wird “gejoint” mit einer Abfrage der Ereignisstabelle, die mit den Befehl “rownum-1” ebenfalls eine Spalte mit diesmal um eine Zeilennummer versetzte Zeilennummer. Ich habe nun eine SQL-Abfrage, die für einen bestimmten Zeitraum die Dauer von diversen Ereignissen liefern soll.
Wenn ich die Abfrage das erste mal ausführe kommt die Dauer “irgendwas” heraus.
Wenn ich dieselbe Abfrage nun wieder ausführe kommt die Dauer “irgendwas anderes” heraus. Dieser Wert kommt auch bei jeder nachfolgenden Abfrage heraus.[/quote]Klingt danach, dass der CBO bei den folgenden Abfragen einen anderen Ausführungsplan wählt weil die Daten schon im Cache sind was zu einer anderen Reihenfolge der der Treffer führt.
wenn es sich um normale Views auf physikalische Tabellen handelt, dann ist das unmöglich
“Diese View erzeugt nun eine Tabelle,” was genau sollen wir darunter verstehen
“Ich sollte noch erwähnen, daß die Tabelle mit den Ereignissen ebenfalls eigentlich eine View ist.” es wird immer interessanter
Wird auch noch sortiert?
Ich vermute eher, ihr macht da einen Fehler bei ROWNUM und ROWNUM-1: da gibt es ja keinerlei Zusicherung, dass bei einer View alle Zeilen immer in der gleichen Reihenfolge daherschwimmbimmen…
[QUOTE=Timothy_Truckle;80049]SQL Debuggen? das wäre mir neu, aber AFAIR man kann dem CBO ziemlich genau auf die Finger schauen. Genaueres müsste ich aber nochmal bei meinen Kollegen nachfragen…
[/QUOTE]
PL/SQL Developer sollte bei jedem der was mit ner Oracle-DB macht auf dem Rechner sein.
Einfach dort ein kleines Script schreiben, dort drin die Abfragen ausführen und durchdebuggen.
Wie andere schon gesagt haben → Sortieren WICHTIG! Wenn die View nicht sortiert ist, kannst du dich nicht drauf verlassen, dass immer dieselbe Reihenfolge innerhalb der View besteht.
Aber das haben ja die andern auch schon geschrieben
@Timothy_Truckle : Danke, die ROW_NUMBER-Funktion war die Lösung für das Problem.
Das Sortieren der Views hat leider nichts gebracht. Daran lag es nicht.
Nur das Ersetzen von rownum mit der ROW_NUMBER-Funktion hat diesen Effekt mit den unterschiedlichen Werten behoben.
Noch ein paar Anmerkungen:
Natürlich wurden die Abfragen getestet. Aber es ist halt schon ein Unterschied, ob man nur eine Handvoll Test-Datensätze hat, deren Werte man auch noch im Kopf addieren kann, oder ein paar 100.000 Datensätze.
DEBUGGING :eek: Das ist doch nur was für Leute, die nicht richtig Programmieren können und fehlerhaften Code produzieren.
(Wo zur Hölle sind diese verflixten Sarkasmus-Tags??? ;))
Spaß beiseite, das Problem war ja, daß die Daten in der Datenbank an sich in Ordnung sind. Von Oracle kam auch keinerlei Fehlermeldung oder Ähnliches. Der Vergleich mit dem Debuggen einer invaliden HTML-Datei hinkt daher etwas.
Und natürlich habe ich einen SQL-Developer auf dem Rechner.
Ich muß allerdings zugeben, daß ich in SQL-Debugging noch nicht so bewand bin. Besonders wenn keine Fehlermeldungen geschmissen werden.
Für so ein Problem ist die Lösung mit Views eigentlich die Schlechteste. Die Zeilennummer ist ja nicht indiziert, und so einen riesigen self-join machen …
Warum nicht in einer Stored Proc mit einem Cursors durch die - nach Datum sortierten - Zeilen iterieren und sich jeweils das letzte Datum merken und für das Ergebnis die Dauer in jedem Schritt berechnen?
[quote=Bleiglanz]Für so ein Problem ist die Lösung mit Views eigentlich die Schlechteste.[/quote]Dem möchte ich widersprechen. Deine SP muss ja auch (bei jedem Aufruf) ein Select auf die Daten machen. Sie bringt also eigentlich keinen Vorteil, nur die Gefahr veraltete Daten zu bekommen, wenn die SP nicht aufegufen wurde.
wir arbeiten an einer seit vielen Jahren „gewachsenen“ Software. Es werden auch viele Stored Procedures verwendet. Diese wurden damals anscheinend von einem Datenbankspezialisten programmiert, der jetzt nicht mehr bei uns arbeitet. Sehr kompliziert plus schlecht bis gar nicht dokumentiert. Macht richtig „Spaß“ dort irgendwelche Erweiterungen einzubauen, nach Fehlern zu suchen oder einfach nur herauszubekommen, was das Teil den genau macht. (Hab’ die Sarkasmus-Tags immer noch nicht gefunden.)
Wir versuchen zur Zeit von den Stored Procedures langsam wegzukommen. Primär, weil die Wartung zu aufwendig ist. In ferner Zukunft könnten wir aber auch andere DB-Managmentsysteme verwenden und wären nicht mehr an die Oracle-DB gebunden.
[quote=hansmueller]und wären nicht mehr an die Oracle-DB gebunden[/quote]Dann solltest Du die Lösung mit row_number() noch mal überdenken, ich weis nicht, ob die auch in anderen DB’s verfügbar ist…
