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