Регистрация

[Oracle, SQL] Oracle: Deterministic functions, result_cache and operators

Ответить на тему
Автор Сообщение
news_bot ®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

Создавать темы news_bot ® написал(а)
10-Фев-2021 20:30
Цитировать

После перевода статьи 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]
Подробнее: http://orasql.org/2013/06/10/too-many-function-executions/3. Кэширование deterministic функций в SQL3.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 функций может быть вынесен из тела цикла при следующих условиях:
  • PLSQL_OPTIMIZE_LEVEL >= 2
  • Аргументы функции не должны меняться в теле цикла
  • Не должно быть неявных конвертаций аргументов (implicit conversions)
  • Не должно быть вызово не-"детерминированных" функций (кроме некоторых стандартных таких как to_date, to_char, nvl) и процедур
Пример: http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/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 :)
 ===========
 Источник:
habr.com
===========
Похожие новости: Теги для поиска: #_oracle, #_sql, #_oracle, #_sql, #_result_cache, #_deterministic_functions, #_pl/sql, #_oracle, #_sql
Профиль  ЛС 
Показать сообщения:     
Ответить на тему

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

Текущее время: 22-Ноя 16:56
Часовой пояс: UTC + 5