[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大小.