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