martes, 4 de agosto de 2009

Como afecta la frecuencia de commits en los procesos de carga masiva

Hace ya casi 7 años leí una nota en el sitio http://www.dbasupport.com/ , donde un "especialista" aconsejaba commitear con la mayor frecuencia posible, dando argumentos falsos que no resistian a la mas mínima prueba. En ese momento yo estaba leyendo un libro de Tom Kyte donde aconsejaba justamente lo contrario, por eso le postee una pregunta a Tom en su reconocido sitio asktom.oracle.com comentandole lo que habia leido, pueden leerla en: "Issue Frequent COMMIT Statements", les aseguro que no tiene desperdicio. La verdad que nunca imaginé la repecución que tuvo eso, ya que el mismo Tom se comunicó con el autor de la nota y luego de unas idas y vueltas, el autor terminó admitiendo que lo que había escrito lo habia sacado de otro sitio y que analizandolo mejor coincidía que estaba mal. Luego este mismo caso fue referenciado por Tom en su libro "Effective Oracle by Design".

Es importante destacar que además de la degradación de performance, producida por las esperas del tipo sync writes debido a la escritura necesaria en los log files cada vez que se realiza un commit, también se promueven los errores ORA-01555 y las posibles violaciones de integridad cuanto mas frecuentes sean los commits. En esta nota solo les voy a mostrar el impacto de la sobrecarga de commits en el rendimiento de la base y no en los otros problemas derivados.

Voy a mostrarles con un ejemplo de prueba (Test Case) que cuanto menos commits se puedan efectuar mejor será la performance en los procesos de carga o actualización masiva de datos.

Para realizar la prueba usé una base de datos versión 10g R2 y se armé un bloque pl/sql anónimo que esencialmente recorre un cursor autogenerado de 1 millón de registros con una transaccionabilidad definida (cantidad de commits). Para la prueba se realizaron 3 iteraciones por cada valor de prueba. El código utilizado fue el siguiente:

declare
l_time int;
begin
l_time := dbms_utility.get_time;
for i in (select rownum x,
dbms_random.string('a',10) y,
sysdate+(dbms_random.value(-200,200)) z
from dual
connect by rownum <= 1000000)
loop

insert
into t values (i.x,i.y,i.z);
if
(mod(i.x,p_filas) = 0) then
commit
;
end
if;
end
loop;
dbms_output.put_line((dbms_utility.get_time-l_time)/100);
end;

Donde el parámetro p_filas denota la cantidad de filas insertadas por cada commit. El proceso carga una tabla T con campos: x (number); y (varchar2) y z (date) con un cursor que genera valores aleatorios en el momento. Este tipo de cursor se creo para evitar cualquier tipo de buffering que pudiera darse si se inserta desde una tabla fuente, ya que haría que la prueba no sea aislada debido a que desde la primera iteración quedarán cacheados en algún substitema de disco o en el cache de Oracle las filas a insertar.

Los resultados obtenidos fueron los descriptos en la siguiente tabla:



En el gráfico de abajo se ve como van bajando los tiempos a medida que se dilata la ejecución del commits, es decir cuanto mas filas se procesan por cada commit:



Como conclusión final podemos ver que cuanto menos commits se realicen mejores serán los tiempos de procesamiento. Por lo tanto, siempre que el tamaño de los segmentos de UNDO estén debidamente estimados y que las reglas de negocio lo permitan es recomendable “commitear” lo menos frecuentemente posible.

3 comentarios:

  1. Hola Pablo, sobre el desempeño con los commit, como hago para mejorar cuando utilizo un insert mediante EXECUTE IMMEDIATE ya que por lo que veo este hace commit automatico. (Lo necesito para un cargue masivo con un query dinamico).

    Gracias

    ResponderEliminar
  2. Que se ejecute dinamicamente no implica que se realice autocommit. El autocommit generalmente lo podes setear desde tu app, por ejemplo si usas sqlplus podes usar: SET AUTOCOMMIT ON. La base siempre usa autocommit en off, no importa si la sentencia es estatica o dinamica. Abajo te demuestro lo que te estoy diciendo:

    rop@DESA10G> drop table t;

    Tabla borrada.

    rop@DESA10G> create table t (x int);

    Tabla creada.

    rop@DESA10G> begin
    2 execute immediate 'insert into t values ('||1||')';
    3 end;
    4 /

    Procedimiento PL/SQL terminado correctamente.

    rop@DESA10G> select * from t;

    X
    ----------
    1

    rop@DESA10G> rollback;

    Rollback terminado.

    rop@DESA10G> select * from t;

    ninguna fila seleccionada

    Como se vió, no se realizo autocommit porque sino el rollback hubiese dejado la tabla vacia (sin el unico insert que tuvo).

    Por el contrario, si seteamos autocommit vemos lo siguiente:
    rop@DESA10G> set autocommit on
    rop@DESA10G> begin
    2 execute immediate 'insert into t values ('||1||')';
    3 end;
    4 /

    Procedimiento PL/SQL terminado correctamente.

    Confirmación terminada.
    rop@DESA10G> select * from t;

    X
    ----------
    1

    rop@DESA10G> rollback;

    Rollback terminado.

    rop@DESA10G> select * from t;

    X
    ----------
    1

    rop@DESA10G>

    Ahora si se realizó el autocommit.

    ResponderEliminar
  3. Hola, tengo un problema similar con los commit, tengo una app java con 150 hilos, cada hilo se encarga de insertar en una tabla (bastante grante por cierto) un registro producto de un analisis, aprox al día genero por este medio 6 millones de registro en esa tabla. El DBA me pide que no haga tantos commits porque afecto el performance, ¿qué opinas? Gracias.

    ResponderEliminar