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
 

赞
(0)
打赏
微信扫一扫

相关文章:

  • oracle自动统计信息时间的修改过程记录

    今天是2022年1月7日今天值夜班，同事让给优化一个sql，优化完成后，顺便看了下新系统的统计信息情况，发现在晚上做数据采集的时间，系统资源增加，发现是统计信息… [阅读全文]

  • 解决plsql因事务未提交造成的锁表问题

    1、执行以下语句可查询被锁的表 select b.owner,b.object_name,a.session_id,a.locked_mode from v… [阅读全文]

  • Oracle进阶DECODE函数使用详解

    decode含义decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)这个是decode的表达式，具体的含义解释为：if 条件=值1 then　　　　ret…

    2021年12月30日 • 数据库

  • oracle中commit之后进行数据回滚的方法

    commit之后第一种：记住大概的时间，获取前大概时间的数据。select * from test as of timestamp to_timestamp(‘… [阅读全文]

  • PLSQL Developer13.0.4最新注册码和使用教程详解

    01. plsql developer简介pl/sql developer 13是一个集成开发环境，专门用于开发 oracle 数据库的存储程序单元。 随着时间的推移，我们已经看到…

    2021年12月29日 • 数据库

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：

最近更新的文章
  • oracle数据库迁移方案对比（数据库迁移的两种方法）
  • oracle数据库配置文件在哪里（oracle配置数据库连接）
  • oracle删除超过N天数据脚本的方法
  • oracle导出表数据语句（oracle导出表结构语句）
  • oracle重置序列从0开始递增1
  • Oracle 触发器trigger使用案例
  • Oracle同步数据到kafka的方法
  • Oracle Session每日统计功能实现
  • Oracle 19c RAC 手工建库的搭建过程
  • Oracle中update和select 关联操作

推荐阅读

大家感兴趣的文章
  • 1 Oracle 19.3 dataguard for Redhat 7.6
  • 2 遇到connection to server failed, probable Oracle Net admin error问题的解决办法
  • 3 Oracle 12c RAC 静默安装文档
  • 4 Oracle Database 12c SQL面试题：WHERE和HAVING条件的题解
  • 5 Oracle 错误代码详解及解决方式–ORA
  • 6 Oracle AutoVue 安装与配置教程
  • 7 ORA-01438:值大于为此列指定的允许精度问题如何解决？
  • 8 ORACLE错误一览表 方便查询
  • 9 win10系统下Oracle报错17002The Network Adapter could not establish the connection的解决办法
  • 10 使用Oracle REPLACE函数替换查询结果中的指定字符（ReplacesubstringinOracle）的调试经验分享
赞 (0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐