Performance de objetos PL/SQL compilados para debbuging

 Olá pessoal,

     Todo DBA ou Desenvolvedor que programa em PL/SQL há algum tempo ou participou de algum curso sobre essa linguagem, provavelmente sabe que existem 2 modos de compilação de blocos PL/SQL: nativo e interpretado, e que o modo interpretado pode ser compilado para debug ou não!

     O modo de compilação interpretado é o modo de compilação padrão e é o mais utilizado. Poucos utilizam o modo nativo, que apesar de ser mais rápido, possui algumas desvantagens, tais como: perda da portabilidade, não permite debugar o código PL/SQL e o tempo de compilação é mais demorado. As 2 primeiras desvantagens são as que mais pesam, por isso, a maioria compila seus blocos PL/SQL em modo interpretado, e muitos nem sabem da possibilidade de se efetuar a compilação em modo nativo. A compilação em modo nativo é indicada para otimizar a performance de casos muito específicos, que não são o foco deste artigo, portanto, apenas informo que explico isso no módulo extra de PL/SQL Tuning dos treinamentos PL/SQL Essentials.

     Na compilação interpretada existem também 2 métodos de compilação: normal (ou nodebug) e debug. A compilação em modo debug deixa a execução do código mais lenta e é recomendada somente quando for realmente necessário debugar um bloco PL/SQL (ver tutorial Developing and Debugging PL/SQL using Oracle SQL Developer), caso contrário, efetue a compilação sempre no modo normal (nodebug). Para que você entenda melhor o que estou comentando e veja o quanto realmente o código compilado em modo debug pode ser mais lento, veja os testes abaixo que eu fiz, criando 2 Stored Procedures que fazem a mesma coisa (um cálculo hipotético simples) e que foram compilados, um em modo debug, e o outro em modo normal, e por fim, compare o tempo de execução deles:

   

1- Criando a procedure SP_TESTA_DEBUGCOMPILE em modo debug:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

ALTER SESSION SET PLSQL_DEBUG = TRUE;

create or replace procedure sp_testa_debugcompile is

  v_number number := 1;

begin

  v_number := 1 * v_number + 2 * v_number + 3 * v_number;

end;

/

2- Criando a procedure SP_TESTA_NODEBUGCOMPILE em modo normal:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

ALTER SESSION SET PLSQL_DEBUG = FALSE;

create or replace  procedure sp_testa_nodebugcompile is

      v_number number := 1;

begin    

  v_number := 1 * v_number + 2 * v_number + 3 * v_number;

end;

/

3- Chamando a procedure SP_TESTA_DEBUGCOMPILE para medir o seu desempenho:

begin

  for i in 1..9999999

  loop

      sp_testa_debugcompile; 

  end loop;

end;

Tempo médio de 3 execuções dela: ( 8,372s + 8.354s + 8.566s ) / 3 =  8,43s

4- Chamando a procedure SP_TESTA_NODEBUGCOMPILE para medir o seu desempenho:

begin

  for i in 1..9999999

  loop

      sp_testa_nodebugcompile; 

  end loop;

end;

Tempo médio de 3 execuções dela: ( 2,185s + 2.257s + 2,22s ) / 3 =  2,22s

Obs.: As procedures foram compiladas e executadas em um Oracle Database Enterprise Edition 11.2.0.4. Se você for reproduzir os mesmos testes no Oracle 12c, leia o doc Database PL/SQL Language Reference, pois ocorreram mudanças no 12c para efetuar a compilação em modo debug.

  

  

CONCLUSÃO

  

     Veja que num teste simples chamando as 2 procedures 9.999.999 vezes dentro de um loop, o tempo de execução total daquela que foi compilada em modo interpretado "normal" foi 3,80 X mais rápido do que aquela compilada em modo debug. Talvez isso lhe pareça pouco, mas saiba que em um sistema crítico que necessita de alta performance, esse pouco que na verdade foi 380% mais rápido, pode fazer muita diferença do desempenho geral da aplicação, portanto, se você quer realmente mais performance em blocos PL/SQL armazenados, não compile-os em modo debug! Um simples detalhe (compilando em modo debug) pode deixá-lo bem mais lento, e é muito fácil cometer este engano utilizando, por exemplo, o SQL Developer (ver item "Compilar para depuração" na caixa vermelha da Imagem 01).

Imagem 01 - Compilando Procedures no SQL Developer v. 4.1.5

     Para recompilar seus objetos PL/SQL que já foram compilados em modo debug para o modo normal (nodebug) no Oracle 11G, execute o script abaixo readequando-o de acordo com a sua necessidade:

SET SERVEROUTPUT ON

BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2';

    EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_DEBUG = FALSE';

    FOR LINHA IN (SELECT  owner, name, TYPE, PLSQL_code_type, PLSQL_DEBUG

                  FROM    dba_plsql_object_settings

                  where   PLSQL_DEBUG = 'TRUE'                  

                  )

    LOOP

        BEGIN

            EXECUTE IMMEDIATE 'ALTER ' || REPLACE(LINHA.TYPE, 'PACKAGE BODY', 'PACKAGE') || ' ' || LINHA.OWNER || '.' || LINHA.NAME || ' COMPILE';

            DBMS_OUTPUT.PUT_LINE('Objeto ' || linha.owner || '.' || linha.name || ' recompilado com sucesso!');

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('Erro ao compilar o objeto ' || linha.owner || '.' || linha.name || '!');

        END;  

    END LOOP;    

END;

     

Referências:
   - Oracle® DatabasePL/SQL Language Reference12c Release 1 (12.1)