💞 🚃 ☺️ Oracle: Fungsi deterministik, result_cache dan operator 🧒🏻 👩🏽‍💼 👩‍👧‍👧

Setelah menerjemahkan artikel Oracle: perbedaan antara deterministik dan result_cache dari Steven Feuerstein, saya ingin melengkapinya dengan detail yang sangat penting dari perangkat mereka. Saya memiliki serangkaian artikel tentang topik ini, tetapi di sini saya hanya ingin merangkum semuanya dan meninggalkan yang paling penting.

1. Query dalam fungsi PL / SQL tidak konsisten dengan query itu sendiri yang memanggilnya

Faktanya adalah bahwa permintaan di dalam fungsi "melihat" data (konsisten / konsisten) pada saat peluncurannya, dan bukan permintaan pemanggil. Dan tidak peduli bagaimana fungsinya itu sendiri didefinisikan, bahkan fungsi yang dideklarasikan dalam klausa WITH dari kueri akan menerima data yang tidak konsisten dengan cara yang sama. Artinya, jika data telah berubah selama interval antara dimulainya permintaan utama dan permintaan di dalam fungsi, maka fungsi tersebut akan mengembalikan data lain. Contohnya disini dan disini .

Dari sini, jelas bahwa salah satu fungsi tidak boleh berisi kueri di dalamnya, atau Anda perlu membuat operator SQL untuk itu, misalnya: operator f1_op untuk fungsi f1:

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

Selain itu, SQL Macro secara resmi muncul di Oracle 21: mereka masih cukup bermasalah, tetapi di masa depan mereka akan memungkinkan Anda untuk meninggalkan fungsi dalam banyak kasus, yang akan memberikan keuntungan kinerja karena pengurangan konteks switch dan akan menyelamatkan Anda dari konsistensi data masalah.

2. Jumlah panggilan fungsi bisa lebih karena transformasi permintaan

Pertimbangkan kueri sederhana seperti ini:

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.
*/

Menurut Anda, berapa kali fungsi xf akan dijalankan?

Jawabannya bergantung pada cara kerja pengoptimal: apakah subkueri akan digabungkan atau tidak, dan apakah bentang bawah filter akan terjadi: contoh rencana:

--------------------------------------------------
-- 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]

Keterangan lebih lanjut

3. Caching fungsi deterministik dalam SQL

3.1 Caching fungsi deterministik menggunakan tabel dan fungsi hash serta caching subkueri skalar

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.

Oracle: Fungsi deterministik, result_cache dan operator