原文链接 个人博客 http://www.killdb.com/?p=216
在11g 新特性之--query result cache 的第一篇文章中，我讲述该特性的使用以及相关的管理等等，
其中在最后提出了几个疑问，query cache结构如何？跟shared pool有何关系？
该特性真的是说的那么好吗？它适用于OLTP 系统吗？ 下面这篇文章将给出解答。
SQL> conn roger/roger Connected. SQL> create table ht02 as select owner,object_id,object_name from dba_objects;
Table created.
SQL> select count(*) from ht02;
COUNT(*) ---------- 71884
SQL> create index ht02_id_idx on ht02(object_id);
Index created.
SQL> SQL> select owner,count(*) from ht02 group by owner;
OWNER COUNT(*) ------------------------------ ---------- OWBSYS_AUDIT 12 MDSYS 1509 ROGER 4 PUBLIC 27696 OUTLN 9 CTXSYS 366 OLAPSYS 719 FLOWS_FILES 12 OWBSYS 2 SYSTEM 529 ORACLE_OCM 8 EXFSYS 310 APEX_030200 2406 SCOTT 6 DBSNMP 57 ORDSYS 2532 ORDPLUGINS 10 SYSMAN 3491 APPQOSSYS 3 XDB 842 ORDDATA 248 SYS 30789 WMSYS 316 SI_INFORMTN_SCHEMA 8 SQL> select count(*) from ht02 where mod(object_id,2)=0 and owner='SYS';
COUNT(*) ---------- 15428 SQL> select max(object_id) from ht02 where mod(object_id,2)=0 and owner='SYS';
MAX(OBJECT_ID) -------------- 73410
SQL>
---session 1 (delete) SQL> set timing on SQL> begin 2 for i in 1..100 loop 3 if mod(i,2)=0 then 4 delete from ht02 where object_id=i; end if; 5 6 end loop; 7 end; 8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19 SQL> begin 2 for i in 1..100 loop if mod(i,2)=0 then delete from ht02 where owner='SYS' and object_id=i; 3 4 5 end if; 6 end loop; 7 end; 8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05 SQL>
---session 2 SQL> show user USER is "ROGER" SQL> set autot traceonly SQL> set lines 150 SQL> select /*+ RESULT_CACHE */ owner,object_name 2 from ht02 where object_id=73400;
no rows selected
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 96 | 1 (0)| 00:00:01 | | 1 | RESULT CACHE | 183x3yt1jbbc42u69x2fv0kh7y | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 1 | 96 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=73400)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=73400"
Note ----- - dynamic sampling used for this statement (level=2)
Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 55 consistent gets 1 physical reads 0 redo size 350 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> SQL> SQL> /
no rows selected
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 96 | 1 (0)| 00:00:01 | | 1 | RESULT CACHE | 183x3yt1jbbc42u69x2fv0kh7y | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 1 | 96 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=73400)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=73400"
Note ----- - dynamic sampling used for this statement (level=2)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 350 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> /
no rows selected
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 96 | 1 (0)| 00:00:01 | | 1 | RESULT CACHE | 183x3yt1jbbc42u69x2fv0kh7y | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 1 | 96 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=73400)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=73400"
Note ----- - dynamic sampling used for this statement (level=2)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 350 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL>
--session 3 SQL> select count(*) from ht02 where owner='SYS' and object_id=1001;
COUNT(*) ---------- 4
Elapsed: 00:00:00.30 SQL> begin 2 for i in 1..100000 loop 3 if mod(i,2)=1 then 4 update ht02 set owner='killdb.com' where owner='SYS' and object_id=i; 5 end if; 6 end loop; 7 end; 8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.34
--session 4 SQL> set timing on SQL> set lines 160 SQL> set autot traceonly SQL> select /*+ RESULT_CACHE */ owner,object_name 2 from ht02 where object_id=1001;
Elapsed: 00:00:00.24
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 144 | 5 (0)| 00:00:01 | | 1 | RESULT CACHE | 20kb0gt7yvjj01xngsp2bcwsub | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 4 | 144 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=1001)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=1001"
Statistics ---------------------------------------------------------- 24 recursive calls 0 db block gets 467 consistent gets 0 physical reads 280 redo size 548 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) 4 rows processed
SQL> /
Elapsed: 00:00:00.02
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 144 | 5 (0)| 00:00:01 | | 1 | RESULT CACHE | 20kb0gt7yvjj01xngsp2bcwsub | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 4 | 144 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=1001)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=1001"
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 464 consistent gets 0 physical reads 256 redo size 548 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) 4 rows processed
SQL> /
Elapsed: 00:00:00.02
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 144 | 5 (0)| 00:00:01 | | 1 | RESULT CACHE | 20kb0gt7yvjj01xngsp2bcwsub | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 4 | 144 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=1001)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=1001"
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 464 consistent gets 0 physical reads 300 redo size 548 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) 4 rows processed
SQL> /
Elapsed: 00:00:00.01
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 144 | 5 (0)| 00:00:01 | | 1 | RESULT CACHE | 20kb0gt7yvjj01xngsp2bcwsub | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 4 | 144 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=1001)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=1001"
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 464 consistent gets 0 physical reads 300 redo size 548 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) 4 rows processed
SQL> /
Elapsed: 00:00:00.02
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 144 | 5 (0)| 00:00:01 | | 1 | RESULT CACHE | 20kb0gt7yvjj01xngsp2bcwsub | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 4 | 144 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=1001)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=1001"
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 562 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) 4 rows processed
SQL> /
Elapsed: 00:00:00.01
Execution Plan ---------------------------------------------------------- Plan hash value: 796030940
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 144 | 5 (0)| 00:00:01 | | 1 | RESULT CACHE | 20kb0gt7yvjj01xngsp2bcwsub | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| HT02 | 4 | 144 | 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HT02_ID_IDX | 4 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=1001)
Result Cache Information (identified by operation id): ------------------------------------------------------
1 - column-count=2; dependencies=(ROGER.HT02); attributes=(ordered); name="select /*+ RESULT_CACHE */ owner,object_name from ht02 where object_id=1001"
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 562 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) 4 rows processed
SQL> conn /as sysdba Connected. SQL> alter session set events 'immediate trace name heapdump level 2';
Session altered.
Elapsed: 00:00:01.41 SQL> @ gettrc.sql
TRACE_FILE_NAME ----------------------------------------------------------------------- /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_3732.trc
Elapsed: 00:00:00.17 SQL>
[oracle@roger trace]$ cat /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_3732.trc|grep Bucket Bucket 0 size=16 Bucket 1 size=20 Bucket 2 size=24 Bucket 3 size=28 Bucket 4 size=32 Bucket 5 size=36 Bucket 6 size=40 Bucket 7 size=44 Bucket 8 size=48 Bucket 9 size=52 Bucket 10 size=56 Bucket 11 size=60 Bucket 12 size=64 Bucket 13 size=68 Bucket 14 size=72 Bucket 15 size=76 Bucket 16 size=80 Bucket 17 size=84 Bucket 18 size=88 Bucket 19 size=92 Bucket 20 size=96 Bucket 21 size=100 Bucket 22 size=104 Bucket 23 size=108 Bucket 24 size=112 Bucket 25 size=116 Bucket 26 size=120 Bucket 27 size=124 Bucket 28 size=128 Bucket 29 size=132 Bucket 30 size=136 Bucket 31 size=140 Bucket 32 size=144 Bucket 33 size=148 Bucket 34 size=152 Bucket 35 size=156 Bucket 36 size=160 Bucket 37 size=164 Bucket 38 size=168 Bucket 39 size=172 Bucket 40 size=176 Bucket 41 size=180 Bucket 42 size=184 Bucket 43 size=188 Bucket 44 size=192 Bucket 45 size=196 Bucket 46 size=200 Bucket 47 size=204 Bucket 48 size=208 Bucket 49 size=212 Bucket 50 size=216 Bucket 51 size=220 Bucket 52 size=224 Bucket 53 size=228 Bucket 54 size=232 Bucket 55 size=236 Bucket 56 size=240 Bucket 57 size=244 Bucket 58 size=248 Bucket 59 size=252 Bucket 60 size=256 Bucket 61 size=260 Bucket 62 size=264 Bucket 63 size=268 Bucket 64 size=272 Bucket 65 size=276 Bucket 66 size=280 Bucket 67 size=284 Bucket 68 size=288 Bucket 69 size=292 Bucket 70 size=296 Bucket 71 size=300 Bucket 72 size=304 Bucket 73 size=308 Bucket 74 size=312 Bucket 75 size=316 Bucket 76 size=320 Bucket 77 size=324 Bucket 78 size=328 Bucket 79 size=332 Bucket 80 size=336 Bucket 81 size=340 Bucket 82 size=344 Bucket 83 size=348 Bucket 84 size=352 Bucket 85 size=356 Bucket 86 size=360 Bucket 87 size=364 Bucket 88 size=368 Bucket 89 size=372 Bucket 90 size=376 Bucket 91 size=380 Bucket 92 size=384 Bucket 93 size=388 Bucket 94 size=392 Bucket 95 size=396 Bucket 96 size=400 Bucket 97 size=404 Bucket 98 size=408 Bucket 99 size=412 Bucket 100 size=416 Bucket 101 size=420 Bucket 102 size=424 Bucket 103 size=428 Bucket 104 size=432 Bucket 105 size=436 Bucket 106 size=440 Bucket 107 size=444 Bucket 108 size=448 Bucket 109 size=452 Bucket 110 size=456 Bucket 111 size=460 Bucket 112 size=464 Bucket 113 size=468 Bucket 114 size=472 Bucket 115 size=476 Bucket 116 size=480 Bucket 117 size=484 Bucket 118 size=488 Bucket 119 size=492 Bucket 120 size=496 Bucket 121 size=500 Bucket 122 size=504 Bucket 123 size=508 Bucket 124 size=512 Bucket 125 size=516 Bucket 126 size=520 Bucket 127 size=524 Bucket 128 size=528 Bucket 129 size=532 Bucket 130 size=536 Bucket 131 size=540 Bucket 132 size=544 Bucket 133 size=548 Bucket 134 size=552 Bucket 135 size=556 Bucket 136 size=560 Bucket 137 size=564 Bucket 138 size=568 Bucket 139 size=572 Bucket 140 size=576 Bucket 141 size=580 Bucket 142 size=584 Bucket 143 size=588 Bucket 144 size=592 Bucket 145 size=596 Bucket 146 size=600 Bucket 147 size=604 Bucket 148 size=608 Bucket 149 size=612 Bucket 150 size=616 Bucket 151 size=620 Bucket 152 size=624 Bucket 153 size=628 Bucket 154 size=632 Bucket 155 size=636 Bucket 156 size=640 Bucket 157 size=644 Bucket 158 size=648 Bucket 159 size=652 Bucket 160 size=656 Bucket 161 size=660 Bucket 162 size=664 Bucket 163 size=668 Bucket 164 size=672 Bucket 165 size=676 Bucket 166 size=680 Bucket 167 size=684 Bucket 168 size=688 Bucket 169 size=692 Bucket 170 size=696 Bucket 171 size=700 Bucket 172 size=704 Bucket 173 size=708 Bucket 174 size=712 Bucket 175 size=716 ---bucket 0~175 以4递增 Bucket 176 size=724 Bucket 177 size=732 Bucket 178 size=740 Bucket 179 size=748 Bucket 180 size=756 Bucket 181 size=764 Bucket 182 size=772 Bucket 183 size=780 Bucket 184 size=788 Bucket 185 size=796 Bucket 186 size=804 Bucket 187 size=812 -----bucket 176~187 以8递增 Bucket 188 size=876 Bucket 189 size=940 Bucket 190 size=1004 Bucket 191 size=1068 Bucket 192 size=1072 Bucket 193 size=1076 Bucket 194 size=1132 Bucket 195 size=1196 Bucket 196 size=1260 Bucket 197 size=1324 Bucket 198 size=1388 Bucket 199 size=1452 Bucket 200 size=1516 Bucket 201 size=1580 Bucket 202 size=1644 Bucket 203 size=1708 Bucket 204 size=1772 Bucket 205 size=1836 Bucket 206 size=1900 Bucket 207 size=1964 Bucket 208 size=2028 Bucket 209 size=2092 Bucket 210 size=2156 Bucket 211 size=2220 Bucket 212 size=2284 Bucket 213 size=2348 Bucket 214 size=2412 Bucket 215 size=2476 Bucket 216 size=2540 Bucket 217 size=2604 Bucket 218 size=2668 Bucket 219 size=2732 Bucket 220 size=2796 Bucket 221 size=2860 Bucket 222 size=2924 Bucket 223 size=2988 Bucket 224 size=3052 Bucket 225 size=3116 Bucket 226 size=3180 Bucket 227 size=3244 Bucket 228 size=3308 Bucket 229 size=3372 Bucket 230 size=3436 Bucket 231 size=3500 Bucket 232 size=3564 Bucket 233 size=3628 Bucket 234 size=3692 Bucket 235 size=3756 Bucket 236 size=3820 Bucket 237 size=3884 Bucket 238 size=3948 Bucket 239 size=4012 --bucket 188~239 以64递增 Bucket 240 size=4096 Bucket 241 size=4100 Bucket 242 size=4108 Bucket 243 size=8204 Bucket 244 size=8460 Bucket 245 size=8464 Bucket 246 size=8468 Bucket 247 size=8472 Bucket 248 size=9296 Bucket 249 size=9300 Bucket 250 size=12320 Bucket 251 size=12324 Bucket 252 size=16396 Bucket 253 size=32780 Bucket 254 size=65548 [oracle@roger ~]$ grep -i Result /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_3732.trc Chunk 24ab3094 sz= 24576 freeable "Result Cache " ds=0x272758b4 Chunk 24bf2000 sz= 24576 recreate "Result Cache " latch=(nil) Chunk 24c18f9c sz= 32816 R-freeable "Result Cache " ds=0x272758b4 Chunk 24c20fcc sz= 32816 R-freeable "Result Cache " ds=0x272758b4 [oracle@roger ~]$
这里对查询sql语句多执行几次 [oracle@roger ~]$ grep -i Result /oracle/product/diag/rdbms/roger/roger/trace/roger_ora_3946.trc sword xsoqsqlresultsetcachesize_ [106E3EB0, 106E3EB4) = 000008A7 RESILVER_TEST_RESULT = 0 result_cache_mode = MANUAL _result_cache_auto_size_threshold = 100 _result_cache_auto_time_threshold = 1000 result_cache_mode = MANUAL _result_cache_auto_size_threshold = 100 _result_cache_auto_time_threshold = 1000 Chunk 24ab3094 sz= 24576 freeable "Result Cache " ds=0x272758b4 Chunk 24bf2000 sz= 24576 recreate "Result Cache " latch=(nil) Chunk 24c18f9c sz= 32816 R-freeable "Result Cache " ds=0x272758b4 Chunk 24c20fcc sz= 32816 R-freeable "Result Cache " ds=0x272758b4 [oracle@roger ~]$
SQL> select 24576*2+32816*2 from dual;
24576*2+32816*2 --------------- 114784
Elapsed: 00:00:00.06 SQL> select * from v$sgastat where name like '%Result%';
POOL NAME BYTES ------------ -------------------------- ---------- shared pool Result Cache: State Objs 2852 shared pool Result Cache 114720 shared pool Result Cache: Memory Mgr 124 shared pool Result Cache: Bloom Fltr 2048 shared pool Result Cache: Cache Mgr 4416
Elapsed: 00:00:00.15 SQL> SQL> select ksmchcom, ksmchcls, ksmchsiz from x$ksmsp 2 where ksmchcom like '%Result%';
KSMCHCOM KSMCHCLS KSMCHSIZ ---------------- -------- ---------- Result Cache R-freea 32816 Result Cache R-freea 32816 Result Cache recr 24576 Result Cache freeabl 24576
Elapsed: 00:00:00.10 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_4201.trc SQL>
*** 2011-08-20 07:56:10.092 Processing Oradebug command 'dump heapdump_addr 2 656890036' ****************************************************** HEAP DUMP heap name="Result Cache" desc=0x272758b4 extent sz=0x8024 alt=32767 het=32767 rec=0 flg=2 opc=2 parent=0x200010b4 owner=(nil) nex=(nil) xsz=0x8024 heap=(nil) fl2=0x20, nex=(nil) EXTENT 0 addr=0x24c18fa8 Chunk 24c18fb0 sz= 32796 perm "perm " alo=32784 Dump of memory from 0x24C18FB0 to 0x24C20FCC 24C18FB0 5000801D 00000000 24C20FE0 00008010 [...P.......$....] 24C18FC0 00000000 24C18FC0 00000000 00000002 [.......$........] 24C18FD0 24C22630 24C22630 27276A48 27276A48 [0&.$0&.$Hj''Hj''] 24C18FE0 00000002 9B29D2C8 D0E973A6 8006F2E4 [......)..s......] 24C18FF0 00000000 4E4ED8C5 00000055 14086F78 [......NNU...xo..] 24C19000 002F2506 00011EDF 00000002 00070000 [.%/.............] 24C19010 000001FF 24C19BC0 24C19FC0 24C1A3C0 [.......$...$...$] 24C19020 24C1A7C0 24C1ABC0 24C1AFC0 24C1B3C0 [...$...$...$...$] 24C19030 24C1B7C0 24C1BBC0 00000000 00000000 [...$...$........] 24C19040 00000000 00000000 00000000 00000000 [................] Repeat 55 times 24C193C0 00000001 24C193C0 00000000 00000003 [.......$........] 24C193D0 27276A50 24C197D0 24C193D8 24C193D8 [Pj''...$...$...$] 24C193E0 00000001 FAA0BF7D CF693355 800558B9 [....}...U3i..X..] 24C193F0 00000000 00000000 00000055 14086F78 [........U...xo..] 24C19400 002F2506 00000000 00000000 00000000 [.%/.............] 24C19410 00000001 00000000 03000002 00000000 [................] 24C19420 00000001 00000000 00000000 00000000 [................] 24C19430 00000000 00000000 00010001 00000000 [................] 24C19440 4315AD84 140FA3F6 B60940FE 2D193D13 [...C.....@...=.-] 24C19450 A4C47F50 F2BD2A87 F93C5839 725A0720 [P....*..9X<. .Zr] 24C19460 00000000 24C19474 0000004C 00011EDF [....t..$L.......] 24C19470 24C18FC0 656C6573 2F207463 52202B2A [...$select /*+ R] 24C19480 4C555345 41435F54 20454843 6F202F2A [ESULT_CACHE */ o] 24C19490 72656E77 6A626F2C 5F746365 656D616E [wner,object_name] 24C194A0 6F72660A 7468206D 77203230 65726568 [.from ht02 where] 24C194B0 6A626F20 5F746365 373D6469 30303433 [ object_id=73400] 24C194C0 00000000 00000000 00000000 00000000 [................] Repeat 47 times 24C197C0 00000002 24C197C0 00000000 00000003 [.......$........] 24C197D0 24C193D0 27276A50 24C197D8 24C197D8 [...$Pj''...$...$] 24C197E0 00000001 FAA0BF7D CF693355 80060C27 [....}...U3i.'...] 24C197F0 00000000 00000000 00000055 14086F78 [........U...xo..] 24C19800 003B2A06 00000000 00000000 00000000 [.*;.............] 24C19810 00000000 00000000 03000002 00000000 [................] 24C19820 00000001 00000000 00000000 00000000 [................] 24C19830 00000000 00000000 00010001 00000000 [................] 24C19840 4315AD84 140FA3F6 B60940FE 2D193D13 [...C.....@...=.-] 24C19850 A4C47F50 F2BD2A87 F93C5839 725A0720 [P....*..9X<. .Zr] 24C19860 00000000 24C19874 0000004C 00011EDF [....t..$L.......] 24C19870 24C18FC0 656C6573 2F207463 52202B2A [...$select /*+ R] 24C19880 4C555345 41435F54 20454843 6F202F2A [ESULT_CACHE */ o] 24C19890 72656E77 6A626F2C 5F746365 656D616E [wner,object_name] 24C198A0 6F72660A 7468206D 77203230 65726568 [.from ht02 where] 24C198B0 6A626F20 5F746365 373D6469 30303433 [ object_id=73400] 24C198C0 00000000 00000000 00000000 00000000 [................] 。。。。。。。。。。 24BF7FF0 00000005 00000006 0001A310 00000000 [................] Total free space = 24488 UNPINNED RECREATABLE CHUNKS (lru first): PERMANENT CHUNKS: Chunk 24c18fb0 sz= 32796 perm "perm " alo=32784 Dump of memory from 0x24C18FB0 to 0x24C20FCC 24C18FB0 5000801D 00000000 24C20FE0 00008010 [...P.......$....] 24C18FC0 00000000 24C18FC0 00000000 00000002 [.......$........] 24C18FD0 24C22630 24C22630 27276A48 27276A48 [0&.$0&.$Hj''Hj''] 24C18FE0 00000002 9B29D2C8 D0E973A6 8006F2E4 [......)..s......] 24C18FF0 00000000 4E4ED8C5 00000055 14086F78 [......NNU...xo..] 24C19000 002F2506 00011EDF 00000002 00070000 [.%/.............] 24C19010 000001FF 24C19BC0 24C19FC0 24C1A3C0 [.......$...$...$] 24C19020 24C1A7C0 24C1ABC0 24C1AFC0 24C1B3C0 [...$...$...$...$] 24C19030 24C1B7C0 24C1BBC0 00000000 00000000 [...$...$........] 24C19040 00000000 00000000 00000000 00000000 [................] Repeat 55 times 24C193C0 00000001 24C193C0 00000000 00000003 [.......$........] 24C193D0 27276A50 24C197D0 24C193D8 24C193D8 [Pj''...$...$...$] 24C193E0 00000001 FAA0BF7D CF693355 800558B9 [....}...U3i..X..] 24C193F0 00000000 00000000 00000055 14086F78 [........U...xo..] 24C19400 002F2506 00000000 00000000 00000000 [.%/.............] 24C19410 00000001 00000000 03000002 00000000 [................] 24C19420 00000001 00000000 00000000 00000000 [................] 24C19430 00000000 00000000 00010001 00000000 [................] 24C19440 4315AD84 140FA3F6 B60940FE 2D193D13 [...C.....@...=.-] 24C19450 A4C47F50 F2BD2A87 F93C5839 725A0720 [P....*..9X<. .Zr] 24C19460 00000000 24C19474 0000004C 00011EDF [....t..$L.......] 24C19470 24C18FC0 656C6573 2F207463 52202B2A [...$select /*+ R] 24C19480 4C555345 41435F54 20454843 6F202F2A [ESULT_CACHE */ o] 24C19490 72656E77 6A626F2C 5F746365 656D616E [wner,object_name] 24C194A0 6F72660A 7468206D 77203230 65726568 [.from ht02 where] 24C194B0 6A626F20 5F746365 373D6469 30303433 [ object_id=73400] 24C194C0 00000000 00000000 00000000 00000000 [................] Repeat 47 times 24C197C0 00000002 24C197C0 00000000 00000003 [.......$........] 24C197D0 24C193D0 27276A50 24C197D8 24C197D8 [...$Pj''...$...$] 24C197E0 00000001 FAA0BF7D CF693355 80060C27 [....}...U3i.'...] 24C197F0 00000000 00000000 00000055 14086F78 [........U...xo..] 24C19800 003B2A06 00000000 00000000 00000000 [.*;.............] 24C19810 00000000 00000000 03000002 00000000 [................] 24C19820 00000001 00000000 00000000 00000000 [................] 24C19830 00000000 00000000 00010001 00000000 [................] 24C19840 4315AD84 140FA3F6 B60940FE 2D193D13 [...C.....@...=.-] 24C19850 A4C47F50 F2BD2A87 F93C5839 725A0720 [P....*..9X<. .Zr] 24C19860 00000000 24C19874 0000004C 00011EDF [....t..$L.......] 24C19870 24C18FC0 656C6573 2F207463 52202B2A [...$select /*+ R] 24C19880 4C555345 41435F54 20454843 6F202F2A [ESULT_CACHE */ o] 24C19890 72656E77 6A626F2C 5F746365 656D616E [wner,object_name] 24C198A0 6F72660A 7468206D 77203230 65726568 [.from ht02 where] 24C198B0 6A626F20 5F746365 373D6469 30303433 [ object_id=73400] 24C198C0 00000000 00000000 00000000 00000000 [................]
从上面的的信息我们可以看出，query cache 这部分内存存在shared pool中， 而且其管理方式跟shared pool类似，甚至我们可以认为一样，其内存类型 也分为freeable，recr，R-freea等等。
另外从上面的query cache 查询来看，对于dml操作频繁的表，使用该特性可能没有想象中的那么好。 我们可以看到上面第2个sql的执行计划，按照以前的情况来看，该处的逻辑读应该为0，而此时却为2. 测试update频繁操作的时候，执行sql语句，发现也不是想象中的那么好。
不过我这里测试不太严谨，最好是能准备一个千万级别的表，然后做相关测试，然后记录cpu以及内存等 的消耗变化然后进行对比，那样估计比较有说服力。
当然从前面的测试来看，query cache特性对于OLTP系统可能并不合适，这样看来，该特性到时适合DW。
love wife love life —Roger的Oracle/MySQL数据恢复博客
Phone:18180207355 提供专业Oracle/MySQL数据恢复、性能优化、迁移升级、紧急救援等服务
