После перевода статьи Oracle: разница между deterministic и result_cache от Steven Feuerstein, хотелось бы дополнить ее действительно важными деталями их устройства. У меня есть серия статей на эти темы, но тут я хотел бы просто все резюмировать и оставить самое важное.

1. Запросы в PL/SQL функциях не консистентны самому запросу, их вызывающему

Дело в том, что запросы внутри функции "видят" данные (согласованы/консистентны) на момент их запуска, а не запроса их вызывающего. И не важно, как определена сама функция, даже функция продекларированная в WITH clause запроса, точно так же будет получать несогласованные данные. То есть, если данные успели измениться за промежуток между стартом основного запроса и запроса внутри функции, то функция будет возвращать другие данные. Примеры тут и тут.

Из этого, очевидно, что либо функции не должны содержать запросов внутри, либо надо создать SQL оператор для нее, пример: оператор f1_op для функции f1:

CREATE OPERATOR f1_op
   BINDING (INT) 
   RETURN INT
   USING F1;

Кроме того, в Oracle 21 официально появляются SQL Macros: они пока весьма глючны, но в будущем они позволят во многих случаях отказаться от функций, что даст как прирост производительности за счет уменьшения переключений контекста, так и избавит от проблем с консистентностью данных.

2. Количество вызовов функций может быть больше из-за трансформации запроса

Рассмотрим такой простой запрос:

select *
from (
     select xf(t10.id) a
     from t10 
     )
where a*a >= 25;
-- таблица t10:
/*
SQL> select id from t10;
 
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 
10 rows selected.
*/

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

Ответ зависит от того, как отработает оптимизатор: будет ли выполнен merge подзапроса или нет и произойдет ли filter pushdown: примеры планов:

--------------------------------------------------
-- Plan 1:
Plan hash value: 2919944937
--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |
|*  1 |  TABLE ACCESS FULL| T10  |     1 |     3 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

--------------------------------------------------
-- Plan 2:
Plan hash value: 2027387203
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |
|   1 |  VIEW              |      |     1 |    13 |
|*  2 |   TABLE ACCESS FULL| T10  |     1 |     3 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

---------------------------------------------------
-- Plan 3:
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |
|*  1 |  VIEW              |      |     1 |    13 |
|   2 |   TABLE ACCESS FULL| T10  |     1 |     3 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("A"*"A">=25)

Column Projection Information 
------------------------------
 
   1 - "A"[NUMBER,22]
   2 - "A"[NUMBER,22]

Подробнее

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 есть и тут приятная особенность: даже в случае превышения кол-ва неудачных попыток, хотя кэширование новых значений отключается, уже сохраненные старые результаты он все равно будет возвращать.

Подробнее тут:

http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/
http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/
http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-3/

Особенности совместного deterministic + result cache, operator + deterministic + result cache:

http://orasql.org/2014/03/31/deterministic-functions-result_cache-and-operators/

4. Кэширование deterministic функций в PL/SQL

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

1. PLSQL_OPTIMIZE_LEVEL >= 2

2. Аргументы функции не должны меняться в теле цикла

3. Не должно быть неявных конвертаций аргументов (implicit conversions)

4. Не должно быть вызово не-"детерминированных" функций (кроме некоторых стандартных таких как to_date, to_char, nvl) и процедур

Пример

5. Result cache

В отличие от SSC and Deterministic functions caching, которые хранят результаты в CGA, Result cache - это shared cache для всех сессий внутри инстанса (в shared pool), со всеми вытекающими отсюда плюсами и минусами. Отслеживание зависимостей происходит с помощью механизма Fine-grained dependency tracking c определенными нюансами (раз, два и три), защищено защелками (RC latches). Зависимости можно отследить с помощью v$result_cache_objects (type=dependency) и v$result_cache_dependency. Минимальной "гранулой" является сегмент (таблицы или секции), соответственно любое изменение или даже select for update c коммитом приведут к инвалидации результатов. Поэтому имеет смысл его использовать только на редко изменяемых таблицах. Использование же на часто изменяемых таблицах может поставить базу "колом".

Резюме резюме

Так как механизм Result Cache сам по себе достаточно тяжелый и гораздо медленнее, чем кэширование скалярных подзапросов или deterministic функций, желательно все такие функции объявлять и как deterministic, чтобы при многократном вызове с одинаковыми аргументами не было лишних достаточно тяжелых обращений к RC, а при потенциальной несогласованности данных лучше использовать собственные операторы. В целом же, надеемся на допиливание SQL Macro лет через 5-10.