/*+ APPEND */ pros y contras.

Recientemente ha caído en mis manos un código PL/SQL (en forma de ‘package’) en el cual se utilizaban profusamente ‘hints’ /*+ APPEND */ en varias partes del mismo, seguidos -claro- de los correspondientes ‘COMMIT’. Esto (hacer ‘commit’ en medio de procesos), dentro de un paquete/procedimiento es algo que, de entrada, repele mis más primarios instintos. Como quiera que pregunté al programador en cuestión y me dijo que lo hacía ‘por rendimiento’, llegué a la conclusión de que muchas veces ciertos programadores ‘oyen campanas y no saben dónde’. Él había oído que así los ‘INSERTS’ corren más, pero no conocía todos los aspectos que, en un entorno real (Base de Datos OLTP con múltiples usuarios), pueden provocar efectos muy perjudiciales.

Vamos a intentar ver las peculiaridades de este ‘hint’ para poder decidir cuando utilizarlo y cuando no.

Para empezar, hay que tener claro qué hace exactamente este ‘hint’: /*+ APPEND */ inserta los datos en los segmentos utilizando bloques nuevos por encima del HWM (High WaterMark). Esto hace que se evite la utilización de segmentos de ‘ROLLBACK/UNDO’, ya que no hay posibidad de que otro usuario registre actividad sobre los mismos. A esto se le llama ‘DIRECT PATH‘. Aquí es (sobre todo si se suma a otros aspectos que pueden también eliminar REDO, como el uso de ‘NOLOGGING’) donde se consigue una mejora en el rendimiento. Esto viene de perlas en el caso de cargas masivas de tablas.

Pero ¿qué pasa si lo utilizamos en un entorno multiusuario (como nuestro amigo el programador lo hacía en mitad de la utilización normal de una base de datos OLTP)?

Como siempre, crearemos una de nuestras tablas de pruebas:

carlosal@db01.xxxxxx> SELECT * FROM V$VERSION
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlosal@db01.xxxxxx> CREATE TABLE PRUEBA07 AS SELECT *
  2  FROM DBA_OBJECTS WHERE 1=0
  3  /

Tabla creada.

carlosal@db01.xxxxxx>

Ahora insertamos filas e inmediatamente intentamos acceder a la tabla (contando cuantas filas tiene)

Primero sin ‘hint’:

carlosal@db01.xxxxxx> INSERT INTO PRUEBA07 SELECT * FROM DBA_OBJECTS
  2  /

11174 filas creadas.

carlosal@db01.xxxxxx> SELECT COUNT('X') FROM PRUEBA07
  2  /

COUNT('X')
----------
     11174

carlosal@db01.xxxxxx> ROLLBACK
  2  /

Rollback terminado.

carlosal@db01.xxxxxx>

Ningún problema. Vamos ahora a utilizar el ‘hint’:

carlosal@db01.xxxxxx> INSERT /*+ APPEND */ INTO PRUEBA07
  2  SELECT * FROM DBA_OBJECTS
  3  /

11174 filas creadas.

carlosal@db01.xxxxxx> SELECT COUNT('X') FROM PRUEBA07
  2  /
SELECT COUNT('X') FROM PRUEBA07
                       *
ERROR en línea 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

carlosal@db01.xxxxxx> COMMIT
  2  /

Confirmación terminada.

carlosal@db01.xxxxxx> SELECT COUNT('X') FROM PRUEBA07
  2  /

COUNT('X')
----------
     11174

¡Ups! Cuando intentamos acceder a la tabla ANTES DE HACER UN COMMIT, nos aparece el ORA-12838. Hasta que no hacemos un ‘commit’ las cosas no funcionan como esperamos.

La explicación es que al utilizar el ‘hint’ y ‘saltarnos’ en ROLLBACK/UNDO estamos imposibilitando el famoso READ COMMITED, por el cual veremos los datos de nuestra transacción en modo aislado. Cuando hacemos el ‘commit’ volvemos al ‘estado normal de las cosas’ y sólo entonces podemos ver que tenemos 11174 filas en la tabla. Esto explica por qué nuestro programador intercalaba ‘commits’ dentro de su procedimiento. Si no, no tendría visibilidad sobre los datos insertados.

Este es uno de los aspectos ‘peliagudos’ del uso de /*+ APPEND */, pero hay más.

Supongamos nuevas inserciones sobre una tabla existente sobre la cual se registra activdad de otros usuarios. En un insert ‘normal’ no habrá problemas de concurrencias. Si hacemos un ‘insert’ masivo mientras otros usuarios modifican datos, la base de datos lo asume ‘como si nada’:

Sesión 1:

carlosal@db01.xxxxxx> INSERT INTO PRUEBA07 SELECT * FROM DBA_OBJECTS
  2  /

11174 filas creadas.

carlosal@db01.xxxxxx>

Nótese que no hemos hecho el ‘commit’, por lo que la transacción está activa.

En la sesión 2:

carlosal@db01.xxxxxx> SELECT MIN (OBJECT_ID), MAX(OBJECT_ID) FROM PRUEBA07
  2  /

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
             2          44228

carlosal@db01.xxxxxx> UPDATE PRUEBA07
  2  SET OBJECT_ID = 44226 WHERE OBJECT_ID =2
  3  /

1 fila actualizada.

carlosal@db01.xxxxxx> ROLLBACK
  2  /

Rollback terminado.

carlosal@db01.xxxxxx>

La sesión 2 puede funcionar sin novedad. Pero si la sesión 1 utiliza el famoso ‘hint’ tenemos que:

Sesión 1:

carlosal@db01.xxxxxx> INSERT /*+ APPEND */ INTO PRUEBA07
  2  SELECT * FROM DBA_OBJECTS
  3  /

11174 filas creadas.

carlosal@db01.xxxxxx>

Otra vez hemos dejado la transacción pendiente.

Sesión 2:

carlosal@db01.xxxxxx> UPDATE PRUEBA07 SET OBJECT_ID = 44226
  2  WHERE OBJECT_ID =2
  3  /
_

Ahora la sesión 2 se queda esperando. La sesión 1 HA BLOQUEADO LA TABLA para poder efectuar su ‘insert’ en ‘DIRECT PATH’, y hasta que no concluya su transacción, la tabla permanecerá bloqueada.

Ni que decir tiene que en un entorno multiusuario esto es el típico ‘peor caso posible’.

En resumen:

Yo no soy muy partidario de los ‘hints’ en general (aunque siempre hay excepciones, claro), pero este ‘hint’ en particular puede ser especialmente dañino. /*+ APPEND */ debería usarse casi exclusivamente en herramientas -‘scripts’- de carga masiva de datos y sólo bajo determinadas circunstancias (principalmente cuando tenemos un entorno ‘aislado’ en el cual no hay más usuarios accediendo a la tabla sobre la que estamos insertando datos).
Por otra parte, bien utilizado en esos casos, su uso puede dar rendimientos espectaculares.

Pero siempre -siempre- sabiendo exactamente qué estamos haciendo. Y por qué.

Saludos.

Carlos.

18 respuestas a /*+ APPEND */ pros y contras.

  1. ivan dice:

    muy buena su explicación!

  2. josgut dice:

    No es del todo preciso su explicación, para que no se registren los cambios en el redo de un objeto este debe tener incluido el parámetro NOLOGGING.

    Otra cosa, esta claro que se trata de una instrucción masiva, si no se quieren tener problemas con otros procesos, lo mejor es bloquear el objeto y realizar la inserción masiva, estas tareas obviamente hay que centralizarlas en un proceso.

    Un saludo.

  3. carlosal dice:

    josgut:

    “No es del todo preciso su explicación, para que no se registren los cambios en el redo de un objeto este debe tener incluido el parámetro NOLOGGING.”

    No sé si has leído con detenimiento el artículo, donde dice:

    “Aquí es (sobre todo si se suma a otros aspectos que pueden también eliminar REDO, como el uso de ‘NOLOGGING’) “.

    Y no. El ‘objeto’ no tiene por qué tener el ‘parámetro’ (yo diría cláusula) ‘NOLOGGING’ en su DDL. NOLOGGING funciona para objetos y para sentencias.

    Saludos.

    Carlos.

  4. Pedro dice:

    Hola.

    Otra contraindicacion importe, es con el hint /*+APPEND*/ no se reaprovecha el posible espacio libre de la tabla.

    En el caso de carga de datos parciales, aunque previamente se haga un DELETE de los datos a sobreescribir, los datos nuevos siempre iran al final de la tabla. Por lo tanto, se puede llegar al caso, que una tabla solo haga que crecer y crecer, hasta que el tablespace este al 100%, y despues error por falta de espacio libre. (Habiendo espacio libre antes del HWM de la tabla ) ¡Que contradiccion!!

    Un saludo

    Pedro

  5. carlosal dice:

    Por supuesto, aunque siempre se puede volver a aprovechar como se explica aquí.

    Saludos.

    Carlos.

  6. elPico dice:

    josgut tiene razón. La explicación es correcta pero no pasa de ahí. Es evidente que se trata de una carga masiva. Lo que si es incorrecto es el uso indiscriminado de este hint, como por ejemplo en un insert simple, pero en extracciones y cargas de datos es altamente recomendable, siempre que antes, claro está, se haga un truncate de la tabla, porque entonces si que pasaría lo que dice Pedro.

    Saludos,

    elPico

  7. carlosal dice:

    “josgut tiene razón. “

    Pues no, no tiene razón. Una cosa es /*+ APPEND */ que evita UNDO, y otra cosa es NOLOGGING que evita REDO.

    “La explicación es correcta pero no pasa de ahí.”

    ¿Y qué querías, la fórmula de la Coca-Cola también?

    “Es evidente que se trata de una carga masiva.”

    ¿Cómo puedes decir eso? Tú NO conoces el proceso original del programador que originó el artículo. Tu ‘intuyes’, pero intuir una cosa no la hace ‘evidente’. Además, en este caso tu intuición falla.

    “…pero en extracciones y cargas de datos…”

    No veo como /*+ APPEND */ se puede utilizar en extracciones.

    Saludos.

    Carlos.

  8. […] efecto. Para los que hayan visto este artículo (y los que no): en determinadas situaciones /*+ APPEND */ y NOLOGGING trabajando juntos pueden […]

  9. GNLS dice:

    Perfecta explicación, parece mentira que un post de hace 2 años sea la mejor explicación de pros y contras de /*+ APPEND */. Gran blog.

  10. Julia Pozo dice:

    Gracias, me has ayudado con tu explicación. Utilizaba el hint /* APPEND*/ en una carga masiva para evitar generación de redo, tenía el ORA reportado… y no evitaba el redo… Gracias de nuevo.

  11. Francisco Estepa dice:

    Excelente post..Es muy importante cada una de tus aclaraciones Carlos, en mi opinión, lo que más resalta de este articulo es el uso del direct path y de este hint, el cual es muy util en cargas masivas de datos, a través de scripts, más no dentro del código fuente PL; Gracias por tus comentarios.

  12. OraDBA dice:

    Muchas Gracias por la explicacion, me ha sido de gran ayuda para mis scripts.

  13. JoanN dice:

    La explicacion no fue muy clara

  14. […] journal -por otra parte, hay que decir que eso es exactamente lo que hace Oracle con ciertos hints (/*+ APPEND */) para escribir en bloques nuevos evitando el […]

  15. ERNESTO dice:

    TAMBIEN SE PUEDE USAR EL NOLOGGING PARA HACER INSERT MASIVOS Y SEA MAS RAPIDO.

  16. Jorems dice:

    Excelente explicación Carlos

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: