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

в 10:47, , рубрики: deterministic, oracle, PL/SQL, result_cache, sql

От переводчика: свой путь на 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 &lt; 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, но не всякая функция, использующая это ключевое слово, является детерминированной.

Автор: idubonos

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js