[20210201]19c计算标量子查询缓存数量.txt

[20210201]19c计算标量子查询缓存数量.txt --//测试19C下标量子查询缓存数量的数量，以前在10g,11g,12c下测试过。 1.环境： > @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. > @ hide  _query_execution_cache_max_size NAME                            DESCRIPTION                       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------------------- --------------------------------- ------------- ------------- ------------ ----- --------- _query_execution_cache_max_size max size of query execution cache TRUE          131072        131072       TRUE  DEFERRED -//该参数与11g一致,估计hash table size没有变化. 2.建立测试脚本： --//脚本scalar_sub_03.sql来源Jonathan Lewis的<基于成本的Oracle优化法则>，我做了少量改写，使用 --//dbms_application_info.set_client_info设置函数调用次数。 --//另外我想测试如果输入参数是字符，输出参数是number，会出现什么情况。主要想探究hash table保存一些什么。 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.测试1： exec  dbms_application_info.set_client_info(0); select count(distinct x) from  ( select  /*+ no_merge */ (select pack1.f_n(n1) from dual) x from t1) ; select 16384*2 - userenv('client_info') hash_table_size from dual; --//简单介绍原理，利用执行的sql语句循环执行2遍，从1到16384，形成一个hash table并且填满，注意执行次数一定要充足。 --//第1遍扫描形成hash table，如果冲突dbms_application_info.set_client_info(userenv('client_info')+1 );,但是该值对应的返 --//回不会进入hash table。 --//第2遍扫描，如果函数返回存在hash table ，不会真正调用函数。 --//如果函数返回不存在hash table(也就是出现冲突），一定会调用函数，并且 --//dbms_application_info.set_client_info(userenv('client_info')+1 ). --//这样2 * 16384 - userenv('client_info')  就是Hash table的大小。 --//注意第一遍扫描的执行次数(这里是16384)一定要填满hash table才行，否则计算错误。 > HASH_TABLE_SIZE ---------------            1024 --//HASH_TABLE_SIZE=1024 4.测试2: --//看看字符串的情况. exec  dbms_application_info.set_client_info(0); select count(distinct x) from ( select  /*+ no_merge */ (select pack1.f_v(v16) from dual) x from t1) ; select 16384*2 - userenv('client_info') hash_table_size from dual; > HASH_TABLE_SIZE ---------------              32 --//HASH_TABLE_SIZE=32             --//继续测试使用v32的情况: exec  dbms_application_info.set_client_info(0); select count(distinct x) from ( select  /*+ no_merge */ (select pack1.f_v(v32) from dual) x from t1) ; select 16384*2 - userenv('client_info') hash_table_size from dual; > select 16384*2 - userenv('client_info') hash_table_size from dual; HASH_TABLE_SIZE ---------------              32 --//HASH_TABLE_SIZE=32             --//函数f_v的返回没有长度限制的字符串,相当于4000个字符.导致两个测试hash table szie＝32。 --//限制字符的返回量仅仅80个字符看看. exec dbms_application_info.set_client_info(0); select count(distinct x) from ( select  /*+ no_merge */ (select substr(pack1.f_v(v32),1,80) from dual) x from t1) ; select 16384*2 - userenv('client_info') hash_table_size from dual; > HASH_TABLE_SIZE ---------------             512 --//改用f_vn函数看看,这个以前没有测试: exec  dbms_application_info.set_client_info(0); select count(distinct x) from ( select  /*+ no_merge */ (select pack1.f_vn(v32) from dual) x from t1) ; select 16384*2 - userenv('client_info') hash_table_size from dual; > HASH_TABLE_SIZE ---------------            1024 --//视乎这个hash_table仅仅保存结果.不然不会出现返回number,可以保存1024的情况. 5.也就是适当增加_query_execution_cache_max_size可以避免hash冲突,增加hash table size大小. SCOTT@test01p> alter session set "_query_execution_cache_max_size"=262144; Session altered. --//注意测试前增加样本的大小2*16384条记录，便于第一遍扫描填满hash table。 --//drop table t1 purge; create table t1 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*2 ; insert /*+ append */ into t1 select * from t1; commit; --//重复前面的测试。 exec  dbms_application_info.set_client_info(0); select count(distinct x) from  ( select  /*+ no_merge */ (select pack1.f_n(n1) from dual) x from t1) ; select 16384*4 - userenv('client_info') hash_table_size from dual; > HASH_TABLE_SIZE ---------------            2048 exec  dbms_application_info.set_client_info(0); select count(distinct x) from ( select  /*+ no_merge */ (select pack1.f_v(v16) from dual) x from t1) ; select 16384*4 - userenv('client_info') hash_table_size from dual; > HASH_TABLE_SIZE ---------------              64 6.总结: --//返回数字,19c缺省hash table size=1024. --//受隐含参数_query_execution_cache_max_size限制,19c缺省131072.字符串hash table size最大1024,最小32. --//增加_query_execution_cache_max_size可以避免hash冲突,增加hash table size大小.