11g 新特性之–query result cache(3)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 11g 新特性之–query result cache(3)
前面2篇文章分别讲了query cache的使用以及探秘其内存结构等等，最后一篇将讲讲
11gR2中，query cache的特别之处，详见下面的实验。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 |
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( 2 OWNER VARCHAR2(30), 3 OBJECT_ID NUMBER, 4 OBJECT_NAME VARCHAR2(128) 5 ) 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> 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> 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> 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> 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的语法如下：
|
1 |
CREATE|ALTER TABLE [<schema>.]<table> ... [RESULT_CACHE (MODE {FORCE|DEFAULT})] |
当然，既然create table有了新的语法，那么必然同时也会增加alter table的语法了，请看测试。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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 同时在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 |
关于字段RESULT_CACHE其中有3个属性，分别为DEFAULT，FORCE和MANUAL，大家可以参考11.2的官方文档。
这里有点需要说明的是，必然当表结构或定义发变化了，那么query cache 缓存的信息都将被清除，如下例子。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
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 |
从上面的信息来看，我想已经完全可以说明问题了，如何有人说这还不能说明问题的话，那请看下面：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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> ! |
|
1 2 3 4 5 6 7 |
[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 ~]$ |
|
1 2 3 4 5 6 7 8 |
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 |
|
1 2 3 4 5 6 |
[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] |
下面我们修改表ht01的表结构，然后再次dump 看看结果如何。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
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> 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 |
|
1 2 3 4 5 6 7 8 |
[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] |
从上面可以看出多了2条信息，24BE19A0。 从上面的实验来看，我们可以推断出oracle这里应该是这样管理的，
那就是即使表结构定义发生改变了，那么原来cache的信息仍然存在query cache中，当然，当cache不够用了，
也是会被清除掉的，至于说oracle这里是如何去判断如何不去选择旧的cache信息，那么我就不得而知了。
如果谁研究的更为透彻，记得告诉我，谢谢！
到最后，大家可能会想query cache的工作原理是什么？sql的结果集缓存超过多少或者说在使用了该特性
的情况下，如何通过算法去检索client所需要的信息呢？这些目前还都是未知数。
One Response to “11g 新特性之–query result cache(3)”
Saved like a favourite, I truly like your weblog!
Leave a Reply
You must be logged in to post a comment.