sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle 11G数据库query result cache新特性深入研究

时间:2016-11-19 18:37   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库11G中新特性研究笔记,详细介绍query result cache新特性深入案例研究笔记。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 11g 新特性之–query result cache(2)

在11g 新特性之–query result cache 的几个疑问,query cache结构如何?跟shared pool有何关系?
该特性真的是说的那么好吗?它适用于OLTP 系统吗? 下面这篇文章将给出解答。

oracleplus.net SQL> conn roger/roger
Connected.

oracleplus.net SQL> CREATE TABLE ht02 AS SELECT owner,object_id,object_name FROM dba_objects;
TABLE created.

oracleplus.net SQL> SELECT COUNT(*) FROM ht02;

  COUNT(*)
----------
     71884

oracleplus.net SQL> CREATE INDEX ht02_id_idx ON ht02(object_id);
INDEX created.

oracleplus.net 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

oracleplus.net SQL> SELECT COUNT(*) FROM ht02 WHERE MOD(object_id,2)=0 AND owner='SYS';

  COUNT(*)
----------
     15428

oracleplus.net SQL> SELECT MAX(object_id) FROM ht02 WHERE MOD(object_id,2)=0 AND owner='SYS';

MAX(OBJECT_ID)
--------------
         73410
---session 1 (delete)
oracleplus.net SQL> SET timing ON
oracleplus.net 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
oracleplus.net 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
---session 2
oracleplus.net SQL> SHOW USER
USER IS "ROGER"
oracleplus.net SQL> SET autot traceonly
oracleplus.net SQL> SET LINES 150
oracleplus.net 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

oracleplus.net 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

oracleplus.net 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
--session 3
oracleplus.net SQL> SELECT COUNT(*) FROM ht02 WHERE owner='SYS' AND object_id=1001;

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

Elapsed: 00:00:00.30

oracleplus.net 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
oracleplus.net SQL> SET timing ON
oracleplus.net SQL> SET LINES 160
oracleplus.net SQL> SET autot traceonly
oracleplus.net 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

oracleplus.net 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

oracleplus.net 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

oracleplus.net 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

oracleplus.net 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

oracleplus.net 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


oracleplus.net SQL> conn /AS sysdba
Connected.

oracleplus.net SQL> ALTER SESSION SET events 'immediate trace name heapdump level 2';

SESSION altered.
Elapsed: 00:00:01.41

oracleplus.net SQL> @ gettrc.SQL

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

Elapsed: 00:00:00.17
[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
这里对查询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 ~]$
oracleplus.net SQL> SELECT 24576*2+32816*2 FROM dual;

24576*2+32816*2
---------------
         114784

Elapsed: 00:00:00.06

oracleplus.net 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

oracleplus.net 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

oracleplus.net SQL> oradebug setmypid
Statement processed.

oracleplus.net SQL> oradebug dump heapdump_addr 2 656890036;
Statement processed.

oracleplus.net SQL> oradebug tracefile_name
/oracle/product/diag/rdbms/roger/roger/trace/roger_ora_4201.trc

oracleplus.net SQL>

*** 2011-08-20 07:56:10.092
Processing OradebuOracleoracleplus.netg 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。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 11G数据库query result cache新特性深入研究

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1292.html

Oracle研究中心

关键词:

Oracle 11G新特性详细介绍

研究query result cache笔记