О производительности обработки исключений

Эта заметка посвящена известному факту о низкой производительности обработки исключений.
Да, действительно, обработка исключений достаточно медленна, однако, не стоит сразу же пытаться избегать их везде, где только можно, да еще и любыми способами. Например, часто вижу как их пытаются избегать даже в случаях поиска по первичному ключу c минимальной вероятностью получения no_data_found.
В целом, стоит учитывать вероятную частоту возникновения исключений и overhead, добавляемый выбранным способом обхода исключений.

Поясню на примере, о котором говорил: пусть есть код, который возвращает поле из таблицы по pk и в случае, если такой записи нет, возвращает null.

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

Создадим эталонную функцию для тестов:

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;

Как я вижу, наиболее часто в таких случаях пытаются обойти механизм исключений следующими способами:

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;

Кстати, в случаях если у вас в курсоре не может быть больше одной записи, то не делайте так:

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;

Иначе будут попытки второй итерации, что вы сможете увидеть в профайлере.

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;

И предложу свой вариант для этого:

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;

И теперь проведем элементарный тест выполнив эти функции по тестовой табличке:

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;

В результате мы получим такое соотношение:

Вариант	Время(c)
1 вариант(c exception)	3.03
2 вариант(c циклом)	3.62
3 вариант(c min)	3.34
4 вариант(скалярный подзапрос)	3.10

Как видите, в случае если исключения не вызываются, то оригинальный запрос быстрее всего! Проверим, теперь с разными процентами исключений: исключения будут для запросов с i<=0, общее кол-во вызовов будет 100001, v_start и v_end буду менять парами: (-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;

Итоговая таблица нескольких сравнений:

Вариант	0%	~5%	~10%	~50%	~90%
1 вариант(c exception)	3.04	3.12	3.16	3.82	4.51
2 вариант(c циклом)	3.18	3.21	3.20	3.51	3.85
3 вариант(c min)	3.37	3.34	3.29	3.25	3.18
4 вариант(скалярный подзапрос)	3.12	3.06	3.03	2.98	2.94

Какие можно сделать выводы:

• Как видите, для данной таблички 5% исключений - это своего рода переломная точка, когда стандартный вариант с exception начинает проигрывать варианту с подзапросом(если чуть точнее, то это было на ~4.5%), и ~10% другим двум вариантам
• Варианты с min и циклом в целом хуже варианта с подзапросом.
• Варианты с подзапросом и min ускоряются с увеличением количества "пустых" запросов.