Oracle Live

22/08/2016

PLSQL – BULK COLLECT with FORALL estudio rendimiento

Filed under: PLSQL — mogukiller @ 4:12 pm

Seguramente en el día a día os haya tocado mirar la sintaxis de algun PLSQL y ver por que ‘va lento’. Y en la mayoría de los casos ves cosas muy curiosas. Os dejo por aqui varios ejemplos donde se hace lo mismo pero el resultado de tiempos y consumo de PGA es distinto.

-- Creamos el entorno con 1M de registros.
-- Monitorizacion de la PGA
-- Metodo FOR LOOP
-- Metodo BULK COLLECT
-- Metodo BULK COLLECT with FORALL
-- Metodo BULK COLLECT LIMIT with FORALL

-- Creamos el entorno con 1M de registros.

CREATE TABLE T_PRUEBAS AS
SELECT level AS id,
TRUNC(DBMS_RANDOM.value(1,254)) AS valor,
'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;

CREATE INDEX IDX_ID ON T_PRUEBAS(ID);

EXEC DBMS_STATS.gather_table_stats('MOGU', 'T_PRUEBAS');


-- Monitorizacion de la PGA

cat >show_pga.sql
BREAK ON REPORT
COMPUTE SUM LABEL Total OF ALLOCATED USED MAX_ALLOCATED ON REPORT
SELECT 
S.SID, P.CATEGORY, P.ALLOCATED, P.USED, P.MAX_ALLOCATED
FROM 
V$SESSION S, V$PROCESS B, V$PROCESS_MEMORY P
WHERE 
S.PADDR = B.ADDR
AND B.PID = P.PID 
AND S.sid = &enter_sid;

exec dbms_session.FREE_UNUSED_USER_MEMORY ;

-- Metodo FOR LOOP

DECLARE 
   CURSOR c_data IS
	SELECT ID,VALOR FROM T_PRUEBAS WHERE ID<1000000;	  
BEGIN
	FOR l_item IN c_data
	LOOP
		IF l_item.valor <100 THEN
			l_item.valor:=l_item.valor*1.1;			
		ELSE
			l_item.valor:=l_item.valor*0.9;
		END IF;		
		UPDATE T_PRUEBAS SET VALOR=l_item.valor WHERE ID=l_item.id;
	END LOOP;	
END;
/

/*
10000 --> Elapsed: 00:00:00.86

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
 ---------- --------------- ---------- ---------- -------------
       493 PL/SQL               27960      23200         59904

100000 --> Elapsed: 00:00:08.14

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL               27960      23104         59904       

1000000 --> Elapsed: 00:01:20.30

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL               27960      23008         59904       
	   
*/

-- Metodo BULK COLLECT

DECLARE 
	
 CURSOR c_data IS
	SELECT ID,VALOR FROM T_PRUEBAS WHERE ID<1000000;	   
	
  TYPE T_PRUEBASColumns IS RECORD
  (
	id			T_PRUEBAS.id%TYPE,
	valor 		T_PRUEBAS.valor%TYPE	
  );
  
  TYPE T_PRUEBASTab IS TABLE OF T_PRUEBASColumns;
  l_item_recs T_PRUEBASTab;		
BEGIN
	OPEN c_data;
	FETCH c_data BULK COLLECT INTO l_item_recs;
	CLOSE c_data;
	
	-- Procesamos los registros
	FOR i IN 1..l_item_recs.count
	LOOP
		IF l_item_recs(i).valor <100 THEN
			l_item_recs(i).valor:=l_item_recs(i).valor*1.1;			
		ELSE
			l_item_recs(i).valor:=l_item_recs(i).valor*0.9;
		END IF;					
		UPDATE T_PRUEBAS SET VALOR=l_item_recs(i).valor WHERE ID=l_item_recs(i).id;
	END LOOP;	
END;
/

/*
10000 --> Elapsed: 00:00:00.76

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL               27960      23296       1956704       

100000 --> Elapsed: 00:00:07.39

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL               32336      27328      19212416       

1000000 --> Elapsed: 00:01:15.10

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL               32336      27232     191709656
	   
*/

-- Metod BULK COLLECT with FORALL


DECLARE 
 CURSOR c_data IS
	SELECT ID,VALOR FROM T_PRUEBAS WHERE ID<1000000;	   
	
  TYPE T_PRUEBASColumns IS RECORD
  (
	id			T_PRUEBAS.id%TYPE,
	valor 		T_PRUEBAS.valor%TYPE	
  );
  
  TYPE T_PRUEBASTab IS TABLE OF T_PRUEBASColumns;
  l_item_recs T_PRUEBASTab;		
BEGIN
	OPEN c_data;
	FETCH c_data BULK COLLECT INTO l_item_recs;
	CLOSE c_data;
	
	-- Procesamos los registros
	FOR i IN 1..l_item_recs.count
	LOOP
		IF l_item_recs(i).valor <100 THEN
			l_item_recs(i).valor:=l_item_recs(i).valor*1.1;			
		ELSE
			l_item_recs(i).valor:=l_item_recs(i).valor*0.9;
		END IF;							
	END LOOP;	
	FORALL j IN 1..l_item_recs.count	
		UPDATE T_PRUEBAS SET VALOR=l_item_recs(j).valor WHERE ID=l_item_recs(j).id;
END;
/
/*
10000 --> Elapsed: 00:00:00.58

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL               70200      64304       1998912       

100000 --> Elapsed: 00:00:04.99

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL              448232     425040      19628312       

1000000 --> Elapsed: 00:00:48.11

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       493 PL/SQL             4212760    4051232     195890080       
	   
*/

-- Metod BULK COLLECT LIMIT with FORALL

DECLARE 
	
 CURSOR c_data IS
	SELECT ID,VALOR FROM T_PRUEBAS WHERE ID<1000000;	   
	
  TYPE T_PRUEBASColumns IS RECORD
  (
	id			T_PRUEBAS.id%TYPE,
	valor 		T_PRUEBAS.valor%TYPE	
  );
  
  TYPE T_PRUEBASTab IS TABLE OF T_PRUEBASColumns;
  l_item_recs T_PRUEBASTab;			
BEGIN
	OPEN c_data;
	LOOP
	FETCH c_data BULK COLLECT INTO l_item_recs LIMIT 1000;
	-- Procesamos los registros
	FOR i IN 1..l_item_recs.count
	LOOP
		IF l_item_recs(i).valor <100 THEN
			l_item_recs(i).valor:=l_item_recs(i).valor*1.1;			
		ELSE
			l_item_recs(i).valor:=l_item_recs(i).valor*0.9;
		END IF;							
	END LOOP;	
	FORALL j IN 1..l_item_recs.count	
		UPDATE T_PRUEBAS SET VALOR=l_item_recs(j).valor WHERE ID=l_item_recs(j).id;
	
	EXIT WHEN c_data%NOTFOUND ;
	
	END LOOP ;
	
	CLOSE c_data ;
END;
/

/*
10000 --> Elapsed: 00:00:00.64

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       334 PL/SQL               32184      27560        227632       

100000 --> Elapsed: 00:00:05.61

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       334 PL/SQL               32184      27440        227632       

1000000 --> Elapsed: 00:00:50.09

       SID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       334 PL/SQL               32184      27320        227632      
	   
*/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

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

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: