Oracle: разница между deterministic и result_cache

От переводчика: свой путь на Habr я решил начать не с попытки написать какой-то уникальный текст с нуля, а с перевода относительно свежей (от 17.08.2020) статьи классика PL/SQL-разработки Стивена Фойерштайна, в которой он достаточно подробно рассматривает разницу между двумя основными вариантами кэша результатов выполнения PL/SQL функций. Надеюсь, что этот перевод будет полезен для многих разработчиков, начинающих работу с технологиями Oracle.

Введение

Рано или поздно к любому опытному разработчику Oracle обращаются с вопросом наподобие:

Я не понимаю, в чем именно разница между deterministic и result_cache. Они имеют разные сценарии использования? Я использую deterministic во многих функциях, получающих данные из справочных таблиц. Необходимо ли мне использовать ключевое слово result_cache вместо deterministic?

Я подумал, что стоит написать про различия этих двух возможностей. Прежде всего, давайте убедимся, что мы все одинаково понимаем, в каких случаях функция является детерминированной (deterministic).

В Википедии дается следующее определение детерминированного алгоритма:

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

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

Резонный вопрос — что же может считаться побочным эффектом для функции на PL/SQL? Как минимум (список не является исчерпывающим):

• любой (то есть совсем любой) DML-оператор

• Использование переменной, объявленной вне этой функции (то есть глобальной, out-of-scope aka «global»)

• вызов любой не-детерминированной подпрограммы

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

Создаем детерминированную функцию

FUNCTION betwnstr (    string_in   IN   VARCHAR2  , start_in    IN   INTEGER  , end_in      IN   INTEGER )    RETURN VARCHAR2 DETERMINISTIC  IS BEGIN    RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END;

Эта функция — простейшая обертка над substr, позволяющая указывать начальную и конечную позицию возвращаемой подстроки, вместо начальной позиции и длины. Я думаю, что все согласны, что эта функция является детерминированной.

Для того, чтобы движок Oracle Database считал ее таковой, необходимо просто добавить ключевое слово DETERMINISTIC к объявлению этой функции (или процедуры).

Что это даст?

• Это позволяет использовать эту функцию для создания индекса, основанного на функции

• Это может (но не обязано) улучшить производительность за счет кэширования и переиспользования результата выполнения этой функции

Давайте посмотрим, как кэширование влияет на детерминированную функцию:

CREATE OR REPLACE FUNCTION pass_number (i NUMBER)    RETURN NUMBER    DETERMINISTIC IS BEGIN    DBMS_OUTPUT.put_line ('pass_number executed');    RETURN 0; END; /  DECLARE    n   NUMBER := 0; BEGIN    FOR rec IN (SELECT pass_number (1)                  FROM all_objects                 WHERE ROWNUM < 6)    LOOP       n := n + 1;    END LOOP;     DBMS_OUTPUT.put_line (n + 1); END; /  pass_number executed 6

Обратите внимание, что, хотя функция была вызвана 5 раз, она была исполнена только один раз. Oracle Database создала короткоживущий кэш, действительный только для этой функции и только в этом вызове (блоке PL/SQL или SQL-запросе, из которого мы обратились к функции).

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

Создаем функцию, использующую result_cache

Давайте изменим уже знакомую нам функцию betwnstr, чтобы она превратилась в использующую result_cache:

FUNCTION betwnstr (    string_in   IN   VARCHAR2  , start_in    IN   INTEGER  , end_in      IN   INTEGER )    RETURN VARCHAR2     RESULT_CACHE IS BEGIN    RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END;

Все очень просто — я просто добавил ключевое слово RESULT_CACHE. Обратите внимание, что я убрал DETERMINISTIC, но это было сделано исключительно для читаемости. Функция может быть одновременно и детерминированной, и использующей result_cache.

Что же дает использование result_cache? Данное ключевое слово:

• указывает Oracle Database, что необходимо использовать память в SGA (Shared Global Area) для кэширования входных и выходных результатов этой функции

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

• иначе (при «попадании» в кэш этого набора входных значений) результат будет просто получен из кэша и возвращен в вызывающий контекст

• если функция зависит от (в терминах Oracle — ссылается на, references) любых таблиц базы данных, то при любом commit в эти таблицы закэшированные значения функции будут автоматически удаляться

Необходимо отметить, что это лишь верхушка айсберга. RESULT_CACHE — это опция заметно «круче» DETERMINISTIC и может оказать заметно большее воздействие (как положительное, так и отрицательное) на производительность системы в целом. Если вы хотите использовать RESULT_CACHE , то начать следует с подробной официальной документации. Пару примеров использования RESULT_CACHE можно найти на Oracle Live SQL.

Давайте посмотрим, как выглядит кэширование для функций, использующих RESULT_CACHE:

CREATE OR REPLACE FUNCTION pass_number (i NUMBER)    RETURN NUMBER    RESULT_CACHE IS BEGIN    DBMS_OUTPUT.put_line ('pass_number executed for ' || i);    RETURN 0; END; /  DECLARE    n   NUMBER := 0; BEGIN    FOR rec IN (SELECT pass_number (100)                  FROM all_objects                 WHERE ROWNUM < 6)    LOOP       n := n + 1;    END LOOP;     DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1)); END; /  pass_number executed for 100 All done 6   BEGIN    DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));    DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));    DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));    DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));    DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));    DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300)); END; /  Returned 0 pass_number executed for 200 Returned 0 pass_number executed for 300 Returned 0 Returned 0 Returned 0 Returned 0

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

Аналогично и со входными значениями 200 и 300 — функция была выполнена лишь однажды для каждого из них, хотя я вызывал ее дважды.

Более того! Если я создам новую сессию (без перекомпиляции функции) и затем повторно вызову эти же два блока PL/SQL кода, в выводе я увижу только:

All done 6 Returned 0 Returned 0 Returned 0 Returned 0 Returned 0 Returned 0

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

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

Что объединяет deterministic и result_cache?

Использование ключевых слов DETERMINISTIC и RESULT_CACHE может улучшить производительность за счет исключения исполнения функции.

Чем они отличаются?

Кэш, создаваемый при вызове функции, объявленной с использованием ключевого слова DETERMINISTIC, имеет узкую область видимости (только одна сессия) и небольшое время жизни (кэш существует только во время исполнения SQL-запроса, вызвавшего функцию). Общее влияние на производительность, вероятно, будет незначительным.

Наоборот, результаты исполнения, добавленные в кэш для функций, объявленных с использованием RESULT_CACHE, доступны для всех пользователей экземпляра (instance) и существуют до тех пор, пока не инвалидированы (изменением таблицы, от которой зависит функция) или не удалены вручную. Такой вариант кэширования может намного сильнее улучшить производительность в целом, но также вынуждает мириться с риском отрицательных последствий.

Когда использовать детерминированные функции?

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

Этим вы даете понять движкам PL/SQL и SQL, как они могут оптимизировать выполнение функции, а также делаете ее доступной для использования в некоторых специфических случаях (например, для построения основанного на функции индекса).

Даже если это не даст немедленного положительного эффекта, он может проявиться в будущем. Соответственно, было бы неплохо добавить это ключевое слово в объявление функции, если бы не одно НО.

Ни в коем случае нельзя использовать ключевое слово DETERMINISTIC в функциях, которые не являются детерминированными. Иногда Oracle поймет, что мы его обманываем, но зачастую это приведет к проблемам во всей вашей системе.

Когда использовать функции, объявленные с использованием result_cache?

Ответить на этот вопрос сложнее. Добавление ключевого слова RESULT_CACHE породит цепную реакцию в рамках всего экземпляра СУБД и повлияет на производительность в целом. Необходимо предметно проработать с DBA, что экземпляры разработки, тестирования и эксплуатации корректно настроены. Прежде всего, что объем SGA достаточен для всех кэшей и сделать все, чтобы избежать конфликта защелок (latch contention).

Необходимо крайне осмотрительно выбирать функции, которые будут использовать result_cache. Можно использовать следующие основные критерии для их определения:

• эта функция часто вызывается с одними и теми же входными значениями?

• если функция зависит от таблицы, неизменны ли данные в этой таблице? Если да, то это хороший кандидат

• если функция зависит от таблицы, верно ли, что данные читаются заметно чаще, чем изменяются? Нужно помнить, что кэш будет очищен при изменении значений в таблице, от которой зависит функция

• зависит ли функция от каких-либо специфических для сессии параметров, например NLS? Если да, то кэшированное значение может оказаться неверным, как, например, при использовании функции TO_CHAR без явно указанной маски формата. Решением в подобных случаях может стать вынос всех определяемых в сессии зависимостей в список входных параметров

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