yang@rac1>show 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提示才能使用结果缓存特性。首先清理缓存,并查看缓存情况。
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
yang@rac1>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 = 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的查询例子。
yang@rac1>set autotrace on
yang@rac1>select 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结果没有被缓存。
yang@rac1>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 = 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 过程已成功完成。
yang@rac1>set autotrace off
yang@rac1>select * 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语句。
yang@rac1>select /*+ 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
yang@rac1>set autotrace off
yang@rac1>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行。
yang@rac1>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 = 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 过程已成功完成。
yang@rac1>set autotrace on
yang@rac1>select /*+ 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
yang@rac1>
yang@rac1>select 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语句的查询结果。实验如下:
yang@rac1>alter session set result_cache_mode = force;
会话已更改。
已用时间: 00: 00: 00.00
yang@rac1>exec dbms_result_cache.flush();
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.00
第一次查询时,即将结果缓存为cache_id为81zjdmh3h4yza1stdry7m73pvg的结果集。
yang@rac1>select 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.
yang@rac1>select 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 模式才会不使用结果缓存的特性。
yang@rac1>alter system set result_cache_mode=force;
系统已更改。
yang@rac1>show 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
yang@rac1>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 = 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 过程已成功完成。
yang@rac1>set autotrace on
yang@rac1>select /*+ 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22664653/viewspace-702126/