这是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“控制
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
测试验证：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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值有关系
|
1 2 3 4 5 |
SQL> select count(distinct object_type) from test.t; COUNT(DISTINCTOBJECT_TYPE) -------------------------- 47 |
可以看到distinct值为47，那么应该只执行47次才对，为什么执行了130次呢？那是因为”_query_execution_cache_max_size“不够大。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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多多借鉴。