Instalación, Configuración y uso del paquete DBMS_PROFILER

En esta nota les voy a mostrar un poco acerca del paquete DBMS_PROFILER, que no es tan conocido, pero si se usa correctamente, puede ayudar a detectar en forma precisa en que lineas de código se encuentran las mayores demoras y asi focalizar la tarea de mejora de rendimiento de una aplicación o bloque de código PL/SQL.
El paquete DBMS_PROFILER fue introducido en 8i y permite al equipo de desarrollo obtener detalle y comportamiento del código PL/SQL. No existe por default y hay que correr dos scripts para instalarlo. Por medio de esta herramienta los desarrolladores podrán obtener con granularidad de línea de código el detalle de ejecuciones, tiempo total, tiempo máximo, tiempo mínimo, etc.


Instalación y Configuración

La instalación consta de 2 pasos: La etapa 1 debe ser ejecutada por un DBA conectado como sys. Esta etapa crea el paquete. La etapa 2 no requiere privilegios especiales y puede ser ejecutada por el dueño (owner) en donde esta definido el modulo de código a instrumentar.


Paso 1

Este paso crea el paquete DBMS_PROFILER en y realiza los grants y creación de sinónimo para poder ser utilizado desde cualquier usuario.

CONNECT sys/password@service AS SYSDBA
@$ORACLE_HOME/rdbms/admin/profload.sql

Paso 2

Este paso crea las 3 tablas y la secuencia necesarias para persistir la información recolectada por el DBMS_PROFILER.

CONNECT profiler/profiler@service
@$ORACLE_HOME/rdbms/admin/proftab.sql
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Como Usar DBMS_PROFILER

Para activar el profiler se debe encerrar la porción de código a evaluar por las siguientes sentencias:

Begin
….
dbms_profiler.start_profiler();
…
dbms_profiler.stop_profiler;
…
End;

Una vez ejecutado el modulo a analizar se deberá consultar en las tablas PLSQL_xxx para obtener los resultados.

Ahora les voy a mostrar su uso con un ejemplo sencillo:

Creamos un procedimiento sencillo que recorre un cursor implícito y va contando las iteraciones.

create or replace procedure foo
is
l_cnt int := 0;
begin
dbms_profiler.start_profiler('Test');  -- Comienzo de traza
for i in (select * from all_objects)
loop
   l_cnt := l_cnt+1;
end loop;
dbms_profiler.stop_profiler;           -- Fin de traza
end;

Luego de ejecutarlo, verificamos los resultados con la siguiente consulta:

SELECT u.runid,
     u.unit_type,
     u.unit_name,
     d.line#,
     s.text,
     d.total_occur,
     d.total_time,
     d.min_time,
     d.max_time
FROM   plsql_profiler_units u,
     plsql_profiler_data d,
     user_source s
WHERE  u.runid = d.runid AND
     u.unit_number = d.unit_number AND
     u.unit_name = s.name AND
     d.line# = s.line AND
     u.runid = 2
ORDER BY u.unit_number, d.line#

La unidad de medida es en nanosegundos (10 elevado a la -9), para pasar a segundos hay que dividir por 1000 millones.
En base a los resultados obtenidos se ve que demoró casi 3s en obtener los datos de las filas de la tabla (linea 6) y 0,03s en ejecutar la asignación (linea 8).