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).

Verificando el uso de "Buenas Practicas" en código PL/SQL

Para verificar si se aplican "Buenas Practicas" de programación y para contribuir a realizar código mas robusto y menos propenso a que se generen errores en tiempo de ejecución, a partir de 10g R1, se introdujo un nuevo mecanismo que permite advertir en tiempo de compilación sobre potenciales problemas (WARNINGS), que si bien dejan compilada la unidad de código, pueden darnos dolores de cabeza y conducir a que las aplicaciones que utilizan dicho código generen errores imprevistos o peor aún, que no se obtengan los datos correctos alterando la semántica pretendida y siendo, en muchas ocasiones, muy complicados de detectar.

Existe 4 categorias de WARNINGS:

SEVERE : Pueden causar acciones inesperadas, errores que hagan
cancelar una operatoria o resultados erroneos.

PERFORMANCE : Pueden causar problemas de rendimiento

INFORMATIONAL: No afectan el rendimiento ni altera los resultados pero
advierte sobre complicaciones en el mantenimiento del
codigo a futuro.

ALL : Contempla todos los casos anteriores.


Para activar los mensajes de warning se puede usar el parametro PLSQL_WARNINGS a nivel sesion o a nivel instancia (cosa que no recomiendo), tambien se puede usar el paquete DBMS_WARNING para setear el nivel de warning deseado a nivel de código PL en procedures, packages, triggers, etc. Consultando la vista [USER | ALL | DBA]_WARNING_SETTINGS se puede saber que objetos tienen activado el warning y consultando la vista [USER | ALL | DBA]_ERRORS, filtrando por el campo ATTRIBUTE='WARNING' se ven todos los warnings generados.

Ahora que ya hice una introduccion rapida al tema, vayamos a los ejemplos:

Habilito para detectar todas las categorias:

rop@DESA10G> alter session set plsql_warnings='ENABLE:ALL';

Sesión modificada.

Creo una tabla sencila

rop@DESA10G> create table t (x int,y varchar(5));

Tabla creada.

rop@DESA10G> insert into t
  2          select rownum,
  3                 trunc(dbms_random.value(1,99999))
  4          from dual
  5          connect by rownum <= 100000;

100000 filas creadas.

rop@DESA10G> commit;

Confirmación terminada.

Voy a crear un procedimiento P_PRUEBA1 de forma tal de que se detecte un warning:

rop@DESA10G>ed
  1  create or replace procedure p_prueba1 (p_val int)
  2          is
  3             l_cnt int;
  4          begin
  5              select count(1) into l_cnt
  6              from t
  7              where y = p_val;
  8              if (l_cnt > 0) then
  9                   dbms_output.put_line ('El valor existe en la tabla');
 10              else
 11                   dbms_output.put_line ('El valor NO existe en la tabla');
 12              end if;
 13*         end;
rop@DESA10G> /

SP2-0804: Procedimiento creado con advertencias de compilación

rop@DESA10G> select text from user_errors where name = 'P_PRUEBA1';

TEXT
----------------------------------------------------------------------------------------------------
PLW-07204: puede que la conversión que no sea de tipo de columna dé como resultado un plan de consulta subóptimo

En el caso de arriba detectó un potencial problema de performance, ya que al comparar la columna "y" de tipo varchar2 con el parametro "p_val" de tipo number se hace una conversión implicita TO_NUMBER() de la columna "y". Oracle siempre pasa a number cuando se comparan los tipos number y char/varchar2.
Veamos otros ejemplos:

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create or replace procedure p_prueba2 (p_val int)
  2          is
  3             l_cnt int;
  4          begin
  5              select count(1) into l_cnt
  6              from t
  7              where y = to_char(p_val);
  8              if ( 0 = 0) then
  9                 if (l_cnt > 0) then
 10                      dbms_output.put_line ('El valor existe en la tabla');
 11                 else
 12                      dbms_output.put_line ('El valor NO existe en la tabla');
 13                 end if;
 14              else
 15                 null;
 16              end if;
 17*         end;
rop@DESA10G> /

SP2-0804: Procedimiento creado con advertencias de compilación

rop@DESA10G> select text from user_errors where name = 'P_PRUEBA2';

TEXT
----------------------------------------------------------------------------------------------------
PLW-06002: Código inaccesible

Este es una advertencia informativa. Ahora voy a crear una función:

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create or replace function f_prueba1
  2          return int
  3  is
  4    l_val int;
  5    begin
  6        l_val := dbms_random.value(1,10);
  7*   end;
rop@DESA10G> /

SP2-0806: Función creada con advertencias de compilación

rop@DESA10G> select text from user_errors where name = 'F_PRUEBA1';

TEXT
----------------------------------------------------------------------------------------------------
PLW-05005: la función F_PRUEBA1 se devuelve sin valor en la línea 7

al no retornar valor se podria generar un problema mas grave

rop@DESA10G> ed
Escrito file afiedt.buf

  1  create or replace procedure p_prueba3 (p_val varchar2)
  2          is
  3          begin
  4              insert into t (x) values (p_val);
  5*         end;
rop@DESA10G> /

SP2-0804: Procedimiento creado con advertencias de compilación

rop@DESA10G>  select text from user_errors where name = 'P_PRUEBA3';

TEXT
----------------------------------------------------------------------------------------------------
PLW-07202: el tipo de enlace daría como resultado una conversión lejos del tipo de columna

rop@DESA10G>

El warning para el procedimiento P_PRUEBA3, aunque la traducción al español no es muy clara, da un posible problema en la conversión de tipos. Asi podriamos seguir probando otros tantos casos.
La idea fue mostrarles que con esta herramienta se puede mejorar la calidad del software pl/sql y detectar en forma automatica y proactiva posibles problemas en tiempo de ejecución.

Como afecta la frecuencia de commits en los procesos de carga masiva

Hace ya casi 7 años leí una nota en el sitio http://www.dbasupport.com/ , donde un "especialista" aconsejaba commitear con la mayor frecuencia posible, dando argumentos falsos que no resistian a la mas mínima prueba. En ese momento yo estaba leyendo un libro de Tom Kyte donde aconsejaba justamente lo contrario, por eso le postee una pregunta a Tom en su reconocido sitio asktom.oracle.com comentandole lo que habia leido, pueden leerla en: "Issue Frequent COMMIT Statements", les aseguro que no tiene desperdicio. La verdad que nunca imaginé la repecución que tuvo eso, ya que el mismo Tom se comunicó con el autor de la nota y luego de unas idas y vueltas, el autor terminó admitiendo que lo que había escrito lo habia sacado de otro sitio y que analizandolo mejor coincidía que estaba mal. Luego este mismo caso fue referenciado por Tom en su libro "Effective Oracle by Design".

Es importante destacar que además de la degradación de performance, producida por las esperas del tipo sync writes debido a la escritura necesaria en los log files cada vez que se realiza un commit, también se promueven los errores ORA-01555 y las posibles violaciones de integridad cuanto mas frecuentes sean los commits. En esta nota solo les voy a mostrar el impacto de la sobrecarga de commits en el rendimiento de la base y no en los otros problemas derivados.

Voy a mostrarles con un ejemplo de prueba (Test Case) que cuanto menos commits se puedan efectuar mejor será la performance en los procesos de carga o actualización masiva de datos.

Para realizar la prueba usé una base de datos versión 10g R2 y se armé un bloque pl/sql anónimo que esencialmente recorre un cursor autogenerado de 1 millón de registros con una transaccionabilidad definida (cantidad de commits). Para la prueba se realizaron 3 iteraciones por cada valor de prueba. El código utilizado fue el siguiente:

declare
l_time int;
begin
l_time := dbms_utility.get_time;
  for i in (select rownum x,
                dbms_random.string('a',10) y,
                sysdate+(dbms_random.value(-200,200)) z
         from dual
         connect by rownum <= 1000000)       
loop          
  insert into t values (i.x,i.y,i.z);          
  if (mod(i.x,p_filas) = 0) then            
     commit;          
  end if;           
end loop;
dbms_output.put_line((dbms_utility.get_time-l_time)/100);
end; 

Donde el parámetro p_filas denota la cantidad de filas insertadas por cada commit. El proceso carga una tabla T con campos: x (number); y (varchar2) y z (date) con un cursor que genera valores aleatorios en el momento. Este tipo de cursor se creo para evitar cualquier tipo de buffering que pudiera darse si se inserta desde una tabla fuente, ya que haría que la prueba no sea aislada debido a que desde la primera iteración quedarán cacheados en algún substitema de disco o en el cache de Oracle las filas a insertar.

Los resultados obtenidos fueron los descriptos en la siguiente tabla:



En el gráfico de abajo se ve como van bajando los tiempos a medida que se dilata la ejecución del commits, es decir cuanto mas filas se procesan por cada commit:



Como conclusión final podemos ver que cuanto menos commits se realicen mejores serán los tiempos de procesamiento. Por lo tanto, siempre que el tamaño de los segmentos de UNDO estén debidamente estimados y que las reglas de negocio lo permitan es recomendable “commitear” lo menos frecuentemente posible.