Optimización pl/sql Oracle 10g

Hace un tiempo estaba trabajando en un proyecto que requería hacer grandes candidades de procesamiento en la base de datos (pl/sql en un oracle 10g).
Entre muchos paquetes que fue necesario crear, estaba el principal que llegó a tener mas de 7000 lineas.

Este paquete tenía que generar código pl/sql dinámico en base a tablas de configuración y luego aplicar este código a un repositorio de transacciones.

El rendimiento era muy importante por lo que era necesario que el procesamiento de las transacciones fuera hecho de la manera más óptima. Googleando me encontré con esta solución.

Oracle trae una paquete de optimización: DBMS_PROFILER. El DBMS_PROFILER es como un log con cronómetro que se activa antes de empezar con la ejecución, de código en la base de datos. Luego de registrar la información, por ejemplo, cuantas veces se ejecutó un linea de código o cuanto tiempo representó la ejecución de una línea de código con respecto al tiempo total de ejecución.

Los pasos para usarlo son:

1) Verificar que existan las siguientes tablas

- plsql_profiler_runs
- plsql_profiler_units
- plsql_profiler_data

Si no existen, buscar en: /opt/oracle/product/10.2/client_1/rdbms/admin # cat proftab.sql , este archivo contiene la definición de las secuencias y tablas necesarias.

2) Con bloque de código como este se activa el DBMS_PROFILER.

BEGIN
--Activar el DBMS_PROFILER
DBMS_OUTPUT.PUT_LINE (
DBMS_PROFILER.START_PROFILER ('mi_procedimiento ' TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS') ) --Para identificar mi log
);

MI_PROCEDIMIENTO('param1',10); --Ejecutar normalmente el procedimiento
COMMIT;

--Desactivar el DBMS_PROFILER
DBMS_OUTPUT.PUT_LINE (
DBMS_PROFILER.STOP_PROFILER);
END;

Al ejecutar el procedimiento se han registrado las estadísticas.

3) Con este query se podrá observar el porcentaje de tiempo usado por cada unidad de procesamiento (un trigger, un procedimiento, una funcion, un paquete, etc.)

select unit_name,unit_number,unit_times.tiempo/t.total*100 as "%"
from
(
select p2.unit_name, p2.unit_number, sum(p3.total_time) tiempo
from plsql_profiler_data p3, plsql_profiler_units p2
where p3.RUNID = p2.RUNID
and p3.UNIT_NUMBER = p2.UNIT_NUMBER
and p2.runid = :runid
group by p2.unit_name, p2.unit_number
order by unit_number asc
) unit_times,
(
select sum(total_time) total
from plsql_profiler_data
where runid = :runid
) t
order by 3 desc

Para saber cual es el runid (el parámetro usado en el query) vea la tabla plsql_profiler_runs, identifíquelo con el nombre que le asignó al iniciar el DBMS_PROFILER.

Un ejemplo del resultado del query:

El UNIT_NUMBER servirá en el siguiente query.

4) Con este query obtenemos el porcentaje de tiempo usado por cada línea de la unidad.

select line#,lines.total_occur, lines.total_time/t.total*100 as "%"
from (
select line#,total_occur,total_time
from plsql_profiler_data p3
where runid = :runid
and unit_number = :unit_num
) lines,
(
select sum(total_time) total
from plsql_profiler_data p3
where runid = :runid
and unit_number = :unit_num
) t
order by 3 desc

Un ejemplo con código ya optimizado:



Al revisar el código se ve que la línea 1939 está dentro de 3 bucles anidados por lo que es lógico que tome el 18% del tiempo. Cuando revise su código sabrá inmediatamente si está sub-optimizado o no.