Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_bulk_collect

In Oracle PL/SQL das BULK COLLECT Feature einsetzen

ab min 9i?

Mit PL/SQL „Bulk Collect“ läßt die Performance von PL/SQL Routinen, die SQL für DML Operationen einsetzen zum Teil dramatisch beschleunigen.

Normalerweise muss für jede SQL Ausführung in PL/SQL zwischen der PL/SQL Engine und der SQL Engine hin und her gesprungen werden.

Wird zum Beispiel ein normales update Statment 100.000 über eine Loop aufgerufen muss die Pl/SQL Engine min. 100.000 mal hin und her schalten.

Bei einem Bulk Collect / Forall wird der ganze zu verarbeitende Block übergeben komplett übergeben und damit das System erheblich entlastet.

Auslesen der Daten mit einem Bulk Collect

Alles auf einmal holen
..
TYPE cur_typ IS REF cursor;
TYPE driver_id IS TABLE OF varchar2 (9) INDEX BY binary_integer;
v_cur		cur_typ;
v_driverids     driver_id;
 
..
OPEN v_cur FOR p_alternativ_getid_sql USING p_key;
 fetch v_cur bulk collect INTO v_driverids
close v_cur;
In einzelnen Blöcken holen - LIMIT verwenden
..
OPEN v_cur FOR p_alternativ_getid_sql USING p_key;
loop
   fetch v_cur	bulk collect INTO v_driverids LIMIT 10000;
   --
   -- do something with the data
   --
   EXIT WHEN v_driverids.COUNT = 0;
END loop;
close v_cur;
 
  • Bei jeden Fetch werden nun 10000 Datensätze geladen
  • Die Collection wird jedes mal dazu zurückgesetzt und fängt bei 1 neu an

Ein einer normalen Cursor Loop würde der exist ja bei „exit when v_cur%notfound“ erfolgen, hier ist das aber dann nicht richtig im Einsatz!

Die Loop muss mit einem EXIT WHEN v_driverids.COUNT = 0; sonst gehen einen die letzten Daten in der Loop verloren!

Schreiben der Daten mit einem FORALL

Um nun die Daten wieder in eine andere Tabelle zu schreiben kann das FORALL Statement verwendet werden.

Beispiel:

forall i IN 1 .. v_driverids.COUNT
    EXECUTE immediate p_alternativ_insert_sql USING v_driverids (i);

Sehr praktisch bei Daten Migrationen ist dabei das Feature bei Exceptions diese zu Speichern und trotzdem weiter das SQL abzuarbeiten, später kann dann auf die Exception reagiert werden.

BEGIN
    forall i IN 1 .. v_driverids.COUNT save exception
        EXECUTE immediate p_alternativ_insert_sql USING v_driverids (i);
 
    v_errors := v_errors + sql%bulk_exceptions.count;
    v_count  := v_count  + sql%rowcount;
 
    dbms_output.put_line ('-- Info ::SUCCESS rows::' || v_count );
    dbms_output.put_line ('-- Info ::Error rows::'   || v_errors);
    -- commit the worked records
    commit;               
exception
  WHEN others
  	THEN
  	-- get the number of errors in the exception array
  	v_errors := v_errors + sql%bulk_exceptions.count;
  	v_message := sqlerrm;
  	dbms_output.put_line ('-- Error::'      || v_message);
  	dbms_output.put_line ('-- Error rows::' || v_errors);
  	FOR i IN 1 .. v_errors
  	loop
	   v_err_i := sql%bulk_exceptions (i).error_index;
	   dbms_output.put_line ('-- Info :: Key=' || v_driverids (v_err_i) || ' :: Error =' || sqlerrm (-1 * sql%bulk_exceptions(i).error_code) );
  	END loop;
END;

Ein kompletter Test im Vergleich

Beispiel Code auf dem HR Demo Schema:

Demo daten erstellen
CREATE TABLE EMPLOYEES2 AS SELECT * FROM hr.EMPLOYEES;
-- create some data 
BEGIN 
 FOR i IN  1..10
 loop
   INSERT INTO EMPLOYEES2  SELECT * FROM EMPLOYEES2;
    commit;
 END loop;
END;
/
Die Test Procedure anlegen
CREATE OR REPLACE PROCEDURE bulck_load_test (p_testcase IN pls_integer
                      , p_key IN varchar2 )
AS
  TYPE cur_typ IS REF cursor;
 
  TYPE driver_id IS TABLE OF varchar2 (9)  INDEX BY binary_integer;
 
  v_cur             cur_typ;
  v_driverids         driver_id;
 
  -- mit bind variablen
  p_sql            varchar2 (200) := 'update EMPLOYEES2 set SALARY = SALARY*0.19 where DEPARTMENT_ID = :key';
  p_getid_sql      varchar2 (200) := 'select EMPLOYEE_ID from EMPLOYEES2         where DEPARTMENT_ID like :key group by EMPLOYEE_ID order by 1';
  p_insert_sql     varchar2 (200) := 'update EMPLOYEES2 set SALARY = SALARY*0.19 where EMPLOYEE_ID   = :key';
 
  v_count          pls_integer := 0;
  v_errors         pls_integer := 0;
  v_message        varchar2 (100) := '';
  v_time_start     NUMBER := dbms_utility.get_time;                                      
  v_time_stop      NUMBER := 0;
 
  v_err_i        pls_integer := 0;
BEGIN
 
    -- A) try in one statement: UPDATE als 1 SQL Statement
 
  IF p_testcase = 1
  THEN
    EXECUTE immediate p_sql USING p_key;
 
    v_count := sql%rowcount;
    commit;
    dbms_output.put_line ('-- Info :: SUCCESS rows ::' || v_count);
 
    -- B) try as loop: UPDATE als Loop with Ref Cursor 
  elsif p_testcase = 2
  THEN
 
    OPEN v_cur FOR p_getid_sql USING p_key;
        <<bulck_collect>>
    loop
 
      v_count := 0;
            v_errors := 0;
 
      fetch v_cur
            bulk collect INTO v_driverids
      LIMIT 10000;
 
      --
      dbms_output.put_line ('-- Info ::read cound ids ::' || v_driverids.count);
 
      --
 
    -----------
    -- EXIT wenn Array leer!!
 
 	EXIT WHEN v_driverids.COUNT = 0;
    ------------
 
 
      -- Verarbeitung
      BEGIN
        --forall i in v_driverids.first .. v_driverids.last save exceptions       -- sichere die Ausnahme, mache aber weiter!
          -- execute immediate p_alternativ_insert_sql using v_driverids (i);
 
                forall i IN 1 .. v_driverids.COUNT save exceptions 
                    EXECUTE immediate p_insert_sql USING v_driverids (i);
 
 
        v_errors := v_errors + sql%bulk_exceptions.count;
        v_count := v_count + sql%rowcount;
 
        dbms_output.put_line ('-- Info ::SUCCESS rows::' || v_count);
        dbms_output.put_line ('-- Info ::Error rows::'   || v_errors);
 
         -- add a commit each 10000
         commit;
 
      exception
        WHEN others
        THEN
          -- get the number of errors in the exception array
          v_errors := v_errors + sql%bulk_exceptions.count;
          v_message := sqlerrm;
 
          dbms_output.put_line ('-- Error::'      || v_message);
          dbms_output.put_line ('-- Error rows::' || v_errors);
 
          FOR i IN 1 .. v_errors
          loop
            v_err_i := sql%bulk_exceptions (i).error_index;
            dbms_output.put_line ('ID=' || v_driverids (v_err_i) || ' error=' || sqlerrm (-1 * sql%bulk_exceptions (i).error_code));
          END loop;
      END;                                                                    
 
 
 
    END loop bulck_collect;                                                                     
 
    IF v_cur%isopen = TRUE
    THEN
      close v_cur;
    END IF;
  -- C) all in one
  ELSE
    OPEN v_cur FOR p_getid_sql USING p_key;
 
    fetch v_cur
    bulk collect INTO v_driverids;                                            -- alles auf einmal einlesen
 
        dbms_output.put_line ('-- Info :: read cound ids ::' || v_driverids.count);
 
    BEGIN
 
      forall i IN 1 .. v_driverids.count save exceptions         
        EXECUTE immediate p_insert_sql USING v_driverids (i);
 
      v_errors := v_errors + sql%bulk_exceptions.count;
      v_count := v_count + sql%rowcount;
 
      dbms_output.put_line ('-- Info ::SUCCESS rows::' || v_count);
      dbms_output.put_line ('-- Info ::Error rows::'   || v_errors);
 
    exception
      WHEN others
      THEN
        -- get the number of errors in the exception array
        v_errors := v_errors + sql%bulk_exceptions.count;
        v_message := sqlerrm;
        dbms_output.put_line ('-- Error::'      || v_message);
        dbms_output.put_line ('-- Error rows::' || v_errors);
 
        FOR i IN 1 .. v_errors
        loop
          v_err_i := sql%bulk_exceptions (i).error_index;
          dbms_output.put_line (
            'ID=' || v_driverids (v_err_i) || ' error=' || sqlerrm (-1 * sql%bulk_exceptions (i).error_code));
        END loop;
    END;
 
    commit;
 
    close v_cur;
  END IF;
 
  v_time_stop := dbms_utility.get_time;
  dbms_output.put_line (   '-- Info :: Variante: '
                 || p_testcase
                 || ' - Zeit:'
                 || to_char (round ( (v_time_stop - v_time_start) / 100, 2 ))
                 || ' sec');
 
END bulck_load_test;

Testlauf

SQL>EXEC bulck_load_test(1,50)
-- Info :: SUCCESS rows ::92160
-- Info :: Variante: 1 - Zeit:1.33 sec
 
PL/SQL PROCEDURE successfully completed.
 
SQL>EXEC bulck_load_test(2,50)
-- Info ::read cound ids ::20
-- Info ::SUCCESS rows::40960
-- Info ::Error rows::0
-- Info ::read cound ids ::20
-- Info ::SUCCESS rows::40960
-- Info ::Error rows::0
-- Info ::read cound ids ::5
-- Info ::SUCCESS rows::10240
-- Info ::Error rows::0
-- Info ::read cound ids ::0
-- Info :: Variante: 2 - Zeit:2.33 sec
 
PL/SQL PROCEDURE successfully completed.
 
SQL>EXEC bulck_load_test(3,50)
-- Info :: read cound ids ::45
-- Info ::SUCCESS rows::92160
-- Info ::Error rows::0
-- Info :: Variante: 3 - Zeit:2.42 sec
 
PL/SQL PROCEDURE successfully completed.

Wie erwartet das reine SQL am schnellsten

Allerdings muss beachtet werden das wir hier nur auf einer sehr kleinen Datenmenge arbeiten, sollen »1M Zeilen upgedated werden, spielt oft der verfügbarer Rollback Tablespace Platz nicht mehr mit!

Dann muss wieder Blockweise gearbeitet werden!

Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/plsql_bulk_collect.txt · Zuletzt geändert: 2016/05/20 17:39 von Gunther Pippèrr