原文链接 个人博客http://www.killdb.com/?p=219
前面2篇文章分别讲了query cache的使用以及探秘其内存结构等等，最后一篇将讲讲
11gR2中，query cache的特别之处，详见下面的实验。
SQL> show user
USER is "ROGER"
SQL> create table ht03 as select * from ht02 where rownum <10000;
Table created.
Elapsed: 00:00:03.51
SQL> desc ht03
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
SQL> create table ht04(OWNER VARCHAR2(30),OBJECT_ID NUMBER,OBJECT_NAME VARCHAR2(128))
2 RESULT_CACHE (MODE FORCE);
Table created.
Elapsed: 00:00:00.14
SQL> insert into /*+append */ ht04 select * from ht03;
9999 rows created.
Elapsed: 00:00:00.32
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL>
SQL> create index ht03_idx on ht03(object_id);
Index created.
Elapsed: 00:00:00.32
SQL> create index ht04_idx on ht04(object_id);
Index created.
Elapsed: 00:00:00.10
SQL> analyze table ht03 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:00.73
SQL> analyze table ht04 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:00.18
SQL>
SQL> set autot traceonly
SQL> set lines 160
SQL> select * from ht03 where object_id=999;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1330547204
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HT03 | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | HT03_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=999)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
570 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from ht04 where object_id=999;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2782040647
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 1wsv07hr29687c877123g0cumt | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT04 | 1 | 36 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HT04_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=999)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT04); attributes=(ordered); name="select * from ht04 where object_id=999"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
566 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_client_result_cache_bypass boolean FALSE
_result_cache_auto_execution_thresho integer 1
ld
_result_cache_auto_size_threshold integer 100
_result_cache_auto_time_distance integer 300
_result_cache_auto_time_threshold integer 1000
_result_cache_block_size integer 1024
_result_cache_global boolean TRUE
_result_cache_timeout integer 10
_xsolapi_sql_result_set_cache_size integer 32
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 960K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL>
SQL> select /*+ RESULT_CACHE */ * from ht03 where object_id=999;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1330547204
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | f42hd8bp1h26hbdqqs6bz47m3z | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT03 | 1 | 36 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HT03_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=999)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT03); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht03 where object_id=999"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
566 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从上面的实验，我们可以看出11gR2 比11gR1 多了其中一点就是这里，那就是在create table的时候，
我们可以指定是否对该表启用query cache特性，create table的语法如下：
CREATE|ALTER TABLE [<schema>.]<table> ... [RESULT_CACHE (MODE {FORCE|DEFAULT})]
当然，既然create table有的新的语法，那么必然同时也会增加alter table的语法了，请看测试。
SQL> set autot off
SQL> alter table ht03 RESULT_CACHE(mode force);
Table altered.
Elapsed: 00:00:00.33
SQL> set autot traceonly
SQL> select * from ht03 where object_id=999;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1330547204
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | f42hd8bp1h26hbdqqs6bz47m3z | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT03 | 1 | 36 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HT03_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=999)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT03); attributes=(ordered); name="select * from ht03 where object_id=999"
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
566 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
同时在11gR2中，oracle在dba_tables和all_tables中增加一个字段，RESULT_CACHE。
SQL> select owner,table_name,RESULT_CACHE from dba_tables where owner='ROGER';
OWNER TABLE_NAME RESULT_
------------------------------ ------------------------------ -------
ROGER HT04 FORCE
ROGER HT03 FORCE
ROGER HT02 DEFAULT
ROGER HT01 DEFAULT
Elapsed: 00:00:02.75
SQL>
关于字段RESULT_CACHE其中有3个属性，分别为DEFAULT，FORCE和MANUAL，大家可以参考11.2的官方文档。
这里有点需要说明的是，必然当表结构或定义发变化了，那么query cache 缓存的信息都将被清除，如下例子。
SQL> set autot traceonly
SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=100)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"
Statistics
----------------------------------------------------------
44 recursive calls
0 db block gets
10 consistent gets
2 physical reads
0 redo size
560 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> desc ht01
Name Null? Type
----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
SQL> alter table ht01 modify (owner VARCHAR2(40));
Table altered.
Elapsed: 00:00:00.22
SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=100)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
560 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=100)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
560 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=100;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | b500gqatyy0zq32az39dhnk3fb | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=100)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=100"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
560 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
从上面的信息来看，我想已经完全可以说明问题了，如何有人说这还不能说明问题的话，那请看下面：
SQL> conn /as sysdba
Connected.
SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
Elapsed: 00:00:04.38
SQL> @ gettrc.sql
TRACE_FILE_NAME
------------------------------------------------------------------------------------
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4106.trc
Elapsed: 00:00:00.82
SQL>
SQL> !
[oracle@roger ~]$ grep -i Result /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4106.trc
Chunk 24bdecac sz= 32816 freeable "Result Cache " ds=0x272758b4
Chunk 24be6cdc sz= 32816 freeable "Result Cache " ds=0x272758b4
Chunk 24beed0c sz= 32816 recreate "Result Cache " latch=(nil)
[oracle@roger ~]$
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump_addr 2 656890036;
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_6683.trc
SQL>
[root@roger ~]# grep -i ht01 /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_6683.trc
24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
[root@roger ~]#
下面我们修改表ht01的表结构，然后再次dump 看看结果如何。
SQL> set autot off
SQL> alter table ht01 modify (owner VARCHAR2(50));
Table altered.
Elapsed: 00:00:00.06
SQL> set autot traceonly
SQL> select /*+ RESULT_CACHE */ * from ht01 where object_id=101;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | br1m2nyfp7v9c5drfp1gn5xp92 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=101)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=101"
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
563 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2671155529
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | br1m2nyfp7v9c5drfp1gn5xp92 | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HT01 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_HT01_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=101)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(ROGER.HT01); attributes=(ordered); name="select /*+ RESULT_CACHE */ * from ht01 where object_id=101"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
563 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump_addr 2 656890036;
Statement processed.
SQL> oradebug tracefile_name
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_28227.trc
SQL>
[root@roger ~]# grep -i ht01 /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_28227.trc
24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE19A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE11A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE15A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
24BE19A0 6F726620 7468206D 77203130 65726568 [ from ht01 where]
[root@roger ~]#
从上面可以看出多了2条信息，24BE19A0。 从上面的实验来看，我们可以推断出oracle这里应该是这样管理的，
那就是即使表结构定义发生改变了，那么原来cache的信息仍然存在query cache中，当然，当cache不够用了，
也是会被清除掉的，至于说oracle这里是如何去判断如何不去选择旧的cache信息，那么我就不得而知了。
如果谁研究的更为透彻，记得告诉我，谢谢！
到最后，大家可能会想query cache的工作原理是什么？sql的结果集缓存超过多少或者说在使用了该特性
的情况下，如何通过算法去检索client所需要的信息呢？这些目前还都是未知数。