Das Grundproblem
Wir müssen eine Tabelle, die einen LOB enthält, umkopieren. Das kann unterschiedliche Gründe haben. Beispielsweise wenn die Tabelle zu fragmentiert ist, oder wenn auf dem aktuellen Tablespace kein Platz mehr ist.
Der gradlinige Ansatz mit Insert /*+ append parallel(n) enable_parallel_dml */ die Daten einfach umzukopieren scheitert aber. Es dauert zunächst sehr lange. Man kann erkennen, dass die Zeit beim Einfügen und nicht beim Lesen anfällt. Hier wiederum ist das Kopieren der LOBS entscheidend. Der Rest der Zeit fällt vergleichsweise kaum ins Gewicht.
Noch ein Problem hat der Insert .. Select Ansatz: Es gibt keine Teilergebnisse. Entweder es geht alles gut, oder man muss wieder von vorne anfangen.
Beim Suchen im Internet fand ich einen Blog von Marek Läll, der sich mit dem Thema LOB kopieren befasst [1].
Der Kern der Sache ist, dass der LOB Locator (LOB Adresse) zum Zeitpunkt des Inserts noch nicht feststeht. Es muss ein neuer LOB angelegt werden, das heißt man braucht auch einen neuen Locator. In der Folge wird der zu kopierende LOB zunächst in den TEMP Tablespace geschrieben wird. Dann wird der neue Locator ermittelt und dann wird der LOB vom TEMP Tablespace noch einmal in das eigentliche Ziel kopiert. Läll schlägt jetzt einen anderen Weg vor: Es wird zunächst ein empty LOB geschrieben, dessen Locator wird dann über SELECT .. FOR UPDATE gelesen. Dorthin kann der Quell LOB in einem Zug kopiert werden. Dadurch erspart man sich einmal Schreiben und Lesen des LOB, was wichtig ist. Wie schon erwähnt zählt für die Effizienz vor allem wie die LOBs behandelt werden.
Ich habe den Code in PL/SQL realisiert und noch etwas optimiert, also auf BULK umgeschrieben und den SELECT .. FOR UPDATE über die returning Klausel vermieden.
Der Code ist sehr effizient verglichen mit anderen Ansätzen, die ich getestet habe. In meinem echten Anwendungsfall komme ich locker bei parallel 24 auf 1,5 Millionen Rows/Stunde.
Kopieren
Um das folgende Beispiel verständlich zu machen, erstelle ich einmal die folgende einfache Tabelle, die kopiert werden soll:
Die Tabelle hat eine BLOB Spalte namens document.
Zu den Kommentaren:
- Der anonymous Block wird im nächsten Schritt über DBMS_PARALLEL_EXECUTE parallelisiert. Es wäre unlogisch innerhalb eines parallelen Prozesses noch einmal zu parallelisieren.
- Die Start – und die End id müssen im zentralen Select verwendet werden. Man darf sie nicht definieren, sie werden über DBMS_PARALLEL_EXECUTE gesetzt.
- Hier wäre über den Hint APPEND_VALUES ein direct path write möglich. Ich habe davon abgesehen, um nicht einen exclusive table lock zu provozieren. Ob dies tatsächlich der Fall wäre, habe ich nicht getestet. Ich bin mit der Perfomance der hier beschrieben Lösung so zufrieden, dass ich einen Test für unnötig erachte.
- Das Returning erspart einen SELECT .. FOR UPDATE.
- COPY ist die schnellste Art zu kopieren und scheint einen direct path zu verwenden.
Parallelisieren
Hier hätte man auch über paralleles SQL eine stored function rufen können, um die Lösung zu parallelisieren. Die Entscheidung für DBMS_PARALLEL_EXECUTE war eher instinktiv begründet. Jedoch wäre es bei einer stored Function zu sehr vielen context Switches gekommen. DBMS_PARALLEL_EXECUTE erlaubt es in PL/SQL zu bleiben.
Einige Tests mit parallelem SQL waren in der Tat auch wenig effizient.
Hier noch der Code zum Parallelisieren, den anonymen Block, den wir besprochen haben, habe ich markiert.
Zu den Kommentaren:
- Es gibt mehrere Möglichkeiten die Arbeit aufzuteilen. Die Chunking Query ist die flexibelste. Die Suchbedingen hier müssen auch im Cursor c1 noch einmal zu finden sein.
- Ntile (10) heißt, dass das Ergebnis der Abfrage in 10 hoffentlich gleich große Abschnitte einteilt.
Quellen:
Marek Läll, binary/textual data insert into BLOB/CLOB columns – use 50% less database (IO) resources,Marek Läll / Technical blog about Oracle database: binary/textual data insert into BLOB/CLOB columns – use 50% less database (IO) resources (mareklall.blogspot.com)
Mag. Lothar Flatz, Senior Principal Consultant @DBConcepts.
Unser Oracle Mastermind kam 1989 erstmals mit Oracle in Berührung. Mittlerweile verfügt er über enormes Wissen und Erfahrung (15 Jahre als Oracle Mitarbeiter). Er war Mitglied in der Real World Performance Group und ist Mitglied im exklusiven Oaktable* und Oracle ACE*. Als einer der wenigen verfügt er über ein Patent zur Verbesserung des Optimizers. Er arbeitete an der Software für den LHC bei CERN in Genf mit. Sein Wissen und seine Erfahrung teilt er gerne als Autor für diverse Informatik Fachmagazine und als Sprecher bei einschlägigen Veranstaltungen (IT-Tagen, DOUG, AOUG, RMOUG, PGDay, POUG, OUGN uvm.). Aber auch als Dozent auf der TU Wien oder hier im Blog.
*Oaktable Member: 100 Mitglieder weltweit. Ausschließlich auf Einladung.
*Oracle ACE: Weltweit 500. Von Oracle für besondere Leistungen ernannt.
