Всем привет!

Меня зовут Андрей Бобронников, я занимаюсь обеспечением надежности ИТ систем в банке Уралсиб. И сегодня я вам расскажу о методах оптимизации для разработчиков на платформе ЦФТ в БД Oracle.

Ввиду роста бизнеса, наши системы стали обрабатывать все больше транзакций и возникла необходимость собрать материалы, которые помогут разработчикам ЦФТ писать оптимальный и быстрый код под Oracle на языке PL+, PL/SQL и SQL.

В данной статье постарался собрать минимум необходимых знаний и методов для написания оптимального кода без ошибок. Можно использовать для обучения новых сотрудников и как справочный материал.

Выполнение SQL

select a from table where b = 123;

select a from table where b = :B;

b := 123;
v_sql := 'select a from table where b = :1';
execute immediate v_sql into c using b;

execute immediate sql_str using in Params, Delimiter;

Далее вспоминаем про приведение типов, как причину длительного выполнения запроса. Важно помнить про типы везде, при сравнении двух разных типов данных в SQL запросе мы накладываем дополнительные расходы на приведение к одному типу и не даем возможности построить лучший план.

Рассмотрим на примере: имеется таблица с историей состояний всех объектов в базе. С индексом по столбцу obj_id.

create table OBJECT_STATE_HISTORY
(
id NUMBER,
time TIMESTAMP(6),
obj_id VARCHAR2(128),
state_id VARCHAR2(16)
);

Количество объектов в базе более, чем очень велико. И есть проблема - во всех таблицах, как и в нашей, id это числовое значение, а obj_id - строковое. И когда мы ищем состояния нужного нам объекта следующим образом:

select *
from table t
,OBJECT_STATE_HISTORY osh
where t.id = 12345
and osh.obj_id = t.id

Oracle быстро находит искомое значение в таблице table и идет читать всю огромную таблицу OBJECT_STATE_HISTORY, в поиске нужных строк.

Для того, чтобы этого избежать, следует в условии and osh.obj_id = t.id привести t.id к строке. Тогда запрос встанет на индекс и отработает значительно быстрее.

Кстати, подобным образом можно поступить, когда мы не хотим, чтобы запрос вставал на индекс, можно добавить +0 к числовому столбцу или конкатенацию с пустой строкой к строке. Тогда Oracle не поймет, что за столбец используется в запросе и не будет использовать индекс для этого него.

План, конечно план.

Обязательно, в случае длительного выполнения запроса нужно посмотреть на его план. Есть множество статей по оптимизации планов запросов. Тут хочу упомянуть основной принцип: необходимо строить план от наиболее уникальной выборки к наименее, т.е. от меньшего к большему, а индексы выбирать по наиболее селективному условию. Селективность это отношение количества искомых строк к общему. Например, у нас есть таблица table со столбцами ключ и значение (key и value). Посмотрим сколько записей по каждому ключу:

Всего записей в таблице 2810, значит селективность ключа 1 составляет 42%, вероятнее всего, что фулскан по таблице с выбором всех значений по ключу 1 отработает быстрее, чем индексное чтение.

Также следует упомянуть о деградации производительности по мере наполнения данных в таблице. На этапе разработки важно понимать какой будет рост и как поведет себя при этом функционал. Из-за изменения селективности запрос может начать работать медленнее. Подсказки в запросах следует ставить с осторожностью.

Выполнение PL+

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

Курсор вместо For select

Частая конструкция в коде:

for ( select ... ) loop ... end loop;

Она компилируется в:

declare 
  cursor c_obj is select ... ; 
begin 
  for plp$c_obj in c_obj 
  loop ... end loop; 
end;

Автоматически создается курсор и далее, последовательно выбираются данные и обрабатываются в теле цикла. Это очень удобно, но есть одно замечание - при большой выборке будет происходит частые обращения в базу за очередными данными для их обработки. Это накладывает большие расходы и время обработки всех экземпляров сильно увеличивается.
Чтобы этого избежать необходимо выбрать данные во временную таблицу и проходить циклом по ней. Это позволит отделить выборку данных от их обработки (+ к сопровождаемости).

Для этого можно просто изменить существующий код в несколько шагов:

1. вынесем запрос в блок объявления переменных как тип:

   type t_cursor_type is select ... ;

2. добавим объявление типа ссылки курсор и соответствующую переменную:

   type t_cursor_ref is ref cursor return t_cursor_type;

   v_cur t_cursor_ref;

3. добавим объявление типа таблицы результата нашего курсора и соответствующую переменную:

   type t_tbl_type is table of t_cursor_type%rowtype;

   v_table t_tbl_type;

4. далее перед нашим циклом for, откуда мы вытащили запрос, открываем курсор, вычитываем данные и закрываем курсор:

   v_cur.open(t_cursor_type);

   v_cur.fetch(v_table);

   v_cur.close;

5. правим наш цикл на перебор новой таблицы:

   for i in 1..v_table.count() loop

6. в теле цикла меняем обращение через алиас бывшего запроса, а в идеале объявляем переменную с одинаковым именем и выгружаем туда экземпляр обрабатываемого элемента таблицы.

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

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

select a.value, s.program, s.username, s.sid, s.serial#, s.client_identifier 
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic# 
and s.sid = a.sid 
and b.name = 'opened cursors current' 
order by 1 desc;

Структуры вместо ссылок

PL+ язык, созданный компанией ЦФТ, компилируется в PL/SQL. Коллеги из ЦФТ постарались следовать принципам ООП, объявляя объекты, как экземпляры таблицы, а ссылка - это числовая переменная, хранящая уникальный по всей базе идентификатор - id экземпляра.
Создавая тип, компилятор генерирует интерфейсные пакеты, в частности для обращения к реквизитам экземпляра. И когда вы пишите:
v_op_date := p_filial_ref.[OP_DATE];
Это компилируется в:
V_OP_DATE := TABLE#INTERFACE.get_op_date(P_FILIAL_REF);
Посмотрев глубже, мы увидим, что в вызываемой функции выполняется запрос в базу:
select C_OP_DATE into val_from TABLE where ID=obj_id;

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

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

for (select cl(cl) in ::[TABLE] where ... )
loop
...
v_name := cl.[NAME]; -- select
...
cl.[NAME] := v_name; -- update
...
end loop;

В теле цикла будет скомпилирован вызов двух интерфейсных пакетов, в первом случае select, во втором случае update. При больших объемах множество вызовов замедлит выполнение программы. Чтобы ускориться, отделяем выборку от обработки и перечисляем в запросе необходимые нам реквизиты. А если нам нужны все реквизиты, можно использовать конструкцию rowtype.

Изменения, которые мы внесем надо положить в базу, переходим к следующему пункту.

Forall вместо одиночных вставок/изменений

Еще одним преимуществом использования массивов структур является фиксация изменений в базе данных одной командой.

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

insert into ::[TABLE] for i in 1..v_table.count loop a( a%rowtype = v_table%rowtype(i) );

update for i in 1..v_table.count loop a( a.[USER_ADD] = stdlib.user_id ) in ::[TABLE] all where a = v_table(i)

Ограничения:

• потребление памяти, рекомендуется ограничивать большие выборки, делить на пачки, используя конструкции limit для fetch или fetch first row для select;

• проблематика с изменением статусов, если присутствуют переходы.

Дополнительно, у конструкции forall есть дополнительные параметры, такие как сохранение результата в таблицу и обработка исключений.

Кэширование и область видимости переменных

Переменные, объявленные как глобальные, сохраняют свое значение в сеансе. Другие сессии эти значения не видят.

Необходимо осторожно подходить к месту объявления переменных. Рекомендуется использовать правило "чем меньше область видимости переменной, тем лучше".

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

Заключение

Чем проще код - тем меньше в нем ошибок, надеюсь данная статья поможет вам в понимании, как работает ваше приложение и как можно его улучшить.

Спасибо за внимание.