ORACLE中Scalar subquery Caching的hash table大小测试浅析
前阵子总结了这篇“”博客，里面介绍了标量子查询缓存（scalar subquery caching），如果使用标量子查询缓存，ORACLE会将子查询结果缓存在哈希表中，如果后续的记录出现同样的值，优化器通过缓存在哈希表中的值，判断重复值不用重复调用函数，直接使用上次计算结果即可。从而减少调用函数次数，从而达到优化性能的效果。另外在ORACLE 10和11中， 哈希表只包含了255个Buckets，也就是说它能存储255个不同值，如果超过这个范围，就会出现散列冲突。 更多详细新可以参考我那篇博客
当然，哈希表只包含了255个Buckets是怎么来的呢？这个是Tom大神推算而来，我也没有测试过，后面网友lfree反馈他的测试结果跟这个结果不同。他反馈在ORACLE 10g下,测试结果实际上是512， ORACLE 11g为1024。由于前阵子比较忙，拖延症犯了，另外也跟他缺少沟通，不过有个志同道合的人讨论感兴趣的技术话题是一件幸事。最近有时间，看完了他的关于这个问题的多篇文章，学到了不少东西，也咨询了一下他一下具体细节，具体测试了一下，感觉他的测试方法有点复杂，部分结论过早给出定论了! 但是自己也没有一个合理的测试验证方法。遂啃了一下Tom大神的On Caching and Evangelizing SQL这篇雄文。在这里结合自己的理解，重新演示一下，下面测试环境为Oracle 11g，关于Hash Table,估计有些人会比较懵，借用Tom大神的述说：
You cannot 'see' the hash table anywhere, it is an internal data structure that lives in your session memory for the duration of the query. Once the query is finished - it goes away.
It is a cache associated with your query - nothing more, nothing less.
You can "see" it in action by measuring how many times your function is called, for example:
首先，创建这个自定义函数，这个函数是用来验证哈希表大小的关键所在（确实是一个构造很巧妙，而且又简单的函数。大神真不是盖的）。如果对函数dbms_application_info.set_client_info不了解的，自行搜索、学习这个知识点！
create or replace function f( x in varchar2 ) return number
as
begin
dbms_application_info.set_client_info(userenv('client_info')+1 );
return length(x);
end
然后创建测试表，插入测试数据。然后就可以开始我们的测试，
CREATE TABLE TEST(ID NUMBER);
INSERT INTO TEST
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;
COMMIT;
准备好上述测试环境，我们就可以用下面脚本来测试、验证标量函数被调用了多少次（注意下面这段脚本会被多次使用，下面测试部分会多次使用，后续可能直接称呼其为test.sql,而不会每次贴出这段脚本）
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
dbms_application_info.set_client_info(0);
end;
/
select id,(select f(id) from dual) as client_info from test;
select dbms_utility.get_cpu_time- :cpu cpu_hsecs,
userenv('client_info')
from dual;
我们可以看到测试结果userenv('client_info')的值为3， 这意味着标量函数被递归调用了3次（如果不理解的话，多补一下基础知识）
如果你对这种方式存在质疑的话，也可以使用10046 trace找到SQL的真实执行计划。具体SQL如下所
alter session set events '10046 trace name context forever,level 12';
select id,(select f(id) from dual) as client_info from test;
alter session set events '10046 trace name context off';
SELECT T.value
|| '/'
|| Lower(Rtrim(I.INSTANCE, Chr(0)))
|| '_ora_'
|| P.spid
|| '.trc' TRACE_FILE_NAME
FROM (SELECT P.spid
FROM v$mystat M,
v$session S,
v$process P
WHERE M.statistic# = 1
AND S.sid = M.sid
AND P.addr = S.paddr) P,
(SELECT T.INSTANCE
FROM v$thread T,
v$parameter V
WHERE V.name = 'thread'
AND ( V.value = 0
OR T.thread# = To_number(V.value) )) I,
(SELECT value
FROM v$parameter
WHERE name = 'user_dump_dest') T;
找到测试生成的trace文件，格式化后，如下截图所示，FAST DUAL表示执行子查询的次数，也就是递归调用次数。
[oracle@DB-Server trace]$ tkprof gsp_ora_11336.trc klb_out.txt
删除这个表，然后我们构造一个拥有从1到255的新表，然后执行test.sql，测试看看标量函数会调用多少次，如下所示：
SQL> drop table test purge;
Table dropped.
SQL> create table test as select rownum id from dual connect by level<=255;
Table created.
如下所示，可以看到当前情况下，标量函数执行了255次
然后插入1、2、 3 三个值，我们再执行一下test.sql，看看优化器是否使用哈希表中缓存的记录，减少函数调用次数。如下所示，函数还是只调用了255次。
INSERT INTO TEST
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;
COMMIT;
然后我们清空表TEST中的数据，然后使用下面脚本构造相关数据后， 执行test.sql继续我们的测试。
SQL> TRUNCATE TABLE TEST;
Table truncated.
SQL> DECLARE RowIndex NUMBER;
2 BEGIN
3 RowIndex :=1;
4 WHILE RowIndex <= 255 LOOP
5 INSERT INTO TEST
6 SELECT RowIndex FROM DUAL;
7
8 RowIndex := RowIndex +1;
9 END LOOP;
10 COMMIT;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> DECLARE RowIndex NUMBER;
2 BEGIN
3 RowIndex :=1;
4 WHILE RowIndex <= 255 LOOP
5 INSERT INTO TEST
6 SELECT RowIndex FROM DUAL;
7
8 RowIndex := RowIndex +1;
9 END LOOP;
10 COMMIT;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
其实这里出现这个问题，是因为1-255中，有些数因为HASH冲突，导致无法缓存到哈希表中，我们来验证测试一下，如下所示，9和16出现HASH冲突（为什么会出现HASH冲突，这个不清楚，因为我们不清楚它的HASH算法），由于9和16出现HASH 冲突，从而导致16无法缓存到哈希表，从而导致两条16的记录调用了两次，所以标量函数被调用了3次。但是如果出现冲突的记录，两次重复出现，那么它会重用上一次的调用函数的结果。如下测试所示：
我们继续往表TEST里面插入一条ID=16的记录， 我们开始测试
SQL> INSERT INTO TEST VALUES(16);
1 row created.
SQL> COMMIT;
SQL> select id,(select f(id) from dual) from test where id in (9,16);
ID (SELECTF(ID)FROMDUAL)
---------- ---------------------
9 9
16 16
9 9
16 16
16 16
SQL> select dbms_utility.get_cpu_time- :cpu cpu_hsecs, userenv('client_info') from dual;
CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
1 3
如上所示，自定义函数调用的次数还是3， 按照推理：ID=9的记录调用一次自定义函数，然后ID=16的记录出现HASH冲突，调用一次自定义函数，然后到记录ID=9，发现可以从内存中的哈希表取值，跳过调用自定义函数，接着到ID=16,由于哈希冲突，哈希表没有缓存相关记录，那么还会调用一次自定义函数，再接下来ID=16的记录，由于两次重复出现，那么它会重用上一次的调用函数的结果。所以调用次数为3
如果我们接下来继续插入两条记录，一条为9，一条为16，那么调用自定义函数的次数就会变为4，如下所示：
SQL> insert into test values(9);
1 row created.
SQL> insert into test values(16);
1 row created.
SQL> commit;
Commit complete.
SQL> variable cpu number;
SQL> begin
2 :cpu := dbms_utility.get_cpu_time;
3 dbms_application_info.set_client_info(0);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> select id,(select f(id) from dual) from test where id in(9,16);
ID (SELECTF(ID)FROMDUAL)
---------- ---------------------
9 9
16 16
9 9
16 16
16 16
9 9
16 16
7 rows selected.
SQL> SQL> select dbms_utility.get_cpu_time- :cpu cpu_hsecs, userenv('client_info') from dual;
CPU_HSECS USERENV('CLIENT_INFO')
---------- ----------------------------------------------------------------
1 4
相关文章:
-
-
最近需要做个小小的验证实验，需要安装MySQL，网上一搜发现教程繁多，bug也多，所以直接把官网的流程翻译过来，注意是压缩文件，不是安装版的，解压直... [阅读全文]
-
在网上找的一个办法 ... [阅读全文]
-
1.约束 说明：SQL约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止，约束可以在建表是规定，也可以在建表后规定，通过ALT... [阅读全文]
-
我个人以为mvvm框架里面最重要的一点就是VM这部分，它要与Model层建立联系，将Model层转换成可以被View层识别的数据结构；其次也要同Vi... [阅读全文]
-
ZooKeeper: 分布式协调服务 ZooKeeper是一个开源的为分布式应用提供分布式协调的服务。它公开了一组简单的原语，分布式应用程序可以基于... [阅读全文] -
版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。
上一篇： 浅谈 SpringMVC入门
下一篇： 详解Sed命令的用法与正则表达式元字符
发表评论