domingo, julio 17, 2005

Uso de cursores y bulk insert.

Uno de los muchos mitos en oracle es que el uso de cursores perjudica seriamente el rendimiento. Incluso he visto algunos proyectos en los que se había prohibido el uso de esta técnica.

El impacto sobre el rendimiento es cierto en parte, ya que el servidor trata individualmente cada registro y, por tanto, esto debería implementarse únicamente cuando fuera estrictamente necesario (sql dinámico, iteraciones dentro del fetch, etc.) utilizando, siempre que sea posible, SQL estándar.

No obstante, hay situaciones en las que no es posible el uso de SQL convencional. Por ejemplo, cuando en una inserción es preciso controlar los errores e insertarlos en otra tabla. Ante este caso, los procedimientos ETL filtran previamente las filas, las mueven a otra tabla, y cuando las filas ya están totalmente "limpias" de errores, entonces realizan
INSERT INTO tabla SELECT * ...

Oracle, a partir de la versión 9i, dispone de la funcionalidad de BULK sql, es decir, tratamiento de
conjuntos de filas "a montón". Realizar bulk inserts puede resultar muy práctico en el caso anterior, ya que las filas en el cursor no se ejecutan individualmente y no es preciso el filtrado previo de las filas.

Así pues, ante la necesidad de insertar en una tabla un volúmen de filas, omitir los errores e insertar las filas erróneas en una tabla, el siguiente código PL/SQL puede ser de gran utilidad, ya que su coste de ejecución resulta idéntico al del INSERT.


-- Código PL/SQL para insertar en una tabla, manejando errores en filas, con BULK INSERT
--------------------------------------------------------------------------------------
DECLARE
    type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
    registros  clientes_array;
    errores NUMBER;
    dml_errores EXCEPTION;
    contador_errores number := 0;
    PRAGMA exception_init(dml_errores, -24381);
    cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
    open c;
    loop
      fetch c BULK COLLECT INTO registros LIMIT 1000;      begin
       FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
         insert into TRASPASO_BD_CLIENTE values registros(i);
      EXCEPTION
        WHEN dml_errores THEN
          errores := SQL%BULK_EXCEPTIONS.COUNT;          contador_errores := contador_errores + errores;
          FOR i IN 1..errores LOOP
            dbms_output.put_line ('Se encontro el error '||;SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                    ':'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);          end loop;
     end;
     exit when c%notfound;
    END LOOP;
    close c;
    dbms_output.put_line( contador_errores );
end;


Su coste de ejecución es idéntico al de ejecutar:

insert into TABLA_DESTINO select * from TABLA_ORIGEN;


Comparativa de costes de ejecución.
******************************


insert into TRASPASO_BD_CLIENTE select * from BK_CLIENTE_BDM_03


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      4.91     102.61      15713      31424      88189      600000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.91     102.62      15713      31425      88189      600000


********************************************************************************

DECLARE
        type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
        registros   clientes_array;
        errores NUMBER;
        dml_errores EXCEPTION;
        contador_errores number := 0;
        PRAGMA exception_init(dml_errores, -24381);
        cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
       open c;
       loop
           fetch c BULK COLLECT INTO registros LIMIT 1000;
           begin
              FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
                 insert into TRASPASO_BD_CLIENTE values registros(i);
           EXCEPTION
               WHEN dml_errores THEN
                    errores := SQL%BULK_EXCEPTIONS.COUNT;
                    contador_errores := contador_errores + errores;
                    FOR i IN 1..errores LOOP
                        dbms_output.put_line
                        ('Se encontrs el error '
                          SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
                       ': '
                          SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
                    end loop;
          end;
          exit when c%notfound;
        END LOOP;
        close c;
        dbms_output.put_line( contador_errores
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      2.40       2.46          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.40       2.47          0          0          0           1


SELECT * FROM BK_CLIENTE_BDM_03


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      601     20.90      65.99      15712      16373          0      600000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      603     20.90      65.99      15712      16373          0      600000

INSERT INTO TRASPASO_BD_CLIENTE
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
  :B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,
  :B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,
  :B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    600     17.40      26.39          0      15835      87647      600000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      601     17.40      26.39          0      15835      87647      600000

********************************************************************************

RESUMEN:

Insert Select  -->   Execute      1      4.91    102.61      15713      31424      88189      600000


BULK INSERT   -->   Execute      1      2.40       2.46          0          0          0           1
+                   Fetch      601     20.90      65.99      15712      16373          0      600000
+                   Execute    600     17.40      26.39          0      15835      87647      600000


Tiempo insert select: 1'42''  (mismos bloques físicos y en caché)
Tiempo bulk insert:  1'33''  (mismos bloques físicos y en caché)
No hay diferencia...

4 comentarios:

Krimarck dijo...

Intente hacer lo indicado, pero cuando trato de insertar filas duplicadas, se gatilla DUP_VAL_ON_INDEX y no BULK_ERROR, luego aborta la ejecución de la instrucción completa. ¿A que se debe esto?, ¿es posible forzar que ignore DUP_VAL_ON_INDEX y seguir procesando?

De antemano gracias,

Kr!

alex dijo...

Hola, soy nuevo lector del Blog y de primera impresión me parece muy interesante, tanto que vista la gran ayuda que estas aportando me voy a atrever a hacer una consulta que tiene que ver con las insert, y es que tengo una sentencia que ejecutandola desde el TOAD (je,je..) funciona de maravilla, pero en cuanto la lanzo desde un programa en VB6.0 el tiempo de respuesta es tal que se sale por timeout, ¿ alguna sugerencia ?
Un saludo y gracias de antemano

Anónimo dijo...

Saludos, mas que nada me gustaria realizar un comentario hacerca de esto, pues bueno hace tiempo tube un problema paracido pues en mi batch emigraba gran cantidad de datos de una db hacia la mia y pues hacia algo con cursores pero realizando 1 a 1 las transacciones y pues el tiempo era exageradamente grande, al utilizar estos bulk insert mejoro demaciado el tiempo de ejecusion y por ahi me entere de que lo que hace oracle es por cada registro o transaccion realizada crea un log en tablas propias de oracle (para el caso del rollback) y pues al realizar transaccion de 1 por 1 esto hace demaciado lento el proceso, lo que hace el bulk insert es mover cantidades de datos almacenando solo en alguna clase de mem virtual o algo asi por lo que no crea mas que solo un log haciendo mas ajil el movimiento masivo de datos, bueno soy nuevo en oracle pero lo poco que se fue eso, solo queria compartirlo y pues si estoy en lo correcto o no hagan lo saber o publiquen su comentario :D grac. a y con lo del comentario de krimarck pues tu mensaje es normal porque esta tratando de insertar en tu tabla un registro duplicado, verifica tus indices y termina el proceso porque no puede hacer el proceso y claro que no te insertara nada porque el commit lo realiza al hacer toda la transaccion (intento hacer el insert y no hubo errores) y pues si quieres puedes definir tus propias exceptions (RAISE) aunque el error es por lo que te mencione.

Anónimo dijo...

hola, tengo el mismo problema que alex, yo utilizo PL/ sql developer pero da igual. El caso es que el bach se lanza desde VB.6.0 pero se me queda colgado despues de conectar con la BBDD.
mientras que ejecutandolo directamente no da problemas.
Estare pendiente de alguna idea.
Un saludo.

Amazon