[20210202]计算标量子查询缓存数量2.txt

来源：这里教程网时间：2026-03-03 16:24:36 作者：

[20210202]计算标量子查询缓存数量2.txt --//昨天做了计算标量子查询缓存数量的测试,实际上还有1个简单的方法测试,就是看执行计划, --//唯一的方式就是打开statistics_level = all,或者设置提示gather_plan_statistics. --//通过例子说明: 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: create table t1 tablespace users as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 3000 ) select /*+ ordered use_nl(v2) */ rownum n1, lpad(rownum,16,'0') v16, lpad(rownum,32,'0') v32 from generator v1, generator v2 where rownum <= 16384 ; insert /*+ append */ into t1 select * from t1; commit; begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 1' ); end; / create or replace package pack1 as g_ct number(10) := 0; function f_n(i in number) return number; function f_v(i in varchar2) return varchar2; function f_vn(i in varchar2) return number; end; / create or replace package body pack1 as function f_n(i in number) return number is begin dbms_application_info.set_client_info(userenv('client_info')+1 ); return i; end; function f_v(i in varchar2) return varchar2 is begin dbms_application_info.set_client_info(userenv('client_info')+1 ); return i; end ; function f_vn(i in varchar2) return number is begin dbms_application_info.set_client_info(userenv('client_info')+1 ); return length(i); end ; end; / 3.测试: SCOTT@book> select count(distinct x) from ( select /*+ gather_plan_statistics no_merge */ (select pack1.f_n(n1) from dual) x from t1) ; COUNT(DISTINCTX) ---------------- 16384 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID cvqr6u5a0yq15, child number 0 ------------------------------------- select count(distinct x) from ( select /*+ gather_plan_statistics no_merge */ (select pack1.f_n(n1) from dual) x from t1) Plan hash value: 2761048924 ------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 81 (100)| | 1 |00:00:00.31 | 275 | | | | | 1 | FAST DUAL | | 31744 | 1 | | 2 (0)| 00:00:01 | 31744 |00:00:00.01 | 0 | | | | | 2 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.31 | 275 | | | | | 3 | VIEW | VW_DAG_0 | 1 | 32768 | 416K| 81 (3)| 00:00:01 | 16384 |00:00:00.31 | 275 | | | | | 4 | HASH GROUP BY | | 1 | 32768 | 416K| 81 (3)| 00:00:01 | 16384 |00:00:00.31 | 275 | 3072K| 2891K| 1843K (0)| | 5 | VIEW | | 1 | 32768 | 416K| 79 (0)| 00:00:01 | 32768 |00:00:00.29 | 275 | | | | | 6 | TABLE ACCESS FULL| T1 | 1 | 32768 | 160K| 79 (0)| 00:00:01 | 32768 |00:00:00.01 | 275 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$3 / DUAL@SEL$3 2 - SEL$C33C846D 3 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 4 - SEL$5771D262 5 - SEL$2 / from$_subquery$_001@SEL$1 6 - SEL$2 / T1@SEL$2 29 rows selected. --//id=2,starts=31744,说明调用函数次数是31744. --//16384*2-31744 = 1024 --//hash table size=1024.