Oracle Live

19/08/2016

PLSQL – Debug con DBMS_PROFILER

Filed under: PLSQL — mogukiller @ 9:50 am

Frecuentemente en alguno de los clientes donde voy me piden que revise procedimientos o funciones con problemas de rendimiento. Para tener un primer vistazo utilizo DBMS_PROFILER para saber dónde focalizar los esfuerzos de optimización.

-- Instalacion
-- Creamos el entorno
-- Ejecucion 
-- Queries de Administracion

Tested: 11.2.0.4
----------------------------
-- Instalacion
----------------------------

connect mogu/temporal
@?/rdbms/admin/proftab.sql

'Crea las tablas:
	PLSQL_PROFILER_RUNS
	PLSQL_PROFILER_UNITS
	PLSQL_PROFILER_DATA
'

sqlplus / as sysdba
@?/rdbms/admin/profload.sql

GRANT EXECUTE ON SYS.DBMS_PROFILER TO MOGU;

----------------------------
-- Creamos el entorno
----------------------------

CREATE TABLE T_FORALL (ID_VALOR NUMBER);

-- NO OPTIMIZADA				           	-- OPTIMIZADA
                                           
DECLARE                                    ||  DECLARE
   TYPE typ_numlist IS TABLE OF NUMBER;    ||     TYPE typ_numlist IS TABLE OF NUMBER;
   num typ_numlist := typ_numlist();       ||     num typ_numlist := typ_numlist();
BEGIN                                      ||  BEGIN
   FOR i IN 1 .. 50000 LOOP                ||     FOR i IN 1 .. 50000 LOOP
     num.extend;                           ||       num.extend;
     num(i) := i;                          ||       num(i) := i;
   END LOOP;                               ||     END LOOP;
   FOR i IN 1 .. 50000 LOOP                ||     FORALL i IN 1 .. 50000
     INSERT INTO t_forall                  ||       INSERT INTO t_forall
       VALUES (num(i));                    ||         VALUES (num(i));
   END LOOP;                               ||  END;
END;                                       ||  

-- Elapsed: 00:00:03.97						 -- Elapsed: 00:00:00.19

----------------------------
-- Ejecucion 
----------------------------
exec dbms_profiler.start_profiler('Prueba FORALL NO Optimizada');
<Bloque No Optimizado>
exec dbms_profiler.stop_profiler;

exec dbms_profiler.start_profiler('Prueba FORALL Optimizada');
<Bloque Optimizado>
exec dbms_profiler.stop_profiler;

----------------------------
-- Queries de Administracion
----------------------------

-- Para Bloques anonimos

COL EXEC_DATE FOR A40
COL total_time HEADING 'Total Time (msec)'
COL min_time HEADING 'Min Time (msec)'
COL max_time FOR A30 HEADING 'Max / Min |Time (msec)' 
COL TEXT FOR A40
BREAK ON EXEC_DATE
SELECT
	A.RUNID||' / '||A.RUN_COMMENT||' / '||TO_CHAR(A.RUN_DATE,'YYYY/MM/DD HH24:MI:SS') AS Exec_date,
	B.LINE#,
	D.TEXT,
	B.TOTAL_OCCUR,
	ROUND(B.TOTAL_TIME/1000000) as total_time, 
	ROUND(B.MIN_TIME/1000000) ||' / '||	ROUND(B.MAX_TIME/1000000) as max_time	
FROM
	PLSQL_PROFILER_RUNS A,
	PLSQL_PROFILER_DATA B,
	PLSQL_PROFILER_UNITS C,
	DBA_SOURCE D
WHERE 
	A.RUNID = B.RUNID 
	AND A.RUNID = C.RUNID
	AND C.UNIT_OWNER = D.OWNER
	AND C.UNIT_NAME = D.NAME
	AND B.LINE# = D.LINE
ORDER BY A.RUNID, B.LINE#;

/* SALIDA
EXEC_DATE                                                         LINE# TOTAL_OCCUR Total Time (msec) Min Time (msec) Max Time (msec)
------------------------------------------------------------ ---------- ----------- ----------------- --------------- ---------------
2 / Prueba FORALL NO Optimizada / 2016/08/19 08:36:34                 1           1                 0               0               0
                                                                      1           0                 0               0               0
                                                                      1           2                 0               0               0
                                                                      3           1                 0               0               0
                                                                      5       50001                17               0               0
                                                                      6       50000                32               0               0
                                                                      7       50000                24               0               0
                                                                      9       50000                20               0               0
                                                                     10       50000              3729               0              26
                                                                     13           1                 0               0               0
3 / Prueba FORALL Optimizada / 2016/08/19 08:38:39                    1           2                 0               0               0
                                                                      1           1                 0               0               0
                                                                      1           0                 0               0               0
                                                                      3           1                 0               0               0
                                                                      5       50001                17               0               0
                                                                      6       50000                31               0               0
                                                                      7       50000                24               0               0
                                                                      9           1                52              52              52
                                                                     12           1                 0               0               0
*/

-- Para funciones 

exec dbms_profiler.start_profiler('Prueba Funcion');
exec PRUEBA;
exec dbms_profiler.stop_profiler;


COL EXEC_DATE FOR A60
COL total_time HEADING 'Total Time (msec)'
COL min_time HEADING 'Min Time (msec)'
COL max_time HEADING 'Max Time (msec)'
BREAK ON EXEC_DATE
SELECT
	A.RUNID||' / '||A.RUN_COMMENT||' / '||TO_CHAR(A.RUN_DATE,'YYYY/MM/DD HH24:MI:SS') AS Exec_date,
	B.LINE#,
	B.TOTAL_OCCUR,
	ROUND(B.TOTAL_TIME/1000000) as total_time, 
	ROUND(B.MIN_TIME/1000000) as min_time, 
	ROUND(B.MAX_TIME/1000000) as max_time	
FROM
	PLSQL_PROFILER_RUNS A,
	PLSQL_PROFILER_DATA B
WHERE 
	A.RUNID = B.RUNID
ORDER BY A.RUNID, B.LINE#;

/* SALIDA
                                                                                                                      Max / Min
EXEC_DATE                                     LINE# TEXT                                     TOTAL_OCCUR Total Time (msec) Time (msec)
---------------------------------------- ---------- ---------------------------------------- ----------- ----------------- ------------------------------
4 / Prueba Funcion / 2016/08/19 09:47:58          1 PROCEDURE PRUEBA AS                                1                 0 0 / 0
                                                  1 PROCEDURE PRUEBA AS                                3                 0 0 / 0
                                                  1 PROCEDURE PRUEBA AS                                0                 0 0 / 0
                                                  1 PROCEDURE PRUEBA AS                                2                 0 0 / 0
                                                  3    num typ_numlist := typ_numlist();               1                 0 0 / 0
                                                  5    FOR i IN 1 .. 50000 LOOP                    50001                18 0 / 0
                                                  6      num.extend;                               50000                35 0 / 1
                                                  7      num(i) := i;                              50000                26 0 / 0
                                                  9    FORALL i IN 1 .. 50000                          1                72 72 / 72
                                                 12 END;                                               1                 0 0 / 0
*/

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: