PDA

Archiv verlassen und diese Seite im Standarddesign anzeigen : SQL Select wird durch Unterabfrage nicht schneller, warum?


Rooter
2014-08-09, 17:51:41
Hallo Leute,

ich habe auf der Arbeit einen SQL Select der eine Anzahl pro Person der letzten 24h ausgeben soll:


Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From (Select *
From TRANSAKT
Where DATUM > sysdate -1) tr
Where tr.ZIEL In ('WAWait','WAWaitWW')
And tr.BEARBEITER In ('$PreRep','$PreRepWW')
Group By tr.USERNUMMER, tr.USERNAME


Da die Tabelle TRANSAKT, aus der ich die Auswertung ziehe, leider etwas größer ist (16+ Mio. Zeilen, keine Ahnung ob das groß ist... :D) dauert die Abfrage normalerweise 2-3 Minuten.
Die Spalte DATUM hat aber einen Index, deshalb habe ich hinter From die Unterabfrage (eigene Tabelle tr, oben in Fettdruck) eingebaut, was die von der Hauptschleife zu verarbeitende Datenmenge massiv einschränken sollte: Wenn ich diese Unterabfrage für sich alleine laufen lasse dauert sie weniger als 1 Sekunde und liefert ca. 3000 Ergebniszeilen. Besonders breit ist die Tabelle auch nicht, ca. ein dutzend Spalten.
Wenn ich sie aber, wie oben, als Unterabfrage einbaue, dauert das Ganze wieder 2-3 Minuten, so als würde ich direkt From TRANSAKT verwenden. :confused: Wie kommt's?
Hier läuft Oracle SQL 9.2

MfG
Rooter

RattuS
2014-08-09, 18:18:07
Was sagt denn EXPLAIN PLAN?

samm
2014-08-09, 18:28:44
Weshalb sollte es denn durch die Unterabfrage* schneller werden? Wenn auf DATUM ein Index ist, wird der Optimizer so schlau sein, und diese Einschräunkung als erste anwenden, auch wenn du "Where DATUM > sysdate -1" direkt in die Hauptabfrage einbaust.


*: (ist evt nicht die richtige Bezeichnung an dieser Stelle, aber wir wissen ja, wovon wir reden ;) )

Marscel
2014-08-09, 18:30:27
Ich wette, dass es dem System egal ist, ob du erst ein Subselect auf den Index machst und dann das WHERE, oder bloß ein einfaces Select einschließlich des Index im WHERE. In beiden Fällen sollte die Datenmenge etwa gleich schnell gefiltert sein.

Bleiben die Strings oder das Gruppieren. Ausschlussverfahren?

Exxtreme
2014-08-09, 18:35:44
Habe mit Oracle keine Erfahrung aber soviel ich weiss mag Oracle kein WHERE x IN (y,z). Versuch's mal mit WHERE x = y or x = z

Und GROUP BY kostet auch Performance wenn auch nicht so viel wie DISTINCT.

Gast
2014-08-09, 19:11:42
wie bereits gesagt wurde ist IN sehr langsam im vergleich zu anderen lösungen.

mit sicherheit wird aber das gruppieren die meiste bearbeitungszeit fressen, da es mit großer wahrscheinlichkeit einen full table scan verursacht. da du hier keine operation mit SUM o.ä. machst würde ich einfach distinct empfehlen. aber auch hier hilft ein entsprechend gesetzter index.

Dio Eraclea
2014-08-09, 20:01:48
Ohne Explain Plan kann man nur raten was der Oracle optimizer daraus macht (besonders bei der doch inzwischen schon älteren Version 9.2). Könnte es sein dass auf ZIEL und/oder BEARBEITER auch ein Index besteht? (Würde ich jetzt vermuten).

Probier es vielleicht einmal mit WITH Statement:


WITH SUB_TRANSAKT AS (
SELECT *
FROM TRANSAKT
WHERE DATUM > sysdate -1
)
SELECT tr.USERNUMMER,
tr.USERNAME,
COUNT(*)
FROM SUB_TRANSAKT
WHERE tr.ZIEL IN ('WAWait','WAWaitWW')
AND tr.BEARBEITER IN ('$PreRep','$PreRepWW')
GROUP BY tr.USERNUMMER, tr.USERNAME;


(Subselects und WITH statements werden wenn ich mich recht erinnere (kann also kompletter blödsinn sein ;-)) anders behandelt => bin selbst nur JAVA Entwickler der nur hin und wieder ein SELECT optimiert).

lg, Heli

Rooter
2014-08-09, 21:09:51
Was sagt denn EXPLAIN PLAN?Habe schon mal davon gehört aber wie funktioniert das? :redface:
Ich sollte dazu erwähnen, dass ich keinen Vollzugriff auf die Datenbank habe! In unserem Warenwirtschaftssystem gibt es einen kleinen Editor für SQLs, der zeigt das Ergebnis als Liste an und man kann es auch nach Excel importieren. Mit Dingen wie COMMIT oder ROLLBACK habe ich da nix am Hut. Ich vermute dann habe ich auch kein EXPLAIN PLAN, oder!?
Außerdem sollte ich noch erwähnen, dass ich reiner Autodidakt bin was SQL angeht. ;)

Weshalb sollte es denn durch die Unterabfrage* schneller werden?
Ich wette, dass es dem System egal ist, ob du erst ein Subselect auf den Index machst und dann das WHERE, oder bloß ein einfaces Select einschließlich des Index im WHERE. In beiden Fällen sollte die Datenmenge etwa gleich schnell gefiltert sein.Ich hatte erwartet, dass durch die Unterabfrage (die ja wie gesagt kaum eine Sekunde braucht) die weiter zu bearbeitende Datenmenge von einer Tabelle mit 16 Mio. Zeilen auf eine Tabelle mit nur 3000 Zeilen reduziert wird. Und eine so kleine Tabelle im RAM nach nur zwei weiteren Kriterien zu filtern und den Rest zu gruppieren kann doch unmöglich 2-3 Minuten dauern, selbst wenn unsere Datenbank auf einem C64 laufen würde...! :rolleyes:
Stattdessen dauern beide Varianten etwa gleich lange.

Bleiben die Strings oder das Gruppieren. Ausschlussverfahren?Was meinst du? :confused:

Habe mit Oracle keine Erfahrung aber soviel ich weiss mag Oracle kein WHERE x IN (y,z). Versuch's mal mit WHERE x = y or x = zIch hatte irgendwo gelesen, dass OR verheerend für die Performance ist, dort wurde sogar empfohlen es durch zwei Abfragen zu ersetzen die mittels UNION zusammengefasst werden.

mit sicherheit wird aber das gruppieren die meiste bearbeitungszeit fressen, da es mit großer wahrscheinlichkeit einen full table scan verursacht. da du hier keine operation mit SUM o.ä. machst würde ich einfach distinct empfehlen. aber auch hier hilft ein entsprechend gesetzter index.SUM() zwar nicht aber ich zähle doch mit COUNT(). Ich will ja die Anzahl der Transaktionen pro User wissen, da nützt mir Distinct doch nichts.

@ Dio:
Danke, werde diese Variante am Montag mal ausprobieren.

MfG
Rooter

Marscel
2014-08-09, 21:45:57
Habe schon mal davon gehört aber wie funktioniert das? :redface:

Einfach EXPLAIN PLAN vor das SELECT knallen. Das gibt dir dann eine Tabelle aus, welche Tabellen es durchgehen wird, welche Indizes (nicht) benutzt werden. Das sollte auch unabhängig von Rechten sein und immer funktionieren.

Ich hatte erwartet, dass durch die Unterabfrage (die ja wie gesagt kaum eine Sekunde braucht) die weiter zu bearbeitende Datenmenge von einer Tabelle mit 16 Mio. Zeilen auf eine Tabelle mit nur 3000 Zeilen reduziert wird. Und eine so kleine Tabelle im RAM nach nur zwei weiteren Kriterien zu filtern und den Rest zu gruppieren kann doch unmöglich 2-3 Minuten dauern, selbst wenn unsere Datenbank auf einem C64 laufen würde...! :rolleyes:
Stattdessen dauern beide Varianten etwa gleich lange.

Triff am besten nie Annahmen über die DB-Interna. Meistens arbeitet sie nämlich smarter als der Benutzer glaubt. Nein, da wird mit Sicherheit keine Untertabelle temp. voll in den RAM geladen. Da gibt es B-Bäume, Hashes, Pointer und sehr viel mehr Dinge, die seit 40 Jahren entwickelt werden um genau sowas zu vermeiden.

Was meinst du? :confused:

Wirf das aus dem Query raus, bis es wieder flott läuft. Also hier String-Vergleiche und/oder das Gruppieren. Und dann schau, was passiert. Oder du kannst das EXPLAIN PLAN deuten.

Ich hatte irgendwo gelesen, dass OR verheerend für die Performance ist, dort wurde sogar empfohlen es durch zwei Abfragen zu ersetzen die mittels UNION zusammengefasst werden.

Bei zwei Vergleichen wird dir das hier egal sein. Der Query-Planer sollte bei Konstanten sowas eh von selbst in die brauchbarste Form transformieren.

Matrix316
2014-08-09, 23:55:03
Und was passiert, wenn man die Abfrage einfach so macht?

Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From TRANSAKT tr
Where tr.Datum > sysdate -1 and tr.ZIEL In ('WAWait','WAWaitWW')
And tr.BEARBEITER In ('$PreRep','$PreRepWW')
Group By tr.USERNUMMER, tr.USERNAME

Rooter
2014-08-10, 00:49:14
Einfach EXPLAIN PLAN vor das SELECT knallen. Das gibt dir dann eine Tabelle aus, welche Tabellen es durchgehen wird, welche Indizes (nicht) benutzt werden. Das sollte auch unabhängig von Rechten sein und immer funktionieren.Okay, werde ich ausprobieren.

Und was passiert, wenn man die Abfrage einfach so macht?

Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From TRANSAKT tr
Where tr.Datum > sysdate -1 and tr.ZIEL In ('WAWait','WAWaitWW')
And tr.BEARBEITER In ('$PreRep','$PreRepWW')
Group By tr.USERNUMMER, tr.USERNAMEDas war ja die ursprüngliche Version. Läuft genau so lange, 2-3 Minuten.

MfG
Rooter

Exxtreme
2014-08-10, 01:08:46
Ich hatte irgendwo gelesen, dass OR verheerend für die Performance ist, dort wurde sogar empfohlen es durch zwei Abfragen zu ersetzen die mittels UNION zusammengefasst werden.

OR ist zwar langsamer aber nicht verheerend langsamer. Ich denke, die String Vergleiche sind die Zeitfresser. Man könnte auf ZIEL und BEARBEITER auch einen Index setzen, das würde auch Zeit sparen.

PHuV
2014-08-10, 01:54:02
Einfach EXPLAIN PLAN vor das SELECT knallen. Das gibt dir dann eine Tabelle aus, welche Tabellen es durchgehen wird, welche Indizes (nicht) benutzt werden. Das sollte auch unabhängig von Rechten sein und immer funktionieren.
Das reicht aber nicht aus, man muß hier oft noch vorher die Tabelle plan_table erzeugen, siehe

http://www.datenbank-tuning.de/explain-plan.htm

War bei mir bei einigen Kundensystemen zumindest so.

Gast2
2014-08-10, 03:25:03
Wenn du kein DBA bist und selber keine geeigneten Indizes anlegen kannst, dauern meist alle Abfrageversionen lange und das Datenmodell ist Scheiße. Das ist dann ein Thema für jemanden der sich mit DB-Tuning auskennt und mehr Berechtigungen und KnowHow hat...

Exxtreme
2014-08-10, 16:11:33
Und hat Oracle nicht ein Tool mit dem man sich Ausführungspläne anzeigen kann? Für die IBM DB2 und den MS Sql Server gibt es sowas als kostenlose Dreingabe.

Matrix316
2014-08-10, 18:57:17
OR ist zwar langsamer aber nicht verheerend langsamer. Ich denke, die String Vergleiche sind die Zeitfresser. Man könnte auf ZIEL und BEARBEITER auch einen Index setzen, das würde auch Zeit sparen.
Das würde die Abfrage bestimmt erheblich schneller machen.

Alternativen:

Die Abfrage auf das Datum in eine neue Tabelle und von dieser dann die weiteren Sachen abfragen. Gibt's stored procedures und sowas bei Oracle? Oder eine Tabellenwertfunktion die die erste schnelle Abfrage macht und man fragt den Rest auf diese ab oder was Oracle da bietet. Oder temporäre Datenbanken. With irgendwas as sollte ja auch schon helfen.

Ganon
2014-08-10, 19:49:07
Dass die Abfrage mit der Unterabfrage genauso langsam ist, wie die Abfrage, wo die Bedingung im WHERE ist, liegt schlicht daran, dass das eine typische Abfrageoptimierung ist. Unterm Strich kommt bei beiden Abfragen im ersten Schritt die gleiche Abfrage bei raus.

Warum es aber jetzt direkt langsam ist, kann einem echt nur eine gezielte Abfrageanalyse beantworten. Alles andere ist Rätselraten.

Gerade bei Tabellen mit so vielen Einträgen muss eigentlich ein DB Admin ran, der das ganze mal ordentlich macht. Ansonsten macht der Abfrageoptimierer der Datenbank einfach nur Mist. Die entsprechenden Histogramme der Datenbank müssen da ordentlich gepflegt werden.

Ich würde einfach vermuten, dass die ganzen Statistikern der Datenbank einfach falsch sind und sich der Optimierer dafür entscheidet, den Index gar nicht erst zu nutzen.

PHuV
2014-08-11, 09:02:37
Und hat Oracle nicht ein Tool mit dem man sich Ausführungspläne anzeigen kann?
Schon, nur verlangt Oracle für das Diagnostic and Tuning Pack eine Menge Geld, und viele kaufen sich das eben nicht. Ganz zu schweigen, daß es nicht in der Standard Version vorhanden ist, sondern nur in der Enterprise-Version zur Verfügung steht. Die Enterprise-Version kostet - je nach CPUs und Nutzer - doch eine fast schon abartige Menge mehr Geld.

@Rooter

Was manchmal hilft, sich eine komplette "Demo- bzw- Testversion" von Oracle als Enterprise-Edition mit allen Features, wie eben Diagnostic- und Tuning-Pack zu installieren, dorthin mit Datapump zu exportieren, und dann auf dem Testsystem mal die entsprechenden Analysen des SQL-Statements durchführen. ;)

Asaraki
2014-08-14, 11:08:47
0) Nein, 16 Millionen ist nichts. Die Ursache für die schlechte Performance liegt nicht dort, sondern eindeutig in schlechten Indizes oder Zugriffspfaden. Bin zwar kein Oraclemensch aber ein Tablescan auf eine 16 Millionen Table dürfte keine 2-3 Minuten dauern - oder der Server ist sau lahm. Korrigiert mich, wenn ich hier falsch liege, meine DB Kenntnisse basieren auf völlig irrsinnig schnellen DB2 Mainframe Systemen.

1) Bei funktionierendem Optimizer (und aktuellen Stats auf der DB) dürfte der Subselect nichts bringen, solange du sicherstellst, dass das DATUM als erstes Kriterium dran kommt. Das resultiert dann in der gleichen Zwischenmenge, die du mit dem Subselect erstellst.

2) Ist nicht immer tragisch, aber ich vermeide * bei Subselects wenn ich onehin nicht alle Spalten brauche. Also wenn, dann Select Usernummer, Username, Ziel, Bearbeiter from TRANSAKT. Normalerweise willst du vermeiden, dass allfällig nötige temp-tables grösser als nötig sind. Bezweifle aber, dass das hier einen grossen impact haben wird.

3) Wenn du das nun optimieren willst, schau dir erstmal die Indizes auf TRANSAKT an. Ich kenn mich selbst mit Oracle nicht wirklich aus, aber was du willst in einen Index finden, in dem möglichst viele von dir verwendete Spalten in der von dir abgefragten Reihenfolge vorkommen.
Das kannst du auch tun, ohne dass du irgendwelche Tools installiert hast. Zugriff auf die Tabellendefinition wirst du ja haben oder? Anschliessend noch kontrollieren ob die Stats aktuell sind und die Indizes nicht z.B. nur alle 14 Tage updated werden ^^ Sollte alles nicht der Fall sein, aber 100%ig wissen ist besser als vermuten.

4) String/Char Spalten sind normalerweise nicht indiziert, ausser es gibt einen Constraint auf den Feldern (nur x mögliche verschiedene Inhalte). Ansonsten machen diese - in der Regel - die Indizes zu gross und inperformant. Daher : Gibt es evt. einen indizierten Wert, den du hernehmen kannst um auf alle Datensätze einzuschränken, welche überhaupt die von dir gewünschten Stringwerte einschränkt? Z.b. "Entrycode : 1". You get the idea.

5) Alternativ, nehmen wir an du kannst weder die Indizes prüfen noch irgendwelche Tools benutzen, dann bau den Select schrittweise aus und schau dir die Entwicklung der Laufzeit an, d.h. fang an mit :

Select *
From TRANSAKT tr
Where tr.DATUM > sysdate -1
AND tr.ZIEL = 'WAWait'


6) Zuletzt noch, bin eben kein Oraclemensch, das Datum > sysdate-1 kann nicht durch etwas schnelleres ersetzt werden? Wenn Einträge in der Zukunft ausgeschlossen sind, wäre DATUM = CURRENT DATE (o.ä.) evt. schneller?

Ganon
2014-08-14, 21:19:22
0) Nein, 16 Millionen ist nichts.

Natürlich ist das was. Nicht jede Datenbank läuft auf einem fetten Mainframe.

Ob der Server jetzt mal eben (geraten) 2GB Daten durchsuchen muss oder eher <1MB ist schon ein Unterschied.

Asaraki
2014-08-15, 01:50:56
Bei einer row size von 5kb und das ist schon viel sind das erst ~80mb. Der Index dazu sind ein paar Kilobytes. Das ist keine Größe. Und selbst 2gb sind in einem tablescan auf nem ordentlichen Server keine 2 Minuten.

Ganon : Sorry, das sollte nicht so überheblich klingen :) War vom Phone gepostet und kurz gefasst, du hast natürlich Recht, dass es eine Rolle spielt. Erklärt aber imho die Laufzeit trotzdem noch nicht, da läuft doch irgendwas schief.

tomvos
2014-08-15, 21:44:28
Was manchmal hilft, sich eine komplette "Demo- bzw- Testversion" von Oracle als Enterprise-Edition mit allen Features, wie eben Diagnostic- und Tuning-Pack zu installieren, dorthin mit Datapump zu exportieren, und dann auf dem Testsystem mal die entsprechenden Analysen des SQL-Statements durchführen. ;)

Ja, das kann ich nur unterschreiben. Auf der Entwicklungs-DB die EE installieren, mal schauen, wie Oracle das Statement optimiert. Dann entweder das Statement ändern oder ggf. ein SQL-Profile einrichten. Da SQL-Profile leider wieder ein EE+TuningPack Feature sind, musst du zum Zurückübertragen in die 9.2 auf Stored Outlines zurückgreifen. Wobei (uralt) Oracle 9.2 sowie noch keine SQL Profiles kann.

Rooter
2014-08-16, 21:25:09
Da ich diese Woche krank geschrieben bin konnte ich EXPLAIN PLAN und die Variante mit WITH aus #7 noch nicht ausprobieren.

Man könnte auf ZIEL und BEARBEITER auch einen Index setzen, das würde auch Zeit sparen.Das liegt leider weit außerhalb meiner Macht. ;)
Wobei ich mich wundere, dass es keinen Index für ZIEL gibt. Denn bei Abfragen auf diese Tabelle geht es immer nur darum WANN etwas WOHIN verschoben wurde.

Dass die Abfrage mit der Unterabfrage genauso langsam ist, wie die Abfrage, wo die Bedingung im WHERE ist, liegt schlicht daran, dass das eine typische Abfrageoptimierung ist. Unterm Strich kommt bei beiden Abfragen im ersten Schritt die gleiche Abfrage bei raus.Das vermute ich auch denn die Laufzeit ist bei beiden Varianten identisch.

Warum es aber jetzt direkt langsam ist, kann einem echt nur eine gezielte Abfrageanalyse beantworten. Alles andere ist Rätselraten.

Gerade bei Tabellen mit so vielen Einträgen muss eigentlich ein DB Admin ran, der das ganze mal ordentlich macht. Ansonsten macht der Abfrageoptimierer der Datenbank einfach nur Mist. Die entsprechenden Histogramme der Datenbank müssen da ordentlich gepflegt werden.

Ich würde einfach vermuten, dass die ganzen Statistikern der Datenbank einfach falsch sind und sich der Optimierer dafür entscheidet, den Index gar nicht erst zu nutzen.Dazu muss ich sagen, dass die aktuell verwendete Version unseres Warenwirtschaftssystems von 2012 ist. Und die Programmierer, die ich früher alle paar Monate hier im Haus gesehen habe, habe ich auch schon min. 3 Jahre nicht mehr gesehen...
Wir können also davon ausgehen, dass DB-mässig rein gar nichts gepflegt ist... X-D

Wir sind ja gerade dabei auf ein anderes, web-basiertes WWS umzusteigen. Dass der Chef der Firma, von der dieses WWS kommt, einer unserer Gesellschafter ist, ist dabei sicherlich nur Zufall... :rolleyes:

Was manchmal hilft, sich eine komplette "Demo- bzw- Testversion" von Oracle als Enterprise-Edition mit allen Features, wie eben Diagnostic- und Tuning-Pack zu installieren, dorthin mit Datapump zu exportieren, und dann auf dem Testsystem mal die entsprechenden Analysen des SQL-Statements durchführen. ;)Selbst wenn ich die Möglichkeit dazu hätte, könnte ich das Ding nicht bedienen.
Überschätzt mich in dem Bereich nicht, ich beherrsche zwar die Syntax und Semantik von SQL(-Selects), aber sonst nichts! Ich habe ein Textfeld in das ich einen SQL-Select eingebe und bekomme eine Tabelle zurück. That's it!

Zugriff auf die Tabellendefinition wirst du ja haben oder?Ich habe eine "SQL Tables & Columns"-Übersicht und weiß auch welche Spalten einen Index haben.

Anschliessend noch kontrollieren ob die Stats aktuell sind und die Indizes nicht z.B. nur alle 14 Tage updated werden ^^ Sollte alles nicht der Fall sein, aber 100%ig wissen ist besser als vermuten.Das liegt leider auch weit außerhalb meiner Macht. ;)

Gibt es evt. einen indizierten Wert, den du hernehmen kannst um auf alle Datensätze einzuschränken, welche überhaupt die von dir gewünschten Stringwerte einschränkt?3) Wenn du das nun optimieren willst, schau dir erstmal die Indizes auf TRANSAKT an. Ich kenn mich selbst mit Oracle nicht wirklich aus, aber was du willst in einen Index finden, in dem möglichst viele von dir verwendete Spalten in der von dir abgefragten Reihenfolge vorkommen.Das wäre dann DATUM. Wie gesagt reduziert der die Tabelle von 16 Mio. auf ~3000 Zeilen. Die anderen abgefragten Spalten haben keinen Index.

5) Alternativ, nehmen wir an du kannst weder die Indizes prüfen noch irgendwelche Tools benutzen, dann bau den Select schrittweise aus und schau dir die Entwicklung der Laufzeit an, d.h. fang an mit :

Select *
From TRANSAKT tr
Where tr.DATUM > sysdate -1
AND tr.ZIEL = 'WAWait'Selbst wenn ich da etwas erkenne, was mache ich dann mit dieser Erkenntnis? Es in eine Unterabfrage packen hat keinen Wert, wenn doch gäbe es diesen Thread ja nicht... ;)

6) Zuletzt noch, bin eben kein Oraclemensch, das Datum > sysdate-1 kann nicht durch etwas schnelleres ersetzt werden? Wenn Einträge in der Zukunft ausgeschlossen sind, wäre DATUM = CURRENT DATE (o.ä.) evt. schneller?Es geht um die letzten 24 Stunden, wie kann ich das optimieren?

MfG
Rooter

Asaraki
2014-08-17, 12:06:41
Wie gesagt, es ist immer tricky solche Sachen aus der Ferne wirklich zu optimieren, aber je näher du an die Ursache kommst desto besser.

3 & 5 ) Also wenn du die Abfrage nur aufs Datum machst, d.h. diese 3000 Rows, dann hast du mehr oder weniger sofort ein Ergebnis? Wie gesagt kann ich die reelle Performance deiner DB ja nicht einschätzen, aber selbst wenn er 3000 mal alles gegen die restlichen Kriterien prüfen muss sollte das imho keine 3 Minuten dauern. Aber es kann natürlich sein... ohne hier Zahlen zu haben, da musst du auch deine Erfahrungswerte ranziehen.

Ich würd mal von hier aus anfangen.

5) Es geht hauptsächlich darum, dass du - ohne entsprechende Tools - verstehst, was die DB genau mit deinem SQL macht. Hier hilft es oft auch das Query mal mit "Fetch first 1 rows only" laufen zu lassen, sollte z.B. für einen Subselect zuerst ein Tablescan nötig werden dann dauern schon der erste Row lange und die restlichen dann "instant", wenn dieser aber sofort kommt, dann stimmt vermutlich einfach etwas mit den Access paths nicht oder die Stats sind z.B. nicht aktuell, so dass er - obwohl er alle Rows hätte - noch weiter sucht.

Zusammengefasst : "Arbeit vor dem ersten Result" vs "Lineare, langsame Performance pro Row"

Bezüglich Datum : Wenn der Index auf Datum ok ist, dann sollte er nach einer Milisekunde schon wissen, dass >Sysdate-1 äquivalent ist zu "TODAY". Aber auch solche Sachen kann man einfach mal ausprobieren, je mehr man probiert, desto mehr Infos hat man zusammen um anschliessend zu optimieren.

P.S: Hier noch was zum Index traversing. Gilt eigentlich für alle relationalen Datenbanken. Weisst du vielleicht schon alles, aber ich dachte ich post's dir mal : http://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes
Deshalb auch meine Frage ob du die Indizes im Detail anschauen kannst oder nur weisst auf welchen Columns ein Index steht. Wenn der halt verkehrt rum aufgebaut ist hast du damit deine PErformance schon erklärt und kannst aber allenfalls das SQL "counter intuitive" basteln und es läuft flotter.

Sumpfmolch
2014-08-22, 00:33:49
Wäre wirklich spannend zu erfahren, was da so schlimm ausbremst... :)

Testweise habe ich schnell mal auf meinem Desktop Rechner hier in meiner PostgreSQL Datenbank eine Tabelle generiert mit ca. 25Mio Einträgen (25.600.000):

Column | Type | Modifiers
usernummer | text |
username | text |
datum | date |
ziel | text |
bearbeiter | text |
id | integer | not null default nextval('test_id_seq'::regclass)
Indexes:
"id" PRIMARY KEY, btree (id)


Die Einträge sind zufallsgeneriert über Listen, d.h. größtenteils einzigartig. Größe der Tabelle ca. 3GB.

------------------------------------------------------------------------

Beispielzeile:

id | usernummer | username | datum | ziel | bearbeiter |
17235236 | PO19186768_6074 | Hiram0 | 2014-07-27 | Hiroshima | McCrary

-------------------------------------------------------------------------

Erster Versuch ohne jeden zusätzlichen Index außer auf "id":

EXPLAIN ANALYZE select tr.usernummer, tr.username, count(*) from (select * from test where datum > current_date-15 ) tr where tr.Ziel IN ('Monterrey','Bangalore','Kampong Cham','Cali') an
d tr.bearbeiter IN ('Rivers','Ostanik','Keller','Wilson') group by tr.usernummer, tr.username;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1051309.84..1051335.17 rows=2533 width=23) (actual time=8056.565..8056.605 rows=209 loops=1)
-> Seq Scan on test (cost=0.00..1051119.92 rows=25322 width=23) (actual time=5.160..8023.942 rows=61696 loops=1)
Filter: ((ziel = ANY ('{Monterrey,Bangalore,"Kampong Cham",Cali}'::text[])) AND (bearbeiter = ANY ('{Rivers,Ostanik,Keller,Wilson}'::text[])) AND (datum > (('now'::cstring)::date - 15)))
Rows Removed by Filter: 25538304
Total runtime: 8056.647 ms



Zweiter Versuch mit mehr Rückmeldungstreffern:

EXPLAIN ANALYZE select tr.usernummer, tr.username, count(*) from (select * from test where datum > current_date-15 ) tr group by tr.usernummer, tr.username;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=993715.93..1008989.16 rows=138848 width=23) (actual time=31183.627..34159.749 rows=4609 loops=1)
-> Sort (cost=993715.93..997187.11 rows=1388475 width=23) (actual time=31183.157..33920.623 rows=1371904 loops=1)
Sort Key: test.usernummer, test.username
Sort Method: external merge Disk: 45488kB
-> Seq Scan on test (cost=0.00..795104.28 rows=1388475 width=23) (actual time=0.018..16067.457 rows=1371904 loops=1)
Filter: (datum > (('now'::cstring)::date - 15))
Rows Removed by Filter: 24228096
Total runtime: 34164.788 ms

------------------------------------------------------

Mal nur das select auf datum...testweise so, dass nichts zurückgeliefert wird:
benchmark=# explain analyze select * from test where datum > current_date-1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..795104.28 rows=2314 width=47) (actual time=14354.454..14354.454 rows=0 loops=1)
Filter: (datum > (('now'::cstring)::date - 1))
Rows Removed by Filter: 25600000
Total runtime: 14354.490 ms

mit index auf datum:

benchmark=# explain analyze select * from test where datum > current_date-1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using idx_datum on test (cost=0.45..8.46 rows=1 width=47) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (datum > (('now'::cstring)::date - 1))
Total runtime: 0.031 ms

Berni
2014-08-23, 23:03:17
Im DB2 ist es so, dass CURRENT DATE in Querys für das DB2 wie eine unbekannte Variable erscheint und somit bei den Ausführungsplänen was ganz Anderes/Schlechtes rauskommt als wie wenn man das Datum wirklich reinsetzt. Evtl. ist das bei Oracle und SYSDATE ähnlich?

Was du auch probieren könntest:
Deine Query mit den 3000 Sätzen in eine "GLOBAL TEMPORARY TABLE" inserten, dort evtl. noch nen Index anlegen und dann den Rest dazujoinen.

Rooter
2014-08-24, 11:58:07
So, konnte das am Freitag endlich testen:

Wenn ich einfach nur EXPLAIN PLAN vor SELECT setze kommt die Fehlermeldung "ORA-00905: missing keyword". Das habe ich aber mit Google noch selbst hingekriegt, es muss heißen: EXPLAIN PLAN FOR

Aber dann kommt die mir unbekannte Fehlermeldung "Error creating cursor handle". Die kommt auch wenn ich es mit "Explain Plan Into PLAN_TABLE For" mache.
Das scheint aus dem WWS heraus also wohl nicht möglich zu sein. :(


Die Variante aus Post #7 (http://www.forum-3dcenter.org/vbulletin/showthread.php?p=10308848#post10308848) mit WITH läuft genauso lange, ca. 3 Minuten.


3 & 5 ) Also wenn du die Abfrage nur aufs Datum machst, d.h. diese 3000 Rows, dann hast du mehr oder weniger sofort ein Ergebnis?So ist es, dauert noch keine Sekunde.

Wie gesagt kann ich die reelle Performance deiner DB ja nicht einschätzen, aber selbst wenn er 3000 mal alles gegen die restlichen Kriterien prüfen muss sollte das imho keine 3 Minuten dauern. Aber es kann natürlich sein... ohne hier Zahlen zu haben, da musst du auch deine Erfahrungswerte ranziehen.Nein, so saulahm ist unsere DB normal nicht. Z.B. die beiden Tabellen mit den Lieferungen an Kunden: Die Tabelle mit den Kopfdaten (Rechnungsadresse, Lieferadresse ect.) hat wohl so an die 100.000 Einträge, die Tabelle mit den zugehörigen Bestellpositionen entsprechend ein vielfaches davon. Wenn ich mit denen arbeite und die beiden über die Auftragsnummer joine, dauern die meisten Abfragen nur ein paar Sekunden.

5) Es geht hauptsächlich darum, dass du - ohne entsprechende Tools - verstehst, was die DB genau mit deinem SQL macht. Hier hilft es oft auch das Query mal mit "Fetch first 1 rows only" laufen zu lassen, sollte z.B. für einen Subselect zuerst ein Tablescan nötig werden dann dauern schon der erste Row lange und die restlichen dann "instant", wenn dieser aber sofort kommt, dann stimmt vermutlich einfach etwas mit den Access paths nicht oder die Stats sind z.B. nicht aktuell, so dass er - obwohl er alle Rows hätte - noch weiter sucht.Bin nicht sicher ob ich dich richtig verstanden habe aber ein
Select * From TRANSAKT Where Rownum < 3000
liefert binnen weniger Millisekunden die ersten 3000 Zeilen der Tabelle.

Was du auch probieren könntest:
Deine Query mit den 3000 Sätzen in eine "GLOBAL TEMPORARY TABLE" inserten, dort evtl. noch nen Index anlegen und dann den Rest dazujoinen.Kann ich nicht, siehe oben, ich kann keine Tabellen erstellen, habe keine Schreibmöglichkeit auf die DB.

MfG
Rooter

Berni
2014-08-24, 21:52:57
Eine Global Temporary Table ist eine temporäre Tabelle die nur innerhalb der Session existiert und danach automatisch wieder verschwindet. Die kann man oft auch erstellen, ohne dass man "richtige" Tabellen erstellen kann. Hängt aber natürlich von der konkreten Einstellung/Rechtevergabe ab.

Rooter@work
2014-08-26, 10:46:49
Es geht doch! =)
In unserer Software gibt es unten einen kleinen Haken "ExecSQL", von dem ich nie wusste wofür er gut ist...
Mit dem drin geht es mit "Explain Plan For..." und ich kann die Tabelle PLAN_TABLE auslesen. Bitte schön:
(leider nur als von Excel exportierter Text :()

Operation Options Object-Name Object-Instance Object-Type Optimizer Search-Columns Id Parent-Id Position Access-Predicates Filter-Predicates
SELECT STATEMENT CHOOSE 0
SORT ORDER BY 1 0 1
SORT GROUP BY 2 1 1
CONCATENATION 3 2 1
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 4 3 1 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 5 4 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 6 5 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 7 5 2 "TRANSAKT"."ZIEL"='WaitPacWW2'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 8 3 2 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 9 8 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 10 9 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 11 9 2 "TRANSAKT"."ZIEL"='WaitPacWW2'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 12 3 3 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 13 12 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 14 13 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 15 13 2 "TRANSAKT"."ZIEL"='WaitPacWW1'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 16 3 4 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 17 16 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 18 17 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 19 17 2 "TRANSAKT"."ZIEL"='WaitPacWW1'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 20 3 5 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 21 20 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 22 21 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 23 21 2 "TRANSAKT"."ZIEL"='WaiPacRepK'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 24 3 6 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 25 24 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 26 25 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 27 25 2 "TRANSAKT"."ZIEL"='WaiPacRepK'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 28 3 7 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 29 28 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 30 29 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 31 29 2 "TRANSAKT"."ZIEL"='WaitRechWW'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 32 3 8 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 33 32 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 34 33 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 35 33 2 "TRANSAKT"."ZIEL"='WaitRechWW'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 36 3 9 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 37 36 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 38 37 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 39 37 2 "TRANSAKT"."ZIEL"='EntsorgWW'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 40 3 10 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 41 40 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 42 41 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 43 41 2 "TRANSAKT"."ZIEL"='EntsorgWW'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 44 3 11 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 45 44 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 46 45 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 47 45 2 "TRANSAKT"."ZIEL"='WAWaitWW'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 48 3 12 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 49 48 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 50 49 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 51 49 2 "TRANSAKT"."ZIEL"='WAWaitWW'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 52 3 13 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 53 52 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 54 53 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 55 53 2 "TRANSAKT"."ZIEL"='Endkontr'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 56 3 14 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 57 56 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 58 57 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 59 57 2 "TRANSAKT"."ZIEL"='Endkontr'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 60 3 15 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 61 60 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 62 61 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 63 61 2 "TRANSAKT"."ZIEL"='ExportWA'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 64 3 16 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 65 64 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 66 65 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 67 65 2 "TRANSAKT"."ZIEL"='ExportWA'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 68 3 17 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 69 68 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 70 69 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 71 69 2 "TRANSAKT"."ZIEL"='EntsorgAU'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 72 3 18 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 73 72 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 74 73 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 75 73 2 "TRANSAKT"."ZIEL"='EntsorgAU'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 76 3 19 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 77 76 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 78 77 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 79 77 2 "TRANSAKT"."ZIEL"='ATestAU'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 80 3 20 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 81 80 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 82 81 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 83 81 2 "TRANSAKT"."ZIEL"='ATestAU'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 84 3 21 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 85 84 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 86 85 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 87 85 2 "TRANSAKT"."ZIEL"='WaiPacVCR2'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 88 3 22 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 89 88 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 90 89 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 91 89 2 "TRANSAKT"."ZIEL"='WaiPacVCR2'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 92 3 23 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 93 92 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 94 93 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 95 93 2 "TRANSAKT"."ZIEL"='WaiPacVCR1'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 96 3 24 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 97 96 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 98 97 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 99 97 2 "TRANSAKT"."ZIEL"='WaiPacVCR1'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 100 3 25 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 101 100 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 102 101 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 103 101 2 "TRANSAKT"."ZIEL"='WaiPackRep'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 104 3 26 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 105 104 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 106 105 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 107 105 2 "TRANSAKT"."ZIEL"='WaiPackRep'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 108 3 27 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 109 108 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 110 109 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 111 109 2 "TRANSAKT"."ZIEL"='WaiEKRepKO'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 112 3 28 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 113 112 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 114 113 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 115 113 2 "TRANSAKT"."ZIEL"='WaiEKRepKO'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 116 3 29 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 117 116 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 118 117 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 119 117 2 "TRANSAKT"."ZIEL"='WaiEKRepGA'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 120 3 30 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 121 120 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 122 121 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 123 121 2 "TRANSAKT"."ZIEL"='WaiEKRepGA'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 124 3 31 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 125 124 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 126 125 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 127 125 2 "TRANSAKT"."ZIEL"='WaitRech'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 128 3 32 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 129 128 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 130 129 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 131 129 2 "TRANSAKT"."ZIEL"='WaitRech'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 132 3 33 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 133 132 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 134 133 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 135 133 2 "TRANSAKT"."ZIEL"='Entsorg'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 136 3 34 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 137 136 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 138 137 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 139 137 2 "TRANSAKT"."ZIEL"='Entsorg'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 140 3 35 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 141 140 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 142 141 1 "TRANSAKT"."BEARBEITER"='$PreRepWW'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 143 141 2 "TRANSAKT"."ZIEL"='WAWait'
TABLE ACCESS BY INDEX ROWID TRANSAKT 1 144 3 36 "TRANSAKT"."DATUM">SYSDATE@!-.5
AND-EQUAL 145 144 1
INDEX RANGE SCAN TRANSAKT_BEARBEITER NON-UNIQUE 1 146 145 1 "TRANSAKT"."BEARBEITER"='$PreRep'
INDEX RANGE SCAN TRANSAKT_ZIEL NON-UNIQUE 1 147 145 2 "TRANSAKT"."ZIEL"='WAWait'


MfG
Rooter

Rooter@work
2014-08-26, 12:02:28
Nachtrag:
Ja, ich weiß, dass ich im Startpost bei "ZIEL In ()" einige Einträge unterschlagen habe. Sollte der Übersicht dienen. :redface: Jetzt ärgert mich das denn mit nur ein oder zwei Einträgen hinter ZIEL geht es deutlich schneller. Sorry für die Verwirrung.
Gibt es dennoch einen Weg das zu beschleunigen? :smile:

MfG
Rooter

Exxtreme
2014-08-26, 13:11:48
Wie ich schon schrieb, Indizes auf ZIEL und BEARBEITER würden wohl helfen. Nur wenn du das nicht machen kannst dann ... blöd. Ansonsten könnte noch eine Aktualisierung der Statistiken was bringen falls du die Berechtigung dazu hast.

sei laut
2014-08-26, 22:49:31
Feuer deine Anfrage mal ohne den "group by" Part ab. Ich hab kaum Ahnung von der Materie, aber diese Zeile kann eine Datenbank dazu bringen, im Kreis zu hüpfen, sich auf den Kopf zu stellen und dir die Zunge rauszustrecken.

Eine temp Table sollte aber nicht helfen - die Datenbank müsste so intelligent sein, eine selbst zu erstellen, wenn es nötig wäre.

(wenn du aber in der Lage bist, da noch einen Index reinzuhämmern wie hier schon geraten, mache erstmal das)

Asaraki
2014-08-27, 00:45:12
Wieder mal : Kein Oracle experte, aber das liest sich doch so, als würde alles über Indizes abgehandelt, dann sollte das schneller gehen. Meine aktuelle Topvermutung, die Statistiken sind nicht up to date.

Hast du in der Firma keine DB-Abteilung, denen du das mal schildern kannst und fragen kannst, ob die auch der Meinung sind, dass das schneller laufen sollte?

EDIT: Derart viele Schlüsselwörter im IN sind natürlich nicht optimal, fernab davon... sehr riskant, da du das Query aktuell halten muss, aber wäre ein NOT IN nicht fast schneller? Soviele andere Schlüsselwörter kanns in der Spalte doch garnicht mehr geben ^^ Punkt ist, dass er dann alles liest, ein paar skipt, und den rest auch liest. Kann je nachdem massiv schneller sein, aber ist EIGENTLICH nicht zu empfehlen, da es sehr anfällig auf unsaubere Tabellen ist.

Rooter@work
2014-08-27, 09:34:31
Ansonsten könnte noch eine Aktualisierung der Statistiken was bringen falls du die Berechtigung dazu hast.Und würde sowas gehen? :) Bei meinen beschränkten Möglickhkeiten überhaupt?

Feuer deine Anfrage mal ohne den "group by" Part ab. Ich hab kaum Ahnung von der Materie, aber diese Zeile kann eine Datenbank dazu bringen, im Kreis zu hüpfen, sich auf den Kopf zu stellen und dir die Zunge rauszustrecken.Hab's ausprobiert, ändert nichts an der Laufzeit, immer ca. 160 Sekunden.

Hast du in der Firma keine DB-Abteilung, denen du das mal schildern kannst und fragen kannst, ob die auch der Meinung sind, dass das schneller laufen sollte?Wenn wir sowas hätten gabe es diesen Thread ja nicht. ;) Unser Admin z.B. hat mit dem WWS und der Oracle DB nichts am Hut, der kümmert sich nur um die nötige Infrastruktur.

EDIT: Derart viele Schlüsselwörter im IN sind natürlich nicht optimal, fernab davon... sehr riskant, da du das Query aktuell halten muss, aber wäre ein NOT IN nicht fast schneller? Soviele andere Schlüsselwörter kanns in der Spalte doch garnicht mehr geben ^^ Punkt ist, dass er dann alles liest, ein paar skipt, und den rest auch liest. Kann je nachdem massiv schneller sein, aber ist EIGENTLICH nicht zu empfehlen, da es sehr anfällig auf unsaubere Tabellen ist.Grad mal geschaut: Es gibt 467 "Ziele". ;)

MfG
Rooter

Berni
2014-08-28, 02:17:35
Wenn dus so in der Art probierst:
Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From (Select t.*, CASE WHEN ZIEL In ('WAWait','WAWaitWW') Or BEARBEITER In ('$PreRep','$PreRepWW') Then 1 Else 0 End as Check
From TRANSAKT t
Where DATUM > sysdate -1) tr
Where tr.Check = 1
Group By tr.USERNUMMER, tr.USERNAME
Alternativ wenn das noch nichts bringt so
Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From (Select t.*, CASE WHEN ZIEL In ('WAWait','WAWaitWW') Or BEARBEITER In ('$PreRep','$PreRepWW') Then 1 Else 0 End as Check,
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
From TRANSAKT T
Where DATUM > sysdate -1) tr
Where tr.Check = 1 AND RN > 0
Group By tr.USERNUMMER, tr.USERNAME
Damit sollte es möglich sein, die reine Datumseinschränkung (welche ja schnell ist laut deiner Aussage) zuerst zu forcieren und dann quasi darauf nur noch zu filtern was bei den angesprochenen 3000 Sätzen ja wohl kein Ding sein sollte...ist zwar nicht die feine Art aber vielleicht klappts ja.

Marscel
2014-08-28, 02:22:00
Immer schön zu sehen, wie sehr man SQL ad absurdum führen kann. Primitive Probleme mit Lösungen beantworten, die man mit SQL als Sprache eigentlich komplett verstecken will :tongue:

Rooter@work
2014-08-29, 12:36:07
Wenn dus so in der Art probierst:
Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From (Select t.*, CASE WHEN ZIEL In ('WAWait','WAWaitWW') Or BEARBEITER In ('$PreRep','$PreRepWW') Then 1 Else 0 End as Check
From TRANSAKT t
Where DATUM > sysdate -1) tr
Where tr.Check = 1
Group By tr.USERNUMMER, tr.USERNAME

...

Damit sollte es möglich sein, die reine Datumseinschränkung (welche ja schnell ist laut deiner Aussage) zuerst zu forcieren und dann quasi darauf nur noch zu filtern was bei den angesprochenen 3000 Sätzen ja wohl kein Ding sein sollte...ist zwar nicht die feine Art aber vielleicht klappts ja.Dafaq, dauert nur noch 2 Sekunden! :eek: Und das Ergebnis stimmt, hab's mit meiner Version vergleichen. =)

Aber jetzt erklär' mir mal bitte warum deine Version mit dem Flag "Check" funktioniert, meine einfache Unterabfrage aber überhaupt nichts bringt!? :confused:

MfG
Rooter

Asaraki
2014-08-29, 12:51:17
Sollte eigentlich offensichtlich sein, sobald du nochmal einen EXPLAIN ausführst.
Was passieren sollte - denke ich - durch die zusätzliche Logik im Subselect muss das DMBS nun zuerst den Subselect komplett ausführen und als temporary table bereithalten. Dieses läuft auch schnell, da das DATUM die einzig Where-Bedigung ist und entsprechend als erstes ausgeführt wird, und auf diese Submenge dann der Case angewendet wird, welcher ja ebenfalls alle Rows betrifft (jeder erhält ein 0 oder 1) und deshalb sequentiell abgearbeitet werden kann.
Auf diese Zwischenmenge wird nun nur noch einmal zugegriffen vom Parentquery mit Check = 1 und anschliessend gezählt und gruppiert.

In deiner Version sieht es eben so aus, dass er pro "ZIEL" das Query einmal laufen gelassen hat, etwas vereinfacht ausgedrückt.

2 Sekunden auch wenn du alle Ziele und Bearbeiter eingetragen hast?

auf jeden fall cool ist es jetzt schneller :)

HIJACK : weil sich hier ja doch Leute tummeln die sich gut auskennen, kann jemand ein Buch zu DB2 empfehlen, darf gerne V11 sein?

Gast
2014-08-29, 15:35:12
Sollte eigentlich offensichtlich sein, sobald du nochmal einen EXPLAIN ausführst.
Was passieren sollte - denke ich - durch die zusätzliche Logik im Subselect muss das DMBS nun zuerst den Subselect komplett ausführen und als temporary table bereithalten.Also wird es durch die eigene Zusatzspalte "Check" gezwungen eine temporary table anzulegen!?
Werde ich mir merken, diesen Trick.

2 Sekunden auch wenn du alle Ziele und Bearbeiter eingetragen hast?Jap! :)

MfG
Rooter

Exxtreme
2014-08-29, 16:50:41
Also wird es durch die eigene Zusatzspalte "Check" gezwungen eine temporary table anzulegen!?
Werde ich mir merken, diesen Trick.

Jap! :)

MfG
Rooter
Normalerweise erkennt das der Optimizer und würde auch eine temporäre Tabelle anlegen. Es sei denn, die Statistiken stimmen nicht. Dann knallt er u.U. Fulltablescans rein und das dauert ewig.

Um Statistiken zu aktualisieren hat Oracle den Befehl "dbms_stats.gather_table_stats" bzw. "dbms_stats.gather_index_stats". Nur wie schon geschrieben, dazu braucht man aber wohl spezielle Berechtigungen da man in Systemtabellen reinschreiben muss.

Hier gibt es eine Doku von Oracle:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1045518

Leider kann ich keine brauchbaren Beispiele liefern da ich keine Oracle-DBs betreue.

Rooter
2014-08-30, 16:43:34
Habe mal ein bisschen mit Bernis Variante rumgespielt weil ich die sicher in Zukunft noch mal öfter brauchen werde. Hab's aber wohl noch nicht vollständig kapiert. :(

Wenn ich z.B. nur ZIEL im Subquery lasse und BEARBEITER ins Parentquery packe, dauert es wieder unverändert knappe 3 Minuten. Warum das jetzt? :confused:
Select tr.USERNUMMER,
tr.USERNAME,
Count(*)
From (Select t.*, CASE WHEN ZIEL In ('WAWait','WAWaitWW') Then 1 Else 0 End as Check
From TRANSAKT t
Where DATUM > sysdate -1) tr
Where tr.Check = 1
And BEARBEITER In ('$PreRep','$PreRepWW')
Group By tr.USERNUMMER, tr.USERNAME



Um Statistiken zu aktualisieren hat Oracle den Befehl "dbms_stats.gather_table_stats" bzw. "dbms_stats.gather_index_stats". Nur wie schon geschrieben, dazu braucht man aber wohl spezielle Berechtigungen da man in Systemtabellen reinschreiben muss.

Hier gibt es eine Doku von Oracle:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1045518Nee, da lasse ich dann lieber die Finger davon. Selbst wenn ich nötigen Tools auf meinem Arbeitsrechner schon habe ("SQL+"?) oder nachinstallieren kann will ich es nicht riskieren dabei etwas zu zerschießen.

In der von dir verlinkten Doku habe ich aber etwas viel interessanteres gefunden: INDEX Hint (http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#BABEFDFC)

Sollte auf dem Wege nicht durch ein einfaches
Select /*+ INDEX (tr DATUM)*/ tr.USERNUMMER,
tr.USERNAME,
Count(*)
From From TRANSAKT tr
Where tr.ZIEL In ('WAWait','WAWaitWW')
And tr.BEARBEITER In ('$PreRep','$PreRepWW')
And DATUM > sysdate -1
Group By tr.USERNUMMER, tr.USERNAME
der FullTableScan verhindert werden!?
Eigentlich reicht doch sogar /*+ INDEX (tr)*/ !? :)

MfG
Rooter

samm
2014-08-30, 17:01:57
Wenn ich z.B. nur ZIEL im Subquery lasse und BEARBEITER ins Parentquery packe, dauert es wieder unverändert knappe 3 Minuten. Warum das jetzt? :confused:
Es gibt dadurch jetzt wieder eine Bedingung auf eine nicht indizierte Spalte, und vielleicht kommt der Optimizer dummerweise auf die Idee, zuerst die Transakt-Resultate nach dieser Bedingung zu filtern statt nach DATUM und danach der Check-Spalte. Wenn er letzeres tun würde, sollte diese Query-Form doch noch etwas schneller sein als die un-optimierte, weil ZIEL kein Filterkriterium mehr ist.

/*+ INDEX (tr)*/ Sieht für mich nach einem Kommentar aus... Was soll dies bewirken?

fezie
2014-08-30, 17:13:34
Siehe den Link von ihm zu den Index Hints
Damit beeinflusst man den Query Optimizer

Exxtreme
2014-08-30, 18:28:47
Nee, da lasse ich dann lieber die Finger davon. Selbst wenn ich nötigen Tools auf meinem Arbeitsrechner schon habe ("SQL+"?) oder nachinstallieren kann will ich es nicht riskieren dabei etwas zu zerschießen.

Mit Statistiken aktualisieren zerschiesst du nichts. Das Ding tut nichts anderes als die Tabellen in die Daten darin zu analysieren bezüglich Anzahl der Datensätze, der Kardinalität der Daten in einer Spalte, Indexfragmentierung etcpp. Und diese Statistiken werden dann in einer internen Tabelle gespeichert.

Was passieren kann ist, dass schlechtes Design der eigentlichen Software u.U. sichtbar wird. Beispiel: wenn eine Liste in der Software nach bestimmten Kriterien sortiert ist dann heisst es nicht, dass die Software das so aufruft sondern weil die Daten zufällig so aus der DB kommen. Mit einer Aktualisierung der Statistiken kann es passieren, dass der Optimizer einen schnelleren Ausführungsplan wählt und die Daten dann in einer anderen Reihenfolge aus der DB kommen. Sortiert die Software explizit nichts dann ist die Liste hinterher durcheinander.

Darkstar
2014-08-31, 00:43:51
Bei Oracle sollte man immer die zur verwendeten DB-Server-Version passende Dokumentation verwenden. Für 9.2 wäre das die hier:
Gathering Optimizer Statistics (http://docs.oracle.com/cd/B10500_01/server.920/a96533/stats.htm) INDEX Hint (http://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#5156)Von Version zu Version verhält sich der Optimizer teilweise komplett anders.
Das Berechnen der Statistiken hat witzigerweise Einfluß auf den Optimizer (Understanding Optimizer Hints (http://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#4752)):
Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.
Das Berechnen der Statistiken an sich ist übrigens nicht risikoreich – eher im Gegenteil: Veraltete Statistiken können sogar zu fehlerhaften Abfrageergebnissen führen. Normalerweise würde man das Berechnen der Statistiken regelmäßig z. B. über einen Job (http://docs.oracle.com/cd/B10501_01/server.920/a96521/jobq.htm) durchführen lassen.
Da es sich beim Oracle-DB-Server um ein hochkomplexes Stück Technik handelt, kann es natürlich auch sein, daß man von einem Bug betroffen ist. Da hilft dann nur noch, auf gut Glück das letzte Patchset einzuspielen oder eben den Oracle-Support zu bemühen.

Rooter@work
2014-09-01, 08:56:59
Moin,

also /*+ INDEX(tr) */ funktioniert aus unserem Editor wohl nicht, dauert jedenfalls wieder ewig. :rolleyes:

Ist aber wohl alles noch viel schlimmer als ich dachte, ich habe mir mal DBA_TABLES und DBA_INDEXES angesehen:
Global-Stats NO
User-Stats NO
Last-Analyzed NULL
:facepalm:

MfG
Rooter

PHuV
2014-09-01, 11:21:01
Da es sich beim Oracle-DB-Server um ein hochkomplexes Stück Technik handelt, kann es natürlich auch sein, daß man von einem Bug betroffen ist. Da hilft dann nur noch, auf gut Glück das letzte Patchset einzuspielen oder eben den Oracle-Support zu bemühen.

Für Oracle 9i und 10g gibt es keinen Support mehr.

Rooter
2017-06-03, 16:13:58
Ich nutze mal das Schäufelchen:

Bisher gab es bei uns keinerlei Änderungen: Nach wie vor nutzen wir Oracle SQL 9.2, nach wie vor wurden keine Statistiken gefahren (obwohl ich meinen Abteilungsleiter mehrfach darauf hin wies, ich glaube aber nicht, dass er verstanden hat was ich da von ihm wollte X-D), nach wie vor ist die Performance beschissen.

Habe kürzlich mit jemand geredet, der als Programmierer bei einem großen Energieversorger arbeitet. Dort haben sie Datenbanken bis zu 2TB Größe, da die Statistiken aber jede Nacht aktualisiert werden, läuft deren längste Abfrage eine viertel Stunde.
Unsere Datenbank ist gerade mal 10GB groß aber unsere längste Abfrage, der Rechnungslauf der Hotline immer zum Monatsende, läuft ganze 3 Tage! :eek: In dieser Zeit ist die Performance natürlich noch viel beschissener als sonst.

Letzte Woche zeigte mir mein Abteilungsleiter nicht ohne Stolz die Testumgebung unserer kommenden "neuen" Datenbank -- "neu" bedeutet hier Oracle 11. :rolleyes: Begeistert war er vor allem weil der Rechnungslauf plötzlich nur noch 20 Minuten dauerte. Ich bat ihn mal DBA_TABLES und DBA_INDEXES aufzulisten und siehe da: In der Testumgebung sind die Statistiken generiert. Ich spekuliere mal, dass das eher für den Geschwindigkeitsboost um Faktor 200 verantwortlich ist, als der Sprung von Oracle 9 zu 11... :usad:

MfG
Rooter

Exxtreme
2017-06-04, 11:06:18
Naja, Faktor 200 wegen Statistiken kann ich mir kaum vorstellen. X-D

Da dürften noch einige andere Dinge im Argen sein. Z.B. dass die Tabellen und Indizes nie reorganisiert wurden etc. Bei Oracle sollen Indizes auch kaputt gehen können und werden dann nicht benutzt. Könnte auch noch zusätzlich der Fall sein.

Rooter
2017-06-04, 11:17:59
Vom Faktor her würde es aber zu meinem Problem hier aus dem Thread passen, die Abfrage wurde ja auch um Faktor 200-300 schneller nachdem sie zwingend den Index nutzen musste. Und ich weiß, dass der Rechnungslauf auch mit dieser Tabelle arbeitet.

Ist mir natürlich klar, dass das alles nur Spekulation ist.

MfG
Rooter

Gnafoo
2017-06-04, 11:54:12
Naja, Faktor 200 wegen Statistiken kann ich mir kaum vorstellen. X-D

Da dürften noch einige andere Dinge im Argen sein. Z.B. dass die Tabellen und Indizes nie reorganisiert wurden etc. Bei Oracle sollen Indizes auch kaputt gehen können und werden dann nicht benutzt. Könnte auch noch zusätzlich der Fall sein.

Naja warum nicht? Wenn die Statistiken fehlen, dann entscheidet die DB evtl., dass ein Full-Table-Scan schneller ist, als den Index zu benutzen, weil sie nicht weiß, dass da mehr als nur ein paar hundert Einträge drin sind.

Ich hatte es bei PostgreSQL zumindest durchaus schon, dass erst das Updaten der Statistikdaten die DB dazu gebracht hat, einen Index zu benutzen, den sie vorher ignoriert hat. War dann zwar "nur" ein Unterschied von ein paar Millisekunden vs. 2-3 Sekunden, aber das kann im größeren Maßstab sicher auch passieren.

Asaraki
2017-06-04, 12:47:57
Naja, Faktor 200 wegen Statistiken kann ich mir kaum vorstellen. X-D

Da dürften noch einige andere Dinge im Argen sein. Z.B. dass die Tabellen und Indizes nie reorganisiert wurden etc. Bei Oracle sollen Indizes auch kaputt gehen können und werden dann nicht benutzt. Könnte auch noch zusätzlich der Fall sein.

Selbst Faktor 10000 geht locker nur wegen miesen/fehlenden Statistiken, hab ich wöchentlich.

Exxtreme
2017-06-04, 15:44:49
Ahh, OK. X-D
Ich glaube, das Höchste was ich mal hatte war Faktor 10 oder so.

Asaraki
2017-06-04, 16:06:22
Ahh, OK. X-D
Ich glaube, das Höchste was ich mal hatte war Faktor 10 oder so.

Das Problem sind ja auch nicht Statistiken, die einen Tag veraltet sind, auf einer Tabelle, die einem Normalbetrieb unterliegt.
Aber ein Klassiker ist z.B. eine Tab, die nur so 5000 Rows hat zum testen, dann alles gut und die wird mit 500m Rows befüllt und der "super" User lädt ohne Statistics. Dann meint das System, die Tabelle sei winzig, benutzt aus Prinzip(!) keine Indizes und schon hast du den Spass. Wobei da viele moderne Systeme Mechanismen haben solche Dinge zu erkennen und es dann zu korrigieren, da kann dann durchaus auch ein Versionsupgrade in Konstellation X einen Boost von 100'000% kriegen, wird dann gerne für die schönen theoretischen Graphen benutzt in den Marketingpräsentation, da nehmen sich die grossen auch nichts ^^

In der Realtität haben veraltete Statistiken (ganz fehlen sollten sie sowieso nie ^^) aber eher im Prozentbereich Einfluss.

Bei Oracle ist das sowieso alles nochmal ein bisschen perfider, da es extensive Möglichkeiten bietet sich seine Zugriffe zurecht zu drehen, welche aber naturgemäss nicht für immer passen können und dann kommst du mit dem flicken nicht mehr hinterher. Bei uns gibt's in der ganzen Firma so gut wie keine einzige manuelle Manipulation eines Zugriffspfads (OPTHINT heisst das bei DB2), da man diese eigentlich gar nie benötigen sollte, sonst hat man nämlich Fehler im Design oder in den Abfragen. Und die wenigen, die wir haben gehen meist auf effektive Fehler im DB-System zurück und sind von mir/uns abgsegnet und werden laufend kontrolliert.

littlejam
2017-06-04, 17:16:24
Also eine Query, die 3 Tage läuft für eine DB von 10 GB... hätte nicht gedacht, dass sowas überhaupt möglich ist.
Bei einem Full Table Scan über alles würde das flotte 40kb/s bedeuten.

Grüße

Asaraki
2017-06-04, 17:25:04
Also eine Query, die 3 Tage läuft für eine DB von 10 GB... hätte nicht gedacht, dass sowas überhaupt möglich ist.
Bei einem Full Table Scan über alles würde das flotte 40kb/s bedeuten.

Grüße

Kb/s sind eine unbrauchbare Größe hier, erst Recht wenn du diese Milchmädchenrechnung machst. Vermutlich gibts viel direct row access aus einem join raus mit gefühlten 10 IOs pro row und schon kumuliert sich das hoch.
Praxisbeispiel : Ein Entwickler schaffte es, sage uns schreibe ca. 25GB zu lesen (und 25 zu schreiben ;)) aus einem Join über ca. 0.5GB Daten.

Rooter
2017-06-04, 18:01:38
Also eine Query, die 3 Tage läuft für eine DB von 10 GB... hätte nicht gedacht, dass sowas überhaupt möglich ist.
Bei einem Full Table Scan über alles würde das flotte 40kb/s bedeuten.So einfach kannst du das wirklich nicht rechnen. Das werden sicher tausende Full Table Scans sein. X-D

Vermutlich gibts viel direct row access aus einem join raus mit gefühlten 10 IOs pro row und schon kumuliert sich das hoch.Ja, auch meine Vorgesetzten joinen gerne und viel! Da werden z.B. 6 Tabellen verjoint, obwohl es 2 auch getan hätten, nur weil man aus den anderen 4 jeweils eine Ergebnisspalte braucht. :rolleyes:
Diese Leute sind ja keine Programmierer sondern Autodidakten was SQL betrifft. Das bin ich zwar selbst auch aber ich habe früher immerhin BASIC und Assembler programmiert und vor allem versuche ich zumindest optimierte Abfragen zu schreiben. Mein Abteilungsleiter wäre z.B. mit der 2-3 Minuten dauernden Abfrage aus dem Startpost vollkommen zufrieden gewesen (er sagte mir mal: "Zwei Minuten für eine Abfrage sind ja nicht lang"...).

MfG
Rooter

littlejam
2017-06-04, 20:29:33
Ist schon klar, dass man das nicht so einfach rechnen kann, bin aber schon beeindruckt darüber.
Habe auch schon Einiges* gesehen, und die Leidensfähigkeit ist gefühlt im DB-Sektor besonders hoch.

Die 40kb waren eher best-case wenn quasi alles von Platte einmal sequenziell geholt werden müsste.
Nun stelle ich mir vor, dass die 10GB im RAM liegen, was da passieren muss, dass das 3 Tage dauert ist schon sportlich.

*Persönliches Highlight war eine Query von mehreren 100 KB, gibt sicher Krasseres, aber fand ich schon interessant.

Grüße

Asaraki
2017-06-04, 23:11:28
Ist schon klar, dass man das nicht so einfach rechnen kann, bin aber schon beeindruckt darüber.
Habe auch schon Einiges* gesehen, und die Leidensfähigkeit ist gefühlt im DB-Sektor besonders hoch.

Die 40kb waren eher best-case wenn quasi alles von Platte einmal sequenziell geholt werden müsste.
Nun stelle ich mir vor, dass die 10GB im RAM liegen, was da passieren muss, dass das 3 Tage dauert ist schon sportlich.

*Persönliches Highlight war eine Query von mehreren 100 KB, gibt sicher Krasseres, aber fand ich schon interessant.

Grüße

Wie gesagt, du rechnest das wirklich falsch. Mal angenommen in den 10 GB liegen zwei gleich grosse Tabellen mit 1kb-Rows, dann wären wir bei ~5Mio Rows pro Tabelle. Also nichts dramatisches.
Ein schlechter Join kann aus dem nun eine Arbeitsmenge von 5Mio * 5Mio Rows machen, also 25 Billionen Rows a je 1 kB, wir sind also jetzt schon bei 23'000 TERRA an Daten, die theoretisch gelesen werden müssen. Jetzt haben wir dafür nunmal nicht genug Ram, das heisst die I/Os vervielfachen sich noch einmal und um jetzt wirklich thoereitsch und unrealistisch zu bleiben :
3 Tage, 23'000 Terra... gar nicht so langsam das System mit 90GB/s :-)

Das jetzt nur um zu zeigen, dass die Rechnung, so wie von dir gemacht, eben nicht einmal zum Spass funktioniert. Es gibt keinen direkten Zusammenhang zwischen Datenmenge und Menge der gelesenen Daten. Und von Orders, Distincts und sonstigen Spässen reden wir noch garnicht, dann verdoppelt sich das ja mindestens nochmal :D

Eine schöne Faustregel :
Ein gutes Query auf eine gute, aber gigantische Tabelle : Super Performance
Ein schlechtes Query auf eine schlechte und winzige Tabelle : Bäääääh
Die grösse einer Tabelle hat bei richtiger Verwendung für den User keine spürbaren Performancenachteile, das müssen alles wir Admins ausbaden, weshalb "wir" nicht unbedingt Freunde riesiger einzelner Objekte sind (Stichwort Recovery)

Rooter
2017-06-05, 00:45:50
Nun stelle ich mir vor, dass die 10GB im RAM liegen, was da passieren muss, dass das 3 Tage dauert ist schon sportlich.Wie gesagt, viele, viele Full Table Scans weil der Queryoptimizer ohne Statistiken zum Index sagt "Ach nöööööö..." ;D

Was den RAM angeht: Der Typ von dem Energieversorger, mit dem ich gesprochen hatte, meinte sie seien inzwischen bei 99% Cache Hits angelangt. Wie das bei uns aussieht will ich lieber gar nicht wissen...

Teilweise dauert ja sogar das bloße Öffnen einer Maske in unserem WWS mehrere Sekunden -- in Einzelfällen auch mal 15-20 Sekunden! Mein Abteilungsleiter erklärte mir das damit, dass auf der Seite ja mehrere Tabellen dargestellt werden müssen... das stimmt zwar, die Seite ist aber nach dem Öffnen noch leer, da wurden noch gar keine Daten abgerufen. :rolleyes:

Ein schlechter Join kann aus dem nun eine Arbeitsmenge von 5Mio * 5Mio Rows machen, also 25 Billionen Rows a je 1 kB, wir sind also jetzt schon bei 23'000 TERRA an Daten, die theoretisch gelesen werden müssen.Ich glaube so doof ist die Datenbank nicht, das wirklich zu so einer Menge anwachsen zu lassen.

MfG
Rooter

Asaraki
2017-06-05, 13:02:46
Ich glaube so doof ist die Datenbank nicht, das wirklich zu so einer Menge anwachsen zu lassen.

MfG
Rooter

Natürlich nicht, die Wahrheit liegt irgendwo zwischen den zwei realitätsfremden best&worst case Szenarios. Sollte mehr ein Beispiel sein, wie man schnell mal auf Faktoren mit vielen Nullen kommt :-)

Haarmann
2017-06-05, 14:02:34
Liegt ein Index aufm Datum?

Würd ich sonst mal legen...

Exxtreme
2017-06-05, 14:17:55
Er hat die Berechtigungen dazu offenbar nicht und der IT-Leiter scheint beratungsresistent und merkbefreit zu sein.

Rooter
2017-06-05, 15:31:06
Liegt ein Index aufm Datum?

Würd ich sonst mal legen...Ja, der wird aber wegen nicht existierender Statistiken nicht verwendet.

Er hat die Berechtigungen dazu offenbar nichtStimmt.
und der IT-Leiter scheint beratungsresistent und merkbefreit zu sein.Das auch. :biggrin: Aber unser Admin ist nicht für die DB zuständig bzw. er sorgt nur dafür, dass der Server läuft. Die DB wird vom Hersteller des WWS betreut.

MfG
Rooter

Haarmann
2017-06-06, 19:54:29
Rooter

Ich mag Deinen Admin - ich betreu nen Ora 10 Sys... genau so. Lasst mich mit dem Inhalt in Ruhe. Ich sichere - ich sorge fürn Betrieb - ich weiss es funktioniert scheisse.

Exxtreme

Ich denke frei

IT Leiter studierte BWL - einst...

IT Leiter scheint beratungsresistent und merkbefreit zu sein.

qed

Asaraki
2017-06-06, 20:43:14
Ich nehme nicht genug Drogen um das posting zu verstehen :D

littlejam
2017-06-06, 22:46:37
Wie gesagt, viele, viele Full Table Scans weil der Queryoptimizer ohne Statistiken zum Index sagt "Ach nöööööö..." ;D

10 GB ist nix, selbst mit Full Table Scans.
Asaraki hats ja schon groß gerechnet.
Werden wohl riesige Kreuztabellen sein oder einfach ein schlechtes Schema; gut möglich, dass da SQL nicht das richtige Mittel ist.

Was den RAM angeht: Der Typ von dem Energieversorger, mit dem ich gesprochen hatte, meinte sie seien inzwischen bei 99% Cache Hits angelangt. Wie das bei uns aussieht will ich lieber gar nicht wissen...

Das Workingset sollte immer im Speicher sein.
Es gibt schon auf den ersten Blick komische Hacks um das zu erreichen.
z.B. cat x > /dev/null auf alle Files oder select * from y
Eleganter gehts aber auch, z.B. lru_dump/restore
Keine Ahnung obs was ähnliches bei Oracle gibt, bin in der MySQL-Welt unterwegs.

Teilweise dauert ja sogar das bloße Öffnen einer Maske in unserem WWS mehrere Sekunden -- in Einzelfällen auch mal 15-20 Sekunden! Mein Abteilungsleiter erklärte mir das damit, dass auf der Seite ja mehrere Tabellen dargestellt werden müssen... das stimmt zwar, die Seite ist aber nach dem Öffnen noch leer, da wurden noch gar keine Daten abgerufen. :rolleyes:

Kann gut sein, dass da 'ne menge Overhead im Hintergrund ist.
Oder die DB ist einfach kacke :freak:

Ich nehme nicht genug Drogen um das posting zu verstehen :D

Ohne jetzt die Rollen bei Rooter zu kennen find ich die Haltung im Gegensatz zu Haarmann vom Admin nicht gut.
Dieserlei Haltung im Sinne von "ich mach meinen Scheiß und guck nicht nach links oder rechts" ist nicht mein Naturell.
Schlimmer ist nur noch "ich mach was mir gesagt wurde".
Das kann ich nur schwer ertragen.

Beim 2. Teil bin ich raus ;D

Gruß

Rooter
2017-06-06, 23:02:19
10 GB ist nix, selbst mit Full Table Scans.Wir haben das System seit ~2003 und ich gehe davon aus, dass seitdem neben der DB auch das OS und die Hardware gleich geblieben sind. Würde das erklären, warum meine Abfrage aus dem Startpost für einen Full Table Scan über 16-20 Mio. Zeilen einer einzelnen Tabelle 2-3 Minuten braucht? Wobei da ~50 Leute gleichzeitig drauf arbeiten.

Zu unserem Admin: Der ist gelernter Fernsehtechniker wie ich, wurde dann aber immer mehr zur rechten Hand unseres alten Admins. Als dieser weg ging hat er übernommen und inzwischen den MCSA gemacht.

MfG
Rooter

Haarmann
2017-06-07, 08:55:17
Rooter

Plan H wie Holzhacker

Füll das "Ergebnis" mal zum Test ab und lass es dann dort malochen. Sollte es merklich schneller sein, dann würde ich den Grund suchen. Es könnte aber auch sein, dass dies nichts bringt.

Dann müsste man uU mal das DBMS an sich optimieren.

Ist die DB jeweils "optimiert" worden oder wuchs die einfach still vor sich hin?

littlejam

Die Anwendung bei mir ist ähnlich alt, wie wohl bei Rooter - nur isse inzwischen in ne VM gewandert.

Wenn man sich beim Hersteller der Anwendung beschwert hat, weil es langsam ist, dann wurde zuerst einmal gesagt - baut 15k Platten ein -> ich baute SSDs ein, vor vielen Jahren -> es war so lahm wie zuvor ...
Und es ist trotz massiv schnellerer Hardware auch nach wie vor nicht schneller geworden. Irgendwann lebste damit... nutzt es solange es geht und schaust für was Besseres, denn jetzt muss mans wohl ablösen, weil die Banken unbedingt nach der neuen ISO Norm die Daten sehen wollen und liefern wollen.

Hilfe kriegst eh keine... ich kanns zwar auch auf 64 Bit OS installieren, aber der Hersteller behauptet nach wie vor, dass dies nicht gehe, ausser man kauft das Update (für 5 Benutzer nur schlappe 75k € -> nein).
Der Hersteller muss ab und an mal bestimmte Werte anpassen - mehr nicht. Das will ich ehrlicherweise nicht tun müssen. Der Hersteller verlangt jedes Jahr ein Lizenz Audit... er hat Fernzugriff - er machts nie selbst. Nur die 8k pa verrechnet er immer pünktlich ;).

Wenn etwas derart teuer ist, aber derart mies läuft und der Hersteller ohnehin nur Update als Lösung kennt, dann erhälst es einfach am Leben (und hoffst es ist bald weg).

littlejam
2017-06-07, 19:54:56
Wir haben das System seit ~2003 und ich gehe davon aus, dass seitdem neben der DB auch das OS und die Hardware gleich geblieben sind. Würde das erklären, warum meine Abfrage aus dem Startpost für einen Full Table Scan über 16-20 Mio. Zeilen einer einzelnen Tabelle 2-3 Minuten braucht? Wobei da ~50 Leute gleichzeitig drauf arbeiten.

Keine Ahnung, das kann an vielen Ecken kranken.
Als Faustregel sollten die ständig benötigten Daten im Ram liegen, das ist meistens der größte Hebel.
Wenn z.B. deine DB nun 10GB groß ist und auf 32 Bit läuft dann hast du deine Bremse.
Die Frage ist auch was in den 20M Zeilen steht, sinds 50 byte oder 5 kb, das kann schon ok sein mit 2-3 Min. das ursprüngliche Problem war ja, dass da kein Index genutzt wird.
Der Output ist auch wichtig, nach /dev/null geht natürlich schneller als ins Terminal oder in eine Applikation.
Was ich auch oft sehe, ist in der MySQL-Welt ein "SELECT SQL_CALC_FOUND_ROWS ... LIMIT x,y" für Pagination. Das ist sehr versteckte Performanceeinbuße, weil intern die Query vollständig läuft und nicht beim Limit stoppt (und limit x,y ist eigentlich auch Mist).

Zu unserem Admin: Der ist gelernter Fernsehtechniker wie ich, wurde dann aber immer mehr zur rechten Hand unseres alten Admins. Als dieser weg ging hat er übernommen und inzwischen den MCSA gemacht.

MfG
Rooter
Quereinsteiger ist ja nicht schlecht, ging mir um die Einstellung.

Die Anwendung bei mir ist ähnlich alt, wie wohl bei Rooter - nur isse inzwischen in ne VM gewandert.

Bei mir ähnlich, ist auch weit ab von Optimal, ich hab aber die Kontrolle übers System und auch den Einfluss das Stückweise in der Anwendung zu verbessern, bzw. das Leid der Nutzer zu mildern.
Hier gibts auch Querys von Minuten bis Tage... bei mehr als 10G allerdings.

Wenn man sich beim Hersteller der Anwendung beschwert hat, weil es langsam ist, dann wurde zuerst einmal gesagt - baut 15k Platten ein -> ich baute SSDs ein, vor vielen Jahren -> es war so lahm wie zuvor ...
Und es ist trotz massiv schnellerer Hardware auch nach wie vor nicht schneller geworden. Irgendwann lebste damit... nutzt es solange es geht und schaust für was Besseres, denn jetzt muss mans wohl ablösen, weil die Banken unbedingt nach der neuen ISO Norm die Daten sehen wollen und liefern wollen.

Hilfe kriegst eh keine... ich kanns zwar auch auf 64 Bit OS installieren, aber der Hersteller behauptet nach wie vor, dass dies nicht gehe, ausser man kauft das Update (für 5 Benutzer nur schlappe 75k € -> nein).
Der Hersteller muss ab und an mal bestimmte Werte anpassen - mehr nicht. Das will ich ehrlicherweise nicht tun müssen. Der Hersteller verlangt jedes Jahr ein Lizenz Audit... er hat Fernzugriff - er machts nie selbst. Nur die 8k pa verrechnet er immer pünktlich ;).

Wenn etwas derart teuer ist, aber derart mies läuft und der Hersteller ohnehin nur Update als Lösung kennt, dann erhälst es einfach am Leben (und hoffst es ist bald weg).
Take it, leave it, change it.
Jeder wie er will, Ersteres ist halt nicht so meins :wink:

Grüße

Asaraki
2017-06-08, 15:31:31
Sorry aber ne DB liegt nie komplett im Ram, sonst hat man entweder ein überdimensioniertes System oder man bräuchte garkein DBMS... eine Buffer Hit Ratio von 99% heisst nicht, dass 99% der Daten permanent im Memory liegen ^^

Haarmann
2017-06-08, 19:55:18
Sorry aber ne DB liegt nie komplett im Ram, sonst hat man entweder ein überdimensioniertes System oder man bräuchte garkein DBMS... eine Buffer Hit Ratio von 99% heisst nicht, dass 99% der Daten permanent im Memory liegen ^^

Warum sollt ich bei armen 10 GB DB nicht 10 GB RAM haben?

Sind viele Daten und viel RAM für 2003er Systeme...

Aber es ist 2017

littlejam
2017-06-08, 19:59:53
Sorry aber ne DB liegt nie komplett im Ram, sonst hat man entweder ein überdimensioniertes System oder man bräuchte garkein DBMS... eine Buffer Hit Ratio von 99% heisst nicht, dass 99% der Daten permanent im Memory liegen ^^
Hab ich doch nicht geschrieben, lies genau.
"ständig benötigte Daten" oder auch Working Set.
Bei 10G allerdings würd ich mir nicht so die Platte machen und dem Dienst einfach 10G geben.

Grüße

PHuV
2017-06-18, 23:56:45
Sorry aber ne DB liegt nie komplett im Ram, sonst hat man entweder ein überdimensioniertes System oder man bräuchte garkein DBMS... eine Buffer Hit Ratio von 99% heisst nicht, dass 99% der Daten permanent im Memory liegen ^^
Öhm, nein, nimm Exasol, Hand mit In-Memory-Techologie, oder verteilte Systeme über hadoop mit Impala, Hive, HBase und Co., die über nur RAM arbeiten können.

Zu Buffer Hit Ratio, korret, heißt nur, daß er die angeforderten Daten zu 99% im Ram-Bereich, sprich SGA und Co findet. Deshalb können hier sehr noch wohl viele Zugriffe von Platte erfolgen.

@Rooter

Falls Du wieder mal mit Analyse ranmußt, aktuell plage ich mich auch mit einer Standard-Edition rum, wo man hier nur mit Statspack ran kann. Hier gibts einen Link, wie man es anwendet, vielleicht brauchst Du das das nächste Mal. ;)

http://www.muniqsoft.de/tipps/tipps-zu-dba/oracle-tuning/statspack5bea.pdf?type=123

Rooter
2017-11-11, 20:12:21
Am letzten Wochenende wurde unsere DB endlich auf Version 11 gehievt und - oh Wunder! - die Statistiken sind auch generiert. Dabei haben seltsamerweise einige Tabellen das LAST_ANALYZED-Datum 03.11.2017, andere Februar 2017 und wieder andere Oktober 2013. :confused:

Aber egal, wie erwartet läuft die Abfrage aus dem Startpost jetzt auch ohne Klimmzüge binnen eines Sekundenbruchteils durch. :D

Weitere Verbesserungen:
- Die nicht nachvollziehbaren, sekundenlangen Pausen bei allen möglichen Aktionen, z.B. wenn man nur ein Feld im Artikelstamm ändert und auf Speichern klickt, sind weg :D
- Das Öffnen des Kleinkundenstamms mit ~300.000 Endkunden dauert keine 20 Sekunden mehr sondern nur noch 2 Sekunden :D
- Mein Dispositions-SQL, der eine Bedarfsermittlung über ~10.000 Artikel durchführt, läuft keine 20 Minuten mehr sondern nur noch knappe 30 Sekunden :D
- Das Löschen von z.B. einem Artikel aus dem Artikelstamm, das erfordert, dass alle anderen Tabellen auf Child-Records geprüft werden, dauert pro Artikel keine 5 Minuten mehr sondern nur noch 2 Sekunden :D

MfG
Rooter