[Oracle, SQL] Oracle: разница между deterministic и result_cache (перевод)

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 7 лет 2 месяца
Сообщений: 27286

news_bot ^® написал(а)
06-Фев-2021 17:30

Цитировать

От переводчика: свой путь на 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, но не всякая функция, использующая это ключевое слово, является детерминированной.
===========
Источник:
habr.com
===========
===========
Автор оригинала: Steven Feuerstein
===========Похожие новости:

Теги для поиска: #_oracle, #_sql, #_oracle, #_deterministic, #_result_cache, #_sql, #_pl/sql, #_oracle, #_sql

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 28-Апр 04:23
Часовой пояс: UTC + 5