Afinamiento SQL y PL/SQL para Desarrolladores

Bulk Collect y FORALL

Lo que aprenderemos en este capítulo

q Las construcciones BULK Collect y FORALL

q Técnicas de Optimización Especializada

2
Optimizando el Desempeño de PL/SQL
Técnicas de Procesamiento Bulk Processing
Acelere la ejecución de programas PL/SQL con sentencias de
Bulk Processing
CREATE OR REPLACE PROCEDURE ajusteSalarioxDept (
dept_id IN empleados.id_dept%TYPE
,porcentaje IN number)
IS Procesamiento fila x fila:
CURSOR empleados_c IS
SELECT id_empleado,salario,fecha_contratacion
elegante pero ineficiente
FROM empleados WHERE id_dept = dept_id;
BEGIN
FOR rec IN empleados_c LOOP

ajuste_salario (rec, porcentaje);

UPDATE empleados SET salario = [Link]

WHERE id_empleado = rec.id_empleado;
END LOOP;
END ajusteSalarioxDept;

3
Procesamiento fila x fila de DML en PL/SQL

Motor  de   Cambios  de  Contexto  

Ejecución  PL/ son  costosos!  
SQL  

Bloque  
PL/SQL  
Motor  de  
Ejecución  SQL  

4
Procesamiento fila x fila de DML en PL/SQL

Motor  de  
Ejecución  PL/
SQL   Motor  de  Ejecución  SQL  
FOR rec IN empleados_c LOOP
UPDATE empleados
SET salario = [Link]
WHERE id_empleado =
rec.id_empleado;
END LOOP;

Cambios  de  Contexto  

son  costosos!  

5
Procesamiento Bulk con FOR ALL
Motor  de  
Ejecución  PL/
FORALL indx IN SQL   Motor  de  Ejecución  SQL  
lista_de_emps.FIRST..
lista_de_emps.LAST
UPDATE empleados
SET salario = [Link]
WHERE id_empleado =
lista_de_emps(indx);
END LOOP;

Update...
Update...
Update...
Update...
Menos  cambios  de   Update...
contexto,  mejor   Update...
performance  

Page 6
Optimizando el Desempeño de PL/SQL
q BULK COLLECT
–  Úselo con queries implícitos y explícitos.
–  Mover datos desde tablas a collections.
q FORALL
–  Úselo con inserts, updates y deletes.
–  Mover datos desde collections a tablas.
q En ambos, el procesamiento a nivel de SQL no se modifica.
–  Idéntico manejo de transacciones y rollback segment.
–  Ejecutan igual número de sentencias SQL individuales.
–  Pero los triggers BEFORE y AFTER a nivel de statement solo se
dispararán una vez por sentencia FORALL.
7
Optimizando el Desempeño de PL/SQL
Técnicas de Procesamiento Bulk Processing
q Use BULK COLLECT para Queries

Create or Replace PROCEDURE prueba_bulk AS

TYPE t_codigo IS TABLE OF all_source%ROWTYPE;
l_codigo t_codigo;
contador pls_integer:=0;
BEGIN
SELECT *
BULK COLLECT INTO l_codigo
FROM all_source;
FOR indx IN 1 .. l_codigo.COUNT
LOOP
contador:=contador+1;
END LOOP;
END prueba_bulk;

Page 8
Optimizando el Desempeño de PL/SQL
Técnicas de Procesamiento Bulk Processing
q Use BULK COLLECT para Queries

create or replace
PROCEDURE prueba_cursor_normal AS
CURSOR codigo IS
SELECT *
FROM all_source;
rec codigo%rowtype;
contador pls_integer:=0;
BEGIN
OPEN codigo;
loop
fetch codigo INTO rec;
contador:=contador+1;
exit WHEN codigo%NOTFOUND;
end loop;
END prueba_cursor_normal;

Page 9
Optimizando el Desempeño de PL/SQL
Técnicas de Almacenamiento Bulk Processing
q Use BULK COLLECT para Queries

create or replace PROCEDURE TESTCASE_bulkcollect AS

t1 NUMBER;
t2 NUMBER;
BEGIN
t1:=dbms_utility.get_time;
prueba_cursor_normal;
t2:=dbms_utility.get_time;
dbms_output.put_line('Cursor normal :'||
to_char(t2-t1));
t1:=dbms_utility.get_time;
prueba_bulk;
t2:=dbms_utility.get_time;
dbms_output.put_line('Usando bulk collect:'||
to_char(t2-t1));
END TESTCASE_bulkcollect;

10

Page 10
Optimizando el Desempeño de PL/SQL
Limite el número de filas retornadas por BULK COLLECT.

CREATE OR REPLACE PROCEDURE RUEBAPERFORMANCECURSOR

IS
CURSOR cur •  Use la cláusula LIMIT para manejar la
IS
cantidad de memoria utilizada con la
SELECT * FROM all_source
WHERE ROWNUM < 20001; operación BULK COLLECT.
una_fila cur%ROWTYPE;
TYPE t IS TABLE OF cur%ROWTYPE •  Técnica recomendada en aplicaciones en
INDEX BY PLS_INTEGER; producción con grandes conjuntos de
muchas_filas t; datos.
BEGIN
OPEN cur;
•  Siempre verifique el contenido de la
LOOP
FETCH cur collection, en lugar de el atributo
BULK COLLECT INTO muchas_filas LIMIT 100; %NOTFOUND, para determinar si se han
--procesamiento de los elementos de la collection procesado todas las filas.
procesar(muchas_filas);
EXIT WHEN muchas_filas.COUNT=0;
END LOOP;
CLOSE cur;
END PRUEBAPERFORMANCECURSOR;
11

Page 11
Optimizando el Desempeño de PL/SQL
Use la sentencia FORALL con
Bulk Bind PROCEDURE ajusteSalarioxDept (
dept_id IN empleados.id_dept%TYPE
Tenga en cuenta cuando use FORALL ,nuevoSalario IN number)
Debe saber como usar collections ! IS
Una sola sentencia DML por cada FORALL. BEGIN
Puede usarse SQL Dinámico y Estático con ……
FORALL. FORALL indx IN
lista_empleados.FIRST ..
Lista_empleados.LAST
UPDATE empleados
SET salary = nuevoSalario
WHERE id_empleados =
lista_empleados (indx);
END;

12

Page 12
Optimizando el Desempeño de PL/SQL
Técnicas de Almacenamiento Bulk Processing
Limitaciones y Características Clave de FORALL

•  Use SQL%BULK_ROWCOUNT para determinar el número de filas modificadas por cada

sentencia ejecutada
•  SQL%ROWCOUNT retorna el total de filas modificadas por todo el FORALL.
•  Use SAVE_EXCEPTIONS y SQL%BULK_EXCEPTIONS para continuar más allá de las
excepciones en todas las sentencias SQL.
•  Recuperación y logging de errores
•  No pueden referenciarse campos de registros en collections desde la sentencia DML

13

Page 13
Optimizando el Desempeño de PL/SQL
Técnicas de Almacenamiento Bulk Processing
De Código Anticuado a Moderno

El código PL/SQL tradicional a menudo utiliza un cursor FOR loop y mútiples DMLs
dentro del loop.
Un estilo fácil de entender, fila-x-fila con la opción de detectar y procesar
excepciones.
Elegante y flexible, pero lento.
Bulk processing significa un cambio a un "estilo en fases".
Fase 1: traer datos con BULK COLLECT
Fase 2: preparar datos en collections
Fase 3 – N: ejecute un FORALL para cada sentencia DML

14

Page 14
Optimizando el Desempeño de PL/SQL
Técnicas de Almacenamiento Bulk Processing
Conclusiones Bulk Processing

q Característica de performance tuning de PL/SQL más importante.

–  Casi siempre es la forma más rápida de ejecutar operaciones SQL multi-fila en
PL/SQL.
q Se sacrifica simplicidad de código por ejecución significativamente más rápida.
–  En Oracle Database 10g y superior, el compilador optimiza automáticamente los
loops FOR para que tengan la eficiencia de BULK COLLECT.
–  No se necesita conversión a menos que el loop tenga DML.
q Hay que considerar el impacto en la memoria PGA/UGA !

15

Page 15
Optimizando el Desempeño de PL/SQL

Técnicas de Optimización Especializadas

16
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas

q Existe algunas técnicas para mejorar la ejecución de PL/SQL que sólamente le ayudarán en
situaciones "extremas".
–  El hint de NOCOPY
–  Aproveche las pipelined table functions

17

Page 17
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas
El hint de NOCOPY

q Por default, Oracle pasa todos los parámetros OUT e IN OUT por valor, no por referencia.
–  Esto significa que los parámetros OUT e IN OUT siempre requieren algún tipo de copia
de datos.
–  Todos los parámetros IN se pasan por referencia (sin copiar).
q Con NOCOPY, se desactiva el proceso de copiado.
–  Pero ésto tiene un riesgo: Oracle no realizará un "rollback" automático de los cambios a
variables si el programa con NOCOPY dispara una excepción

PROCEDURE calc2 (p_tab IN OUT NOCOPY t_tabla);

18

Page 18
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas
Pipelined table functions

•  Una table function es una función que se puede llamar desde una cláusula FROM
en un query, y que el resultado sea equivalente a seleccionar de una tabla
relacional.

•  Table functions permiten hacer transformaciones complejas a los datos y luego

accederlos mediante un query: ¡"sólo" filas y columnas!
•  No todo puede hacerse con SQL.

•  Pueden usarse para transferir datos a otros lenguajes

•  Java, por ejemplo
19

Page 19
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas
Construyendo una table function

•  Una table function debe devolver una nested table o varray basado en un tipo
definido en el esquema
•  Tipos definidos en un paquete PL/SQL sólo pueden usarse con pipelined table
functions.

•  El function header y la forma en que ésta es invocada debe ser compatible con
SQL: todos los parámetros usan SQL types; sin notación nombrada
•  En algunos casos (streaming and pipelined functions), el parámetro IN debe
ser un cursor variable – un resultado de un query.

20

Page 20
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas
“Streaming” de datos con table functions

-­‐  Los  datos  resultantes  se  envían  poco  a  poco  ,  en  lugar  del  resultado  
completo  como  funciona  con  las  table  func=ons  regulares.  
 
-­‐  Las  funciones  pipelined  permiten  conver=r  procedimientos  PL/SQL  en  
generadores  de  filas  para  procesamiento  SQL  Bulk,  combinando  lógica  
compleja  de  transformación  con  los  beneficios  del  SQL.  

21

Page 21
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas
Uso de pipelined functions para mejorar performance

CREATE OR REPLACE FUNCTION mis_filas(cantidad_filas in

number)
RETURN lista_numeros_t PIPELINED

q Pipelined functions permite retornar datos iterativamente, de

forma asincrona a la terminación de la función
–  A medida que los datos se producen dentro de la función,
se envían al proceso/query original
q Pipelined functions sólamente pueden llamarse desde SQL
–  No tienen sentido en bloques PL/SQL sin multi-thread

22

Page 22
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas
Posibles usos de pipelined functions

q Ejecución de funciones en paralelo

–  Desde Oracle9i Database Release 2 y superior, use la clausula PARALLEL_ENABLE
para que su pipelined function forme parte de un parallel query.
–  Crítico para aplicaciones de data warehouse
q Mejore la velocidad de entrega de datos a interfaz de usuario
–  Use una pipelined function para "servir" datos a una webpage y permitir que los
usuarios visualicen los datos aún antes de que la función haya finalizado
q Además las pipelined functions usan menos PGA memory que las que no son pipelined!

23

Page 23
Optimizando el Desempeño de PL/SQL
Obteniendo filas de una pipelined function
CREATE OR REPLACE TYPE tipo_tab_num AS TABLE OF NUMBER;
/
CREATE FUNCTION gen_filas (
filas IN PLS_INTEGER
) RETURN tipo_tab_num PIPELINED IS
BEGIN
FOR i IN 1 .. filas LOOP
PIPE ROW (i);
END LOOP;
RETURN;
END;
/

24

Page 24
Optimizando el Desempeño de PL/SQL
Table functions – Resumen

q Table functions ofrecen nueva flexibilidad para el desarrollo PL/SQL

q Pipelined table functions adicionan performance a ésta flexibilidad. Estas funciones ofrecen
mejor uso de la memoria y menores tiempos de ejecución por que retornan resultados
parciales (a medida que se van obteniendo resultados estos se van entregando al ambiente
de llamada)

q Considere usarlas cuando …

–  Necesite retornar resultados complejos a través de una capa SQL;
–  Necesita llamar una función dentro de un query y ejecutarlo en paralelo.

25

Page 25
Optimizando el Desempeño de PL/SQL
Técnicas de optimización especializadas - Resumen

q Aproveche las características más importantes de optimización:

–  BULK COLLECT y FORALL
–  Caché de datos
q P riorice la facilidad de mantener y cambiar su código sobre código
excesivamente optimizado.

q Identifique cuellos de botella y aplique las técnicas de optimización apropiadas

para resolverlos.

26

Page 26