Depois de traduzir o artigo da Oracle: a diferença entre deterministic e result_cache de Steven Feuerstein, gostaria de complementá-lo com detalhes realmente importantes de seu dispositivo. Tenho uma série de artigos sobre esses temas, mas aqui gostaria apenas de resumir tudo e deixar o mais importante.
1. Consultas em funções PL / SQL não são consistentes com a própria consulta que as chama
O fato é que as solicitações dentro de uma função "veem" os dados (consistentes / consistentes) no momento de seu lançamento, e não a solicitação do chamador. E não importa como a função em si seja definida, até mesmo a função declarada na cláusula WITH da consulta receberá dados inconsistentes da mesma maneira. Ou seja, se os dados foram alterados durante o intervalo entre o início da solicitação principal e a solicitação dentro da função, a função retornará outros dados. Exemplos aqui e aqui .
A partir disso, é óbvio que as funções não devem conter consultas internas ou você precisa criar um operador SQL para elas, por exemplo: o operador f1_op para a função f1:
CREATE OPERATOR f1_op
BINDING (INT)
RETURN INT
USING F1;
Além disso, as macros SQL aparecem oficialmente no Oracle 21: elas ainda têm muitos bugs, mas no futuro permitirão que você abandone funções em muitos casos, o que trará ganhos de desempenho devido às mudanças de contexto reduzidas e evitará a consistência de dados problemas.
2. O número de chamadas de função pode ser maior devido à transformação da solicitação
Considere uma consulta simples como esta:
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.
*/
Quantas vezes você acha que a função xf será executada?
A resposta depende de como o otimizador funciona: se a subconsulta será mesclada ou não e se ocorrerá um pushdown de filtro: exemplos de planos:
--------------------------------------------------
-- 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. Funções determinísticas de cache em SQL
3.1 Funções determinísticas de cache usam tabelas e funções hash, bem como cache de subconsulta escalar
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)
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.