ORACLE 11: DELETE sehr langsam

[Furtbichler]

Hallo,

Auf einem Oracle-Server ist eine Tabelle mit ca. 2 Mio Datensätzen a ca. 10 Spalten und ca. 1kb pro Spalte.

Das Löschen per

Code:

DELETE FROM MyTable WHERE FooBar>10

(ca 1,5 Mio Records) dauert 9 Minuten.

1. Frage: Ist das sehr lange (rethorische Frage, oder?)
2. Was ist die wahrscheinliche Ursache für dieses sehr lahme Verhalten? Es ist kein Foreign Key ohne Index. Die Tabelle enhält einige Spalten mit NVARCHAR(150), auf denen jeweils ein Index liegt. Kann das daran liegen?

Ich habe noch etwas von PCTUSED und PCTFREE gelesen. Bringt es, daran zu drehen oder womit sollte man anfangen? Morgen kann ich Details über die Tabelle posten. Aber wenn wer bis dahin schon einen Tipp hat, wäre das Klasse.

[Uwe Raabe]

Nur so aus dem Handgelenk: Kannst du die Aktualisierung des Index versuchsweise abschalten bzw. den Index löschen und nach dem DELETE wieder aufbauen? Immerhin werden 3/4 der Datensätze gelöscht, somit müssen zum Neuaufbau auch nur 1/4 der Indexoperationen durchgeführt werden.

[Jumpy]

Könnte es daran liegen, dass für das evtl. riesige Rollback irgendwas angelegt werden muss?
Hab's aus Neugier mal gegoogelt und die Frage scheint öfter zu kommen, mit dem Fazit, dass das löschen von Datensätzen für die DB eine aufwendige Sache ist und es gibt wohl einige Performance-Tricks:
-Index aus und nachher neu anlegen
-Statt die unerwünschten Daten zu löschen, die erwünschten via Create Table As... in eine neue Tabelle zu packen, wenn dann die alte gedroped wird, macht Oracle die Eigentliche Arbeit in seiner "Freizeit".
-Partitionstabellen
-...

[Furtbichler]

Hey, hätte nicht gedacht, das hier noch Leute unterwegs sind.
Es ist so, das die DB von einem Dienstleister supported wird und der meint, das das DELETE so lange braucht und ich sage:'Habt ihr einen an der Waffel? Da ist doch was faul' und die 'Öhhh.... wir sind die Profis da isnix faul'.

Ich werde morgen mal prüfen, das ich ...
1. das erst einmal verifiziere, denn die 9 Min-Aussage stammt von den 'Profis'.
2. Informationen über Index etc einhole und mal prüfe, ob ein DROP INDEX, DELETE, CREATE INDEX nicht viel schneller ist. Es gibt auch noch den Tipp, das man ein

Code:

SELECT * INTO Temp FROM MyTable WHERE ShouldNotBeDeleted=1; 
DROP MyTable; 
RENAME Temp To MyTable; 
REBUILD INDEXES

machen kann, aber das sind doch alles workarounds.

In MSSQL habe ich eine Tabelle mit 6,5 Mio Recs und 4 Indexen und MSSQL löscht das Teil per DELETE in 4 secs... (allerdings ohne WHERE klausel, vielleicht war das zu faul von mir)

Ich melde mich morgen mit Details. Ich will den Profis dieser Dienstleisterbude unbedingt zeigen, das sie Unrecht haben (wenn sie es denn haben)


Aber eine Frage noch: 9 Minuten ist doch nicht normal für Oracle, oder?

[Sir Rufo]

Ohne WHERE Einschränkung kann aber auch der Index einfach so gedroppt werden, ohne Rücksicht auf Verluste. Das sollte die Datenbank auch erkennen und ist daher nicht vergleichbar ;)

[p80286]

Also sooo langsam ist das eigentlich nicht.
Neben dem Indexabschalten, wären die Trigger noch eine Option, und falls möglich zwischendurch ein commit.

oder Uwes Idee ganz konsequent: neue Tabelle mit 500K Sätzen und alte Tabelle in den Orkus.

Gruß
K-H

Mist, viel zu langsam.

[Furtbichler]

Ok, also
1. Verifizieren, wie MSSQL sich mit WHERE verhält und dann das als Referenz nehmen
2. Mit dem Abschalten der Indexe spielen und der Option, die zu behaltenden Datensätze zu sichern, dann ein DROP und dann RENAME etc. (natürlich vorher INDEX aus)

Trigger sind nicht drin und das Häppchenweise-DELETE-mit-COMMIT-zwischendurch bringt nix (haben sie wohl schon probiert)

[Bernhard Geyer]

a, Für Indexe löschen bracht man entsprechende Rechte. Der normale User sollte solche Rechte nicht haben
b, Wiviel RAM darf sich der Oracle-Server genehmigen?

[Furtbichler]

Das läuft unter dem ADMIN account und RAM sollte nicht das Problem sein. Gips einen Trick?

[EgonHugeist]

Oracle hat da auch so seine eigenen Tricks. Bei Zeos hab ich da eine PrefetchCount prop mit eingebastelt. Es ist unglablich was der Server schneller wird, wenn man diese Werte erhöht.

Nun steh ich allerdings auf dem Schlauch, wie man solche spezial Sachen via ADO lösen kann. IMHO Oracle bevorzugt nach wie for native CAPI call's via OCI.

Delphi-Quellcode:

AttrSet(Handle, OCI_HTYPE_STMT, @PrefetchCount, SizeOf(ub4),
    OCI_ATTR_PREFETCH_ROWS, ErrorHandle);

AFAIK dieses command soll for !jedem! Statement gefeuert werden und die gesamte interne Daten-Sammlung schneller zusammenstellen. Welches ich auch auf dein delete beziehen würde. Du kann da auch die Oracle docs ausquetschen. Sind ziehmlich ergiebig im Ggs. zu Zeos. :stupid:

[hoika]

Hallo,

Ich würde mir doch mal den Query-Plan ansehen.


Heiko

[Furtbichler]

Stimmt, hab gestern danach gegoogelt und gesehen, wie das geht. Habe es nur noch nie gemacht. Aber es gibt ja immer das erste Mal.

Bin aber noch im Delirium.

[Morphie]

Ich musste neulich auch mal in einer MySQL Datenbank 99,9% aller Datensätze (ca. eine Million) löschen. (Die Tabelle verfügt über sehr viele Indizes)
Nach ca. 2 Stunden habe ich den Vorgang frustriert abgebrochen, habe eine leere, identische Tabelle angelegt, die gewünschten Datensätze über SQL rüber kopiert und den Rest mittels "TRUNCATE TABLE" gelöscht. Da war die Dauer zum Löschen kaum messbar...

Hätte ich das mal vorher gemacht...

[jobo]

Ich vermute mal, dass es ein wiederkehrendes Problem ist, sonst muss man sich wohl nicht groß den Kopf zerbrechen.

Das Löschen kann schon sehr lange dauern. Die Löschanweisung auf einem nicht indizierten Feld bedeutet zuerst mal einen Fullscan.
Dann ist die Frage, wieviel Datensätze betroffen sind.
1 oder 500T?

Ausführungsplan
Es wäre zu prüfen, ob regelmäßig (nächtlich) Statistiken aktualisiert werden bzw. ein planmäßiges Vorgehen da ist.
Wenn Statistiken aktualisiert werden, muss sichergestellt sein, dass auch die Indizes einbezogen werden.
Andernfalls kommt eben Schrott raus.

Im Datenmodell wäre zu prüfen, ob FK auf die Tabelle zeigen oder sie selber Fremdbezüge hat, Cascade Constraints definiert sind usw.
All das bedeutet keinen Geschwindigkeitszuwachs.

Indizes
können gedropped werden oder "abgeschaltet"

Code:

ALTER INDEX myIndex UNUSABLE;
[delete]
ALTER INDEX myIndex  rebuild (online)

Beim Thema Indizes wäre noch zu überlegen, welches DML Verhalten auf der Tabelle existiert. Viele Inserts, Deletes wirken sich negativ auf den Index aus. PCTFree usw können auch hier vielleicht lindern. Ein Index der immer voll gepackt wird, kommt nicht gut mit Änderungen klar.

Dann wäre noch die Frage, was während des Löschens noch/sowieso schon läuft und welcher User die Löschanweisung ausführt und in welchem Kontext (ora scheduler, OS Cronjob, ..).
Gibt es konkurrierende Last auf dem System?
Laufen (nachts) bspw. zeitgleich Jobs via Scheduler (z.B. Statistiken sammelen, haha), zieht der Löschjob ggF den kürzeren.

[Furtbichler]

Hallo,

Heute hatte ich die Hucke voll zu tun und bin erst kurz vor Feierabend dazu gekommen, mir das Problem näher anzuschauen. Also: Es gibt 6 Indexe, keine Foreign Key Constraints auf die Tabelle. Ich denke, ich kürze das einfach ab:

Temp Tabelle erstellen, drop die Originaltabelle, rename die temp in die Originaltabelle, indexe neu erstellen. sollte funzen und schnell genug sein.

Edit: Achso, Mensch: Vielen Dank für die tollen Beiträge.