Cuando estamos intentando optimizar cargas masivas de datos en Oracle, una de las primeras recomendaciones que vamos a encontrar, incluso en la documentación de Oracle, es el uso del Hint Append para el modo Direct Path insert. Estos Hints aceleran considerablemente la carga, evitando operaciones en el SGBD. Esto nos puede llevar a pensar ¿Por qué no usarlo siempre?
Como todo, saltarse restricciones para hacer algo mas rápido entraña riesgos, que no podemos correr en cualquier momento. Sobretodo, si estamos en un entorno productivo en real, con múltiples usuarios, es necesario no correr riesgos. Además, en estos casos, y con las implicaciones que tienen estos Hints, pueden no funcionar correctamente.
Contenidos
Qué son y cómo funcionan los Hints
Un Hint es un comentario embebido en una SQL que se utiliza para alterar (se dice influenciar) la forma en la que Oracle debe ejecutarla. Existen varios tipos de Hints, la mayoría sirve para indicar al optimizer que altere el plan de ejecución de una consulta SELECT. Sin embargo hay otros Hints, como APPEND, cuya función está relacionada con el almacenamiento.
Los Hint deben ir siempre detrás de la palabra reservada SELECT:
SELECT /*+ HINT */ FROM NOMBRE_TABLA WHERE CONDICION;Un caso de uso de Hint muy habitual es indicarle a Oracle que utilice un índice concreto en una consulta poco frecuente para el que la indexación no es la adecuada. Es decir, si tenemos un conjunto de tablas, con sus índices ya optimizados según el plan de ejecución de las consultas mas frecuentes, pero vamos a lanzar una consulta diferente que se volverá pesada en costes para la indexación actual.
Esto podemos hacerlo con el Hint INDEX, que permite forzar el uso de un índice para una tabla concreta. Por ejemplo, con el siguiente Hint:
SELECT /*+ INDEX(nombre_tabla nombre_indice) */ * FROM nombre_tabla WHERE CONDICION;Estaríamos pidiendo a Oracle que fuerce el uso del índice «nombre_indice» de la tabla «nombre_tabla» en esta consulta que estamos lanzando.
Prefiero decir «pidiendo» que no «forzando», ya que, realmente, si Oracle puede usar el Hint, lo usará, pero si no, lo ignorará. Por esto se llama Hint (sugerencia).
En el caso de los Hint APPEND irán siempre detrás de la palabra reservada INSERT, de esta manera:
INSERT /*+ APPEND */ INTO NOMBRE_TABLA VALUES (...);Cómo funciona el Hint APPEND en Oracle
Cuando se insertan registros en una tabla en Oracle, el SGBD busca rellenar espacio disponible por debajo de la High Water Mark.
El Hint APPEND habilita el modo de inserción llamado Direct Path. Esto viene a ser, insertar cada registros de forma secuencial, consecutiva, y por encima del High WaterMark de la tabla. De esta manera, el SGBD no realiza búsquedas de huecos entrelazados en la tabla para rellenarlos.
El High WaterMark es la marca del momento en el que la tabla ocupó mas tamaño. Es decir, el mayor peso que ha tenido la tabla desde su creación. Al insertar datos por encima del HVM, se evita la utilización de segmentos ROLLBACK y UNDO, ya que los datos que se están insertando no pueden ser accedidos por otro usuario. Con esto, no solo nos ahorramos las operaciones de búsqueda de bloques libres para insertar, además estos datos no pasan por el buffer cache. Los datos que se insertan en modo DIRECT PATH se escriben directamente en los datafiles. Otro ahorro de costes, este modo no chequea las restricciones de integridad.
Son muchos los puntos a favor, pero ya se empiezan a vislumbrar los riesgos de estos puntos.
Un evidente, mirando los gráficos: No se aprovecha el espacio libre de la tabla, ya que los registros se insertan al final, encima del HWM. Esto es posible reconstruirlo después, pero si tuviésemos un Hint APPEND en entorno productivo, nuestra tabla estaría creciendo siempre. Aunque borrásemos registros, al insertar nuevos con APPEND volvería a crecer, aún estando vacía.
Tenemos tres formas de uso del Hint APPEND, dos de ellas a partir de Oracle 11g:
INSERT /*+ APPEND */ INTO NOMBRE_TABLA SELECT * FROM NOMBRE_TABLA2;Es la forma que existía desde antes, únicamente para una INSERT donde los valores a insertar provenían de una select. A partir de Oracle 11g R1 se incluye la posibilidad de inserción direct path con valores externos:
INSERT /*+ APPEND */ INTO NOMBRE_TABLA VALUES (...);Y a partir de Oracle 11g R2 se permite incluir los valores literales:
INSERT /*+ APPEND_VALUES */ INTO NOMBRE_TABLA VALUES (...);Condiciones de uso del Hint APPEND
Para que este modo se active, y obtener las máximas prestaciones reduciendo considerablemente las escrituras en REDO log, se deben cumplir alguna de estas condiciones:
- La base de datos debe estar en modo NOARCHIVELOG.
- La tabla implicada debe estar en el modo NOLOGGING.
Si no se cumple, el Hint APPEND no tendrá efecto en cuanto a la reducción de las escrituras en REDO, Oracle ignorará dicha sugerencia.
Consecuencias de utilizar el Hint APPEND en entornos no aislados
El Hint APPEND no parece funcionar correctamente
Como dijimos, el Hint APPEND funcionará si se cumplen las condiciones necesarias. En un entorno productivo es difícil que se den, por lo que en muchas ocasiones este Hint será ignorado por Oracle, o no tendrá el efecto esperado.
Veamos un ejemplo: Creamos una tabla de prueba, y realizamos un insert de 1 millón de registros, con el autotrace statistics activado.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> CREATE TABLE prueba(campo1 number, campo2 number);
Table created.
SQL> set autotrace traceonly statistics
SQL> insert into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
Statistics
----------------------------------------------------------
309 recursive calls
22919 db block gets
3685 consistent gets
0 physical reads
20948280 redo size
831 bytes sent via SQL*Net to client
836 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL>Hemos utilizado 20mb de redo para insertar estos registros. Es bastante. Si vamos a realizar una inserción masiva, el consumo del redo puede saturar.
Ahora, usaremos la misma SQL sobre la misma tabla, pero con el Hint APPEND para que use el modo de inserción Direct Path y reducir el consumo de redo.
SQL> insert /*+ APPEND */ into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
Statistics
----------------------------------------------------------
605 recursive calls
2933 db block gets
616 consistent gets
2 physical reads
14948280 redo size
819 bytes sent via SQL*Net to client
850 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
20 sorts (memory)
0 sorts (disk)
1000000 rows processedNo parece que hayamos conseguido el efecto deseado. El consumo de redo se ha reducido, pero sigue siendo excesivo.
Esto es porque la base de datos está en modo ARCHIVELOG y la tabla en modo LOGGING.
Cambiamos la base de datos a modo NOARCHIVELOG con SYSDBA:
SQL> Shutdown inmediate;
SQL> Startup mount;
SQL> Alter database noarchivelog;
SQL> alter database open;Ahora, haremos que cumpla la condición de estar la tabla implicada en modo NOLOGGING y repetiremos la operación para ver la diferencia de estadísticas.
SQL> alter table prueba NOLOGGING;
Table altered.
SQL> insert /*+ APPEND */ into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
Statistics
----------------------------------------------------------
256 recursive calls
2614 db block gets
775 consistent gets
0 physical reads
39600 redo size
817 bytes sent via SQL*Net to client
850 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL>Como podemos ver, ahora sí hemos reducido las escrituras en REDO, pasando de unos 20 mb iniciales, a 38k ahora.
Como es lógico, reducir las escrituras en REDO reduce las operaciones del SGBD, el consumo de recursos, y el tiempo de carga.
¿Podemos poner modo NOLOGGING en una tabla en entorno productivo?
No debemos, porque supone un riesgo. El modo NOLOGGING, como la palabra describe, no registra los cambios realizados en el Redolog Buffer. Esto significa que en caso de caída de la base de datos podría no llegar a recuperarse, o hacerlo en un estado inconsistente.
Por tanto, no podemos tener un entorno productivo multiusuario con tablas en NOLOGGING, implica riesgos, y de no hacerlo, el Hint APPEND no tiene el efecto que necesitamos.
Bloqueo de tabla
Oracle tiene por defecto el modo READ COMMITED, lo que evita problemas de bloqueos en un entorno productivo multiusuario, salvo ocasiones en las que se hacen las cosas mal.
Si estamos insertando registros normalmente en una tabla convencional, no tendremos problemas de concurrencia, incluso en una inserción masiva.
Sin embargo, si usamos el Hint APPEND, al hacer byPass sobre ROLLBACK/UNDO va a inhabilitar el modo READ COMMITED, y bloquea la tabla hasta finalizar la transacción con COMMIT.
Veamos un ejemplo con dos sesiones de usuarios distintas:
Usuario 1, insert normal y sin finalizar la transacción, no hacer COMMIT:
SQL> insert into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL>
Usuario 2, el READ COMMITED me permite acceder a la tabla y consultar los datos que están consolidados, sin los cambios de la transacción activa del usuario 1:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
0 rows updated.
SQL>No ocurre problema, ni error. El modo READ COMMITED sigue funcionando. Termino la transacción del usuario 1 y repito la operación del usuario 2.
Usuario 1:
SQL> insert into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL>Usuario 2:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
0 rows updated.
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL>Ahora haremos lo mismo, idéntico, pero el usuario 1 en lugar de insertar convencional, utilizará el Hint APPEND:
Usuario 1:
SQL> insert into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL>Usuario 2:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
0 rows updated.
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
¿Qué ha pasado? Al usuario 2 no le finaliza la consulta porque la tabla está bloqueada por el Direct Path insert del usuario 1. Hasta que no haga commit el usuario 1, no finaliza:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
0 rows updated.
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update disrupcion.prueba set campo1 = 0 where campo1 = 1;
1 row updated.
SQL>Solo hay que imaginar bloqueos de tablas en un entorno de producción con múltiples usuarios para concluir que el Hint APPEND no es posible utilizarlo.
ORA-12838
Uno de los errores mas frecuentes cuando se está utilizando el Direct Path de forma inadecuada, en entorno productivo, con accesos de otros usuarios, es el ORA-12838.
Veamos, sobre nuestra tabla prueba, un ejemplo del problema.
Primero, la truncamos para empezar de nuevo, hacemos un insert sin el Hint APPEND, y seguido, una select sobre la misma tabla, para que nos devuelva el número de filas.
SQL> truncate table prueba;
Table truncated.
SQL> insert into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL> select count(*) from prueba;
COUNT(*)
----------
1000000
SQL>Ahora repetimos la misma operación, pero incluyendo el Hint APPEND en la sentencia Insert, para activar el Direct Path.
SQL> truncate table prueba;
Table truncated.
SQL> insert /*+ APPEND */ into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL> select count(*) from prueba;
select count(*) from prueba
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL>Ahí lo tenemos… ¿Qué ha pasado?
El modo Direct Path inhabilita la lectura READ COMMITED, al hacer bypass sobre ROLLBACK/UNDO. Esto significa que nuestra transacción estará en modo aislado, y no se puede realizar ninguna otra consulta o modificación sobre la tabla, hasta hacer COMMIT.
Lo vemos con ejemplo: Repetimos la misma operación, pero con un COMMIT antes de la select.
SQL> truncate table prueba;
Table truncated.
SQL> insert /*+ APPEND */ into prueba select level,level+1 from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from prueba;
COUNT(*)
----------
1000000
SQL>Por tanto, no podemos usar el modo Direct Path en una base de datos no aislada, en entorno productivo con múltiples usuarios accediendo. Estaríamos creando un problema importante.
Conclusiones del Direct Path Insert
Como hemos visto, el modo Direct Path aporta muchas ventajas para cargas masivas, pero no es posible utilizarlo en producción por los riesgos y problemas que entraña.
Es muy recomendable cuando estamos haciendo una carga de una base de datos nueva, una migración, situaciones en las que necesitamos hacer una carga masiva. Esto es, en entornos aislados, donde sabemos que ningún otro usuario está utilizando la base de datos.
En entornos productivos, multiusuario, no es posible utilizar este HINT. Los problemas que puede crear son mucho mayores que las ventajas.
Una idea sobre “El Hint APPEND en Oracle”
Gracias por compartir este conocimiento, artículo muy útil, he probado algunas cosas que comentas sobre el Direct Path Insert y me ha servido mucho para una carga masiva.
¿Esto es lo mismo que usa el sqlLoader de Oracle?