От переводчика: свой путь на 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 ISBEGIN 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 DETERMINISTICISBEGIN 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 executed6
Обратите внимание, что, хотя функция была вызвана 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_CACHEISBEGIN 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_CACHEISBEGIN 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 100All done 6BEGIN 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 0pass_number executed for 200Returned 0pass_number executed for 300Returned 0Returned 0Returned 0Returned 0
Хотя я вызвал эту функцию трижды со входным значением 100 (один раз в первом блоке и дважды во втором), она исполнилась лишь однажды, при самом первом вызове, хотя сами вызовы относились к разным блокам исполняемого кода.
Аналогично и со входными значениями 200 и 300 - функция была выполнена лишь однажды для каждого из них, хотя я вызывал ее дважды.
Более того! Если я создам новую сессию (без перекомпиляции функции) и затем повторно вызову эти же два блока PL/SQL кода, в выводе я увижу только:
All done 6Returned 0Returned 0Returned 0Returned 0Returned 0Returned 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
, но не всякая функция, использующая это
ключевое слово, является детерминированной.