Oracle 9.2; BULK COLLECT kopieren großer Datenmengen

tplanitz

Erfahrenes Mitglied
Hallo,

ich hänge hier meine procedur ein die folgendes machen soll, hoffentlich hat jemand Muße mir zu helfen:
In einer Quelldatenbank sollen Nachrichten eines bestimmten Types gefunden werden und diese in einer Zieltabelle abgelegt werden.
Es handelt sich um mehrere millionen Datensätze die gefunden werden können.
Nachdem ich mich in das Thema BULK COLLECT eingearbeitet habe hatte ich bei kleinen Datenmengen ca. einen Faktor 100 (Geschwindigkeit) beim importieren. Jetzt wo ich die Procedur für > 1000 000 Datensätze verwende dauert es quälend lange bis die Daten kopiert sind.
Habt Ihr ne Idee was ich falsch mache?

Kurz zur Procedur:
-- Selecte mir alle Nachrichten des Typs LL in der Quelltabelle
-- Schreibe jeweils 10000 Datensätze in die Zieltabelle
-- Wenn 20000 Datensätze geschrieben wurden soll commited werden (das mußte ich machen damit das ROLL Back Segment nicht überläuft)
-- Ich hätte auch gerne nach jedem COMMIT eine Ausgabe in der SQL-PLUS Konsole, das klappt hier leider nur wenn das Programm fertig ist.Dann werden alle Zeilen auf einmal geschrieben Hm... evtl. habt Ihr nen Ansatz.

Schon jetzt vielen Dank für Anregungen.



PHP:
CREATE OR REPLACE PROCEDURE Pd_Copy_Message_Fast IS
 TYPE myarray IS TABLE OF T_RAW_MESSAGE%ROWTYPE;
 l_data myarray;
 v_CommitAbstand NUMBER (5) := 0;
 v_data_array_cnt NUMBER (5) :=0;
 CURSOR copy_curser IS 
 SELECT  DISTINCT A_RAW_MESSAGE_ID
 , A_DEVICE_ID
 , A_MESSAGE_TYPE_ID
 , A_REGION_ID
 , A_TIMESTAMP
 , A_DATE
     FROM T_RAW_MESSAGE
   JOIN T_RAW_MESSAGE_TYPE USING (a_raw_message_type_id)
   WHERE a_raw_message_type_name = 'LL';
 BEGIN
  OPEN copy_curser;
  LOOP
   v_CommitAbstand := v_CommitAbstand +1;
 v_data_array_cnt := v_data_array_cnt +1;
    FETCH copy_curser BULK COLLECT INTO l_data LIMIT 10000;
    FORALL  i IN 1..l_data.COUNT
    INSERT INTO T_RAW_MESSAGE_COPY VALUES l_data(i);
  
   IF v_CommitAbstand = 2
    THEN COMMIT;
    DBMS_OUTPUT.PUT_LINE('committed bei ' ||  v_CommitAbstand); -- Das soll in der Konsole ausgegeben werden
    DBMS_OUTPUT.PUT_LINE('data array Nr. =  ' ||  v_data_array_cnt); -- Das soll in der Konsole ausgegeben werden, zeigt die Anzahl der Pakete an
    v_CommitAbstand := 0;
   END IF; 
 EXIT WHEN copy_curser%NOTFOUND;
 END LOOP;
  
 COMMIT; -- Sichewrheitshalber noch mal alles committen
 CLOSE copy_curser;
  


END Pd_Copy_Message_Fast;
/

Viele Grüße

Thorsten
 
Zuletzt bearbeitet:
Das Perfomanceproblem dürfte genau in diesen 3 Zeilen liegen:
SQL:
FETCH copy_curser BULK COLLECT INTO l_data LIMIT 10000;
FORALL  i IN 1..l_data.COUNT
INSERT INTO T_RAW_MESSAGE_COPY VALUES l_data(i);

Ein BULK Collect ist zwar eine feine Sache und bringt große Performanceverbesserungen im Vergleich
zu dem normalen LOOP und FETCH, ist hier aber eigentlich gar nicht notwendig wenn ich das richtig sehe.
Anscheinend kopierst du die Daten ohne Änderungen von einer Tabelle in die andere. Langsam wird
es dadurch, dass Oracle zuerst alle (bzw. in 10.000er Blöcken) Daten in die PL/SQL Engine überträgt
und danach wieder (ohne Änderung!) in die SQL Engine zurückschreibt. Das bedeutet jedesmal eine Menge
Overhead da Speicher allociert und wieder freigegeben werden muss etc...

In deinem Fall reicht m.E. ein einfaches INSERT AS SELECT. Damit bleiben alle Daten in der SQL Ebene
und du sparst eine Menge Overhead. Damit die Transaktion nicht zu groß wird kann du das Subselect über
die Pseudospalte ROWNUM eingrenzen und immer nur 10.000 Sätze einfügen.

Wenn in deine COPY Tabelle nur Daten reingeschrieben werden und es nicht so sehr auf den Platzverbrauch
ankommt kannst du beim INSERT auch mit dem HINT /*+ APPEND */ arbeiten. Dies weist Oracle an alle neuen
Daten hinter die HWM (High Water Mark), also das Ende der Tabelle zu schreiben. Dadurch entfällt die
Zeit zum Suchen von freien Blöcken im Datenfile und kann so weitere Performanceverbesserungen bringen.

Es macht auch Sinn mal die Eigenschaften der Datafiles und des Segments (COPY_TABELLE) anzusehen.
z.B. wäre es interessant wie oft das Segment oder die Files extended werden (müssen). Wenn er beim
INSERT oft die Datei oder die Tabelle erweitern muss kostet das zusätzlich Zeit.

Und zum Abschluss noch: Ein DBMS_OUTPUT.PUT_LINE schreibt alle Zeichen in einen internen Puffer
der Datenbank. SQL*Plus liest erst am Ende der Prozedur diesen Puffer aus. Somit ist es mit SQL*Plus
nicht möglich während der Ausführung einer Prozedur eine Textausgabe zu erzeugen.
 
Zuletzt bearbeitet von einem Moderator:
Hallo,

ganz herlichen Dank für Deine wie immer sehr ausführlichen Tips + Tricks. Noch eine Frage zur Procedur, wie es sich anhört lohnt sich die Verarbeitung in einem PLSQL Programm nur wenn ich Daten ändern möchte also z.B. neue Attributinhalte setzen oder? Oder wenn man mehrere Tabellen miteinander joint, gehe ich da richtig in der Annahme.

Gruß an Dich
 
Äh ich glaube du meinst das richtige, ja ;-)
PL/SQL ist im Prinzip immer dann sinnvoll wenn du etwas "Prozedural" abarbeiten musst, also z.B. Fallunterscheidungen mit "IF" oder so benötigst, komplizierte Rechenoperationen usw.. daher auch das "P" in PL/SQL

Bei dir ist das ja eigentlich auch der Fall, da du ja aus gutem Grund nicht alle Sätze auf einmal kopieren willst. Man sollte eben nur aufpassen was man mit seinen Datenmengen so anstellt und ob man sie wirklich in der PL/SQL Engine zum verabeiten braucht, oder ob man PL/SQL nur nutzt um die Verarbeitungslogik an sich zu steuern.
Hmmm, kann man das verstehen ?
 
Zurück