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:

CREATE table doc_table( doc_id Number,
                        document BLOB); 

Die Tabelle hat eine BLOB Spalte namens document.

DECLARE
   TYPE t_BLOB_tab IS TABLE OF BLOB; 
   v_blob t_BLOB_tab;
   v_blob_length NUMBER;
   CURSOR c1 is
      SELECT /*+ noparallel */ doc_id , document   -- 1: noparallel hint
	 FROM doc_table 
   	WHERE ROWID BETWEEN :start_id AND :end_id;  -- 2: Start and End Rowid
   TYPE c1_tab IS TABLE OF c1%rowtype;
   v_c1_tab c1_tab;	
   c_limit PLS_INTEGER := 10000;			 
 BEGIN 
 OPEN c1;
 LOOP
	 FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
	 EXIT WHEN v_c1_tab.COUNT=0;
	 FORALL i IN 1 .. v_c1_tab.COUNT
	    INSERT INTO doc_table_new (doc_id , document P) -- 3: Conventional Insert
			   VALUES (v_c1_tab(i).doc_id, empty_blob())
                       RETURNING document BULK COLLECT INTO v_blob; -- 4: Loblocator of new LOB
	 FOR i IN 1 .. v_c1_tab.COUNT 
	 LOOP
	    v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
	    IF nvl(v_blob_length,0) > 0 THEN -- 5: DBMS_LOB.COPY will throw an exception
		DBMS_LOB.COPY(v_blob(i),       -- for empty LOBS
                           v_c1_tab(i).document,
				 v_blob_length);
	    END IF;
	 END LOOP;
	 COMMIT; 
 END LOOP;
 COMMIT;
END;
/

Zu den Kommentaren:

1. 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.
2. 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.
3. 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.
4. Das Returning erspart einen SELECT .. FOR UPDATE.
5. 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.

DECLARE
 l_sql_stmt CONSTANT VARCHAR2 ( 20000 ) := 
	 q'[DECLARE
		 TYPE t_BLOB_tab IS TABLE OF BLOB; 
		 v_blob t_BLOB_tab;
		 v_blob_length NUMBER;
		 CURSOR c1 is
		    SELECT /*+ noparallel */ doc_id , document 
		      FROM doc_table 
		     WHERE ROWID BETWEEN :start_id AND :end_id;
		 TYPE c1_tab IS TABLE OF c1%rowtype;
		v_c1_tab c1_tab;	
        c_limit PLS_INTEGER := 10000;			
	 BEGIN 
	 OPEN c1;
	 LOOP
		 FETCH c1 bulk collect INTO v_c1_tab LIMIT c_limit;
		 EXIT WHEN v_c1_tab.COUNT=0;
		 FORALL i IN 1 .. v_c1_tab.COUNT
		    INSERT INTO doc_table (doc_id , document) 
				   VALUES (v_c1_tab(i)."doc_id", empty_blob()) 
                             RETURNING document BULK COLLECT INTO v_blob; 
		 FOR i IN 1 .. v_c1_tab.COUNT 
		 LOOP
			 v_blob_length := DBMS_LOB.GETLENGTH(v_c1_tab(i).document);
			 IF nvl(v_blob_length,0) > 0 THEN
				DBMS_LOB.COPY(v_blob(i),
							 v_c1_tab(i).document,
							 v_blob_length);
			 END IF;
		 END LOOP;
		 COMMIT; 
	 END LOOP;
	 COMMIT;
	END; ]';

 l_chunk_sql CONSTANT VARCHAR2 ( 10000 ) :=       -- 1: chunking statement. Breaks the input data
	 q'[SELECT min(r) start_id, max(r) end_id  -- into equal size pieces
		 FROM (
		SELECT ntile(10) over (order by rowid) grp, rowid r –- 2: 10 chunks will be produced
		 FROM doc_table                                     -- this can be equal or a multiple
		 )                                                -- of the parallel_level
	 GROUP BY grp]';
 l_try INTEGER;
 l_status INTEGER;
 l_task_name CONSTANT VARCHAR2( 20 ) := 'BLOB_MOVE';
BEGIN
 BEGIN
 dbms_parallel_execute.drop_task( l_task_name );
 EXCEPTION
 WHEN others then
 null;
 END;
 dbms_parallel_execute.create_task( l_task_name );
 dbms_parallel_execute.create_chunks_by_sql(l_task_name, l_chunk_sql, true);

 dbms_parallel_execute.run_task(
 task_name => l_task_name,
 sql_stmt  => l_sql_stmt,
 language_flag => dbms_sql.native,
 parallel_level => 10 –- 3: that many processes will be generated                 
 );
 
 dbms_output.put_line( 'DONE..' || dbms_parallel_execute.task_status(l_task_name));
 
END;
/

Zu den Kommentaren:

1. 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.
2. 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)