Después de traducir el artículo de Oracle: la diferencia entre deterministic y result_cache de Steven Feuerstein, me gustaría complementarlo con detalles realmente importantes de su dispositivo. Tengo una serie de artículos sobre estos temas, pero aquí solo me gustaría resumir todo y dejar los más importantes.
1. Las consultas en las funciones PL / SQL no son consistentes con la consulta en sí que las llama
El hecho es que las solicitudes dentro de una función "ven" los datos (consistentes / consistentes) en el momento de su lanzamiento, y no la solicitud de la persona que llama. Y no importa cómo se defina la función en sí, incluso la función declarada en la cláusula WITH de la consulta recibirá datos inconsistentes de la misma manera. Es decir, si los datos han cambiado durante el intervalo entre el inicio de la solicitud principal y la solicitud dentro de la función, la función devolverá otros datos. Ejemplos aquí y aquí .
A partir de esto, es obvio que las funciones no deben contener consultas en su interior, o necesita crear un operador SQL para ello, por ejemplo: el operador f1_op para la función f1:
CREATE OPERATOR f1_op
BINDING (INT)
RETURN INT
USING F1;
Además, las macros SQL aparecen oficialmente en Oracle 21: todavía tienen bastantes errores, pero en el futuro le permitirán abandonar funciones en muchos casos, lo que proporcionará ganancias de rendimiento debido a cambios de contexto reducidos y le salvará de la consistencia de los datos problemas.
2. La cantidad de llamadas a funciones puede deberse más a la transformación de la solicitud
Considere una consulta simple 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.
*/
¿Cuántas veces cree que se ejecutará la función xf?
La respuesta depende de cómo funciona el optimizador: si la subconsulta se fusionará o no, y si se producirá un pushdown de filtro: ejemplos de planes:
--------------------------------------------------
-- 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. Almacenamiento en caché de funciones deterministas en SQL
3.1 El almacenamiento en caché de funciones deterministas utiliza tablas y funciones hash, así como el almacenamiento en caché de subconsultas escalares
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.