oracle表注释与查询提示(result_cache_mode)的关系

1、result_cache_mode比表注释优先使用的情况。

create table test_Result_cache (id number) result_cache (mode default);

mode default这个值仅移除任何已经设置的表注释，并不允许包含这张表的查询结果进行缓存。

SQL> select t.table_name,t.result_cache from user_Tables t where t.table_name='TEST_RESULT_CACHE' ;

TABLE_NAME RESULT_CACHE
-------------------------------------------------------------------------------- ------------
TEST_RESULT_CACHE DEFAULT

上面创建表的语句与下面创建表的语句其作用是一样的。

create table test_Result_cache (id number)

下面查看一下相关结果集缓存参数的设置

SQL> show parameter result_cache;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 4608K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

这时需要对结果集进行缓存可以使用查询提示，如下

select /*+result_cache*/* from test_Result_cache

可以通过下面方式查看结果集是否成功缓存

SQL> select id,name,value from v$result_cache_statistics;

ID NAME VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------- 1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 32
4 Result Size Maximum (Blocks) 230
5 Create Count Success 5
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 0
13 Latch (Share) 0

Create Count Success：表示成功缓存结果集的数量。

2、result_cache_mode比表注释优先使用的情况二。

alter table test_result_cache result_cache(mode force);

这时确保result_cache_mode的值为MANUAL

SQL> show parameter result_cache_mode;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL

清空结果集缓存中的数据。

SQL> exec dbms_result_cache.Flush;

PL/SQL procedure successfully completed

SQL> select id,name,value from v$result_cache_statistics;

ID NAME VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 0
4 Result Size Maximum (Blocks) 230
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 0
12 Find Copy Count 0
13 Latch (Share) 0

通过下面的语句测试情况

SQL> select /*+no_result_cache*/* from test_Result_cache;

执行计划
----------------------------------------------------------
Plan hash value: 5006760

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_RESULT_CACHE | 2 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

从上面的查看结果中看出，查询并没有的使用结果集缓存中的内容。也可以直接查询相关的视图

SQL> select id,name,value from v$result_cache_statistics;

ID NAME VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 0
4 Result Size Maximum (Blocks) 230
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 0
12 Find Copy Count 0
13 Latch (Share) 0

其结果也是一样。

3、表注释优先于result_cache_mode的情况。

alter table test_result_cache result_cache(mode force);

这时可以查看一下result_cache_mode的值

SQL> show parameter result_cache_mode;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL

这时通过下面的查询会直接读取结果集缓存中的数据

SQL> select * from test_Result_cache;

执行计划
----------------------------------------------------------
Plan hash value: 5006760

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 5z4pvwymt41zz4hjnb3pwvcfuy | | | | |
| 2 | TABLE ACCESS FULL| TEST_RESULT_CACHE | 2 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(DESIGNER.TEST_RESULT_CACHE); name="select * from test_Result_cache"

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

也可以直接查看缓存结果集的数量

SQL> select id,name,value from v$result_cache_statistics;

ID NAME VALUE
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 4608
3 Block Count Current 32
4 Result Size Maximum (Blocks) 230
5 Create Count Success 6
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 1
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
12 Find Copy Count 0
13 Latch (Share) 0