[20230705]提示result_cache与调用执行次数.txt

--//简单探究提示result_cache与调用执行次数.

1.环境：
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

--//以sys用户执行:
grant EXECUTE ON  dbms_lock to scott;
grant dba to scott;

CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)
   RETURN NUMBER
AS
BEGIN
   sys.DBMS_LOCK.sleep (seconds);
   RETURN seconds;
END;
/

--//exec dbms_result_cache.flush;

2.测试1:

SCOTT@test01p> set timing on
SCOTT@test01p> select /*+ result_cache */ sleep(1) from dual;
  SLEEP(1)
----------
         1
Elapsed: 00:00:02.12

--//从执行需要时间2.12秒看,至少调用sleep函数2次.
SCOTT@test01p> @  hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1138656018 5rnkp151xx0sk            0      33554      1388734953  43de8312  2023-07-05 21:36:37    16777216

SCOTT@test01p> @ tpt/sqlid  5rnkp151xx0sk %
Show SQL text, child cursors and execution stats for SQLID 5rnkp151xx0sk child %
HASH_VALUE PLAN_HASH_VALUE   CH# SQL_TEXT
---------- --------------- ----- ------------------------------------------------------------------------------------------------------------------------------------------------------
1138656018      1388734953     0 select /*+ result_cache */ sleep(1) from dual

Elapsed: 00:00:00.04
  CH#  PLAN_HASH     PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED ROWS_PER_FETCH CPU_SEC_EXEC ELA_SEC_EXEC LIOS_PER_EXEC PIOS_PER_EXEC TOTAL_CPU_SEC TOTAL_ELA_SEC TOTAL_IOWAIT_SEC TOTAL_LIOS TOTAL_PIOS      SORTS USERS_EXECUTING LAST_ACTIVE_TIME    PARENT_HANDLE    OBJECT_HANDLE
----- ---------- ---------- ---------- ---------- ---------- -------------- -------------- ------------ ------------ ------------- ------------- ------------- ------------- ---------------- ---------- ---------- ---------- --------------- ------------------- ---------------- ----------------
    0 1388734953          1          1          1          1              1              1         .047         .126           853             9          .047          .126          .070646        853          9          0               0 2023-07-05 21:36:39 000007FF1589B1A8 000007FF1589AC48
Elapsed: 00:00:00.02

--//从执行次数看语句仅仅执行1次,很明显在加入result_cache时,如果result cache没有命中的情况下,调用sleep函数2次.
--//再次执行:

SCOTT@test01p> select /*+ result_cache */ sleep(1) from dual;
  SLEEP(1)
----------
         1
Elapsed: 00:00:00.02
--//如果result cache没有命中的情况下,调用sleep函数0次.

SCOTT@test01p> select * from v$result_cache_objects where lower(name) like '%sleep%'
  2   @pr
==============================
ID                            : 252
TYPE                          : Dependency
STATUS                        : Published
BUCKET_NO                     : 4006
HASH                          : 1279713190
NAME                          : SCOTT.SLEEP
NAMESPACE                     :
CREATION_TIMESTAMP            : 2023-07-05 21:36:38
CREATOR_UID                   : 81
DEPEND_COUNT                  : 1
BLOCK_COUNT                   : 1
SCN                           : 15465384
COLUMN_COUNT                  : 0
PIN_COUNT                     : 0
SCAN_COUNT                    : 0
ROW_COUNT                     : 0
ROW_SIZE_MAX                  : 0
ROW_SIZE_MIN                  : 0
ROW_SIZE_AVG                  : 0
BUILD_TIME                    : 0
LRU_NUMBER                    : 0
OBJECT_NO                     : 22915
INVALIDATIONS                 : 0
SPACE_OVERHEAD                : 0
SPACE_UNUSED                  : 0
CACHE_ID                      : SCOTT.SLEEP
CACHE_KEY                     : SCOTT.SLEEP
CHECKSUM                      : 0
EDITION_ID                    : 0
DB_LINK                       : No
CON_ID                        : 3
==============================
ID                            : 225
TYPE                          : Result
STATUS                        : Published
BUCKET_NO                     : 549
HASH                          : 1367958053
NAME                          : select /*+ result_cache */ sleep(1) from dual
NAMESPACE                     : SQL
CREATION_TIMESTAMP            : 2023-07-05 21:36:38
CREATOR_UID                   : 81
DEPEND_COUNT                  : 1
BLOCK_COUNT                   : 1
SCN                           : 15465384
COLUMN_COUNT                  : 1
PIN_COUNT                     : 0
SCAN_COUNT                    : 1
ROW_COUNT                     : 1
ROW_SIZE_MAX                  : 5
ROW_SIZE_MIN                  : 5
ROW_SIZE_AVG                  : 5
BUILD_TIME                    : 100
LRU_NUMBER                    : 93
OBJECT_NO                     : 0
INVALIDATIONS                 : 0
SPACE_OVERHEAD                : 325
SPACE_UNUSED                  : 375
CACHE_ID                      : bvvy8dkvmhfgh2qr0t4vm48uzw
CACHE_KEY                     : g1w90h24jk80m8vu74drbxqadr
CHECKSUM                      : 2611632069
EDITION_ID                    : 133
DB_LINK                       : No
CON_ID                        : 3
PL/SQL procedure successfully completed.


3.测试2:
--//加入DETERMINISTIC呢?
CREATE OR REPLACE FUNCTION SCOTT.sleep1 (seconds IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
AS
BEGIN
   sys.DBMS_LOCK.sleep (seconds);
   RETURN seconds;
END;
/

SCOTT@test01p> select /*+ result_cache */ sleep1(1) from dual;
 SLEEP1(1)
----------
         1
Elapsed: 00:00:01.02

--//很明显这样sleep1在result cache没有命中的情况下，调用1次。这个有点像以前的标量子查询.

SCOTT@test01p> select /*+ result_cache */ sleep1(1) from dual;
 SLEEP1(1)
----------
         1
Elapsed: 00:00:00.00

4.测试3:
--//加入RESULT_CACHE呢?

CREATE OR REPLACE FUNCTION SCOTT.sleep2 (seconds IN NUMBER)
   RETURN NUMBER
   RESULT_CACHE
AS
BEGIN
   sys.DBMS_LOCK.sleep (seconds);
   RETURN seconds;
END;
/


SCOTT@test01p> select /*+ result_cache */ sleep2(1) from dual;
 SLEEP2(1)
----------
         1
Elapsed: 00:00:01.01

SCOTT@test01p> select /*+ result_cache */ sleep2(1) from dual;
 SLEEP2(1)
----------
         1
Elapsed: 00:00:00.00