Oracle Database 12c: PL/SQL inside SQL query

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

SELECT 
  inc_amount(a_a.amount)
FROM 
  ACCOUNTS_AMOUNT a_a; 

При этом в СУБД имеется одна хранимая процедура и одна функция, которые используются в этом запросе:

CREATE OR REPLACE PROCEDURE inc(v_pValue in out number,
                                v_pSize  in number) is
BEGIN
  v_pValue := v_pValue + v_pSize;
END;
/
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; 

В вышеприведенном запросе, для каждой строки таблицы исполняющая среда 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, включать определение функций которые в нем используются. Это позволяет минимизировать затраты на переключение контекста.

Таким образом, в версии 12с, вышеприведенный пример можно переписать следующим образом:

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_a.amount))
FROM 
  ACCOUNTS_AMOUNT a_a; 

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

SQL> SELECT
  2     max(inc_amount(a_a.amount))
  3   FROM
  4     ACCOUNTS_AMOUNT a_a;

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1100

Elapsed: 00:00:01.43

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

SQL> WITH
PROCEDURE inc(v_pValue in out number,
  2           v_pSize  in number) is
  3  BEGIN
  4    v_pValue := v_pValue + v_pSize;
  5  END;
  6
  7  FUNCTION inc_amount(v_pValue in number) RETURN number is
  8    v_xValue number(9);
  9  BEGIN
  10   v_xValue := v_pValue;
  11   inc(v_xValue,10);
  12   
  13   return v_xValue;
  14 END;
  16 SELECT
  17   max(inc_amount(a_a.amount))
  18 FROM
  19    ACCOUNTS_AMOUNT a_a
  /

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1100

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_a.amount))
FROM 
  ACCOUNTS_AMOUNT a_a; 
  /

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1100

Elapsed: 00:00:00.65

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

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

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

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

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

SQL> 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;
/
Procedure created.

SQL> 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; 
/
Function created.

SQL> SELECT
  2     max(inc_amount(a_a.amount))
  3   FROM
  4     ACCOUNTS_AMOUNT a_a;

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1100

Elapsed: 00:00:00.52

Перед первым выполнением запрос неявно будет переписан исполняемой средой SQL, таким образом, как если бы в него были явно включены определения вызываемых PL/SQL-функций которые были скомпилированы с директивой компиляции UDF.
При этом, даже если процедура или функция имеет эту прагму в своем объявлении, ничего не мешает вызывать ее в 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:

SQL> declare
  k number;
begin
  for i in 1..10000000
  loop
    PRAGMA INLINE (inc_amount, 'NO');
    k := inc_amount(1);
  end loop;
end;
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.32

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

SQL> 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 в подзапросе, то немедленно получим ошибку:

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_a.amount))
FROM 
  ACCOUNTS_AMOUNT a_a);

(WITH
 *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause

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

SQL> 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_a.amount))
FROM 
  ACCOUNTS_AMOUNT a_a);

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1100

Elapsed: 00:00:00.52

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

--Для удобства очистим таблицу и вставим в нее всего 5 записей, при этом некоторые значения будут повторяться:

SQL> TRUNCATE TABLE accounts_amount;

Table truncated.

SQL> INSERT INTO accounts_amount VALUES(1100);

1 row created.

SQL> INSERT INTO accounts_amount VALUES(11);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM accounts_amount;

    AMOUNT
----------
      1100
        11
        11
        11
        11
        11

6 rows selected.

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

SQL> set serveroutput on

SQL> 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_a.amount))
FROM
  ACCOUNTS_AMOUNT a_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> 

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

SQL> 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.

SQL> set serveroutput on
SQL> SELECT
   max(inc_amount(a_a.amount))
FROM
  ACCOUNTS_AMOUNT a_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.

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

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

SQL> CREATE OR REPLACE FUNCTION inc_amount(v_pValue in number) RETURN number DETERMINISTIC is
  2          v_xValue number(9);
  3        BEGIN
  4         dbms_output.put_line('Function inc_amount called.');
  5         v_xValue := v_pValue;
  6         inc(v_xValue,10);
  7  
  8         return v_xValue;
  9       END;
 10  /

Function created.

SQL> set serveroutput on
SQL> SELECT
   max(inc_amount(a_a.amount))
FROM
  ACCOUNTS_AMOUNT a_a;
 /

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1110

Function inc_amount called.
Function inc_amount called.
SQL>

Будем надеяться, что в будущих версиях СУБД, этот недочет будет устранен.

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

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

SQL> CREATE OR REPLACE FUNCTION inc_amount(v_pValue in number) RETURN number RESULT_CACHE 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.

SQL> SELECT
   max(inc_amount(a_a.amount))
FROM
  ACCOUNTS_AMOUNT a_a;

MAX(INC_AMOUNT(A_A.AMOUNT))
---------------------------
                       1110

Function inc_amount called.
Function inc_amount called.

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

SQL> WITH
  2  FUNCTION inc_amount(v_pValue in number) RETURN number RESULT_CACHE is
  3    v_xValue number(9);
  4  BEGIN
  5    dbms_output.put_line('Function inc_amount called1.');
  6
  7    v_xValue := v_pValue;
  inc(v_xValue,10);
  8
  return v_xValue;
  9   10   11  END;
SELECT
 12   13     max(inc_amount(a_a.amount))
 14  FROM
 15    ACCOUNTS_AMOUNT a_a;
 16  /
   max(inc_amount(a_a.amount))
       *
ERROR at line 13:
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

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

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

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