Orasql.ru

Appearance

Взаимодействие PL/SQL и SQL. Переключение контекста ​

Движки SQL и PL/SQL ​

Когда Oracle получает команду выполнить какой-либо SQL запрос, он передаёт эту работу SQL движку. Следует взять за правило, что SQL движок является более быстрым, чем PL/SQL движок - все его функции встроены в ядро БД и написаны на языке C. Это основной язык для работы с данными в БД, он лучше оптимизирован. PL/SQL код, в свою очередь, выполняется PL/SQL движком. Он добавляет возможность процедурного программирования, но, в свою очередь, он медленнее, чем SQL, несмотря на то, что в Oracle постоянно работают над его улучшением.

Мы знаем, что из SQL можно вызывать PL/SQL функции, а в PL/SQL свободно использовать SQL запросы, и большинство задач решаются с использованием как SQL, так и PL/SQL. Давайте разберёмся, как взаимодействуют между собой эти движки и как частое переключение между ними может повлиять на производительность приложений.

Итак, когда нужно выполнить SQL запрос, в работу включается SQL движок. Что будет, если в нашем запросе мы будем вызывать PL/SQL функцию, например вот так:

sql
select get_discount(o.id) disc_value,
       o.num,
       o.order_date
from orders o
select get_discount(o.id) disc_value,
       o.num,
       o.order_date
from orders o

Помимо номера и даты заказа, мы получаем ещё и размер скидки по нему, который считается PL/SQL функцией get_discount. Так как это PL/SQL функция, Oracle придется использовать PL/SQL движок для её выполнения. Если представить, что в таблице заказов 100 строк, то это означает, что выполнение в PL/SQL движок придётся передать 100 раз.

Подобная передача выполнения из одного движка в другой называется переключением контекста(англ. Context switch), и это то, чего следует по возможности избегать, так как переключение из одного движка в другой занимает дополнительное время. Но здесь есть одна особенность - вызов PL/SQL из SQL даёт большую нагрузку, чем вызов SQL из PL/SQL кода, так что первое, чего следует избегать - это вызова PL/SQL функций из SQL запросов.

Давайте посмотрим на обратный пример - вызов SQL запроса из PL/SQL процедуры:

plsql
create or replace procedure set_phone_notes(
    pstart date,
    pend date
) is
begin
    update app_users au
    set au.note = (select listagg(ui.phone, ';')
        from user_phones ui
        where ui.user_id = au.id)
    where au.reg_date between pstart and pend
    and au.notes is null;
end;
create or replace procedure set_phone_notes(
    pstart date,
    pend date
) is
begin
    update app_users au
    set au.note = (select listagg(ui.phone, ';')
        from user_phones ui
        where ui.user_id = au.id)
    where au.reg_date between pstart and pend
    and au.notes is null;
end;

Данная процедура обновляет поле с примечанием (note) на номера телефонов пользователя, перечисляемые через точку с запятой. Данные обновляются для пользователей, зарегистрировавшихся за указанный период, и у которых поле с примечанием не пустое.

Как будет происходить переключение контекста, если мы вызовем эту процедуру?

plsql
declare
    l_start date := sysdate;
    l_end date := sysdate - 10;
begin
    set_phone_notes(l_start, l_end);
end;
/
declare
    l_start date := sysdate;
    l_end date := sysdate - 10;
begin
    set_phone_notes(l_start, l_end);
end;
/

В данном примере мы вызываем PL/SQL процедуру, которая внутри себя вызывает один SQL запрос, передавая его исполнение SQL движку. Таким образом, у нас производится одно переключение контекста.

С другой стороны, наш код мог бы выглядеть следующим образом:

plsql
create or replace procedure set_phone_notes(
    pstart date,
    pend date
) is
begin
    update app_users au
    set au.note = get_phones_agg(au.id)
    where au.reg_date between pstart and pend
    and au.notes is null;
end;
create or replace procedure set_phone_notes(
    pstart date,
    pend date
) is
begin
    update app_users au
    set au.note = get_phones_agg(au.id)
    where au.reg_date between pstart and pend
    and au.notes is null;
end;

Здесь получение строки с номерами телефонов по заданному пользователю вынесено в отдельную функцию get_phones_agg. Казалось бы, код стал выглядеть лучше в плане читаемости программистом - теперь проще разобраться, что же за значение мы устанавливаем, но вызывая PL/SQL функцию, мы добавляем по одному переключению контекста для каждой из обновляемой строки, причём это переключение из SQL в PL/SQL.

Вообще, довольно сложно бывает выбрать правильный баланс между монстрообразным SQL запросом или последовательностью вызова PL/SQL функций, выполняющих более мелкие задачи по отдельности, но лучше всегда стараться использовать SQL. Здесь нельзя не процитировать известную мантру Тома Кайта:

  1. Вы должны сделать это одним SQL выражением
  2. Если вы не можете сделать это одним SQL выражением, используйте PL/SQL
  3. Если вы не можете сделать это на PL/SQL, попробуйте хранимые процедуры на Java
  4. Если вы не можете сделать это на хранимых процедурах Java, используйте хранимые процедуры на C
  5. Если вы не можете сделать это с помощью хранимых процедур на C, вам следует серьёзно подумать о том, зачем вы вообще это делаете.

DRY ​

Известный принцип DRY (Dont Repeat Yourself) [Wikipedia] гласит, что мы должны стремиться к максимальному переиспользованию кода, и в разрезе темы переключения контекста слепое следование данному принципу может негативно сказаться на скорости выполнения кода.

Допустим что у нас уже определены функции для получения определённых частей информации по заказам. Пусть это будут следующий набор:

  • get_order_status - получить статус заказа
  • get_discount_value - получить размер скидки
  • get_order_owner - получить владельца заказа

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

sql
select o.id,
       o.num,
       o.get_order_status(o.id) status,
       o.get_order_owner(o.id) owner,
       o.get_phones_agg(o.id) phones
from orders o
where o.order_date between :pstart and :pend
select o.id,
       o.num,
       o.get_order_status(o.id) status,
       o.get_order_owner(o.id) owner,
       o.get_phones_agg(o.id) phones
from orders o
where o.order_date between :pstart and :pend

Но в результате получаем крайне неэффективный запрос, который для каждой из обрабатываемых строк делает лишние переключения контекста, хотя в данном случае их легко избежать, используя чистый SQL:

sql
select o.id,
       o.num,
       o.status,
       o.owner,
       (select listagg(ui.phone, ';')
        from user_phones ui
        where ui.user_id = o.owner) phones
from orders o
where o.order_date between :pstart and :pend
select o.id,
       o.num,
       o.status,
       o.owner,
       (select listagg(ui.phone, ';')
        from user_phones ui
        where ui.user_id = o.owner) phones
from orders o
where o.order_date between :pstart and :pend

Поля status и owner вообще являются колонками в таблице orders, список телефонов легко получается с использованием коррелированного подзапроса. Всё, теперь никаких переключений контекста, задача решена с использованием чистого SQL. Конечно, повторного использования кода по возможности следует избегать. Но когда речь идёт об получении набора данных из большого количества строк, следует использовать SQL настолько, насколько вы можете.

Нужно отметить, что нет ничего прохого в том, чтобы иметь функции, которые возвращают какое-то одно значение из БД, ведь мы вполне можем столкнуться с ситуацией, когда нам и нужно только одно это значение:

plsql
declare
    l_order_owner number;
    l_order_num number := 10;
begin
    l_order_owner := get_owner(l_order_num);
    -- ... Код, который как-либо использует l_order_owner
end;
declare
    l_order_owner number;
    l_order_num number := 10;
begin
    l_order_owner := get_owner(l_order_num);
    -- ... Код, который как-либо использует l_order_owner
end;

Как бороться со сложностью ​

Изучайте SQL ​

Иногда бывает так, что кажется, будто задача не может быть решена на чистом SQL, но на самом деле вполне себе может. Разработчик может попросту не знать о каких-то возможностях SQL, функциях или приёмах их использования. Некоторые возможности SQL могли быть отсутствовать в предыдущей версии БД, разработчик привык к ней, а после обновления на более новую версию не изучал новые возможности или ещё не научился их применять. Одним словом, нужно больше всего времени уделять изучению именно SQL.

Используйте views ​

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

Используйте subquery factoring ​

Это можно отнести к совету об изучении SQL, но мы упомянем эту возможность отдельно. Напомним, subquery factoring позволяет разбить запрос на именованные подзапросы, сильно облегчив понимание того, что же делает весь запрос в целом. В какой-то степени это как разбиение кода на функции в императивных языках, только в SQL запросе.