CODEWITHSUNDEEP
oracle-databaseexceptionplsql
Allan Lewis
Allan Lewis

Reputation: 309

Do nested blocks have any performance impact in PL/SQL procedures?

In PL/SQL procedures, I often wrap statements in a block (i.e. begin...end) so that I can isolate exceptions from that statement. For example, if I'm doing a select that might raise "no_data_found", I can handle that in the knowledge that the exception was raised by that statement, but let other exceptions propagate to the main exception handler for the procedure.

What I'm wondering is if these additional blocks have any performance impact. I know raising exceptions has a performance impact, so I never rely on exceptions as a control flow mechanism. But does the block itself have any impact?

For example, should there be any difference between the performance of the this:

procedure do_something
as
declare
  some_var number;
begin
  select some_value into some_var from some_table;
exception
when others then
  raise_application_error(-20000, 'Exception: ' || sqlerrm, true);
end do_something;

and this:

procedure do_something
as
declare
  some_var number;
begin
  begin
    select some_value into some_var from some_table;
  exception
  when no_data_found then
    some_var := -23; --some default value
  end;
exception
when others then
  raise_application_error(-20000, 'Exception: ' || sqlerrm, true);
end do_something;

(I know this code is nonsensical, but I hope it illustrates what I mean!)

All I really care about is the performance when no exceptions are raised - I can accept performance degradation when there is an exception.

Upvotes: 2

Views: 794

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

They don't appear to:

set timing on
set serveroutput on

declare
  x number := 0;
begin
  dbms_output.put_line('No inner blocks');
  for i in 1..1000000 loop
    x := x + 1;
  end loop;
  dbms_output.put_line(x);
end;
/

anonymous block completed
Elapsed: 00:00:00.095
No inner blocks
1000000

Runs in the same time, with a bit of variation each way, as:

declare
  x number := 0;
begin
  dbms_output.put_line('Nested inner blocks');
  for i in 1..1000000 loop
    begin
      begin
        begin
          begin
            x := x + 1;
          exception
            when others then
              raise;
          end;
        exception
          when others then
            raise;
        end;
      exception
        when others then
          raise;
      end;
    exception
      when others then
        raise;
    end;
  end loop;
  dbms_output.put_line(x);
end;
/

anonymous block completed
Elapsed: 00:00:00.090
Nested inner blocks
1000000

Of course it's possible the compiler is removing the redundant layers, but I'm not sure it really can with the exception handlers there as it would affect the result.

I haven't seen any limitation on how deep nested blocks can go - the documentation just says 'blocks can be nested'. The model you're using, catching a specific error and letting others propagate, is fine and pretty standard - though obviously in your contrived example it isn't necessary, but you know that.

Upvotes: 5

Related Questions