[20230526]RESULT_CACHE提示选项.txt
--//一般如果查询信息很少变化，可以通过提示缓存结果，这样可以一定程度减少latch，逻辑读等等资源的使用。
--//实际上RESULT_CACHE提示还支持一些选项shelflife，snapshot。
--//测试参考链接：http://www.dbi-services.com/index.php/blog/entry/resultcache-hint-expiration-options
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
2.例子建立：
SCOTT@test01p> create table DEMO as select rownum id from xmltable('1 to 100000');
Table created.
--//分析略。
SCOTT@test01p> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
3.测试：
SCOTT@test01p> set autotrace on
SCOTT@test01p> select /*+ result_cache */ count(*) from DEMO;
COUNT(*)
--------
100000
Execution Plan
---------------------------
Plan hash value: 2180342005
-----------------------------------------------------------------------------
|Id|Operation |Name |Rows|Cost(%CPU)|Time |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1 | 47 (3)|00:00:01|
| 1| RESULT CACHE |1s07f2h70ga9484jsgc1s50zjr| | | |
| 2| SORT AGGREGATE | | 1 | | |
| 3| TABLE ACCESS FULL|DEMO |100K| 47 (3)|00:00:01|
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.DEMO); attributes=(single-row); name="select /*+ result_cache */ count(*) from DEMO"
Statistics
-------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--//这是第2次运行的结果。
SCOTT@test01p> set autotrace off
SCOTT@test01p> select * from v$result_cache_dependency;
RESULT_ID DEPEND_ID OBJECT_NO CON_ID
--------- --------- --------- ------
243 242 30020 3
SCOTT@test01p> col name format a50
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects where id in (242,243) order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- --------------------------------------------- -------------------------- -------------
242 Dependency Published SCOTT.DEMO SCOTT.DEMO 0
243 Result Published select /*+ result_cache */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//the query result (id=243) depends on the table (id=242).
4.继续：
--//DML
SCOTT@test01p> delete from DEMO where null is not null;
0 rows deleted.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30020;
no rows selected
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects where id in (242,243) order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- --------------------------------------------- -------------------------- -------------
242 Dependency Published SCOTT.DEMO SCOTT.DEMO 1
243 Result Invalid select /*+ result_cache */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
SCOTT@test01p> set autotrace on
SCOTT@test01p> select /*+ result_cache */ count(*) from DEMO;
COUNT(*)
----------
100000
Execution Plan
---------------------------
Plan hash value: 2180342005
-----------------------------------------------------------------------------
|Id|Operation |Name |Rows|Cost(%CPU)|Time |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1 | 47 (3)|00:00:01|
| 1| RESULT CACHE |1s07f2h70ga9484jsgc1s50zjr| | | |
| 2| SORT AGGREGATE | | 1 | | |
| 3| TABLE ACCESS FULL|DEMO |100K| 47 (3)|00:00:01|
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.DEMO); attributes=(single-row); name="select /*+ result_cache */ count(*) from DEMO"
Statistics
----------------------------------------------------------
51 recursive calls
4 db block gets
197 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
--//出现逻辑读。
SCOTT@test01p> set autotrace off
SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30020;
RESULT_ID DEPEND_ID OBJECT_NO CON_ID
--------- --------- --------- ------
250 242 30020 3
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects where id in (242,243,250) order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- -------------------------------------------------- -------------------------- -------------
242 Dependency Published SCOTT.DEMO SCOTT.DEMO 1
243 Result Invalid select /*+ result_cache */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
250 Result Published select /*+ result_cache */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//ID=243的结果集已经无效。可以看出结果集合涉及到的表有DML操作,status变成Invalid.
5.测试RESULT_CACHE(SHELFLIFE=seconds):
SCOTT@test01p> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
SCOTT@test01p> set autotrace on
SCOTT@test01p> select /*+ result_cache(shelflife=10) */ count(*) from DEMO;
COUNT(*)
--------
100000
Execution Plan
---------------------------
Plan hash value: 2180342005
-----------------------------------------------------------------------------
|Id|Operation |Name |Rows|Cost(%CPU)|Time |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1 | 47 (3)|00:00:01|
| 1| RESULT CACHE |1s07f2h70ga9484jsgc1s50zjr| | | |
| 2| SORT AGGREGATE | | 1 | | |
| 3| TABLE ACCESS FULL|DEMO |100K| 47 (3)|00:00:01|
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.DEMO); attributes=(single-row, shelflife=10); name="select /*+ result_cache(shelflife=10) */ count(*) from DEMO"
Statistics
---------------------------------------------
1 recursive calls
4 db block gets
165 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--//we see the 'shelflife' attribute but the dependency is the same as without the option:
SCOTT@test01p> set autotrace off
SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30020;
RESULT_ID DEPEND_ID OBJECT_NO CON_ID
--------- --------- --------- ------
225 224 30020 3
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects where id in (224,225) order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- ------------------------------------------------------------ -------------------------- -------------
224 Dependency Published SCOTT.DEMO SCOTT.DEMO 0
225 Result Published select /*+ result_cache(shelflife=10) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects where id in (224,225) order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- ------------------------------------------------------------ -------------------------- -------------
224 Dependency Published SCOTT.DEMO SCOTT.DEMO 0
225 Result Expired select /*+ result_cache(shelflife=10) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects where id in (224,225) order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- ------------------------------------------------------------ -------------------------- -------------
224 Dependency Published SCOTT.DEMO SCOTT.DEMO 0
225 Result Invalid select /*+ result_cache(shelflife=10) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//注意id=225的status从Published -> Expired -> Invalid.按照作者介绍等10秒就失效了。你可以设置shelflife大一些测试。
--//当然这样情况DML会失效。
SCOTT@test01p> delete from DEMO where null is not null;
0 rows deleted.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30020;
no rows selected
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ---------- --------- ------------------------------------------------------------ -------------------------- -------------
224 Dependency Published SCOTT.DEMO SCOTT.DEMO 0
225 Result Invalid select /*+ result_cache(shelflife=10) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
227 Result Invalid select /*+ result_cache(shelflife=10) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//result_cache(shelflife=N),增加了失效时间的控制,当然发生DML变得无效.
6.测试RESULT_CACHE(SNAPSHOT=seconds):
SCOTT@test01p> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
SCOTT@test01p> select /*+ result_cache(snapshot=900) */ count(*) from DEMO;
COUNT(*)
--------
100000
SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30020;
no rows selected
--//注意没有依赖!!
SCOTT@test01p> select /*+ result_cache(snapshot=900) */ count(*) from DEMO;
COUNT(*)
--------
100000
SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30020;
no rows selected
SCOTT@test01p> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
--- ------ --------- ------------------------------------------------------------ -------------------------- -------------
224 Result Published select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//执行dml操作:
SCOTT@test01p> delete from DEMO where id=1;
1 row deleted.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select /*+ result_cache(snapshot=900) */ count(*) from DEMO;
COUNT(*)
----------
100000
--//注意看这样结果没有变化，还是100000.而实际上现在是100000-1.
SCOTT@test01p> select /*+ r11esult_cache(snapshot=900) */ count(*) from DEMO;
COUNT(*)
----------
99999
--//破坏提示,可以发现真实的结果是99999.
SCOTT@test01p> select CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
------------------- --- ------ --------- ------------------------------------------------------------ ------------------------------ -------------
2023-05-26 21:22:22 224 Result Published select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//900秒=15分钟,等一小会看看,时间有点长...
SCOTT@test01p> select sysdate,CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
SYSDATE CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
------------------- ------------------- --- ------ --------- ------------------------------------------------------------ -------------------------- -------------
2023-05-26 21:37:21 2023-05-26 21:22:22 224 Result Published select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
SCOTT@test01p> select sysdate,CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
SYSDATE CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
------------------- ------------------- --- ------ --------- ------------------------------------------------------------ -------------------------- -------------
2023-05-26 21:37:22 2023-05-26 21:22:22 224 Result Expired select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
SCOTT@test01p> select sysdate,CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
SYSDATE CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
------------------- ------------------- --- ------ --------- ------------------------------------------------------------ -------------------------- -------------
2023-05-26 21:37:23 2023-05-26 21:22:22 224 Result Invalid select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
--//注意status的变化。
SCOTT@test01p> select /*+ result_cache(snapshot=900) */ count(*) from DEMO;
COUNT(*)
--------
99999
SCOTT@test01p> select sysdate,CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects where name like '%snapshot%' order by id;
SYSDATE CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
------------------- ------------------- --- ------ --------- ------------------------------------------------------------ -------------------------- -------------
2023-05-26 21:40:06 2023-05-26 21:38:07 42 Result Published select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
2023-05-26 21:40:06 2023-05-26 21:22:22 224 Result Invalid select /*+ result_cache(snapshot=900) */ count(*) from DEMO 1s07f2h70ga9484jsgc1s50zjr 0
7.结论:
--//http://www.dbi-services.com/index.php/blog/entry/resultcache-hint-expiration-options
Conclusion
There are two reasons to invalidate a result cache: DML on dependency, or expiration after a number of seconds. And we
can use any combination of them with undocumented hints. Oracle itself uses them internally. SNAPSHOT is used by Active
Dynamic Sampling: stale result are acceptable for one hour. SHELFLIFE is used on dictionary views bases on X$ tables
where dependency cannot be tracked.
使结果缓存无效的原因有两个：依赖关系上的DML，或在数秒钟后过期。我们可以使用它们的任何组合。Oracle本身也在内部使用它们。
快照是可使用的主动动态采样：陈旧的结果是可以接受的一个小时。基于无法跟踪依赖关系的X$表的字典视图。
I hope it will get documented in future releases. There are some cases where we can accept stale results in order to get
better performance. We already do that with materialized views, so why not do it with result cache?
我希望它能在未来的版本中得到记录。在某些情况下，我们可以接受陈旧的结果，以获得更好的性能。我们已经对实例化视图这样做了，
那么为什么不使用结果缓存呢？