原文链接 个人博客 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。