11g 新特性之–query result cache(2)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 11g 新特性之–query result cache(2)
在11g 新特性之–query result cache 的第一篇文章中，我讲述该特性的使用以及相关的管理等等，
其中在最后提出了几个疑问，query cache结构如何？跟shared pool有何关系？
该特性真的是说的那么好吗？它适用于OLTP 系统吗？ 下面这篇文章将给出解答。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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> 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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
---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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
---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> / 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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
--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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 |
--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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 |
[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语句多执行几次
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[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 ~]$ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
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> 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> 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。
2 Responses to “11g 新特性之–query result cache(2)”
Greetings! I would just like to thank you for the excellent information contained in this post. I will be coming back to your blog for more groovy information soon.
Wow! Thank you! I permanently wanted to write on my website something like that. Can I take a part of your post to my site?
Leave a Reply
You must be logged in to post a comment.