Oracle 11g新特性:Result Cache之一
在Oracle Database 11g中,Oracle引入了一个令人关注的新特性:结果集缓存(Result Cache)。
顾名思义,这个新特性的含义就是将查询的结果集Cache起来,以便随后相同的查询请求可以直接利用,从而避免了再次查询。今天开始来学习一下这个新特性。进一步的 Result Cache又可以分为:Server Result Cache 和 Client Result Cache。
前者通过服务器端SGA来缓存结果集,后者通过客户端来缓存结果集。
缓存是提高性能的一个常用手段,可以说在Oracle数据库中,Cache无处不在。
对于Client Result Cache:
在使用OCI应用程序时,可以通过客户端内存来缓存查询的结果集,缓存结果可以在所有session间共享,当查询反复执行时,查询结果可以直接从客户段的缓存中获得,从而极大地提高应用效率。
客户端结果集缓存并不使用服务器端的内存,不会对服务器的内存使用造成影响,这一点和Server Result Cache不同。
同Client Result Cache相关的视图主要有:
SQL> select * from dict where table_name like '%CLIENT_RESULT_CACHE%';同Client Result Cache相关的参数有:
TABLE_NAME COMMENTS
------------------------------ ---------------------------------------------
CLIENT_RESULT_CACHE_STATS$ Synonym for CRCSTATS_$
GV$CLIENT_RESULT_CACHE_STATS Synonym for GV_$CLIENT_RESULT_CACHE_STATS
V$CLIENT_RESULT_CACHE_STATS Synonym for V_$CLIENT_RESULT_CACHE_STATS
SQL> show parameter client_result如果我们不想启用Client Result Cache的特性,可以设置参数client_result_cache_size为0即可。
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
对于Server Result Cache:
服务器端结果集缓存使用Shared Pool中的内存来进行结果缓存,这部分内存使用可以通过v$sgastat视图来查询观察:
SQL> select * from v$sgastat
2 where lower(name) like '%result%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool Result Cache: State Objs 2852
shared pool Result Cache: Memory Mgr 124
shared pool Result Cache: Bloom Fltr 2048
shared pool Result Cache: Cache Mgr 108
进一步的和Result Cache相关的视图有:
SQL> select * from dict where table_name like '%RESULT_CACHE%';
TABLE_NAME COMMENTS
------------------------------ ---------------------------------------------
CLIENT_RESULT_CACHE_STATS$ Synonym for CRCSTATS_$
GV$CLIENT_RESULT_CACHE_STATS Synonym for GV_$CLIENT_RESULT_CACHE_STATS
GV$RESULT_CACHE_DEPENDENCY Synonym for GV_$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY Synonym for GV_$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS Synonym for GV_$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS Synonym for GV_$RESULT_CACHE_STATISTICS
V$CLIENT_RESULT_CACHE_STATS Synonym for V_$CLIENT_RESULT_CACHE_STATS
V$RESULT_CACHE_DEPENDENCY Synonym for V_$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY Synonym for V_$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS Synonym for V_$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_STATISTICS Synonym for V_$RESULT_CACHE_STATISTICS
11 rows selected.
相关的参数主要有:
SQL> show parameter result_cache_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result integer 5
result_cache_max_size big integer 992K
类似的,result_cache_max_size参数可以被看作Server Result Cache的一个开关,如果设置为0,则Server Result Cache功能会被禁用。而result_cache_max_result则用于定义每个Cache结果集能够使用的Result Cache的百分比。
参数result_cache_mode用于控制Server Result Cache的方式,该参数有3个选项:Manual、AUTO、Force
SQL> show parameter result_cache_mo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
关于这个新特性的参考链接:
http://www.ningoo.net/2007/08/22/oracle11g_new_feature_server_result_cache2.htm
http://yangtingkun.itpub.net/post/468/391015
http://yangtingkun.itpub.net/post/468/391560
==========================
Oracle 11g新特性:Server Result Cache测试
现在我们来看一下Server Result Cache的作用。
首先创建一张测试表:
SQL> connect eygle/eygle
Connected.
SQL> create table eygle as select * from dba_objects;
Table created.
在以前版本中,我们第一次执行该SQL可以看到consistent gets和physical reads大致相同:
SQL> set autotrace on
SQL> select count(*) from eygle;
COUNT(*)
----------
15993
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
282 consistent gets
217 physical reads
0 redo size
420 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
再次执行同样查询时,由于数据Cache在内存中,physical reads会减少到0.
但是consistent gets仍然不变:
SQL> select count(*) from eygle;现在我们来看看在Server Result Cache下,Oracle的行为。
COUNT(*)
----------
15993
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
221 consistent gets
0 physical reads
0 redo size
420 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
首先在result_cache_mode参数设置为MANUAL时:
SQL> show parameter result_cache_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
我们需要在SQL中手工指定Cache,这需要通过加入一个hints来实现,这个hints是result_cache:
SQL> select /*+ result_cache */ count(*) from eygle;注意到这个执行计划已经和以往的不同,RESULT CACHE以76rwwyazv6t6c39f1d8rrqh8rb名称创建。
COUNT(*)
----------
15993
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | RESULT CACHE | 76rwwyazv6t6c39f1d8rrqh8rb | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(EYGLE.EYGLE);
attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
280 consistent gets
0 physical reads
0 redo size
420 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
那么在接下来的查询中,这个Result Cache就可以被利用:
SQL> select /*+ result_cache */ count(*) from eygle;
COUNT(*)
----------
15993
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | RESULT CACHE | 76rwwyazv6t6c39f1d8rrqh8rb | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(EYGLE.EYGLE);
attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
420 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
在这个利用到Result Cache的查询中,consistent gets减少到0,直接访问结果集,不再需要执行SQL查询。
这就是Result Cache的强大之处。
我们可以通过查询v$result_cache_memory视图来看Cache的使用情况:
SQL> select * from V$RESULT_CACHE_MEMORY
2 where FREE='NO';
ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0
通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息:
SQL> select * from V$RESULT_CACHE_STATISTICS;
ID NAME VALUE
---------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 992
3 Block Count Current 32
4 Result Size Maximum (Blocks) 49
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
10 rows selected.
V$RESULT_CACHE_OBJECTS记录了Cache的对象:
SQL> SELECT ID,TYPE,NAME,BLOCK_COUNT,ROW_COUNT FROM V$RESULT_CACHE_OBJECTS;一个新的系统包被引入,DBMS_RESULT_CACHE可以用于执行关于Result Cache的管理:
ID TYPE NAME BLOCK_COUNT ROW_COUNT
---------- ---------- ------------------------------ ----------- ----------
0 Dependency EYGLE.EYGLE 1 0
1 Result select /*+ result_cache */ cou 1 1
nt(*) from eygle
SQL> set serveroutput on
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 = 992K bytes (992 blocks)
Maximum Result Size = 49K bytes (49 blocks)
[Memory]
Total Memory = 100836 bytes [0.059% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 95704 bytes [0.056% of the Shared Pool]
....... Overhead = 62936 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 procedure successfully completed.
参考:
查询结果集cache 谷歌