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

来源：这里教程网时间：2026-03-03 18:51:20 作者：

[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