does exception affect execution time?

user_15960558 0
Viewed 105

I have a procedure that is linked to other procedures ( not my procedure ). After executing it it takes a very long time ( more than 1 hour ). I tried searching for the issue until I tried to remove the used exception.

After removing the exception from both procedures the execution time lasts only few secs. So my question is does the use of :

EXCEPTION 
 WHEN OTHERS

affect the execution time that much or what exactly the issue with it ? ps : the line after the 1st EXCEPTION WHEN OTHERS was just DBMS_OUTPUT.PUT_LINE(SQLCODE||' <--> '||SQLERRM);. The second exception was RAISE ;

plsql oracle exception performance
1 Answers

Exception handling does not directly create performance problems. Raising and catching exceptions in PL/SQL is about a hundred times slower than simple addition, but as long as you're not generating billions of exceptions the performance shouldn't be a problem. The performance problem must be somewhere else in your code, and you should consider completely removing your exception handler.

Demonstrating that exception handling is fast

The below code shows that a million divide by zero exceptions can be raised and caught in a second.

--1 million exceptions: 1.4 seconds
declare
    v_count number := 0;
begin
    for i in 1 .. 1000000 loop
        begin
            v_count := v_count + 1/0;
        exception when others then
            null;
        end;
    end loop;
    dbms_output.put_line('Number: ' || v_count);
end;
/

--1 million additions: 0.03 seconds
declare
    v_count number := 0;
begin
    for i in 1 .. 1000000 loop
        begin
            v_count := v_count + 1;
        exception when others then
            null;
        end;
    end loop;
    dbms_output.put_line('Number: ' || v_count);
end;
/

What is slow?

Most likely something else in your exception handler is causing problems. Calling DBMS_OUTPUT.PUT_LINE can be very expensive depending on your IDE. If your IDE has server output enabled, and reads the output one line at a time, reading the output can take a long time.

Why even handle exceptions?

You might want to completely rethink your exception handling strategy. Why have any custom exception handling at all? If your code does nothing, by default the exception will be raised, propagate to the top if the application, and display the full error stack with the right line numbers. Your custom exception handler won't include the full error stack, won't report the correct line number, and won't display anything if the server output is disabled.

Why is this such a common mistake?

Many PL/SQL exception handling examples are wrong so it's very easy to copy these mistakes. I think this is because developers rightfully want to use the simplest possible code in their training examples, but PL/SQL exception handling is generally only useful in complex scenarios.

user_4091720
Related