oracle deterministic что это
Oracle: Deterministic functions, result_cache and operators
После перевода статьи Oracle: разница между deterministic и result_cache от Steven Feuerstein, хотелось бы дополнить ее действительно важными деталями их устройства. У меня есть серия статей на эти темы, но тут я хотел бы просто все резюмировать и оставить самое важное.
1. Запросы в PL/SQL функциях не консистентны самому запросу, их вызывающему
Дело в том, что запросы внутри функции «видят» данные (согласованы/консистентны) на момент их запуска, а не запроса их вызывающего. И не важно, как определена сама функция, даже функция продекларированная в WITH clause запроса, точно так же будет получать несогласованные данные. То есть, если данные успели измениться за промежуток между стартом основного запроса и запроса внутри функции, то функция будет возвращать другие данные. Примеры тут и тут.
Из этого, очевидно, что либо функции не должны содержать запросов внутри, либо надо создать SQL оператор для нее, пример: оператор f1_op для функции f1:
Кроме того, в Oracle 21 официально появляются SQL Macros: они пока весьма глючны, но в будущем они позволят во многих случаях отказаться от функций, что даст как прирост производительности за счет уменьшения переключений контекста, так и избавит от проблем с консистентностью данных.
2. Количество вызовов функций может быть больше из-за трансформации запроса
Рассмотрим такой простой запрос:
Как вы думаете сколько раз выполнится функция xf?
Ответ зависит от того, как отработает оптимизатор: будет ли выполнен merge подзапроса или нет и произойдет ли filter pushdown: примеры планов:
3. Кэширование deterministic функций в SQL
3.1 Кэширование deterministic функций использует hash-таблицы и функции так же как и scalar subquery caching
И Scalar Subquery Caching(далее SSC) и Deterministic Functions Caching хэшируют входящие аргументы для хранения результатов, соответственно, возможны hash-коллизии.
3.2 Кэш результатов хранится только в течение одного fetch-call’a
Это значит, что эффективность кэширования зависит от fetch size (arraysize в sql*plus) и при каждом Fetch call кэш сбрасывается. Есть лишь одно исключение: когда аргумент функции это константы-литералы. У SSC такого ограничения нет. Кроме того, у SSC есть еще одна приятная особенность: он хранит кэш последнего результата даже в случае hash-коллизии.
3.3 Размер кэша и частота хэш-коллизий зависят от параметра «_query_execution_cache_max_size»
От этого же параметра зависит и SSC.
3.4 Кэширование отключается после определенного кол-ва неудачных попыток
Это зависит от параметра «_plsql_minimum_cache_hit_percent». У SSC есть и тут приятная особенность: даже в случае превышения кол-ва неудачных попыток, хотя кэширование новых значений отключается, уже сохраненные старые результаты он все равно будет возвращать.
Особенности совместного deterministic + result cache, operator + deterministic + result cache:
4. Кэширование deterministic функций в PL/SQL
Вызов deterministic функций может быть вынесен из тела цикла при следующих условиях:
Аргументы функции не должны меняться в теле цикла
Не должно быть неявных конвертаций аргументов (implicit conversions)
Не должно быть вызово не-«детерминированных» функций (кроме некоторых стандартных таких как to_date, to_char, nvl) и процедур
5. Result cache
Резюме резюме
Так как механизм Result Cache сам по себе достаточно тяжелый и гораздо медленнее, чем кэширование скалярных подзапросов или deterministic функций, желательно все такие функции объявлять и как deterministic, чтобы при многократном вызове с одинаковыми аргументами не было лишних достаточно тяжелых обращений к RC, а при потенциальной несогласованности данных лучше использовать собственные операторы. В целом же, надеемся на допиливание SQL Macro лет через 5-10.
Oracle: разница между deterministic и result_cache
От переводчика: свой путь на Habr я решил начать не с попытки написать какой-то уникальный текст с нуля, а с перевода относительно свежей (от 17.08.2020) статьи классика PL/SQL-разработки Стивена Фойерштайна, в которой он достаточно подробно рассматривает разницу между двумя основными вариантами кэша результатов выполнения PL/SQL функций. Надеюсь, что этот перевод будет полезен для многих разработчиков, начинающих работу с технологиями Oracle.
Введение
Рано или поздно к любому опытному разработчику Oracle обращаются с вопросом наподобие:
Я не понимаю, в чем именно разница между deterministic и result_cache. Они имеют разные сценарии использования? Я использую deterministic во многих функциях, получающих данные из справочных таблиц. Необходимо ли мне использовать ключевое слово result_cache вместо deterministic?
Я подумал, что стоит написать про различия этих двух возможностей. Прежде всего, давайте убедимся, что мы все одинаково понимаем, в каких случаях функция является детерминированной (deterministic).
В Википедии дается следующее определение детерминированного алгоритма:
Иначе говоря, детерминированная подпрограмма (процедура или функция) не имеет побочных эффектов. Передавая определенный набор значений в качестве входных параметров, на выходе вы всегда получите один и тот же результат, причем вне зависимости от того, когда, где или как часто вы будете вызывать эту подпрограмму.
любой (то есть совсем любой) DML-оператор
Использование переменной, объявленной вне этой функции (то есть глобальной, out-of-scope aka «global»)
вызов любой не-детерминированной подпрограммы
Далее мы кратко рассмотрим, как использовать deterministic и result_cache при разработке, после чего рассмотрим отличия этих двух возможностей и сценарии их использования. В данной статье мы будем рассматривать функции. Процедуры тоже могут быть детерминированными (но не result_cache), хотя такие процедуры, насколько мне известно, используются достаточно редко.
Создаем детерминированную функцию
Для того, чтобы движок Oracle Database считал ее таковой, необходимо просто добавить ключевое слово DETERMINISTIC к объявлению этой функции (или процедуры).
Это позволяет использовать эту функцию для создания индекса, основанного на функции
Это может (но не обязано) улучшить производительность за счет кэширования и переиспользования результата выполнения этой функции
Давайте посмотрим, как кэширование влияет на детерминированную функцию:
Обратите внимание, что, хотя функция была вызвана 5 раз, она была исполнена только один раз. Oracle Database создала короткоживущий кэш, действительный только для этой функции и только в этом вызове (блоке PL/SQL или SQL-запросе, из которого мы обратились к функции).
Намного более детальный разбор поведения и производительности детерминированных функций можно найти у Роба ван Вейка.
Создаем функцию, использующую result_cache
Что же дает использование result_cache? Данное ключевое слово:
указывает Oracle Database, что необходимо использовать память в SGA (Shared Global Area) для кэширования входных и выходных результатов этой функции
таким образом, при вызове функции она будет исполнена лишь в том случае, если отсутствует кэшированный результат для данных входных параметров
иначе (при «попадании» в кэш этого набора входных значений) результат будет просто получен из кэша и возвращен в вызывающий контекст
Давайте посмотрим, как выглядит кэширование для функций, использующих RESULT_CACHE :
Хотя я вызвал эту функцию трижды со входным значением 100 (один раз в первом блоке и дважды во втором), она исполнилась лишь однажды, при самом первом вызове, хотя сами вызовы относились к разным блокам исполняемого кода.
Более того! Если я создам новую сессию (без перекомпиляции функции) и затем повторно вызову эти же два блока PL/SQL кода, в выводе я увижу только:
Нужно помнить: если неосторожно использовать функции, использующие result_cache, то можно получить ворох непредусмотренных проблем, некоторые из которых описаны в этой статье.
Что объединяет deterministic и result_cache?
Использование ключевых слов DETERMINISTIC и RESULT_CACHE может улучшить производительность за счет исключения исполнения функции.
Чем они отличаются?
Когда использовать детерминированные функции?
Можно вывести простое правило: следует добавлять ключевое слово DETERMINISTIC в объявление любой функции, которая действительно является детерминированной.
Этим вы даете понять движкам PL/SQL и SQL, как они могут оптимизировать выполнение функции, а также делаете ее доступной для использования в некоторых специфических случаях (например, для построения основанного на функции индекса).
Даже если это не даст немедленного положительного эффекта, он может проявиться в будущем. Соответственно, было бы неплохо добавить это ключевое слово в объявление функции, если бы не одно НО.
Ни в коем случае нельзя использовать ключевое слово DETERMINISTIC в функциях, которые не являются детерминированными. Иногда Oracle поймет, что мы его обманываем, но зачастую это приведет к проблемам во всей вашей системе.
Когда использовать функции, объявленные с использованием result_cache?
Ответить на этот вопрос сложнее. Добавление ключевого слова RESULT_CACHE породит цепную реакцию в рамках всего экземпляра СУБД и повлияет на производительность в целом. Необходимо предметно проработать с DBA, что экземпляры разработки, тестирования и эксплуатации корректно настроены. Прежде всего, что объем SGA достаточен для всех кэшей и сделать все, чтобы избежать конфликта защелок (latch contention).
Необходимо крайне осмотрительно выбирать функции, которые будут использовать result_cache. Можно использовать следующие основные критерии для их определения:
эта функция часто вызывается с одними и теми же входными значениями?
если функция зависит от таблицы, неизменны ли данные в этой таблице? Если да, то это хороший кандидат
если функция зависит от таблицы, верно ли, что данные читаются заметно чаще, чем изменяются? Нужно помнить, что кэш будет очищен при изменении значений в таблице, от которой зависит функция
зависит ли функция от каких-либо специфических для сессии параметров, например NLS? Если да, то кэшированное значение может оказаться неверным, как, например, при использовании функции TO_CHAR без явно указанной маски формата. Решением в подобных случаях может стать вынос всех определяемых в сессии зависимостей в список входных параметров
DETERMINISTIC: детерминированные функции в PL/SQL на примере
Vovan_ST
ИТ специалист со стажем. Автор статьи. Профиль
Функция называется детерминированной, если при одном наборе параметров IN и IN OUT она всегда возвращает одно и то же значение. Отличительной чертой детерминированных функций является отсутствие побочных эффектов: все изменения, вносимые программой, отражаются в списке параметров.
Пример детерминированной функции, которая представляет собой простую инкапсуляцию substr :
Если при вызове функции передаются, например, строка «abcdef» (string_in), число 3 (start_in) и 5 (end_in), то сколько бы раз вы ни вызывали функцию betwnStr с этим набором параметров, она всегда будет возвращать строку « cde ». Тогда почему бы Oracle не сохранить результат, связанный с конкретным набором аргументов? Ведь при следующем вызове функции с теми же параметрами можно получить результат, не выполняя код функции! Чтобы добиться подобного эффекта, включите предложение DETERMINISTIC в заголовок функции:
Решение об использовании сохраненной копии возвращаемого результата (если такая копия доступна) принимается оптимизатором запросов Oracle. Сохраненные копии могут браться из материализованного представления, функционального индекса или повторного вызова одной функции в команде SQL.
Deterministic-функции Oracle
Предлагаю Вашему вниманию перевод интересного на мой взгляд поста про неочевидную особенность Oracle.
Создаем таблицу FRUITS.
CREATE TABLE fruits (fruit_name varchar2(30));
Заполняем таблицу данными: 5 бананов, 7 яблок, 3 черники.
INSERT INTO fruits VALUES (‘banana’);
INSERT INTO fruits VALUES (‘banana’);
INSERT INTO fruits VALUES (‘banana’);
INSERT INTO fruits VALUES (‘banana’);
INSERT INTO fruits VALUES (‘banana’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘apple’);
INSERT INTO fruits VALUES (‘blueberry’);
INSERT INTO fruits VALUES (‘blueberry’);
INSERT INTO fruits VALUES (‘blueberry’);
Чтобы знать сколько раз запускалась наша функция, создаем сиквенс.
CREATE SEQUENCE seq START WITH 1;
Напишем функцию, которая возвращает цвет фрукта (входной параметр) и инкрементирует сиквенс, как индикатор своей работы.
CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
IS
l_num number;
BEGIN
SELECT seq.nextval INTO l_num FROM dual;
CASE p_fruit_name
WHEN ‘banana’ THEN RETURN ‘yellow’;
WHEN ‘apple’ THEN RETURN ‘green’;
WHEN ‘blueberry’ THEN RETURN ‘blue’;
END CASE;
END get_colour;
/
Узнаем цвет каждого фрукта в нашей таблице
SELECT get_colour(fruit_name) FROM fruits;
Вопрос: Что вернет этот запрос?
SELECT seq.nextval FROM dual;
Так, в таблице 15 записей, значит функция будет вызвана 15 раз. И поскольку мы выполняем seq.nextval, то можем ожидать, что результат будет 16. Давайте сбросим сиквенс для проведения еще одного эксперимента
DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 1;
И опять используем нашу функцию, чтобы получить цвет фруктов в таблице, но на этот раз обернем ее выражением SELECT FROM dual.
SELECT (SELECT get_colour(fruit_name) FROM dual)
FROM fruits;
Вопрос: что на этот раз вернет запрос?
SELECT seq.nextval FROM dual;
Можно предположить, что как и в предыдущий раз функция будет выполнена 15 раз и запрос опять вернет 16. Однако, это не так.
Мы обнаруживаем, что возвращается число 4, а это означает, что функция была вызвана всего 3 раза.
Что же произошло?
Прим. переводчика.
Для полноты картины следует упомянуть о возможности объявить эту функцию как DETERMINISTIC, тогда и в запросе SELECT get_colour(fruit_name) FROM fruits; она будет выполнена всего 3 раза.
Oracle deterministic что это
The deterministic option marks a function that returns predictable results and has no side effects.
Function-based indexes, virtual column definitions that use PL/SQL functions, and materialized views that have query-rewrite enabled require special function properties. The DETERMINISTIC clause asserts that a function has those properties.
The DETERMINISTIC clause may appear at most once in a function declaration or definition. A function so marked is called deterministic.
A deterministic function must return the same value on two distinct invocations if the arguments provided to the two invocations are the same.
A DETERMINISTIC function may not have side effects.
A DETERMINISTIC function may not raise an unhandled exception.
If a function with a DETERMINISTIC clause violates any of these semantic rules, the results of its invocation, its value, and the effect on its invoker are all undefined.
The DETERMINISTIC clause is an assertion that the function obeys the semantic rules. If the function does not, neither the compiler, SQL execution, or PL/SQL execution may diagnose the problem and wrong results may be silently produced.
Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects, because results might vary across invocations.
Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function.
When the DETERMINISTIC option appears, the compiler may use the mark to improve the performance of the execution of the function.
It is good programming practice to make functions that fall into these categories DETERMINISTIC :
Functions that MAP or ORDER methods of a SQL type
Functions that help determine whether or where a row appears in a result set
«CREATE FUNCTION Statement» for more information about standalone function syntax and semantics
Oracle Database Data Warehousing Guide for information about materialized views
Oracle Database SQL Language Reference for information about function-based indexes