Oracle数据库中 Scalar-subquery 缓存和 DETERMINISTIC Function
前一段在Postgresql中的函数有Volate属性，Volatile 是每次执行，而另外Immutable 和Stable可以所有会话或单个查询中对于相同的值cache结果，减少不必要的执行。在oracle的DETERMINISTIC FUNCTIONS和Scalar-subquery 缓存同样的机制，如DETERMINISTIC FUNCTIONS的意义在于，如果 Oracle 可以确定当前对该函数的调用使用的输入与上一次调用相同，那么它可以使用上一次结果并避免调用。 Scalar-subquery 标量子查询缓存 (SSC) 和DETERMINISTIC FUNCTIONS确定性函数的缓存机制都是基于哈希函数的， 那cache大小有没有限制？
Scalar-subquery 缓存
如标量子查询循环《Cost-Based Oracle Fundamentals》书中的Scalar Subqueries章节 提到了该限制。
Oracle limits the size of the in-memory hash table (presumably to stop excessive memory consumption in unlucky cases).
In 8i and 9i the limit on the size of the hash table seems to be 256 entries, 10g Depends on the function’s input, output, and the value of parameter “_query_execution_cache_max_size”This means the performance of a subquery filter can be affected by the number of different driving values that exist, the order in which they appear in the pass through the driving table,and the actual values. If the hash table is simply too small, or you have driving values that just happen to cause excessive collisions on the hash table, then you may execute the subquery farmore frequently than is strictly necessary.
you have two manual options for improving the performance. Try to reduce the total size of the input and output (concatenating strings, explicit substrings), or change the session setting for the _query_execution_cache_max_size parameter
哈希表的大小似乎由固定的内存限制控制，而不是哈希条目的绝对数量。
SQL> @pd "execution cache"
Show all parameters and session values from x$ksppi/x$ksppcv...
NUM N_HEX NAME VALUE DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
1929 789 _query_execution_cache_max_size 131072 max size of query execution cache
标量子查询 执行缓存
测试 oracle 11g
SQL> insert into test_ec1 select rownum,lpad('anbob',16,'x')||rownum from dual connect by rownum<=100000;
100000 rows created.
SQL> insert into test_ec2 select rownum,lpad('anbob',16,'x')||(mod(rownum,20)+1) from dual connect by rownum<=100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL> select count(distinct name) from test_ec2;
COUNT(DISTINCTNAME)
-------------------
20
select /*+monitor anbob2*/ id,(select name from test_ec1 e1 where e1.name=e2.name and rownum<=1) from test_ec2 e2; SQL> @xpi 3972fa8hkmq1s
eXplain with Profile: Running DBMS_SQLTUNE.REPORT_SQL_MONITOR for SQL_ID 3972fa8hkmq1s...
SQL Monitoring Report
SQL Text
------------------------------
select /*+monitor anbob1*/ id,(select name from test_ec1 e1 where e1.name=e2.name and rownum<=1) from test_ec2 e2
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
Session : SYS (14:43687)
SQL ID : 3972fa8hkmq1s
SQL Execution ID : 33554432
Execution Started : 12/28/2024 17:48:07
First Refresh Time : 12/28/2024 17:48:07
Last Refresh Time : 12/28/2024 17:48:16
Duration : 9s
Module/Action : sqlplus@11g-node2 (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@11g-node2 (TNS V1-V3)
Fetch Calls : 6668
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.47 | 0.09 | 0.38 | 6668 | 307K |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=862112804)
==============================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==============================================================================================================================
| 0 | SELECT STATEMENT | | | | 10 | +0 | 100K | 100K | | |
| 1 | COUNT STOPKEY | | | | 10 | +0 | 100K | 100K | | |
| 2 | TABLE ACCESS FULL | TEST_EC1 | 1 | 80 | 10 | +0 | 100K | 100K | | |
| 3 | TABLE ACCESS FULL | TEST_EC2 | 100K | 75 | 10 | +0 | 1 | 100K | | |
==============================================================================================================================
execs列执行了10000次。 增加_query_execution_cache_max_size。
SQL> alter session set "_query_execution_cache_max_size"=2097152; Session altered. SQL> @xpi 0bqj1wkv285w0 eXplain with Profile: Running DBMS_SQLTUNE.REPORT_SQL_MONITOR for SQL_ID 0bqj1wkv285w0... SQL Monitoring Report SQL Text ------------------------------ select /*+monitor anbob2*/ id,(select name from test_ec1 e1 where e1.name=e2.name and rownum<=1) from test_ec2 e2 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 2 Session : SYS (404:14029) SQL ID : 0bqj1wkv285w0 SQL Execution ID : 33554432 Execution Started : 12/28/2024 18:03:23 First Refresh Time : 12/28/2024 18:03:23 Last Refresh Time : 12/28/2024 18:03:26 Duration : 3s Module/Action : sqlplus@11g-node2 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@11g-node2 (TNS V1-V3) Fetch Calls : 1001 Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.06 | 0.01 | 0.05 | 1001 | 1449 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=862112804) ============================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ============================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 20 | 20 | | | | 1 | COUNT STOPKEY | | | | 1 | +0 | 20 | 20 | | | | 2 | TABLE ACCESS FULL | TEST_EC1 | 1 | 80 | 1 | +0 | 20 | 20 | | | | 3 | TABLE ACCESS FULL | TEST_EC2 | 100K | 75 | 4 | +0 | 1 | 100K | | | ==============================================================================================================================
Note:
在标量子查询中execs降到了20次(NDK distinct数).
DETERMINISTIC函数
DETERMINISTIC函数如果您为参数传递一组特定的参数，则无论您何时、何地或多久运行一次，您都将始终获得相同的结果。DETERMINISTIC 和 RESULT_CACHE 都会导致数据缓存，DETERMINISTIC 关键字导致的缓存范围较窄（仅您的会话）和较短的生命周期（缓存发生在执行函数的 SQL 语句的持续时间内）。因此，整体性能影响可能不会太大。RESULT_CACHE 功能缓存的数据对数据库实例的所有用户都可用。并且该数据将保持缓存状态，直到缓存失效或刷新。它在提高应用程序性能方面具有更大的潜力 – 但它也带来了更大的负面影响的危险。
CREATE OR REPLACE FUNCTION f_get_id(pname VARCHAR2)
return int
DETERMINISTIC
as
l_id int;
BEGIN
SELECT id
INTO l_id
FROM test_ec1 e1
WHERE e1.name = pname and rownum=1;
RETURN l_id;
END;
/
SQL> @46on 4
select /*+ anbob6*/ id,f_get_id(e2.name) from test_ec2 e2
SQL> @t
SQL ID: 27w9jwwa7qnqj Plan Hash: 441612769
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 2.64 3.07 0 0 0 0
Fetch 100000 0.57 0.66 0 300000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200001 3.21 3.73 0 300000 0 100000
单看execute没有减少， 换个方法，在函数中调用put_line.
create or replace function is_number
(p_value in varchar2)
return char
DETERMINISTIC
is
p_num number;
begin
dbms_output.put_line('is_number ('||p_value||') called');
p_num := to_number(p_value);
return 'Y';
exception
when value_error then
return 'N';
end;
/
select is_number(name) from test_ec2;
SQL> alter session set "_query_execution_cache_max_size"=20971520;
Session altered.
Note：
也并不是想象中打印20行。 为了更好观察，创建个小表,行数逐渐增加，如50， 100,200，300行。
create table testv(dep int); truncate table testv; insert into testv select mod(rownum,5) from dual connect by rownum<= xxxxxxx; --更改 set serveroutput on SQL> select /*+ anbob8*/ is_number(e2.dep) from testv e2; IS_NUMBER(E2.DEP) ------------------------------------------------------------------------------------------------------------------------------------- Y Y ... 60 rows selected. is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called SQL> select /*+ anbob8*/ is_number(e2.dep) from testv e2; IS_NUMBER(E2.DEP) ------------------------------------------------------------------------------------------------------------------------------------- Y Y Y ... 100 rows selected. is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called SQL> select /*+ anbob8*/ is_number(e2.dep) from testv e2; ... 200 rows selected. is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called SQL> select /*+ anbob8*/ is_number(e2.dep) from testv e2; ... 300 rows selected. is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called is_number (2) called is_number (3) called is_number (4) called is_number (0) called is_number (1) called
Note:
哈希冲突都取决于单个参数“_query_execution_cache_max_size”，但DETERMINISTIC Function似乎受“_query_execution_cache_max_size”影响并不大。
确实性函数缓存与arraysize 大小
select /*+ anbob10*/ is_number(e2.dep) from testv e2; insert into testv select mod(rownum,3) from dual connect by rownum<=300; SQL> set arraysize 300 SQL> r 1* select /*+ anbob10*/ is_number(e2.dep) from testv e2 300 rows selected. is_number (1) called is_number (2) called is_number (0) called is_number (1) called SQL> set arraysize 200 300 rows selected. is_number (1) called is_number (2) called is_number (0) called is_number (1) called is_number (1) called is_number (2) called is_number (0) called SQL> set arraysize 100 is_number (1) called is_number (2) called is_number (0) called is_number (1) called is_number (0) called is_number (1) called is_number (2) called is_number (1) called is_number (2) called is_number (0) called
Note:
注意确定性缓存取决于获取大小（arraysize），从上面改变arraysize大小，可以改变调用中缓存的次数，而 Scalar-subquery 没有这个限制。
Summary:
_query_execution_cache_max_size 的增加， Scalar-subquery cache影响较大， 可以减少不必要的执行；
arraysize的增加，对deterministic影响较大， 可以减少不必要的执行；
— enjoy —
微信扫一扫，打赏作者吧～上一篇：
目前这篇文章还没有评论(Rss)