martes, 18 de diciembre de 2018

Mejora el rendimiento PL/SQL con PLSQL_CODE_TYPE=NATIVE

En la base de datos Oracle desde la version 9i y 10g se incluyo el tipo  de compilación  Native, pero su implementación requería de la ayuda de un DBA para la incorporación de un compilador C externo y la configuración del repositorio donde se almacenarían estas compilaciones.

Interpreted: El codigo PL/SQL en su compilación es almacenado de forma intermedia y es necesario interpretarlo cada vez que se ejecute. Esta es una compilación no nativa del motor de base de datos la cual se almacena de forma intermedia.

Native: el codigo  PL / SQL en su compilación es almacenado en un código nativo que no necesita ser interpretado en tiempo de ejecución. En comparación, el código que no se compila de forma nativa se almacena en una forma intermedia que debe interpretarse en tiempo de ejecución.

Desde la version 11g el tipo Native se  simplifcó a tal punto que es implementado a nivel del sistema o sesión. El repositorio se lo incluyó dentro de la base de datos.

Debido a que el código compilado de forma nativa no necesita ser interpretado en tiempo de ejecución, la expectativa es que se ejecutará más rápido. Sin embargo, la compilación nativa solo se aplica al código de procedimiento en una unidad de programa PL/SQL, no a cualquier sentencia de SQL. Como resultado, las mejoras en el rendimiento que se deben realizar realmente dependen de la cantidad de código de procedimiento existente en relación con el código SQL. Como mínimo, una unidad de programa compilada de forma nativa que sea SQL pesada debe ejecutarse al menos tan rápido como su contraparte no compilada de forma nativa. A medida que aumenta el volumen de código de procedimiento, más rápido debería ser el código compilado de forma nativa.

Como se indico este parametro puede ser modificado a nivel del sistema "Alter system" o a nivel de la sesion "Alter session", cabe indicar que el parámetro por defecto de PLSQL_CODE_TYPE es "INTERPRETED", podemos cambiar el parametro en cualquier momento del tiempo, los PLSQL compilados antes de cambiar el parametro quedaran con el tipo que se compiló, para actualizar al tipo del parámetro establecido se deberá recompilar el PLSQL.

Ahora si explicado, realizaremos un ejemplo para comprar rendimientos. Vamos a crear dos funciones con el mismo contenido F_INTERPRETED_1 y F_NATIVE_1 pero se se compilaran con tipo Interpreted y Native respectivamente.

CREATE TABLE DATOS_TEST AS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=50;

--Objetos PL/SQL INTERPRETED
alter session set plsql_code_type=interpreted;

CREATE OR REPLACE FUNCTION F_INTERPRETED_1 (P_VALOR NUMBER,P_VALOR_2 NUMBER)RETURN NUMBER IS
L_VALOR_N  NUMBER;
L_VALOR_PROM_N  NUMBER;
BEGIN
    for i in 1..P_VALOR_2 loop
        L_VALOR_N:=L_VALOR_N+I;
        L_VALOR_PROM_N:=L_VALOR_N/I;
    end loop;
    RETURN L_VALOR_PROM_N;
END;
/

CREATE OR REPLACE FUNCTION F_INTERPRETED_2 RETURN NUMBER IS
L_VALOR_N  NUMBER;
L_VALOR_N2 NUMBER;
BEGIN
    FOR C_DAT IN (SELECT * FROM DATOS_TEST)LOOP
        IF MOD(C_DAT.OBJECT_ID,2)=0 THEN
            L_VALOR_N:=L_VALOR_N+C_DAT.OBJECT_ID+ C_DAT.DATA_OBJECT_ID;
        ELSE
            L_VALOR_N:=L_VALOR_N+C_DAT.OBJECT_ID+ C_DAT.DATA_OBJECT_ID-2;
        END IF;
        IF C_DAT.OBJECT_NAME LIKE '%AS%' THEN
            L_VALOR_N:=L_VALOR_N-5;
        END IF;        
        IF MOD(C_DAT.DATA_OBJECT_ID,2)=0 THEN
            SELECT AVG(OBJECT_ID) INTO L_VALOR_N2 FROM DATOS_TEST;
            L_VALOR_N:=L_VALOR_N+L_VALOR_N2;
        ELSE
            SELECT MIN(OBJECT_ID) INTO L_VALOR_N2 FROM DATOS_TEST;
            L_VALOR_N:=L_VALOR_N-L_VALOR_N2;
        END IF;         
    END LOOP;
    RETURN L_VALOR_N;
END;
/


--Objetos PL/SQL NATIVE
alter session set plsql_code_type=native;

CREATE OR REPLACE FUNCTION F_NATIVE_1 (P_VALOR INTEGER,P_VALOR_2 INTEGER)RETURN NUMBER IS
L_VALOR_N  NUMBER;
L_VALOR_PROM_N  NUMBER;
BEGIN
    for i in 1..P_VALOR_2 loop
        L_VALOR_N:=L_VALOR_N+I;
        L_VALOR_PROM_N:=L_VALOR_N/I;
    end loop;
    RETURN L_VALOR_PROM_N;
END;

CREATE OR REPLACE FUNCTION F_NATIVE_2 RETURN NUMBER IS
L_VALOR_N  NUMBER;
L_VALOR_N2 NUMBER;
BEGIN
    for c_dat in (select * from datos_test)loop
        if mod(c_Dat.OBJECT_ID,2)=0 then
            L_VALOR_N:=L_VALOR_N+c_dat.OBJECT_ID+ c_dat.DATA_OBJECT_ID;
        else
            L_VALOR_N:=L_VALOR_N+c_dat.OBJECT_ID+ c_dat.DATA_OBJECT_ID-2;
        end if;
        if c_Dat.OBJECT_NAME like '%AS%' then
            L_VALOR_N:=L_VALOR_N-5;
        end if;        
        if mod(c_Dat.DATA_OBJECT_ID,2)=0 then
            select avg(OBJECT_ID) into L_VALOR_N2 from datos_test;
            L_VALOR_N:=L_VALOR_N+L_VALOR_N2;
        else
            select min(OBJECT_ID) into L_VALOR_N2 from datos_test;
            L_VALOR_N:=L_VALOR_N-L_VALOR_N2;
        end if; 
        
    end loop;
    RETURN L_VALOR_N;
END;
/


Veamos ahora como quedo registro en la base de datos con el SQL "select name, type,plsql_code_type from USER_PLSQL_OBJECT_SETTINGS"

NAMETYPEPLSQL_CODE_TYPE
F_INTERPRETED_1FUNCTIONINTERPRETED
F_INTERPRETED_2FUNCTIONINTERPRETED
F_NATIVE_1FUNCTIONNATIVE
F_NATIVE_2FUNCTIONNATIVE

Como vemos se han copilado la funciones e identificamos que se compilaron con como INTERPETRED y NATIVE.

Ahora realizaremos el llamado a la funciones para ver los tiempos de ejecución.

Comparación F_INTERPRETED_1 con F_NATIVE_1
set timing on
declare
l_val   integer;
begin
  for n in 1..2000 loop
    l_val:=F_INTERPRETED_1  (12,100000);
  end loop;
end;
/
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:18.64

declare
l_val   integer;
begin
  for n in 1..2000 loop
    l_val:=F_NATIVE_1       (12,100000);
  end loop;
end;
/
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:14.12

Comparación F_INTERPRETED_2 con F_NATIVE_2
set timing on
declare
 l_val   integer;
begin
 for n in 1..9000 loop
   l_val:=F_INTERPRETED_2;
 end loop;
end;
/
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:21.46

declare
l_val   integer;
begin
  for n in 1..9000 loop
    l_val:=F_NATIVE_2;
  end loop;
end;
/
Procedimiento PL/SQL terminado correctamente.
Transcurrido: 00:00:19.72

A simple vista los tiempos de ejecución disminuyen con la compilación tipo NATIVE. Ahora veamos el comparativo de la compilación INTERPRETED VS NATIVE.

FUNCIONTiempo Ejecucion Seg.Reducción
INTERPRETEDNATIVEINTERPRETEDNATIVESegundos%
F_INTERPRETED_1F_NATIVE_118,6414,124,5224,25 
F_INTERPRETED_2F_NATIVE_221,4619,721,748,11 





Como vemos en el comparativo, el tiempo de ejecución se reduce en la ejecución de los PLSQL compilados del tipo NATIVE.

La conclusión de este ejercicio que vamos a obtener un mejor rendimiento de nuestros PL/SQL si los compilamos de forma nativa. Para aplicar el cambio del parametro de forma global debemos configurar el parametro del system:
ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE SCOPE=BOTH;

Deben de tener en cuenta que para que su código anterior al cambio del parametro quede establecido con el nuevo parámetro debemos de compilara de nuevo nuestros objetos PL/SQL.