Inserción Directa en Oracle (DIRECT INSERT)

La inserción directa de registros en tablas se realiza con las sentencias INSERT y MERGE (la parte de inserción) o desde una aplicación que utilice la interface directa de OCI (ej sqlloader). Cuando se necesita insertar un gran vólumen de filas en un tiempo óptimo, es necesario sacrificar cierta funcionalidad a expensas de velocidad. La mejora de rendimiento en la inserción no es gratis y hay ciertos requisitos que se deben cumplir y ciertas consecuencias a considerar antes de utilizarla. La inserción directa se activa de dos formas posibles:

- Agregando el hint /*+ APPEND */ en la sentencia INSERT INTO... SELECT ..
- Agregando el hint /*+ APPEND */ para INSERT INTO .. VALUES .. (en 11g R1)
- Agregando el hint /*+ APPEND_VALUES */ para INSERT INTO .. VALUES .. (en 11g R2)
- Ejecutando el insert en paralelo

En los siguientes casos no se puede utilizar la inserción directa

- La tabla a modificar tiene un trigger activo que se dispara con los inserts.
- La tabla a modificar tiene una foreign key habilitada.
- La tabla a modificar es una tabla indexada.
- La tabla a modificar esta almacenada en un cluster.
- La tabla a modificar contiene columna del tipo object type.


A continuación voy a comparar el insert normal con el directo poniendo foco en el espacio de redo y undo consumido en cada caso. Tambien voy a mostrar como el modo directo "saltea" el buffer cache. Justamente esto ultimo es la clave para acelerar los inserts, ya que se arman los bloques nuevos en memoria y se agregan a la tabla en forma directa sin necesidad de usar el cache. Durante la inserciòn no se incrementa el HWM y solo se actualiza al commitear la transacción. Por este motivo no se puede realizar ninguna operación adicional sobre la tabla modificada hasta tanto no se haya confirmado la transacciòn de insert directo. Si intentamos ejecutar cualquier sentencia que referencie a tabla luego de insertar en modo directo Oracle genera el error: "ORA-12838: No se puede leer/modificar un objeto despues de modificarlo en paralelo".

Para el ejemplo voy a crear una tabla T con dos columnas. Voy a usar una columna CHAR(500) para que se utilicen mas bloques sin tener que cargar tantas filas.

drop table t

create table t (x int,y char(500))

Ahora se insertaran 100000 registros en forma normal:

INSERT CONVENCIONAL
-------------------

insert into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

El insert demoró: 10.8 segundos.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

La cantidad de redo usado fue de: 53Mb

  
SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

1104k

La cantidad de undo usado fue de: 1104Kb

Con la siguiente consulta chequeamos si se utilizo el buffer para el insert:

select count(1)
from v$bh bh,
     user_objects ob
where bh.OBJD = ob.data_object_id
  and ob.object_name = 'T'
  and bh.STATUS != 'free'
  and bh.CLASS# = 1

7174 bloques

Se cargaron todos los bloques en cache para la inserción.

Veamos que sucede con el insert en modo directo:

INSERT DIRECTO
--------------

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

10.3s

Demoró solo .5 segundos menos que el insert convencional. Esto puede llegar a desalentarnos de usar el modo directo, ya que no se ve tanta mejora, pero es importante aclarar que la diferencia de tiempos se va a notar mas cuando trabajemos con una cantidad de registros mas importante, yo diria del orden de los millones.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

56Gb

Ahora consumió un poco mas de redo, pero veamos el undo:

SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

8kb

Con el insert directo solo se consumieron 8k de undo, es decir 138 veces menos que con la el insert convencional.

select count(1)
from v$bh bh,
     user_objects ob
where bh.OBJD = ob.data_object_id
  and ob.object_name = 'T'
  and bh.STATUS != 'free'
  and bh.CLASS# = 1
  
0 Bloques

No se usaron bloques de cache, justamente esto era esperable ya que como comenté mas arriba el insert directo no utiliza memoria intermedia.
Como se vio con el insert directo se redujo notablemente el espacio de undo. Para reducir tambien el redo debemos realizar el insert sobre una tabla que tenga desactivado el logging, es decir que este en nologging (si la base esta en noarchivelog ya tiene desactivado el logging para la tablas cdo se inserta en modo directo).

INSERT DIRECTO CON NOLOGGING
---------------------------------  

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

9.6s

Se redujo el tiempo de inserción, ahora fue de 9.6s

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

0

El consumo de redo fue nulo. Si bien esto parece muy bueno hay que tener en cuenta que deshabilitar el logging (en realidad se minimiza, ya que operaciones internas como correr el HWM o agregar extent generan redo y undo) provoca que ante un evento de falla no podamos realizar recovery de los datos recien insertados. Es recomendable realizar un backup lógico de las tablas o un backup incremental con RMAN inmediatamente luego de la carga directa. No es posible "bypassear" el redo para las tablas alojadas en tablespaces con force logging.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

8 kb

El undo se mantuvo exactamente igual que la prueba anterior.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

0 bloques

y tampoco se cargaron bloques en memoria.

Recordemos que para las 3 pruebas anteriores utilizamos una tabla sin indices. Veamos que pasa cuando se le agrega un indice a la tabla T

create index t_idx on t (x)

insert /*+ APPEND */ into t 
select rownum,dbms_random.string('a',20)
from dual
connect by rownum <= 100000

11.2.s

Claramente el insert demoró mas, ya que tuvo que mantener el indice actualizado durante las inserciones.

select round(ms.value/1024/1024) redo_size
from v$mystat ms,
     v$statname sn
where ms.STATISTIC# = sn.STATISTIC# 
  and sn.NAME = 'redo size'

7

El espacio consumido de redo ahora no fue 0, sigue siendo poco pero ahora es de 7kb, ya que tuvo que guardar información de redo para el indice.

SELECT t.used_ublk*8 undo_size
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')  

1600 kb

El consumo de undo tambien se incremento dado que se guardaron registros de undo para el nuevo indice

La siguiente tablita resume el resultado de las pruebas:




Como resumen, recomiendo utilizar siempre que sea posible el modo directo sobre todo cuando se necesita realizar una carga masiva (procesos ETL) y la performance en la carga es el principal objetivo. Tambien, en lo posible, y teniendo conciencia de lo que implica usar nologging, tambien recomiendo configurar la tabla receptoras en nologging. Por ultimo, es recomendable deshabilitar los indices (ponerlos en unusables) ya que como vimos en el ultimo caso, el mantenimiento de los indices durante la inserción genera mas undo/redo y ralentiza la carga general. Luego de la inserción habrá que reconstruir los indices inutilizados.