原文链接 个人博客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所需要的信息呢？这些目前还都是未知数。
love wife love life —Roger的Oracle/MySQL数据恢复博客
Phone:18180207355 提供专业Oracle/MySQL数据恢复、性能优化、迁移升级、紧急救援等服务
