Cuando necesitamos hacer una carga masiva de datos en Oracle, hablamos de millones de registros, sea por una carga inicial de un sistema o una migración desde otro SGBD, el rendimiento en la inserción puede suponer una diferencia de tiempos considerable. Por tanto, en este tipo de proyectos es imprescindible trabajar en la optimización de carga de datos. De ello puede depender el éxito o fracaso del mismo.
Normalmente para las cargas masivas de datos en Oracle la mejor opción es la herramienta SQL*Loader. De hecho es la opción recomendada por Oracle, aunque no siempre es factible, debe ser la primera opción a tener en cuenta.
En el caso de una migración, es una tarea ardua, que supone la descarga a ficheros desde el SGBD origen. Comentaré un caso de migración online, desde Informix, por lo que la optimización de la carga de datos en Oracle, así como la lectura desde Informix, son clave para el éxito del proyecto. En el caso concreto se usó Mirth Connect como plataforma, con acceso a bases de datos través de JDBC. Existe mucha literatura sobre el uso de varias directrices de optimización, pero mi intención es mostrarlo con JDBC.
Contenidos
¿Qué supone la optimización de la carga de datos?
Cuando tenemos que insertar millones de registros en una base de datos, la eficiencia de los procesos pueden provocar un incremento exponencial de los tiempos. Una inserción poco eficiente puede llegar a suponer que el proceso sea inviable.
En el caso de una carga de toda una base de datos de tamaño considerable, la optimización del proceso de inserción puede reducir el tiempo de carga, desde 90 horas, a 6 horas. Este fue mi caso. Era evidente que 90 horas era inviable, suponía parar la actividad en un Hospital, pero 6 horas permitía realizar las tareas en horario nocturno.
Hay varias técnicas que evitan cálculos al SGBD a la hora de insertar un gran número de registros. Es evidente que, cuanto menos cálculo tenga que hacer el SGBD, mas ágil será la operación. Por tanto, en este artículo examinaremos algunas de las técnicas mas útiles para una inserción masiva.
Técnicas de optimización de carga de datos en Oracle
Índices y restricciones de integridad
Los índices, y las restricciones de integridad, ralentizan la inserción de registros en una tabla, ya que requieren que el SGBD realice mas operaciones. Por ejemplo, reordenación de un índice si está ordenado, comprobación fullscan si es un índice único o clave primaria, para chequear que no exista el valor, o comprobar, en una clave foránea, que hay referencias en el índice relacionado. Todo esto además de buscar hueco y almacenar la nueva referencia en el índice.
Es bastante evidente que, cuantos mas índices tenga una tabla, mas operaciones requiere al SGBD realizar con un registro nuevo, y cuantos menos, mas rápida será la inserción.
Es algo muy común, trabajando con grandes volúmenes de datos, desactivar las relaciones e índices para realizar la carga, y activarlos posteriormente.
Desactivar una restricción en Oracle con la siguiente instrucción:
alter table nombre_tabla DISABLE constraint nombre_constraint;Y para desactivar un índice, también existe la opción disable, aunque en este caso lo óptimo es borrarlo y volverlo a crear una vez cargados los datos:
drop index nombre_indice;Desactivar Autocommit en JDBC
Es fácil observar, cuando se trabaja con transacciones en Oracle, que el commit es mucho mas rápido que deshacer la transacción con un rollback. Oracle asume que la mayoría de las ocasiones las transacciones acaban con commit. Por tanto, la optimización lleva a mejorar la consolidación asumiendo que se realizará. El rollback tendrá que rescatar información.
Una opción interesante para mejorar los tiempos de carga, cuando realizamos muchas inserciones, es desactivar el modo Autocommit en JDBC, y trabajar en modo transaccional. Esto obliga a consolidar manualmente, pero acelera el proceso de inserciones con SQL compiladas.
En Oracle por defecto el autocommit está desactivado, se trabaja en modo transaccional, pero cuando usamos JDBC esto no ocurre, y necesitaremos desactivarlo.
Cuando trabajamos con JDBC la manera de desactivar el Autocommit en una conexión dbConn abierta en Oracle es mediante el método setAutoCommit(true/false):
dbConn.setAutoCommit(false);Este modo nos obliga a consolidar la transacción una vez hemos terminado, o bien hemos acumulado un número de inserts considerable, con el método commit:
dbConn.commit();NoLogging
El modificador NoLogging es una opción interesante para la optimización de cargas masivas de datos en Oracle. Permite omitir la escritura en el registro de REDO log. Eso evita cálculos al SGBD, lo que mejora el rendimiento considerablemente, pero añade un riesgo de no poder recuperar un error en la base de datos.
Además, más adelante veremos otra técnica que se potencia muchísimo cuando está activado NoLogging.
Uso:
alter table tabla nologging;Batch Insert
Existe un método en JDBC que permite acumular instrucciones en memoria antes de ejecutarlas, que se denomina inserción por lotes.
No se debe confundir con transaccionalidad, aunque pueda resultar similar, el batch insert no implica transaccionalidad, eso lo vimos en la opción de desactivar Autocommit.
El método addBatch() de un PrepareStatement permite agregar en memoria, lo hace el driver, antes de enviar al SGBD, una instrucción. Se puede ir acumulando instrucciones pendientes de envío hasta lanzar el método executeBatch().
¿Qué ocurre con la transaccionalidad?
Si tenemos activado el Autocommit, en el momento en el que invocamos a executeBatch() en cada instrucción del bloque el SGBD asegura la integridad. Si se llegase a producir un error de integridad en alguna de ellas, no todo el bloque sería afectado por rollback, las instrucciones anteriores al problema de integridad ya estarían consolidadas.
Si tenemos Autocommit desactivado, como dijimos en pasos previos, tendremos que ejecutar un commit en algún momento. Idealmente el commit debería coincidir con la ejecución del bloque.
Insertar una instrucción como batch insert en un PrapareStatement prdStmt:
prdStmt.addBatch(); Ejecutar un bloque batch:
prdStmt.executeBatch(); Inserción directa con el Hint APPEND
Los HINTs son una especie de parámetros de influencia en los que «sugerimos» al optimizador de Oracle la forma en la que debe actuar ante una sentencia SQL. Cuando en una sentencia SQL incluimos un HINT estamos forzando o pidiendo al optimizador que utilice un índice, un orden, o un cálculo de costes.
Existen otros HINTs como APPEND y APPEND_VALUES, que permite indicar a Oracle que se salte las restricciones, e incluso el segmento de Rollback o UNDO.
En el modo convencional de funcionamiento, en una inserción de registros, Oracle busca un hueco entre filas para rellenarlo, además de tener en cuenta las constraints, y escribir en los segmentos UNDO y ROLLBACK de la transacción.
Con el modo DIRECT PATH, sin embargo, Oracle inserta secuencialmente los datos en nuevos bloques, mas allá del High WaterMark. Con esto se consigue evitar el uso de segmentos ROLLBACK y UNDO, ya que no es posible que otro usuario pueda acceder a ellos, y permite eliminar el REDO, con la cláusula vista anteriormente NOLOGGING.
Para usar este HINT sólo hay que incluir el parámetro APPEND_VALUES tras la palabra INSERT, por ejemplo así:
insert /*+ APPEND_VALUES */ into TABLA values (?,?)Para mas información sobre este HINT, en la web de documentación de Oracle se explica cómo funciona, y características del DIRECT PATH INSERT.
Resultado de la optimización de carga de datos
En la prueba realizada durante una migración en real, con una de las tablas de mayor tamaño de la base de datos, la reducción de tiempos de carga fue considerable. Se pasó de 382 minutos de carga inicial, únicamente desactivando índices y restricciones, a 62 minutos utilizando todas las técnicas aquí descritas.
La inserción en esta tabla elegida como modelo se realizó con las siguientes condiciones:
- Se insertaron 2 millones de registros.
- La tabla destino tiene 10 campos de distintos tipos: char, integer, datetime, y el de mayor peso, un campo CLOB.
- El campo CLOB contiene ficheros XML de distinto tamaño, con una media de 50 kbytes.
En las pruebas realizadas se ha ido sumando las técnicas utilizadas en las pruebas anteriores. Esto es, en la última prueba se activan las técnicas utilizadas en todas las anteriores.
En el gráfico podemos observar las diferencias de tiempos empleados en la inserción de los 2 millones de registros en cada prueba, expresados en minutos.
Un ejemplo simple de estas técnicas de optimización de carga
A raíz de este artículo surgió una petición de un compañero para que pusiera ejemplos del código utilizado, por lo que he actualizado el artículo incluyendo un ejemplo.
Utilizaremos las técnicas vistas con una tabla muy simple, con dos campos, que crearemos con el siguiente script.
SQL> create table tabla(
2 campo1 number(10),
3 campo2 varchar2(100)
4 );
Table created.
Test 1: Insert convencional
En esta prueba se insertarán registros uno a uno, teniendo activado el Autocommit. Esto significa que cada registro se consolidará tras la inserción.
Únicamente se desactivan las restricciones de integridad de la tabla, y eliminado los índices, para evitar cálculos en el SGBD con cada inserción.
Eso sí, además, usaremos siempre PreparedStatement, vamos a partir de una mínima optimización. Utilizar Statement no está recomendado, es óptimo utilizar bind variables y PreparedStatement, puesto que el SGBD no tendrá que «compilar» la sentencia cada vez que se ejecuta, estará precompilada.
// Test 1 : Obtención de tiempos en la inserción de 1 millon de registros sin mas optimización que:
// Eliminar indices y constraints
// Utilizar PrepareStatement
// Ejemplo de optimización de carga de datos en Oracle.
// Jose Ramón Pascual
importPackage(Packages.oracle.jdbc.driver);
java.sql.DriverManager.registerDriver (new OracleDriver());
var i = 0;
try{
var dbConn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","disrupcion", "tecnologica");
logger.info("COMIENZO Test1: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
var prdStmt = dbConn.prepareStatement("insert into TABLA values (?,?)");
for (i = 0; i < 1000000; i++) {
prdStmt.setInt(1,i);
prdStmt.setString(2,"Cadena de texto del registro " + i);
prdStmt.execute();
}
prdStmt.close();
logger.info("FIN Test1: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
}catch(e){
logger.error("Se produjo un error : " + e);
}
prdStmt.close();
dbConn.close();
return;
Test 2: NoLogging
En esta prueba, antes de realizar las inserciones modificaremos la tabla para desactivar el Logging, y evitar inserciones en el Undo durante la carga.
Para ello ejecutamos la siguiente línea, que desactivará el logging en la tabla.
SQL> alter table tabla nologging;
Table altered.El script de carga utilizado es el mismo que en el Test 1, no añadimos por código ningún cambio.
Test 3: AutoCommit = False
En la siguiente prueba desactivaremos el modo Autocommit, de manera que consolidaremos la transacción por lote de inserciones, ahorrando al SGBD un gran número de operaciones.
Cada 5000 inserciones, ejecutaremos el commit del driver JDBC.
// Test 3 : Obtención de tiempos en la inserción de 1 millon de registros
// Optimización: Sin indices ni constraints + autocommit false
// Ejemplo de optimización de carga de datos en Oracle.
// Jose Ramón Pascual
importPackage(Packages.oracle.jdbc.driver);
java.sql.DriverManager.registerDriver (new OracleDriver());
var i = 0;
var lote = 5000;
try{
var dbConn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","disrupcion", "tecnologica");
dbConn.setAutoCommit(false);
logger.info("COMIENZO Test3: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
var prdStmt = dbConn.prepareStatement("insert into TABLA values (?,?)");
for (i = 0; i < 1000000; i++) {
prdStmt.setInt(1,i);
prdStmt.setString(2,"Cadena de texto del registro " + i);
prdStmt.execute();
if(i % lote == 0){
dbConn.commit();
}
}
dbConn.commit();
prdStmt.close();
logger.info("FIN Test3: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
}catch(e){
logger.error("Se produjo un error : " + e);
}
prdStmt.close();
dbConn.close();
return;
Test 4: Batch Insert
Añadimos un paso mas en nuestra optimización de la carga de 1 millón de registros. En este caso, las inserciones se realizan utilizando la Batch Insert, ajustando a 5000 inserciones antes de realizar el executeBatch, y el commit.
Ahora tenemos en nuestra prueba número 4 una inserción en tabla NoLogging, sin índices ni contraints, con Autocommit desactivado, y en inserción por lotes. Veremos nuestra carga de registros volar.
// Test 4 : Obtención de tiempos en la inserción de 1 millon de registros
// Optimización: Sin indices ni constraints + Nologging + autocommit false + Batch Insert
// Ejemplo de optimización de carga de datos en Oracle.
// Jose Ramón Pascual
importPackage(Packages.oracle.jdbc.driver);
java.sql.DriverManager.registerDriver (new OracleDriver());
var lote = 5000;
var i = 0;
try{
var dbConn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","disrupcion", "tecnologica");
logger.info("COMIENZO Test4: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
dbConn.setAutoCommit(false);
var prdStmt = dbConn.prepareStatement("insert into TABLA values (?,?)");
for (i = 0; i < 1000000; i++) {
prdStmt.setInt(1,i);
prdStmt.setString(2,"Cadena de texto del registro " + i);
prdStmt.addBatch();
if(i % lote == 0){
prdStmt.executeBatch();
dbConn.commit();
}
}
prdStmt.executeBatch();
dbConn.commit();
prdStmt.close();
logger.info("FIN Test4: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
}catch(e){
logger.error("Se produjo un error : " + e);
}
prdStmt.close();
dbConn.close();
return;
Test 5: Direct Path
Para finalizar, en esta prueba el HINT APPEND_VALUES para una inserción óptima conocida como DIRECT PATH, que permite no utilizar segmentos ROLLBACK y UNDO. Con esto conseguimos una mejora considerable de rendimiento.
En este punto tenemos todas las técnicas aplicadas en la misma prueba, y podemos comparar la diferencia de tiempos de carga desde el Test 1, hasta el Test 5.
// Test 5 : Obtención de tiempos en la inserción de 1 millon de registros
// Optimización: Sin indices ni constraints + NoLogging + autocommit false + Batch Insert + Direct Path
// Ejemplo de optimización de carga de datos en Oracle.
// Jose Ramón Pascual
importPackage(Packages.oracle.jdbc.driver);
java.sql.DriverManager.registerDriver (new OracleDriver());
var count = 0;
var lote = 5000;
var i = 0;
try{
var dbConn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","disrupcion", "tecnologica");
logger.info("COMIENZO Test5: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
dbConn.setAutoCommit(false);
var prdStmt = dbConn.prepareStatement("insert /*+ APPEND_VALUES */ into TABLA values (?,?)");
for (i = 0; i < 1000000; i++) {
prdStmt.setInt(1,i);
prdStmt.setString(2,"Cadena de texto del registro " + i);
prdStmt.addBatch();
if(i % lote == 0){
prdStmt.executeBatch();
dbConn.commit();
}
}
prdStmt.executeBatch();
dbConn.commit();
prdStmt.close();
logger.info("FIN Test4: " + DateUtil.getCurrentDate('yyyyMMddHHmmss'));
}catch(e){
logger.error("Se produjo un error : " + e);
}
prdStmt.close();
dbConn.close();
return;
Conclusión
Ahora que ya tenemos los tiempos de las pruebas podemos ver las diferencias.
El caso de NoLogging, el Test 2, donde el tiempo es mayor que en Test 1, no es algo representativo y tiene como explicación el hecho de que esta cláusula no haya tenido efecto al no estar la base de datos en modo noarchivelog.
Lo que sí es significativo es haber pasado de 366 segundos, en la inserción convencional, a 171 únicamente al desactivar el autocommit, y trabajar con transacciones. Y de ahí a la espectacular cifra de 5 segundos para insertar 1 millón de registros usando además Batch Insert y Direct Path.
Si además observamos la cantidad de operaciones escritas en el buffer REDO, con Direct Path y Nologging activado se reduce considerablemente. Esto agiliza las operaciones y reduce el consumo de recursos.
