Tuning de Exception em Oracle

Exception é um dos blocos do PL/SQL que é opcional, mas que, na minha visão, deveria ser obrigatório. Tratamentos de erros ou de saídas inesperadas promovem a execução de programas de forma mais harmoniosa, visando, principalmente, uma maior integridade do processo, seja para atualização ou recuperação de informações.

Ainda, não é boa prática tratar as exceptions apenas com OTHERS. O recomendado é que se faça a tentativa de mapear todas as saídas possíveis, manipulando, assim, cada situação com as Exceptions pre definidas ou até mesmo criando a sua própria. Utilize OTHERS como um coringa, apenas para evitar unhandled exceptions.

Outra dica, para questão de performance, é efetuar as validações no processamento, evitando passagem pelos blocos de exception. Mesmo aumentando a quantidade de condicionante e, consequentemente, a troca de contexto, tem-se um ganho, haja vista que a passagem entre os dois blocos é bem mais dispendioso.

Diante da impossibilidade de não tratar a ida a um bloco de Exception e na necessidade de exibição ou gravação do erro ocorrido, vejo em diversos programas a utilização das functions SQLCODE e SQLERRM, haja vista que são essas as maneiras mais comuns e conhecidas de apresentar o problema. Contudo, existe a DBMS_UTILITY.FORMAT_ERROR_STACK que além de diversos outros benefícios e características, provém uma melhor performance no tempo de execução.

A própria Oracle já recomenda a utilização da package DBMS_UTILITY, conforme trecho abaixo extraido da Oracle Database PL/SQL Language Reference

“Oracle recommends using DBMS_UTILITY.FORMAT_ERROR_STACK, except when using the FORALL statement with its SAVE EXCEPTIONS clause”

Para o teste de comparação de desempenho foram criados dois blocos anônimos que emitem 1 milhão de exceptions para ZERO_DIVIDE, contudo para um os erros são exibidos utilizando DBMS_UTILITY.FORMAT_ERROR_STACK e para o segundo com SQLERRM. Vamos verificar o tempo de processamento e verificar o ganho.

DBMS_UTILITY.FORMAT_ERROR_STACK

set timing on

DECLARE
  n_divisor    NUMBER;
  v_time_i_one NUMBER;
  v_time_f_one NUMBER;

BEGIN
  FOR nX IN 0 .. 1000000
  LOOP
    BEGIN
      n_divisor := 1/0;

    EXCEPTION
    WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);

    END;

  END LOOP;

END;

SQLERRM

set timing on

DECLARE
  n_divisor    NUMBER;
  v_time_i_one NUMBER;
  v_time_f_one NUMBER;

BEGIN
  FOR nX IN 0 .. 1000000
  LOOP
    BEGIN

      n_divisor := 1/0;

    EXCEPTION
    WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);

    END;

  END LOOP;

END;

Ao rodar os dois blocos anônimos, vê-se que com a utilização do DBMS_UTILITY.FORMAT_ERROR_STACK o tempo de execução é de 07,725 segundos, enquanto para utilização do SQLERRM tem-se a tomada de 13,699 segundos. O ganho de performance chega a 77%, o que justificaria seu uso quando para tuning.

Referências

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS007