Performanceproblem bei Datenmigration

[SilencerandLois]

Hallo zusammen,
aktuell ist ein Kollege dabei, eine Datenmigration von einem Datenbankschema in ein anderes zu schreiben.
Betroffen davon ist ein Mengengerüst von 20 Millionen Tupeln.

Bei der Implementierung des Skriptes wurde darauf geachtet es möglich dynamisch zu halten. Bei der Ausführung des Skriptes wird PL/SQL benutzt. Die einzelnen UPDATE-Statements werden dabei pro Tupel durch Stringkontakenationen erstellt und anschließend per execute immediate ausgeführt.

Aktuell benötigt das Skript sage und schreibe 8 Tage zum laufen.
Basierend auf der langen Zeit muss das Skript umgebaut werden. Ich habe mir das Skrip mal angesehen und bin dabei auf folgende performancekritischen Gestoßen. Ich wollte Euch nun fragen, ob meine Einwände hier auch wirklich stimmen (nicht dass das Skript unnötigerweise falsch umgebaut wird):
1) Für jedes Tupel wird über zig Stringkonkatenationen eine Query zusammengebaut. Dieses zusammenbauen ist bei diesem Mengengerüst sicherlich nicht sonderlich performant.
2) Durch das Zusammenbauen der Query und dem execute immediate muss die Datenbank zur Laufzeit für jedes Tupel die Anfrage immer wieder parsen, kompilieren und den Ausführungsplan berechnen (stimmt diese Annahme?). Für 20 Millionen Tupel ist das ein erheblicher Aufwand.

Schon einmal Danke für Eure Hilfe!
Viele Grüße,
Martin

[dimitri]

Beide Punkte korrekt, allerdings lassen sich auch mit execute immediate SQLs bauen, die nicht jedesmal neu gepartst werden müssen..
Warum nicht ein UPDATE pro Tabelle generieren? Damit würde pro tabelle nur ein Statement erzeugt und geparst werdne müssen, und die Datenbnak könnte alles auf einen Rutsch verarbeiten anstelle Zeile für Zeile schreiben zu müssen.

Falls auch neue Zeilen berücksichtigt werden sollen, kann statt dessen der MERGE Befehl verwendet werden.

Evtl ist es auch sinvoll sich zu überlegen, ob das andere Schema nicht einfach per truncate geleert und dann mittles insert /*+append*/ wieder neu geladen wird.

Dim

[SilencerandLois]

Hallo Dim,
danke für die schnelle Antwort.
Wie würde ein solches execute immediate statement aussehen?

Ein möglicher Update-Befehl könnte theoretisch möglich sein. Aber aus praktsichen Gründen wäre das hier leider nur schwer machbar; schon einmal aus Gründen der Wartbarkeit und Lesbarkeit wäre es hier nur schwer möglich, da die Migration wirklich sehr komplex ist. Aber ich ich werde diesen Punkt nochmals genauer betrachten!

Ich hätte mir folgendes gedacht: Statt die Query dynamisch per Kontakenation zusammenbauen zu lassen sollten die Queries wirklich als fertige SQL-Query mit Bind-Variablen bereits vorhanden sein.
Durch If-Abfragen sollte dann die korrekte Query ausgewählt werden; hierbei kann man ja noch beachten, dass die Query mit der höchsten Trefferwahrscheinlichkeit als erste Query bei den if-Abfragen auftritt (um unnötige CPU-Kosten bei den if-Abfragen zu vermeiden).

Danke und viele Grüße,
Martin

[dimitri]

Wie würde ein solches execute immediate statement aussehen?

Z.B. so:

execute immediate 'update tabelle set wert1=:1, wert2=:2 where id=:3' using varWert1,varWert2,varId;

Ich hätte mir folgendes gedacht: Statt die Query dynamisch per Kontakenation zusammenbauen zu lassen sollten die Queries wirklich als fertige SQL-Query mit Bind-Variablen bereits vorhanden sein.

Welchen Prozensatz der 8 Tage hat die Datenbank mit parsen verbracht?

Durch If-Abfragen sollte dann die korrekte Query ausgewählt werden; hierbei kann man ja noch beachten, dass die Query mit der höchsten Trefferwahrscheinlichkeit als erste Query bei den if-Abfragen auftritt

Arbeiten diese Statements dann immer noch in Einsatzsatzverarbeitung?

Dim

[SilencerandLois]

Hallo Dimitri,
wo wird die Parsezweit protokolliert? In der SGA habe ich leider nichts direkt gefunden. Hier wurde leider nur die vollständige Laufzeit des Skriptes protokolliert. Weiterhin wurde die SGA leider schon geflushed.

Die Statements würden dann immer noch als Einsatzverarbeitung arbeiten.
Da du so direkt nachfrägst: hier könnte folglich auch noch optimiert werden?

Danke und viele Grüße,
Martin

[dimitri]

Die parse Time bekommst Du so heraus:

select
  stat_name,
  Round(value/1000000) "Sekunden"
from v$sys_time_model where stat_name like '%parse%'

Wenn ihr eine AWR Lizenz habt, dann kann man das auch noch viel schöner auflisten, aber das sollte als Anhaltspunkt auch reichen.

Du kannst ja mal einen vorher nacher Snapshot machen. Nimm die where Bedingung weg und speichere das Ergebnis in einer extra Tabelle mit dem zusätzlichen Attribut 'VORHER'.
Dann lässt Du einen gewissen Teil der Migration laufen z.B. eine Stunde lang und speicherst dann das Ergebnis des obigen SQL zusammen mit dem Attribut 'NACHHER' in der gleichen Tabelle. Dann kannst sehr schön die Veränderungen sehen.
Beachte aber, dass diese View für alle Aktivitäten in der Instance gilt.

Die Statements würden dann immer noch als Einsatzverarbeitung arbeiten.
Da du so direkt nachfrägst: hier könnte folglich auch noch optimiert werden?

Natürlich. Einselsatzverarbeitung = schlecht, Batchverarbeitung= gut bei solchen Datenmengen. Da geht es um Faktoren wenn man es schafft das umzustellen.

Weiterhin wurde die SGA leider schon geflushed.

Das Mittel der Verlorenen

Dim

[SilencerandLois]

Hallo Dimitri,
das sind doch schon einmal einige Anhaltspunkte für die Optimierung :-)
Werden diese mal in den nächsten Tagen antesten!


Schon mal besten Danke!


Viele Grüße,
Martin