CODEWITHSUNDEEP
oracle-databaseperformanceexceptionplsql
Anouer Kassaa
Anouer Kassaa

Reputation: 61

does exception affect execution time?

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 ;

Upvotes: 2

Views: 483

Answers (2)

Vlad
Vlad

Reputation: 3

I have the exactly same issue. I know this thread is old, but if each Exception has a RAISE, it works fast (3sec). In case any of the RAISEs is commented, the procedure runs in about 90sec :-| (there is no error, all the rows are processed correctly) Still looking how to overcome this apparently oracle bug. The need is to continue processing the loop, even if there is an exception/error

Don't have the tools to check if this is the issue - https://nenadnoveljic.com/blog/pl-sql-exception-handling-performance-degradation-oracle-12-2/ I'm using v19.0

After doing extensive debuging, I found that the performance issue came from the fact that I had multiple procedure (a new procedure) calls with a table of record IN OUT parameter. As soon as I added NOCOPY hint to that parameter, the performance was back to normal even without RAISE from the exception block of the main procedure Hope it helps :)

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 3

Related Questions