BULK COLLECT con miles de registros y la cláusula LIMIT

Veo muchísimas veces, que cuando se utiliza BULK COLLECT en los códigos PL/SQL, no se coloca la cláusula LIMIT. Cuando no utilizamos ésta cláusula, lo único que ganamos es arruinar la memoria del proceso.
La cláusula LIMIT nos permite definir la cantidad de 'datos' que vamos a colocar en memoria. Cuando utilizamos LIMIT, lo ideal es definirlo en un valor entre 100 a 500. Personalmente, elijo el valor 100 porque en base a mi experiencia suele ser el mejor valor. Pero porque elijo un valor tan chico y no 1000 o 5000 por ejemplo? Bueno, por la simple razón que manejar gran cantidad de datos en memoria es más costoso que manejar poca cantidad.
Si elegimos un valor alto en el LIMIT, puede darse 3 casos:
- Que nuestro código se ejecute más rápidamente (improbable).
- Que nuestro código se ejecute en igual tiempo (improbable).
- Que nuestro código se ejecute más lentamente (probable).

Veamos un ejemplo para entender mejor las consecuencia de no utilizar la cláusula LIMIT.

Primero creamos una tabla TEST con 10.000 registros (para ver la diferencia del uso de la cláusula LIMIT, no hace falta realizar el ejemplo con millones de registros. Con unos miles de registros nos alcanza para entender el tema) y una tabla TEST_2 con la estructura de la tabla TEST pero sin registros:

SQL_9iR2> CREATE TABLE test AS
  2  SELECT level id , 'oracle_'||level texto
  3  FROM dual
  4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE test_2 AS
  2  SELECT *
  3  FROM test
  4  WHERE 1 = 2 ;

Table created.

Antes de ejecutar el primer código, voy a liberar la memoria que ya no se está utilizando en mi sesión actual para poder ver claramente la diferencia de las estadísticas tomadas en cada ejecución.

SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ejecutamos un código PL/SQL con Bulk Collect pero SIN la cláusula LIMIT:

SQL_9iR2> DECLARE
  2  TYPE t_array_number   IS TABLE OF NUMBER ;
  3  TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
  4  t_array_id     t_array_number ;
  5  t_array_texto  t_array_varchar2 ;
  6  CURSOR cur IS
  7      SELECT * FROM test ;
  8  BEGIN
  9      OPEN cur ;
 10      LOOP
 11
 12      FETCH cur BULK COLLECT INTO t_array_id , t_array_texto ;
 13
 14      FORALL i IN 1 .. t_array_id.COUNT
 15          INSERT INTO test_2
 16          VALUES (t_array_id(i) , t_array_texto(i)) ;
 17
 18      EXIT WHEN cur%NOTFOUND ;
 19
 20      END LOOP ;
 21      COMMIT ;
 22      CLOSE cur ;
 23  END ;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Antes de ejecutar el segundo código, voy a liberar nuevamente la memoria que ya no se está utilizando en mi sesión actual.

SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ahora ejecutamos el mismo código PL/SQL con Bulk Collect pero CON la cláusula LIMIT:

SQL_9iR2> DECLARE
  2  TYPE t_array_number   IS TABLE OF NUMBER ;
  3  TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
  4  t_array_id     t_array_number ;
  5  t_array_texto  t_array_varchar2 ;
  6  CURSOR cur IS
  7      SELECT * FROM test ;
  8  BEGIN
  9      OPEN cur ;
 10      LOOP
 11
 12      FETCH cur BULK COLLECT INTO t_array_id , t_array_texto LIMIT 100 ;
 13
 14      FORALL i IN 1 .. t_array_id.COUNT
 15          INSERT INTO test_2
 16          VALUES (t_array_id(i) , t_array_texto(i)) ;
 17
 18      EXIT WHEN cur%NOTFOUND ;
 19
 20      END LOOP ;
 21      COMMIT ;
 22      CLOSE cur ;
 23  END ;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Podemos ver que en la segunda ejecución, estoy cargando en memoria 100 registros en cada Fetch que realizo. A diferencia de la primer ejecución, que carga todos los datos de una vez.

Veamos las estadísticas obtenidas de las 2 ejecuciones anteriores:

Nombre                         Ejecución_1 Ejecución_2  Diferencia
------------------------------ ----------- ----------- -----------
LATCH.kwqit: protect wakeup ti           1           0          -1
LATCH.simulator lru latch                1           0          -1
LATCH.spilled msgs queues list           1           0          -1
LATCH.transaction allocation             3           0          -3
LATCH.session timer                      5           0          -5
LATCH.multiblock read objects            8           0          -8
LATCH.channel operations paren          11           0         -11
LATCH.child cursor hash table           20           8         -12
LATCH.Consistent RBA                    56           6         -50
LATCH.lgwr LWN SCN                      56           6         -50
LATCH.mostly latch-free SCN             56           6         -50
LATCH.active checkpoint queue           63           7         -56
LATCH.session idle bit                 183          45        -138
LATCH.enqueues                         219          49        -170
LATCH.redo writing                     241          25        -216
LATCH.SQL memory manager worka         337           0        -337
LATCH.messages                         427          42        -385
LATCH.simulator hash latch             844           4        -840
LATCH.dml lock allocation            1,428         154      -1,274
LATCH.shared pool                    1,586         271      -1,315
LATCH.row cache enqueue latch        1,648         194      -1,454
LATCH.cache buffers lru chain        1,521           5      -1,516
LATCH.library cache pin alloca       2,900         362      -2,538
LATCH.row cache objects              4,438         502      -3,936
LATCH.enqueue hash chains            4,841         508      -4,333
LATCH.checkpoint queue latch         6,296         521      -5,775
LATCH.session allocation            19,333       1,893     -17,440
LATCH.undo global data              30,208       2,945     -27,263
LATCH.redo allocation               30,716       3,231     -27,485
LATCH.sequence cache                42,506       4,124     -38,382
LATCH.library cache pin             60,580       6,241     -54,339
LATCH.library cache                 76,555       7,911     -68,644

LATCHES:

 Ejecución_1   Ejecución_2   Diferencia   Porcentaje
     670,187        67,521     -602,666      992.56%

Una de las cosas que me interesa mostrarles acerca de éstas ejecuciones son los LATCHES (loqueos). Si bien la ejecución de los 2 códigos PL/SQL demoraron exactamente lo mismo en ejecutarse (en éste ejemplo procesando solamente 10.000 registros), las estadísticas nos muestran que estamos empleados muchísimos más loqueos en la primer ejecución que en la segunda. Pero porqué sucede ésto? Recordemos que en la segunda ejecución, lo único que modificamos en el código fue el agregado de la cláusula LIMIT. Bien, como dijimos en el comienzo, manejar gran cantidad de memoria es más costoso que manejar poca cantidad, por lo que Oracle tiene que emplear mayor cantidad de loqueos para manejar los 10.000 registros que subimos a memoria en la primer ejecución, que manejar solamente 100 registros en la segunda ejecución.
Este ejemplo lo realizamos con un sólo usuario concurrente.... pero imagínese qué sucedería si tenemos muchos usuarios concurrentes realizando lo mismo que nosotros... y por lo tanto, generando gran cantidad de loqueos....

NOTA: Tenemos que evitar los loqueos a toda costa ya que los loqueos afectan la performance del sistema. Mientras mayor sea la cantidad de loqueos, nuestro sistema se vuelve cada vez menos escalable; y como consecuencia, cada vez soporta menor cantidad de usuarios concurrentes.