在之前的文章中，我们详细了解了Oracle 11g的Result Cache特性和机理。本篇我们讨论一下使用Table Annotation来控制数据表Result Cache使用的功能。
10、Table Annotation与Result Cache
在之前的介绍中，无论是直接的设置参数Result_Cache_Mode，还是手工的在SQL语句中添加RESULT_CACHE的Hint，在实际中都有一些问题。
Oracle推荐的Result Cache应用场景，有两个前提：一个是从前端来的高频度SQL语句，而且语句结构相同。另一个是数据表只读Read Only特性强烈。通过参数Result_Cache_Mode，我们直接就对所有数据表采用了结果集合缓存，显然不满足条件，纯Read Only的数据库也是不常见的。
手工添加Result Cache Hint，的确是一种方便的手段。如果在测试阶段发现了这样的问题，的确可以手工修改代码，加上Hint语句。但是，如果系统已经上线投产，没有条件添加Hint，我们怎么办呢？
单独从调优的角度看，的确有很多选择，比如成熟的SQL Profile、SPM等。其实，我们是可以使用Table Annotation功能进行Result Cache设置。
Table Annotation是Oracle对数据表的一种属性设置，如同Storage一样，很多特性我们都可以设置在Table的层面上。
首先，我们构建数据表和环境信息。
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
当前result cache参数维持在默认Default值情况。
SQL> show parameter result_cache_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
数据表属性，我们都是可以通过dba_tables视图来看到。进入11g之后，这个视图中添加了一个result_cache数据列。
SQL> desc dba_tables;
Name Type Nullable Default Comments
------------------------- ------------ -------- ------- ------------------------------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the table
TABLE_NAME VARCHAR2(30) Name of the table
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace containing the table
（篇幅原因，有省略……）
RESULT_CACHE VARCHAR2(7) Y The result cache mode annotation for the table
默认情况下，数据表的Result Cache功能是关闭的，取值为Default。
SQL> select result_cache from dba_tables where wner='SCOTT' and table_name='T';
RESULT_CACHE
------------
DEFAULT
我们实验了一个SQL。
SQL> explain plan for select /*+demo*/count(*) from t where wner='SCOTT';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1232703844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | 6 | |
|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
14 rows selected
真正执行SQL之后，我们在shared pool中可以找到共享游标。
SQL> select sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+demo*/count(*) from t%';
SQL_ID VERSION_COUNT EXECUTIONS
------------- ------------- ----------
60d8dpwmpacv4 1 1
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '60d8dpwmpacv4',format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 60d8dpwmpacv4, child number 0
-------------------------------------
select /*+demo*/count(*) from t where wner='SCOTT'
Plan hash value: 1232703844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
44 rows selected
我们使用alter table命令就可以修改数据表的属性。
SQL> alter table t result_cache (mode force);
Table altered
SQL> select result_cache from dba_tables where wner='SCOTT' and table_name='T';
RESULT_CACHE
------------
FORCE
Result cache功能两个取值，一个是Default，一个Force。如果设置为force，表示开启了result cache功能。相同的SQL语句，我们检查一下优化器和shared pool情况。
--相同的SQL语句
SQL> explain plan for select /*+demo*/count(*) from t where wner='SCOTT';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1232703844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 6
| 1 | RESULT CACHE | a6h12b8a88gkk2as3cngj03t16 | | |
| 2 | SORT AGGREGATE | | 1 | 6 |
|* 3 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SCOTT')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(SCOTT.T); attributes=(single-row, ordered);
"
优化器已经发现了SQL使用Result Cache的情况，并且修改了执行计划。Shared Pool情况如下：
--执行了两次
SQL> select /*+demo*/count(*) from t where wner='SCOTT';
COUNT(*)
----------
19
SQL> select /*+demo*/count(*) from t where wner='SCOTT';
COUNT(*)
----------
19
--sql id相同，但是原有的执行计划已经被废止；
SQL> select sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+demo*/count(*) from t%';
SQL_ID VERSION_COUNT EXECUTIONS
------------- ------------- ----------
60d8dpwmpacv4 1 2
实际的缓存结果执行计划如下：
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '60d8dpwmpacv4',format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 60d8dpwmpacv4, child number 0
-------------------------------------
select /*+demo*/count(*) from t where wner='SCOTT'
Plan hash value: 1232703844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6
| 1 | RESULT CACHE | a6h12b8a88gkk2as3cngj03t16 | | |
| 2 | SORT AGGREGATE | | 1 | 6 |
|* 3 | INDEX RANGE SCAN| IDX_T_OWNER | 2205 | 13230 | 6
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SCOTT')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - COUNT(*)[22]
2 - (#keys=0) COUNT(*)[22]
Result Cache Information (identified by operation id):
------------------------------------------------------
1 -
51 rows selected
从cache pool中，我们的确可以看到对象。
SQL> select id, type, status, cache_id, name from v$result_cache_objects;
ID TYPE STATUS CACHE_ID NAME
---------- ---------- --------- -------------------- --------------------
0 Dependency Published SCOTT.T SCOTT.T
1 Result Published a6h12b8a88gkk2as3cng select /*+demo*/coun
j03t16 t(*) from t where ow
ner='SCOTT'
综上实验：我们没有修改SQL语句，只是通过数据库层面的修改，就开启了Result Cache功能。Table Annotation在这些场景下是非常实用的。
11、结论
最后，我们从系统架构和设计角度看看Result Cache功能。Result cache是从数据库层面上提供的一种结果集合缓存方法。所有的缓存都会有两个特点：一个是数据冗余，借助空间来换取时间和效率。另一个是数据尽可能的往前端“推动”，减少中间传输环节。
从这样的角度看，我们感觉Result Cache本质上也有“运维阶段弥补”的味道。如果我们是一个成熟的设计，这样的缓存应该是通过应用缓存等手段进行处理，而不是转化为高频度的SQL执行。所以，Result Cache的处理，其实更多的场景是后期运维人员的效果弥补工作。
在实际中，一定要注意Result Cache使用的两个前提：Read Only和高频度数据访问。同时，SQL结果集合数量尽量不要很多，因为会消耗shared pool空间。