About the performance of exception handling

Posted on by Posted in oracle, query optimizing 1,877 Page views 1 Comment

This article is about a well-known fact about the poor performance of exception handling.

Yes, the exception handling is rather slow, however, it is not necessary to try to avoid exceptions whenever possible, and by any means. For example, I often see that people are trying to avoid them even in cases of search by primary key where probability of receiving “no_data_found” is minimal.
In general, we should analyze the possible frequency of exceptions and “overhead”, which is added by the chosen way with exception handlers.

Let me explain this with an example, which I mentioned earlier: suppose we have a code that returns a field from the table by “pk” and it returns “null” in case there is no such entry.
Test table:

create table t_test(a primary key, b)
as
select level,level from dual connect by level<=1e5;

Lets create a standard function for tests:

create or replace function f1(p in number) return number
as
  res number;
begin
  select/*+ F1 */ b into res
  from t_test t
  where t.a=p;
  return res;
exception when no_data_found then
  return null;
end;

The most common options to avoid the exceptions mechanism in these cases are the followings:

Variant 1

[sourcecode language=”sql”]
create or replace function f2(p in number) return number
as
begin
for rec in (select/*+ F2 */ b from t_test t where t.a=p) loop
return rec.b;
end loop;
return null;
end;
[/sourcecode]

[collapse]

By the way, don’t use this in case your cursor can not have more than one entry:
Spoiler

[sourcecode language=”sql”]
create or replace function f2(p in number) return number
as
res number;
begin
for rec in (select/*+ F2 */ b from t_test t where t.a=p) loop
res:=rec.b;
end loop;
return res;
end;
[/sourcecode]

[collapse]

Otherwise, there will be attempts of the second iteration, which you can observe in the profiler.
Variant 2

[sourcecode language=”sql”]
create or replace function f3(p in number) return number
as
res number;
begin
select/*+ F3 */ min(b) into res
from t_test t
where t.a=p;
return res;
end;
[/sourcecode]

[collapse]

Here I would like to propose my own option for this:
Spoiler

[sourcecode language=”sql”]
create or replace function f4(p in number) return number
as
res number;
begin
select/*+ F4 */
(select b from t_test t where t.a=p)
into res
from dual;
return res;
end;
[/sourcecode]

[collapse]

And now lets carry out a basic test by executing these functions for the test table:

declare
  v       integer;
  v_start integer:= 1;
  v_end   integer:= 100000;
    l_timer integer := dbms_utility.get_time;
    procedure print(msg varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9990.00')||' '||msg);
      l_timer:=dbms_utility.get_time;
    end;
     
begin
  print('start');
  for i in v_start..v_end loop
    v:=f1(i);
  end loop;
  print('1');
  for i in v_start..v_end loop
    v:=f2(i);
  end loop;
  print('2');
  for i in v_start..v_end loop
    v:=f3(i);
  end loop;
  print('3');
  for i in v_start..v_end loop
    v:=f4(i);
  end loop;
  print('4');
end;

As a result, we get the following ratio:

Variant Time(sec)
Variant 1(with exception) 3.03
Variant 2(with cycle) 3.62
Variant 3(with min) 3.34
Variant 4(scalar subquery) 3.10

As you can see, the original query is the fastest in case the exceptions are not called! Lets now check it with different percents of exceptions: exceptions will be for queries с i<=0, the total number of calls will be 100001, I will change v_start and v_end in pairs: (-5000, 95000), (10000, 90000), (-50000, 50000), (-90000, 10000): 

declare
  v       integer;
  v_start integer:=-50000;
  v_end   integer:= 50000;
    l_timer integer := dbms_utility.get_time;
    procedure print(msg varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9990.00')||' '||msg);
      l_timer:=dbms_utility.get_time;
    end;
     
begin
  print('start');
  for i in v_start..v_end loop
    v:=f1(i);
  end loop;
  print('1');
  for i in v_start..v_end loop
    v:=f2(i);
  end loop;
  print('2');
  for i in v_start..v_end loop
    v:=f3(i);
  end loop;
  print('3');
  for i in v_start..v_end loop
    v:=f4(i);
  end loop;
  print('4');
end;
/

Summary table of multiple comparisons:

Variant 0% ~5% ~10% ~50% ~90%
Variant 1(with exception) 3.04 3.12 3.16 3.82 4.51
Variant 2(with cycle) 3.18 3.21 3.20 3.51 3.85
Variant 3(with min) 3.37 3.34 3.29 3.25 3.18
Variant 4(scalar subquery) 3.12 3.06 3.03 2.98 2.94

What conclusions can be drawn from this:

  • As you can see, 5% of exceptions is a kind of turning point for this table, when the standard option with exception becomes less effective than the option with subquery (by about ~4.5% to be precise), and about ~10% than the remaining two.
  • Options with “min” and a cycle as a whole are worse than the option with a subquery.
  • Options with subquery and “min” becomes faster when the number of “empty” queries increases.
Page views: 1,877