Привет! Меня зовут Сергей, я технический эксперт в компании Bercut. Когда-то увлекался Delphi и J2ME, издал несколько книг на эти темы, и профессионально уже около 20 лет создаю высоконагруженные приложения баз данных, используя для этого PL/SQL и SQL.

В Bercut мы занимаемся разработкой и развитием IT-продуктов и решений для операторов цифровых услуг и мобильных сервисов. Наши системы работают на различном железе, разных СУБД и обслуживают 24x7x365 в режиме онлайн сотни миллионов абонентов по всему миру. И это только кажется, что абонент один раз в день куда-то позвонил и все, больше никакой нагрузки от него нет и никто с ним не работает. На самом деле каждый смартфон каждого абонента непрерывно обменивается данными с сетью, а это все работа инфраструктуры операторов сотовой связи, а значит, нашего ПО. Как же система справляется с таким огромным объемом нагрузки?

Сегодня расскажу, что нужно делать и чего избегать, чтобы построить максимально производительную систему на примере СУБД Оракл.

Итак,

Первое, с чего все начинается, это архитектура системы в целом. Именно на этом этапе уже должны быть продуманы решения для возможности снижения и распределения нагрузки между различными системами, серверами и базами данных. Кэширование всего, что нужно, и там, где это максимально необходимо. Возможность максимального вертикального и горизонтального расширения системы (например, установка более мощных серверов или увеличения их количества, разбивка данных на произвольное число БД, шардирование и т.д.). В данной статье рассмотрим работу в одной конкретной БД.

Далее, опираясь на архитектуру, проектируются бизнес-процессы и схема базы данных для конкретного процесса. При проектировании продумывается структура таблиц, индексов, основных единиц программного кода, реализующих логику бизнес-процессов. Здесь опять же все должно быть устроено так, чтобы создавать минимально возможную нагрузку. Допустим, требуется что-то посчитать, но не прямо в момент нажатия пользователем кнопки, а чуть позже. При этом мы знаем, что посчитать гораздо тяжелее, чем отправить заявку в очередь на расчет. Система вызывает метод, который помещает заявку в очередь. Другие процессы просматривают очередь запросов, выполняют расчеты и помещают результаты в очередь ответов. В этом нам помогает система очередей Оракл. 

И наконец, в игру вступают разработчики. Никто лучше разработчиков не знает, как и где нужно построить индексы, какие написать запросы, чтобы система работала максимально быстро, потребляя минимальные ресурсы. А ресурсы в СУБД - это время БД, состоящее, в том числе, из времени CPU, логических и дисковых чтений, записи, защелок, блокировок и т.д. Однако не все разработчики хорошо знакомы с СУБД, для которой пишут код, а потому код получается не особо качественный. Встречаются разработчики, которые якобы могут писать код под 5 разных СУБД. По опыту такой код неэффективно работает в каждой из них.

Хороший код не только работает быстро, но и отлично масштабируется. Например, требуется выполнить расчет некоторого показателя для N клиентов. При расчете в один поток (одним джобом) система выполнит это за X секунд. Если код написан качественно, то мы можем легко распараллелить этот расчет на произвольное (но ограниченное) число M потоков (джобов). Возможное число потоков зависит от производительности сервера и нагрузки от других процессов. И тогда расчет для того же числа N клиентов в M потоков будет занимать уже не X секунд, а ненамного больше, чем X/M. Если же код некачественный, то выполнив распараллеливание процесса на M потоков, мы получим время расчета значительно больше X/M. В некоторых случаях даже гораздо больше, чем X, и нагрузку на БД >= X*M. А все почему? Блокировки, защелки, конкуренция, ошибки управления транзакциями.

Следует избегать реализации алгоритмов, степень роста которых n^2 и выше. Если на обработку n клиентов нужно время t, а на обработку 10n клиентов – 100t, то это плохой алгоритм, нужно разработать более оптимальный.

Блокировки

В Оракл ни один процесс не может блокировать чтение строк из базы данных (промолчим про частный случай с result_cash). Но любой процесс может блокировать обновление строк. Если есть два джоба, и первый из них проапдейтил строку s1 в таблице t, но не закоммитил, то второй, когда захочет обновить эту же строку, будет обязан ждать коммита или роллбека первого джоба. Получаем блокировку.

Таким образом, джобов стало два вместо одного, но работает только первый, а второй ждет, ускорения никакого нет.

Взаимоблокировки

Еще хуже, если возникает взаимоблокировка (deadlock). Это когда джоб1 проапдейтил, но не закоммитил строку s1, а джоб2 проапдейтил строку s2 (и тоже не закоммитил). Затем джоб1 пытается апдейтить строку s2, а она заблокирована джобом2. Тогда джоб1 ждет, а джоб2 пытается проапдейтить строку s1, а она, в свою очередь, заблокирована джобом1. В итоге первый джоб ждет второго, а второй ждет первого. Происходит взаимоблокировка. Оракл из этих двух сессий выбирает «жертву» и в ней генерирует исключение. Неуспешная транзакция откатывается. Тогда вместо ускорения получаем замедление - нужно подождать какое-то время, а потом еще и откат выполнить, -  и нарушение логики. 

Блокировки и взаимоблокировки ухудшают масштабируемость приложений, а потому необходимо их избегать. Значит:

• разные сессии должны работать с разными строками таблиц и не мешать друг другу (например, джоб1 обрабатывает всех четных клиентов, а джоб2 - всех нечетных);

• транзакция должна быть построена верно с точки бизнес-логики и не мешать другим бизнес-процессам. Не должна быть слишком долгой, то есть не длиться часами. Слишком коротких транзакций бояться не стоит. Многие пытаются экономить на коммитах, получая таким образом длинную транзакцию. Но в системе с большим числом одновременно работающих процессов это приводит к возникновению блокировок. Но помните, коммит - предельно легкая операция, и если исходя из бизнес-логики приложения, целостности и согласованности данных он нужен, то он должен быть.

Параллельная обработка

Джобов, выполняющих обработку порции данных, может быть произвольное количество. Как распределить данные между ними? Есть множество способов, которые можно применить. В случае c клиентами, у нас есть таблица клиентов customers, в ней первичный ключ - идентификатор клиента cust_id.

Так, если используются очереди Оракл, то ничего делить не нужно. Предположим, очередь читается одним джобом. Запускаем второй с точно тем же текстом. Теперь очередь обрабатывают два джоба, а это почти в два раза быстрее. Запускаем еще десять точно таких же джобов - очередь теперь обрабатывают двенадцать джобов, клиенты из очереди автоматически распределяются между джобами. Важно, чтобы в очереди было не более одной заявки по каждому клиенту, иначе они могут попасть к разным джобам, причем одновременно, что приведет к блокировкам и снижению масштабируемости.

Если мы используем не очереди, а пакет DBMS_PARALLEL_EXECUTE для обработки всей таблицы, то таблица бьется на порции (чанки), которые автоматически распределяются между предназначенными для нее джобами.

А что, если нет ни очереди, ни пакета DBMS_PARALLEL_EXECUTE, и ведется просто выборка данных запросом из таблицы и обработка их в цикле?

Тогда, если работает один джоб, не получится просто запустить второй. Сначала нужно остановить первый и выполнить изменение кода вызова процедуры. После этого уже можно запустить столько джобов, сколько требуется (только нужно сразу знать, сколько). Для этого в процедуре, реализующей джоб, предусмотрены два параметра - количество потоков и номер текущего потока.

procedure calc_clients_data( p_process_count in number := 1, p_process_idx   in number := 0) is
begin
  for i in (select cust_id
              from customers c
            where mod( c.cust_id, p_process_count ) = p_process_idx  ) loop
    -- тут код, выполняющий все расчеты по одному клиенту
    commit;
  end loop; 
end;

Тогда код первого джоба, если всего джобов десять, будет выглядеть:

begin
  calc_clients_data( 10, 0);
end;

Код второго джоба:

begin
  calc_clients_data( 10, 1);
end;

И так далее. Возможно распараллеливание и по строковым полям. Тогда вместо mod в запросе можно применить функцию dbms_utility.get_hash_value( name => строковое поле таблицы, base => 0, hash_size => p_process_count )

Выше я привел несколько упрощенный код, просто чтобы показать сам принцип.

Не следует строить долгих циклов по запросу - чем дольше он работает, тем ему будет тяжелее. Он будет замедляться все больше и больше. Есть множество других процессов, которые апдейтят таблицу customers, а запрос обязан восстановить таблицу до того состояния, в котором она была на момент начала его выполнения. Чем больше было изменений этой строки в других процессах, тем тяжелее системе будет восстановить ее первоначальное состояние. В определенный момент может оказаться, что информации, необходимой для восстановления исходного состояния строки, уже нет (данные в журнале отката были переписаны другой информацией), и мы получим ошибку ORA-01555 "Snapshot too old". Есть еще множество других нюансов, но я не ставлю целью в этой статье рассказать все о разработке в Оракл.

СУБД поддерживает также параллельное выполнение запроса – для его использования достаточно в запросе указать хинт /*+parallel*/. Но следует помнить, что:

1. далеко не каждому запросу параллельное выполнение идет на пользу;

2. если запрос выполняется параллельно, он забирает у системы ресурсы, которые могли бы использоваться другими сессиями. Причем запрос будет стремиться забрать вообще все свободные ресурсы, чтобы этого не происходило, нужно в хинте указать желаемую степень параллельности, которая может быть индивидуально рассчитана разработчиком или DBA для каждой конкретной БД и сервера.

Потому – оптимизация запроса в первую очередь, а параллельное выполнение – только если дальнейшая оптимизация неэффективна, запрос работает долго, а его распараллеливание значительно ускоряет работу, незначительно мешая работать другим. Как правило, это достижимо в хранилищах данных, а в OLTP-системах параллельное выполнение запросов вредно и обычно DBA отключают возможность его использования.

При достижении определенного количества потоков, дальнейшее увеличение числа потоков приводит к замедлению обработки. Например, у системы нет больше ресурсов по CPU, памяти, дискам и т.д. Или возрастает конкуренция.

Конкуренция

Чем меньше код создает защелок, тем меньше конкуренции.

Если джобы читают одну и ту же таблицу, то для снижения конкуренции может быть использовано партиционирование таблицы - так, чтобы каждый джоб работал со своей партицией. Но количество партиций фиксированно, задается при создании таблицы и не может быть оперативно изменено. С другой стороны, никто не мешает в одном джобе, если он только один, последовательно обрабатывать все партиции. Или завести для каждой партиции два-три джоба. Но в случае партиционирования нужно не забывать - оно не предназначено для ускорения работы системы. Хотя в случае хранилища данных, ускорение и даже значительное вполне возможно. Партиционирование в системах OLTP необходимо в первую очередь для повышения управляемости данными средствами DBA. Но если в запросах забывать указывать ключ партиционирования, либо указывать его в неочевидной для СУБД форме, а индексы локальные, то запрос будет просматривать все партиции и процесс замедлится значительно.

Оптимальность запросов

Запросы должны быть написаны таким образом чтобы уже при отборе данных только по индексу мы получали почти то, что нужно. Для этого должна быть правильно спроектирована структура таблиц и построены нужные индексы.  Например, в таблице customers есть сто миллионов строк, из них нам нужно получить одну строку, исходя из условий:

• клиент активен (cust_status=1),

• находится в филиале (cust_branch=1),

• на тарифном плане (cust_tp_id=123),

• у него тип (cust_type=2), 

• подтип (cust_sub_type=3),

• дата создания  (cust_created=01.02.2022 13:44:55).

Предположим, имеется индекс (cust_status, cust_branch,cust_tp_id,cust_type,cust_sub_type). По такому индексу будет отобрано из ста миллионов где-то сто тысяч клиентов, по каждому из них будет выполняться чтение таблицы для проверки даты создания. В итоге получим одного клиента, но это будет совершенно неэффективно. К тому же индекс содержит много столбцов, его чтение - тоже тяжелая операция. При добавлении новых строк в таблицу (или обновлении старых) системе придется проводить большую работу по изменению индекса. А теперь предположим, что у нас есть индекс (cust_created,cust_branch,cust_tp_id). Считав из индекса только cust_created = 01.02.2022 13:44:55, система уже получает всего несколько десятков или сотен клиентов, а обработав оставшиеся два столбца индекса, система получит менее десяти строк, для которых нужно обращение непосредственно к таблице. Таким образом, один и тот же запрос в случае второго индекса оказывается в тысячи раз легче для системы, чем первый.

Помимо эффективного использования индексов, также на нагрузку от запроса влияют:

• сортировка (если ее нет, запрос легче);

• количество возвращаемых полей (чем меньше, тем запрос легче);

• наличие/отсутствие distinct, union. Если можно не указывать distinct, лучше этого не делать, если есть возможность вместо union использовать union all, то стоит использовать union all;

• вызов pl/sql функций. Нужно избегать вызова функций в запросе, т.к. это лишнее переключение контекста, а в некоторых случаях (если функция в условии where) она может вызываться гораздо чаще, чем вы ожидаете. Все зависит от плана, который построит оптимизатор. Он может ее вызвать на конечной стадии выполнения запроса, когда в предварительных результатах уже почти нет ничего лишнего, так и на начальной, когда вместо одной итоговой строки у нас есть тысячи строк. Если функция в запросе необходима, можно рассмотреть возможность ее объявления с pragma UDF. Если функция на одних и тех же входных параметрах должна возвращать один и тот же результат, имеет смысл объявить ее как детерминированную, что снизит число ее вызовов системой.

Также важно для полей, которые могут быть использованы для отбора данных по индексу, избегать условия OR.

Запрос вида:

begin
  select c.cust_id
      bulk collect into v_cust_id_tab
      from customers c
    where c.cust_created = v_created
      and (     v_query_type = 1
            and c.cust_branch = v_branch
             or  v_query_type != 1
            and c.cust_tp_id = v_tp_id);
end;

работает значительно хуже, чем

begin
  if ( v_query_type = 1 ) then
    select c.cust_id
      bulk collect into v_cust_id_tab
      from customers c
    where c.cust_created = v_created
     and c.cust_branch = v_branch;
  else
    select c.cust_id
      bulk collect into v_cust_id_tab
      from customers c
    where c.cust_created = v_created
      and c.cust_tp_id = v_tp_id;
  end if;
end;

особенно, если в большинстве случаев v_query_type = 1. Оптимизатор не знает, чему равен v_query_type, и потому не может искать данные в индексе по условию c.cust_branch = v_branch. Данные будут найдены только по первому столбцу индекса (по c.cust_created = v_created), считаны и затем уже для них будет применяться фильтр c.cust_branch = v_branch.

Динамический sql

Всегда, если есть возможность избежать использования динамического sql, но это не приведет к ухудшению выполняемого запроса, нужно избегать его использования. Динамический sql нужен, если на момент написания кода неизвестно, какой именно запрос должен быть исполнен. То есть может поменяться набор возвращаемых полей, список таблиц или набор предикатов во фразе where.

Если без динамического sql никак, то нужно использовать execute immediate, а не пакет dbms_sql. Если без dbms_sql никак, то стоит хотя снизить число вызовов dbms_sql.parse. Например, через переменные пакета реализовать: не вызывать parse, если в этот раз сгенерирован такой же текст запроса, как в предыдущий раз.

С execute immediate бывают сложности, т.к. он игнорирует названия переменных привязки, обращаясь к ним по позиции. Это – проблема, если количество переменных может меняться:

begin
  v_sql := 'select c.cust_name
              from customers c
            where c.cust_branch = :i_branch';
  if i_cust_tp_id is not null then
    v_sql := v_sql || ' and c.cust_tp_id = :i_cust_tp_id';
  end if;
  if i_cust_type is not null then
    v_sql := v_sql || ' and c.cust_type = :i_cust_type';
  end if;
  if i_cust_sub_type is not null then
    v_sql := v_sql || ' and c.cust_sub_type = :i_cust_sub_type';
  end if;
  if     i_cust_tp_id is null
     and i_cust_type  is null
     and i_cust_sub_type is null then
    execute immediate v_sql
    bulk collect into v_cust_name_tab
    using i_branch;
  elsif     i_cust_tp_id is not null
     and i_cust_type  is null
     and i_cust_sub_type is null then
    execute immediate v_sql
    bulk collect into v_cust_name_tab
    using i_branch,
          i_cust_tp_id;
   elsif     i_cust_tp_id is not null
     and i_cust_type  is not null
     and i_cust_sub_type is null then
    execute immediate v_sql
    bulk collect into v_cust_name_tab
    using i_branch,
          i_cust_tp_id,
          i_cust_type;
   elsif     i_cust_tp_id  is not null
     and i_cust_type is not null
     and i_cust_sub_type is not null then
    execute immediate v_sql
    bulk collect into v_cust_name_tab
    using i_branch,
          i_cust_tp_id,
          i_cust_type,
          i_cust_sub_type;
   elsif ...

из-за чего многие используют dbms_sql – там достаточно для каждой  переменной привязки, которая есть в запросе вызвать dbms_sql.bind_variable. Однако сам код для dbms_sql сложнее для написания и понимания. А в плане нагрузки для CPU, dbms_sql тяжелее в несколько раз.

Потому лучше генерировать запрос с постоянным числом переменных привязки, пусть некоторые условия в запросе будут избыточны, и использовать execute immediate:

begin
  v_sql := 'select c.cust_name
              from customers c
            where c.cust_branch = :i_branch';

  if i_cust_tp_id is not null then
    v_sql := v_sql || ' and c.cust_tp_id = :i_cust_tp_id';
  else
    v_sql := v_sql || ' and :i_cust_tp_id is null';
  end if;

  if i_cust_type is not null then
    v_sql := v_sql || ' and c.cust_type = :i_cust_type';
  else
    v_sql := v_sql || ' and :i_cust_type is null';
  end if;

  if i_cust_sub_type is not null then
    v_sql := v_sql || ' and c.cust_sub_type = :i_cust_sub_type';
  else
    v_sql := v_sql || ' and :i_cust_sub_type is null';
  end if;
    
  execute immediate v_sql
  bulk collect into v_cust_name_tab
  using i_branch,
        i_cust_tp_id,
        i_cust_type,
        i_cust_sub_type;
end;

Проверка что переменная привязки is null практически никак не сказывается на быстродействии запроса.

Никаких лишних запросов

Самое тяжелое, что есть в системе - это SQL-запросы, даже самые легкие. Потому если есть возможность не выполнять их, лучше не выполнять. В идеале ситуация такая: один раз в начале процесса получили все данные и дальше передаем их через параметры процедуры, не выполняя повторно никаких запросов. Вот пример медленного кода:

function get_cust_name( p_cust_id in number ) return varchar2 is
  v_name varchar2(2000);
begin
  select c.cust_name
    into v_name
    from customers c
  where c.cust_id = p_cust_id; 
  return v_name;
end;

function get_cust_crnc( p_cust_id in number ) return varchar2 is
  v_crnc varchar2(200);
begin
  select c.cust_crnc
    into v_crnc
    from customers c
  where c.cust_id = p_cust_id; 
  return v_crnc;
end;   

function get_cust_amount( p_cust_id in number ) return number is
  v_amt number;
begin
  select c.cust_amount
    into v_amt
    from customers c
  where c.cust_id = p_cust_id; 
  return v_amt;
end;

procedure process_one_cust( p_cust_id in number ) is
  v_name varchar2(2000);
  v_crnc varchar2(200);
  v_amt number;
begin
  v_name := get_cust_name(p_cust_id);
  v_crnc := get_cust_crnc(p_cust_id);
  v_amt := get_cust_amount(p_cust_id);
  …-- тут выполним необходимые действия
end;

procedure process_custs is
begin
  for i in (select c.cust_id
              from customers c
            where mod( c.cust_id, p_process_count ) = p_process_idx  ) loop
    process_one_cust((i.cust_id);
  end loop;
end;

то же самое можно написать гораздо короче, эффективнее и надежнее:

procedure process_one_cust( p_cust_id in number,
                            p_name in varchar2,
                            p_crnc in varchar2,
                            p_amt in number ) is
begin
  …-- тут выполним необходимые действия
end;

procedure process_cust is
  cursor lc_cust is
  select c.cust_id, c.cust_name, c.cust_crnc, c.cust_amount
    from customers c
  where mod( c.cust_id, p_process_count ) = p_process_idx;
  
  type lt_cust_tab is table of lc_cust%rowtype;
  v_cust_tab lt_cust_tab;
begin
  open lc_cust;
  fetch lc_cust bulk collect into v_cust_tab;
  close lc_cust;
  for i in 1..v_cust_tab.count  loop
    process_one_cust( v_cust_tab(i).cust_id,
                      v_cust_tab(i).cust_name,
                      v_cust_tab(i).cust_crnc,
                      v_cust_tab(i).cust_amount );
  end loop;
end;

Пусть есть 100 000 клиентов, которых должна обработать процедура. В первом варианте будет выполнен 1 основной запрос и по 100 000 раз еще 3 запроса - итого 300 001 выполнений запросов. Во втором варианте всего 1 выполнение запроса.

Но не следует считывать в коллекцию, особенно многомерную, миллионы строк и десятки полей - есть риск потратить всю память PGA (если в системе очень много сессий).

Если единственным источником данных запроса является коллекция, при этом в запросе нет сортировки, то смысла в таком запросе нет, лучше сделать тоже самое циклом по коллекции. Потому что цикл — это просто повторить действия n раз и все. Доступ к элементу коллекции из pl/sql-кода происходит практически мгновенно. А SQL-запрос – это сложный алгоритм. Нужно разобрать текст запроса, подставить переменные привязки, считать данные из индекса/таблицы/коллекции, отфильтровать, повторить и т.д.

Если в запросе что-то меняется, то это должны быть переменные привязки. Причем таких переменных в запросе должно быть немного (не десятки, а единицы штук). Если что-то постоянно, это должны быть литералы, а не константы, определенные в пакете. Потому что константы для оптимизатора — это те же переменные привязки, а значит их значение заранее неизвестно, потому план может получиться менее оптимальным. На этапе выполнения придется передавать значение констант в запрос, это тоже затраты времени (хоть они и незначительные, но тем не менее). Если же в SQL-запросе будут меняться литералы, то для каждого выполнения в Оракл это будет новый SQL-запрос с hard parse и всеми вытекающими из этого последствиями.

Тестирование

Любой код должен быть протестирован как функционально, так и нагрузочно. Не выполнив нагрузочное тестирование, мы ничего не будем знать о том, какую нагрузку на систему он создает и насколько быстро он будет работать. Даже если разработчик напрямую не участвует в нагрузочных испытаниях, результаты должны быть ему доступны для анализа возможных улучшений кода. Только имея данные о производительности – трассировочные файлы, отчеты AWR, отчеты профилировщика, – можно значительно повышать производительность приложения.

Итак, сегодня мы рассмотрели некоторые важные моменты разработки в Оракл, про которые должен помнить каждый разработчик, чтобы писать эффективный код. Тем, кого интересует данная тема, рекомендую также почитать книгу Тома Кайта "Оракл для профессионалов".