Oracle Database 12c: PL/SQL inside SQL query

 

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

 

В ходе обработки блока программного кода ядро PL/SQL выполняет процедурные команды самостоятельно, а команды SQL передает ядру SQL. Уровень SQL выполняет команды и при необходимости возвращает результаты ядру PL/SQL. Передача управления между ядрами PL/SQL и SQL (см. схему выше) называется переключением контекста.

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

Создадим таблицу и наполним ее данными:

 

create table accounts

(id number generated always as identity,

acc number,

amount number);

 

alter table accounts add constraint pk_accid primary key (id);

begin

 

insert into accounts

(acc, amount)

select 0 + level as code, round(dbms_random.value*100,2) as amount

from dual

connect by level <= 1000000;

commit;

end;

 

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

 

-- Procedure

create or replace procedure inc(v_pValue in out number,

                                v_pSize  in number) is

begin

  v_pValue := v_pValue + v_pSize;

end;

 

-- Function 

create or replace function inc_amount(v_pValue in number) return number is

  v_xValue number(9);

 

begin

  v_xValue := v_pValue;

  inc(v_xValue,10);

 

  return v_xValue;

 

end; 

 

Рассмотрим простой запрос:

 

select

  max(inc_amount(a.amount))

from

  accounts a;

 

В этом запросе для каждой строки таблицы исполняющая среда SQL (SQL-engine) должна вызвать функцию inc_amount, выполнение которой осуществляет виртуальная машина PL/SQL (PL/SQL Virtual Machine). После того, как выполнение функции будет завершено, полученный результат должен быть возвращен в SQL-engine и, далее будет использован для дальнейшего получения результата выборки.

Этот процесс, связанный с переключением в среду PL/SQL VM и возвратом результатов в SQL-engine называется "переключение контекста между SQL и PL/SQL" (SQL and PL/SQL context switch).

На переключение контекста расходуется дополнительные ресурсы, - прежде всего процессорное время.

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

1.1 PL/SQL-подпрограммы в определении SQL-запроса

До Oracle Database версии 12c приходилось мириться с потерей производительности на переключение контекста между исполняющей средой SQL и PL/SQL VM.

В версии 12c появилась возможность прямо в тексте SQL-запроса, в фразе WITH, включать определение функций которые в нем используются. Это позволяет минимизировать затраты на переключение контекста.

Попробуем сравнить время выполнения запроса написанного в двух вариантах.

💧 Вариант с PL/SQL-подпрограммами, определенными вне запроса (всего в таблице находятся 1 млн. записей):

 

select

  max(inc_amount(a.amount))

from

  accounts a;

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       110

 

Elapsed: 00:00:01.36

 

💧 Вариант с функциями, определенными в самом тексте запроса:

 

with

procedure inc(v_pValue in out number,

              v_pSize  in number) is

begin

  v_pValue := v_pValue + v_pSize;

end;

 

function inc_amount(v_pValue in number) RETURN number is

  v_xValue number(9);

begin

  v_xValue := v_pValue;

  inc(v_xValue,10);

 

  return v_xValue;

end;

 

select

  max(inc_amount(a.amount))

from

  accounts a;

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       110

 

Elapsed: 00:00:00.41

 

Скорость выполнения запроса увеличилась более чем в три раза!!!

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

Попробуем определить процедуру inc, вне запроса, то есть обычным образом

 

with

  function inc_amount(v_pValue in number) return number is

    v_xValue number(9);

  begin

    v_xValue := v_pValue;

    inc(v_xValue,10);

 

    return v_xValue;

  end;

 

select

  max(inc_amount(a.amount))

from

  accounts a;

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       110

 

Elapsed: 00:00:00.59

 

 

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

Следует отметить, что при определении PL/SQL-подпрограмм внутри запроса не происходит его непосредственного выполнения в среде SQL-engine, то есть среда выполнения SQL не имеет в своем составе собственной виртуальной машины PL/SQL. Происходит генерирование дополнительной информации для SQL-компилятора, с помощью которой в ходе компиляции и выполнения запроса уменьшаются затраты на переключение контекста. Таким образом, переключение контекста, как таковое, все равно происходит, но значительно снижаются накладнhttps://observsp.blogspot.com/2021/05/merge.htmlые расходы на это переключение.

 

1.2 Прагма компиляции UDF для уже существующих PL/SQL-подпрограмм

Как же быть в том случае, если уже есть работающее унаследованное приложение (разработанное до версии Oracle Database 12c), в котором обычным образом определены PL/SQL-функции, и эти функции используются в SQL-запросах?
Переписывать все SQL-запросы, в которых есть PL/SQL-вызовы, может быть трудоемкой задачей. Более того, может оказаться так, что часть PL/SQL-подпрограмм вызываются как в SQL-запросах, так и в других PL/SQL-объектах. В этом случае переписывание запросов приведет к дублированию кода, то есть один и тот же код нужно будет сопровождать в двух местах.

Для решения этой проблемы в Oracle Database 12c введена новая директива компилятора (прагма) которая позволяет отметить подпрограммы, которые затем будут вызываться в SQL-запросах.

 

Добавим прагму в функцию и процедуру:

 

-- Procedure

create or replace procedure inc(v_pValue in out number,

                                v_pSize  in number) is

   PRAGMA UDF;

begin

  v_pValue := v_pValue + v_pSize;

end;

 

-- Function

create or replace function inc_amount(v_pValue in number) return number is

   PRAGMA UDF;

  

  v_xValue number(9);

 

begin

  v_xValue := v_pValue;

  inc(v_xValue,10);

  return v_xValue;

 

end;

 

Выполним запрос. 

 

select

  max(inc_amount(a.amount))

from

  accounts a;

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       110

 

Elapsed: 00:00:00.50

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

По-прежнему существует переключение контекста, но часть работы выполняется во время компиляции, а не во время выполнения. 

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

SQL> set serveroutput on

 

begin

  dbms_output.put_line(inc_amount(1));

end;

/

11

 

PL/SQL procedure successfully completed.

 

Интересно, изменится ли время выполнения PL/SQL-функции внутри PL/SQL-блока, если ее пометить с помощью прагмы UDF?

Давайте сравним время вызова.

 

💧 Вариант c функциями, определенными с прагмой компилятора UDF:

declare

  k number;

begin

  for i in 1..10000000

  loop

    PRAGMA INLINE (inc_amount, 'NO');

    k := inc_amount(1);

  end loop;

end;

 

💧 Вариант c функциями без прагмы компилятора UDF.

Закомментируем PRAGMA UDF в функции и процедуре и выполним тот же блок:

 

declare

  k number;

begin

  for i in 1..10000000

  loop

    PRAGMA INLINE (inc_amount, 'NO');

    k := inc_amount(1);

  end loop;

end;

 

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.04

 

Время выполнения вызовов PL/SQL-процедур, объявленных с директивой компиляции UDF, больше примерно на семь процентов.

Замедление вызовов небольшое, но все же не стоит устанавливать эту директиву компиляции в всех PL/SQL-подпрограммах - это нужно делать только в тех процедурах, которые будут интенсивно вызываться в SQL-запросах.

1.3 Использование PL/SQL-подпрограмм в подзапросах

Есть одна важная особенность, связанная с использованием PL/SQL вызовов в подзапросах - если использовать определение PL/SQL в подзапросе, то немедленно получим ошибку: 

 

select *

  from

      (with procedure inc(v_pValue in out number,

                          v_pSize  in number) is

      begin

        v_pValue := v_pValue + v_pSize;

      end;

 

      function inc_amount(v_pValue in number) RETURN number is

        v_xValue number(9);

      begin

        v_xValue := v_pValue;

        inc(v_xValue,10);

 

        return v_xValue;

      end;

 

      select

        max(inc_amount(a.amount))

      from

        accounts a);

 

(WITH

 *

ERROR at line 2:

ORA-32034: unsupported use of WITH clause

 

Добавление подсказки оптимизатора (hint) WITH_PLSQL в запросе верхнего уровня (top level query) решает эту проблему:

 

select /*+ WITH_PLSQL */ *

  from

      (with procedure inc(v_pValue in out number,

                          v_pSize  in number) is

      begin

        v_pValue := v_pValue + v_pSize;

      end;

 

      function inc_amount(v_pValue in number) RETURN number is

        v_xValue number(9);

      begin

        v_xValue := v_pValue;

        inc(v_xValue,10);

 

        return v_xValue;

 

      end;

 

      select

        max(inc_amount(a.amount))

      from

        accounts a);

 

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       110

 

Elapsed: 00:00:00.49

 

1.4 Проблема с использованием детерминированных функций в запросах

Особое внимание следует обратить на использование недерминированных функций в запросе. Внутри запроса функция становится недетерминированной, даже несмотря на то, что опция DETERMINISTIC была указана в ее определении.

Для удобства очистим таблицу и вставим в нее 6 записей: 1 строку с неповторяющимися знаениями и продублируем 5 строк.

 

truncate table accounts;

 

begin

 

insert into accounts (acc, amount) values(1,1100);

insert into accounts (acc, amount) values(2,11);

insert into accounts (acc, amount) values(2,11);

insert into accounts (acc, amount) values(2,11);

insert into accounts (acc, amount) values(2,11);

insert into accounts (acc, amount) values(2,11);

 

commit;

 

end;

 

Теперь попробуем функцию inc_amount объявить детерминированной:

with

procedure inc(v_pValue in out number,

              v_pSize  in number) is

begin

  v_pValue := v_pValue + v_pSize;

end;

 

function inc_amount(v_pValue in number) RETURN number DETERMINISTIC is

  v_xValue number(9);

 

BEGIN

 

  dbms_output.put_line('Function inc_amount called.');

  v_xValue := v_pValue;

  inc(v_xValue,10);

 

  return v_xValue;

end;

 

select

   max(inc_amount(a.amount))

  from accounts a;

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       1110

 

К сожалению, если посмотреть в OUTPUT, то увидим, функция inc_amount стала вызываться для каждой строки  запроса, несмотря на то, что была объявлена в тексте запроса как детерминированная. 

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

 

При этом неважно, было ли тело функции явно включено в текст запроса, либо мы воспользовались директивой компиляции UDF:

create or replace function inc_amount(v_pValue in number) return number DETERMINISTIC is

 

       PRAGMA UDF;

       v_xValue number(9);

 

 begin

 

   dbms_output.put_line('Function inc_amount called.');

   v_xValue := v_pValue;

   inc(v_xValue,10);

 

   return v_xValue;

 end;

 

Function created.

 

 

select max(inc_amount(a.amount))

  from accounts a;

 

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       1110

 

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Function inc_amount called.

Функция перестает быть детерминированной внутри SQL-запроса

 

 

 

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

 

create or replace function inc_amount(v_pvalue in number) return number DETERMINISTIC is

   v_xValue number(9);

 

 begin

 

   dbms_output.put_line('Function inc_amount called.');

   v_xValue := v_pValue;

   inc(v_xValue,10);

 

   return v_xValue;

 

 end;

 

Function created.

 

 

select max(inc_amount(a.amount))

  from accounts a;

 

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       1110

 

Function inc_amount called.

 

В 18с это уже устранено. Результат детерминированной функции в 18-й версии будет таким же, как и со старым синтаксисом.

 

1.5 Использование внутри запросов функций с кэшем результатов

Хорошая новость заключается в том что для существующих функций, использующих кэш результатов (RESULT_CACHE), их встраивание в запрос через директиву компиляции UDF замечательно работает:

 

is

  PRAGMA UDF;

  v_xValue number(9);

 

begin

  dbms_output.put_line('Function inc_amount called.');

 

  v_xValue := v_pValue;

  inc(v_xValue,10);

 

  return v_xValue;

end;

 

Function created.

 

 

select max(inc_amount(a.amount))

  from accounts a;

 

 

MAX(INC_AMOUNT(A_A.AMOUNT))

---------------------------

                       1110

 

Function inc_amount called.

Function inc_amount called.

 

Но для функций, определение которых непосредственно входит в запрос, ошибка возникает еще на этапе компиляции:

 

with function inc_amount(v_pValue in number) return number RESULT_CACHE is

   v_xValue number(9);

 

begin

  dbms_output.put_line('Function inc_amount called1.');

 

  v_xValue := v_pValue;

  inc(v_xValue,10);

 

  return v_xValue;

 

end;

 

 

select max(inc_amount(a.amount))

  from accounts a;

 

 

ORA-06553: PLS-313: 'INC_AMOUNT' not declared in this scope

ORA-06552: PL/SQL: Item ignored

ORA-06553: PLS-999: implementation restriction (may be temporary) RESULT_CACHE

is disallowed on subprograms in anonymous blocks

Будем надеяться, что в следующих версиях (или патчсетах) СУБД эта проблема тоже будет устранена. В 18с еще не устранили.

 

Заключение

Встраивание PL/SQL-хранимых процедур непосредственно в текст запросов представляет собой очень сильный инструмент повышения производительности SQL-запросов использующих вызовы PL/SQL.

Директива компиляции UDF (pragma UDF) позволяет с минимальной модификацией кода приложения использовать эту новую технологию.

При использовании встраивания PL/SQL-подпрограмм в определение запроса следует обратить внимание на детерминированные (deterministic) PL/SQL-функции. В настоящий момент детерминированные функции при переносе их в тело запроса (явно или неявно, с помощью директивы UDF) теряют это свойство.

Мнение автора статьи:

На мой взгляд первое, что должен сделать разработчик после перехода на Oracle Database 12c - это вставить прагму UDF в все недетерминированные PL/SQL-функции, которые используются в SQL-запросах! :-)

Данный пост почти целиком взят из блога  Oracle Database 12c: PL/SQL inside SQL query - Life in Oracle - Блог Игоря Мельникова.