After translating the Oracle article : the difference between deterministic and result_cache from Steven Feuerstein, I would like to supplement it with really important details of their device. I have a series of articles on these topics, but here I would just like to summarize everything and leave the most important thing.
1. Queries in PL / SQL functions are not consistent with the query itself that calls them
The fact is that requests inside a function "see" the data (consistent / consistent) at the time of their launch, and not the request of the caller. And no matter how the function itself is defined, even the function declared in the WITH clause of the query will receive inconsistent data in the same way. That is, if the data has changed during the interval between the start of the main request and the request inside the function, then the function will return other data. Examples here and here .
From this, it is obvious that either the functions should not contain queries inside, or you need to create an SQL operator for it, for example: the f1_op operator for the f1 function:
CREATE OPERATOR f1_op
BINDING (INT)
RETURN INT
USING F1;
In addition, SQL Macros officially appear in Oracle 21: they are still quite buggy, but in the future they will allow you to abandon functions in many cases, which will give both performance gains due to reduced context switches and will save you from data consistency problems.
2. The number of function calls can be more due to the transformation of the request
Consider a simple query like this:
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.
*/
How many times do you think the xf function will be executed?
The answer depends on how the optimizer works: whether the subquery will be merged or not, and whether a filter pushdown will happen: examples of plans:
--------------------------------------------------
-- 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. Caching deterministic functions in SQL
3.1 Caching deterministic functions uses hash tables and functions as well as 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)
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.