11g 新特性之–Query Cache Result 研究（2）

 原文链接 个人博客 http://www.killdb.com/?p=216

在11g 新特性之--query result cache 的第一篇文章中，我讲述该特性的使用以及相关的管理等等，
其中在最后提出了几个疑问，query cache结构如何？跟shared pool有何关系？
该特性真的是说的那么好吗？它适用于OLTP 系统吗？ 下面这篇文章将给出解答。

SQL> conn roger/rogerConnected.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                           12MDSYS                                1509ROGER                                   4PUBLIC                              27696OUTLN                                   9CTXSYS                                366OLAPSYS                               719FLOWS_FILES                            12OWBSYS                                  2SYSTEM                                529ORACLE_OCM                              8EXFSYS                                310APEX_030200                          2406SCOTT                                   6DBSNMP                                 57ORDSYS                               2532ORDPLUGINS                             10SYSMAN                               3491APPQOSSYS                               3XDB                                   842ORDDATA                               248SYS                                 30789WMSYS                                 316SI_INFORMTN_SCHEMA                      8SQL> select count(*) from ht02 where mod(object_id,2)=0 and owner='SYS';

  COUNT(*)----------     15428SQL> 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 onSQL> 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.19SQL> begin  2  for i in 1..100 loopif mod(i,2)=0 thendelete 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.05SQL>

---session 2SQL> show userUSER is "ROGER"SQL> set autot traceonlySQL> set lines 150SQL> 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_namefrom 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_namefrom 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_namefrom 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 3SQL> select count(*) from ht02 where owner='SYS' and object_id=1001;

  COUNT(*)----------         4

Elapsed: 00:00:00.30SQL>  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 4SQL> set timing onSQL> set lines 160SQL> set autot traceonlySQL>  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 sysdbaConnected.SQL> alter session set events 'immediate trace name heapdump level 2';

Session altered.

Elapsed: 00:00:01.41SQL> @ gettrc.sql

TRACE_FILE_NAME-----------------------------------------------------------------------/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_3732.trc

Elapsed: 00:00:00.17SQL>

[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.trcsword xsoqsqlresultsetcachesize_ [106E3EB0, 106E3EB4) = 000008A7        RESILVER_TEST_RESULT = 0result_cache_mode                   = MANUAL_result_cache_auto_size_threshold   = 100_result_cache_auto_time_threshold   = 1000result_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.06SQL> select * from v$sgastat where name like '%Result%';

POOL         NAME                            BYTES------------ -------------------------- ----------shared pool  Result Cache: State Objs         2852shared pool  Result Cache                   114720shared pool  Result Cache: Memory Mgr          124shared pool  Result Cache: Bloom Fltr         2048shared pool  Result Cache: Cache Mgr          4416

Elapsed: 00:00:00.15SQL>SQL> select ksmchcom, ksmchcls, ksmchsiz from x$ksmsp  2  where ksmchcom like '%Result%';

KSMCHCOM         KSMCHCLS   KSMCHSIZ---------------- -------- ----------Result Cache     R-freea       32816Result Cache     R-freea       32816Result Cache     recr          24576Result Cache     freeabl       24576

Elapsed: 00:00:00.10SQL>SQL> oradebug setmypidStatement processed.SQL> oradebug dump heapdump_addr 2 656890036;Statement processed.SQL> oradebug tracefile_name/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4201.trcSQL>

*** 2011-08-20 07:56:10.092Processing 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=32784Dump of memory from 0x24C18FB0 to 0x24C20FCC24C18FB0 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 times24C193C0 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 times24C197C0 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   =    24488UNPINNED RECREATABLE CHUNKS (lru first):PERMANENT CHUNKS:  Chunk 24c18fb0 sz=    32796    perm      "perm           "  alo=32784Dump of memory from 0x24C18FB0 to 0x24C20FCC24C18FB0 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 times24C193C0 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 times24C197C0 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。