--oracle 11g 结果集缓存特征
--日期:2007-08-22
--Oracle 11g的Server Result Cache特征可以重用相同的结果集,减少逻辑IO,提高系统性能.
--相关视图:
--result cache的统计信息.
V$RESULT_CACHE_STATISTICS
--result cache内存的使用情况.
V$RESULT_CACHE_MEMORY
--result cache里面的对象信息.
V$RESULT_CACHE_OBJECTS
--result cache对象的依赖信息.
V$RESULT_CACHE_DEPENDENCY
--对result_cache进行操作的包,具体参考文档.
DBMS_RESULT_CACHE
--相关参数:
PHP code:
SQL> show parameter result_cache
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 4640K
result_cache_mode string MANUAL
result_cache_remote_expiration integer &nb
RESULT_CACHE_MAX_SIZE指定result cache的大小,设置为0时那ORACLE会禁用result cache.
RESULT_CACHE_MODE参数控制使用reslult cache的模式,它有两个指 MANUAL,FORCE.
如果设置的值为MANUAL,用户必须用reslut_cache 提示才可以缓存结果集.
如果设置为FORCE,ORACLE会缓冲所有的结果,除非用户用了no_result_cache提示.
RESULT_CACHE_MAX_RESULT指定单个result set可以占用RESULT_CACHE_MAX_SIZE的比例,默认为5%.
client_result*的两个参数用于指定客户端也开辟结果集缓存.
result_cache_remote_expiration指定缓存远程对象的过期时间
--测试过程:
由于result_cache_mode默认为manual,所以必须使用hint才能cache result set.PHP code:
SQL> select /*+ result_cache */ owner,count(*) from t_compress group by owner;
OWNER COUNT(*)
------------------------------ ----------
NEWHC 3371
TSMSYS 3
PUBLIC 23101
OUTLN 9
TEST 4
SYSTEM 511
ORACLE_OCM 8
EXFSYS 303
DBSNMP 55
XDB 784
SYS 29401
WMSYS
--看下相关视图的内容:PHP code:
SQL> select * from V$RESULT_CACHE_STATISTICS;
ID NAME VALUE
---------- ---------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 4640
3 Block Count Current 32
4 Result Size Maximum (Blocks) 232
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
10 rows selected.
--也可以使用dbms_result_cache.memory_report来查看内存分配情况PHP code:
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 = 4640K bytes (4640 blocks)
Maximum Result Size = 232K bytes (232 blocks)
[Memory]
Total Memory = 103528 bytes [0.020% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.019% of the Shared Pool]
....... Overhead = 65628 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.
SQL> select * from V$RESULT_CACHE_MEMORY;
ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0
2 0 2 YES 0 0
3 0 3 YES 0 0
4 0 4 YES 0 0
5 0 5 YES 0 0
6 0 6 YES 0 0
7 0 7 YES 0 0
8 0 8 YES 0 0
9 0 9 YES 0 0
10 0 10 YES 0 0
ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
11 0 11 YES 0 0
12 0 12 YES 0 0
13 0 13 YES 0 0
14 0 14 YES 0 0
15 0 15 YES 0 0
16 0 16 YES 0 0
17 0 17 YES 0 0
18 0 18 YES 0 0
19 0 19 YES 0 0
20 0 20 YES 0 0
21 0 21 YES 0 0
ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
22 0 22 YES 0 0
23 0 23 YES 0 0
24 0 24 YES 0 0
25 0 25 YES 0 0
26 0 26 YES 0 0
27 0 27 YES 0 0
28 0 28 YES 0 0
29 0 29 YES 0 0
30 0 30 YES 0 0
31 0 31 YES 0 0
--32个使用的块在那个chunk以及offset
SQL> select cache_id,name from v$result_cache_objects;
CACHE_ID NAME
--------------------------------------- ----------------------------------------------------------------------------------------------------
TEST.T_COMPRESS TEST.T_COMPRESS
5bg7fxv3404w32v0vqv0bhgccf select /*+ result_cache */ owner,count(*) from t_compress group by owner
--这个result cache 对象信息.
SQL> select * from V$RESULT_CACHE_DEPENDENCY;
RESULT_ID DEPEND_ID OBJECT_NO
---------- ---------- ----------
1 0 58797
--对象依赖信息.
--重新执行语句复用reselt set.
SQL> select /*+ result_cache */ owner,count(*) from t_compress group by owner;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2456935480
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 72 | 79
(8)| 00:00:01 |
| 1 | RESULT CACHE | 5bg7fxv3404w32v0vqv0bhgccf | | |
| |
| 2 | HASH GROUP BY | | 12 | 72 | 79
(8)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_COMPRESS | 57865 | 339K| 74
(2)| 00:00:01 |
--------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(TEST.T_COMPRESS); parameters=(nls); name="s
elect /*+ result_cache */ owner,count(*) from t_compress group by owner"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
12 rows processed
--不需要再去读取数据,注意name是cache_id.
SQL> select owner,count(*) from t_compress group by owner;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2456935480
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 72 | 79 (8)| 00:00:01
| 1 | HASH GROUP BY | | 12 | 72 | 79 (8)| 00:00:01
| 2 | TABLE ACCESS FULL| T_COMPRESS | 57865 | 339K| 74 (2)| 00:00:01
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
258 consistent gets
12 rows processed
--去掉hints没法使用cache result set.
--修改result_cache_mode为force
SQL> alter session set result_cache_mode=force;
Session altered.
SQL> select owner,count(*) from t_compress group by owner;
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2456935480
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 72 | 79
(8)| 00:00:01 |
| 1 | RESULT CACHE | 5bg7fxv3404w32v0vqv0bhgccf | | |
| |
| 2 | HASH GROUP BY | | 12 | 72 | 79
(8)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_COMPRESS | 57865 | 339K| 74
(2)| 00:00:01 |
--------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(TEST.T_COMPRESS); parameters=(nls); name="s
elect owner,count(*) from t_compress group by owner"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 row
--这种情况下才能使用result set.
