Oracle随笔：oracle scalar subquery caching
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
本文转自朋友的真实案例分享。
Oracle随笔：oracle scalar subquery caching
这是oracle特有的特性，当对于子查询未展开(包括标量子查询)时，oracle会维护一个hash table的内存区域缓存，对于重复的关联列，并不需要重复的执行子查询。该特性在Cost Based Oracle Fundamental书中有介绍。
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, in 10g it seems to be
1,024.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 far more frequently than is strictly necessary.
内存大小由参数”_query_execution_cache_max_size“控制
SQL> @sp query_exec
-- show parameter by sp
-- show hidden parameter by sp
old 3: where x.indx=y.indx and ksppinm like '_%&p%'
new 3: where x.indx=y.indx and ksppinm like '_%query_exec%'
NAME VALUE DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_query_execution_cache_max_size 131072 max size of query execution cache
测试验证：
SQL Text
------------------------------
select /*+monitor*/ t.object_id,(select object_name from test.t1 where t1.object_type=t.object_type and rownum=1) from test.t
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (286:55319)
SQL ID : 929f4hgjd1sqc
SQL Execution ID : 16777216
Execution Started : 12/20/2024 22:09:14
First Refresh Time : 12/20/2024 22:09:14
Last Refresh Time : 12/20/2024 22:09:24
Duration : 10s
Module/Action : sqlplus@db (TNS V1-V3)/-
Service : vastdata
Program : sqlplus@db (TNS V1-V3)
Fetch Calls : 4885
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.73 | 0.36 | 0.26 | 0.11 | 4885 | 62983 | 28 | 12MB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=962711881)
=========================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=========================================================================================================================================
| 0 | SELECT STATEMENT | | | | 11 | +0 | 130 | 130 | | | | |
| 1 | COUNT STOPKEY | | | | 11 | +0 | 130 | 130 | | | | |
| 2 | TABLE ACCESS FULL | T1 | 2 | 3 | 11 | +0 | 130 | 130 | | | | |
| 3 | TABLE ACCESS FULL | T | 73257 | 440 | 11 | +0 | 1 | 73257 | 28 | 12MB | | |
=========================================================================================================================================
可以看到外表查询返回了73257条记录，但是标量子查询只执行了130次。这其实与t表关联列object_type的distinct值有关系
SQL> select count(distinct object_type) from test.t;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
47
可以看到distinct值为47，那么应该只执行47次才对，为什么执行了130次呢？那是因为”_query_execution_cache_max_size“不够大。
SQL> alter session set "_query_execution_cache_max_size"=1310720;
Session altered.
SQL Text
------------------------------
select /*+monitor test1*/ t.object_id,(select object_name from test.t1 where t1.object_type=t.object_type and rownum=1) from test.t
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (286:55319)
SQL ID : 9m73h1h434gcy
SQL Execution ID : 16777216
Execution Started : 12/20/2024 22:16:08
First Refresh Time : 12/20/2024 22:16:08
Last Refresh Time : 12/20/2024 22:16:21
Duration : 13s
Module/Action : sqlplus@db (TNS V1-V3)/-
Service : vastdata
Program : sqlplus@db (TNS V1-V3)
Fetch Calls : 4885
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.23 | 0.16 | 0.07 | 4885 | 20496 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=962711881)
==========================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==========================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 47 | 47 | | |
| 1 | COUNT STOPKEY | | | | 12 | +0 | 47 | 47 | | |
| 2 | TABLE ACCESS FULL | T1 | 2 | 3 | 14 | +0 | 47 | 47 | | |
| 3 | TABLE ACCESS FULL | T | 73257 | 440 | 14 | +0 | 1 | 73257 | | |
==========================================================================================================================
增大缓存大小之后，可以看到标量子查询只执行了外表关联列的distinct值次数。该特性对于子查询未展开走filter的情况同样适用。并且这个特性是国产数据库普遍不支持的特性，确实还需要向oracle多多借鉴。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号