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.
