[20210202]計算標量子查詢快取數量2.txt

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