【11gR2新特性】result cache 的三種模式
parameter result
NAME TYPE VALUE
------------------------------------ ----------- -------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2080K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
結果快取的三種儲存方式: MANUAL,AUTO,FORCE ,這篇文章將分別介紹三種模式的使用方法。
當result_cache_mode=MANUAL的時候，必須使用hint提示才能使用結果快取特性。首先清理快取，並檢視快取情況。
dbms_result_cache.flush();
PL/SQL 過程已成功完成。
dbms_result_cache.memory_report();
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 過程已成功完成。
進行不帶hint的查詢例子。
autotrace on
object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 2 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從快取報告中可以看出上面的sql結果沒有被快取。
dbms_result_cache.memory_report();
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 9460 bytes [0.004% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 過程已成功完成。
autotrace off
* from v$result_cache_statistics;
ID NAME VALUE
---------- ----------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 0
4 Result Size Maximum (Blocks) 104
5 Create Count Success 0
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 0
已選擇11行。
使用hint提示執行sql語句。
/*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */object_type,count(*) from yangobj group by object_type"
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
753 consistent gets
748 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
autotrace off
* from v$result_cache_statistics;
ID NAME VALUE
---------- ----------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 32
4 Result Size Maximum (Blocks) 104
5 Create Count Success 1 --結果被快取。
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
已選擇11行。
dbms_result_cache.memory_report();
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 102372 bytes [0.044% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 92912 bytes [0.040% of the Shared Pool]
....... verhead = 60144 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL 過程已成功完成。
autotrace on
/*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */object_type,count(*) from yangobj group by object_type"
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets --利用了結果快取。
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
>
name,cache_id,cache_key from v$result_cache_objects;
CACHE_ID CACHE_KEY NAME
---------- -------------------------- -----------------------------
YANG.YANGOBJ YANG.YANGOBJ YANG.YANGOBJ
93qg9pxgyrhd35bxgp9ay1mvqw fpn1dsgmvbq9cbhu4vs188mqr3 select /*+ result_cache */ object_type,count(*)
from yangobj group byobject_type
--------------------------autot-------------------------------------------------
當result_cache_mode=MANUAL的時候，情況比較複雜一點，oracle 根據自己非公開的演算法來實現結果快取的。從下面的實驗來看，不管在shared POOL 裡有沒有結果集，oracle都不會使用那個結果集，使用hint提示時，才會使用結果集。
SQL>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65536
已用時間: 00: 00: 00.02
SQL>exec dbms_result_cache.memory_report();
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 107812 bytes [0.046% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 98352 bytes [0.042% of the Shared Pool]
....... verhead = 65584 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... AUTO = 1 blocks (1 count)
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.03
SQL>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ --------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 32
4 Result Size Maximum (Blocks) 104
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
已選擇11行。
已用時間: 00: 00: 00.03
SQL>set autotrace on
SQL>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65536
已用時間: 00: 00: 00.02
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; type=AUTO; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select object_type,count(*) from yangobj group by object_type"
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
496 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)
1 rows processed
SQL>set autotrace off
SQL>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ ---------------------------------------------------------------------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 2080
3 Block Count Current 32
4 Result Size Maximum (Blocks) 104
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 1
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
已選擇11行。
已用時間: 00: 00: 00.03
SQL>set autotrace on
SQL>select object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65536
已用時間: 00: 00: 00.02
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; type=AUTO; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select object_type,count(*) from yangobj group by object_type"
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
496 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)
1 rows processed
SQL>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65536
已用時間: 00: 00: 00.02
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls); name="select /*+ result_cache */ object_type,count(*) from yangobj group by obje
ct_type"
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
496 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)
1 rows processed
SQL>select /*+ result_cache */ object_type,count(*) from yangobj group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65536
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | RESULT CACHE | 93qg9pxgyrhd35bxgp9ay1mvqw | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(YANG.YANGOBJ); parameters=(nls);
name="select /*+ result_cache */ object_type,count(*) from yangobj group by obje
ct_type"
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
496 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)
1 rows processed
--------------------------force-------------------------------------------------
當result_cache_mode=MANUAL的時候，oracle 會強制快取sql語句的查詢結果。實驗如下：
session set result_cache_mode = force;
會話已更改。
已用時間: 00: 00: 00.00
dbms_result_cache.flush();
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.00
第一次查詢時，即將結果快取為cache_id為81zjdmh3h4yza1stdry7m73pvg的結果集。
count(*) from yangobj where object_id=74594;
COUNT(*)
----------
65536
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 362321706
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 206 (1)| 00:00:03 |
| 1 | RESULT CACHE | 81zjdmh3h4yza1stdry7m73pvg | | | | |
| 2 | SORT AGGREGATE | | 1 | 5 | | |
|* 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 320K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_ID"=74594)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row); name="select count(*) from yangobj where object_id=74594"
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
再次查詢時，一致性讀為0.
count(*) from yangobj where object_id=74594;
COUNT(*)
----------
65536
已用時間: 00: 00: 00.00
執行計劃
----------------------------------------------------------
Plan hash value: 362321706
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 206 (1)| 00:00:03 |
| 1 | RESULT CACHE | 81zjdmh3h4yza1stdry7m73pvg | | | | |
| 2 | SORT AGGREGATE | | 1 | 5 | | |
|* 3 | TABLE ACCESS FULL| YANGOBJ | 65536 | 320K| 206 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_ID"=74594)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(YANG.YANGOBJ); attributes=(single-row);
name="select count(*) from yangobj where object_id=74594"
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
除非使用no_result_cache hint提示 在force 模式才會不使用結果快取的特性。
system set result_cache_mode=force;
系統已更改。
parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 2080K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0
dbms_result_cache.memory_report();
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 102372 bytes [0.044% of the Shared Pool]
... Fixed Memory = 9460 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 92912 bytes [0.040% of the Shared Pool]
....... verhead = 60144 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL 過程已成功完成。
autotrace on
/*+ no_result_cache */ object_type,count(*) from yangobj group by
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 65527
執行計劃
----------------------------------------------------------
Plan hash value: 1913842841
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 209 (2)| 00:00:03 |
| 1 | HASH GROUP BY | | 1 | 6 | 209 (2)| 00:00:03 |
| 2 | TABLE ACCESS FULL| YANGOBJ | 65536 | 384K| 206 (1)| 00:00:03 |
------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
753 consistent gets
0 physical reads
0 redo size
496 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-702126/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11gR2 Result Cache特性文章收集Oracle
- 【11gR2新特性】DBMS_RESULT_CACHE管理結果快取的包快取
- Oracle 11g新特性：Result CacheOracle
- Oracle11g新特性：SQL Result Cache [zt]OracleSQL
- 11G result cache新特性的更多深入研究
- SQL Query Result Cache的使用和配置--Oracle 11G新特性SQLOracle
- 11gR2新特性之二 - Flash Cache 的SSD支援
- ORACLE 11GR2 新特性CACHE表與以前的區別Oracle
- 聊聊Oracle 11g的Result Cache（三）Oracle
- 11G result cache新特性的一些發現和個人見解
- 11g新特性--result caching
- Oracle Query Result CacheOracle
- 11GR2新特性(轉)
- oracle 11GR2 新特性Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 12 DBMS_RESULT_CACHE管理包Oracle
- 淺談Oracle Result CacheOracle
- 【RAC】11gR2 新特性： Rebootless RestartbootREST
- Guava Cache使用的三種姿勢Guava
- 聊聊Oracle 11g的Result Cache（一）Oracle
- 聊聊Oracle 11g的Result Cache（二）Oracle
- 聊聊Oracle 11g的Result Cache（四）Oracle
- query result cache in oracle 11gOracle
- 11GR2的新特性Deferred Segment Creation
- Oracle 11gr2 的新特性-延遲段建立Oracle
- Oracle11新特性——PLSQL新特性（三）OracleSQL
- 11gR2新特性---Gpnp守護程式
- 【11gR2新特性】extent延遲建立
- 12c設定RESULT_CACHE_MODE=MANUAL發生'Result Cache:RC Latch'型別的Latch Free等待型別
- oracle result cache 結果集快取的使用Oracle快取
- 使用RESULT CACHE加速SQL查詢效率SQL
- Oracle 11.2.0.1 Result Cache 測試 - 1Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 5Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 6Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 7Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 8Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 9Oracle
- Oracle 11.2.0.1 Result Cache 測試 - 10Oracle
- oracle 11g result_cache分析Oracle