11g推出result cache特性
可以在service 端 和client 端 cache 结果集
service 端 由 两部分组成
1.sql query result cache（存储SQl查询的结果集）
2.pl/sql funcation result cache（存pl/sql 函数结果集）
SQL> show user
USER is "SYS"
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> 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 ~~~控制单个cache结果可以占总server result cache大小%
result_cache_max_size big integer 1248K ~~~~cache的大小，若为0表示禁用
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
SQL>
result_cache_mode：manual 查询时候要手动 加hint result_cache 通知优化器才可以
auto:优化器 会判断 是否将使用已经存在的result cache（用hint产生的result cache） （依据查询执行的频率、生成结果的成本以及针对底层数据库对象更改的频率。）,另外只有使用hint的时候产生result cache，oracle才会使用
force:尽可能的cache 查询结果，将result_cache加到select语句中（no_result_cache 可拒绝cache）
SSQL> create user xh identified by a831115
2 ;
User created.
SQL> grant dba to xh
2 ;
Grant succeeded.
SQL> conn xh/a831115
Connected.
SQL> create table xht as select * from all_objects;
Table created.
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace trace
SQL> select count(*) from xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 278 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1062 consistent gets
994 physical reads
0 redo size
420 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)
1 rows processed
SQL> select count(*) from xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 278 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
998 consistent gets~~~~~~~~~~~~~~
0 physical reads
0 redo size
420 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)
1 rows processed
SQL> select /*+result_cache*/ count(*) from xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
998 consistent gets
0 physical reads
0 redo size
420 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)
1 rows processed
SQL> select /*+result_cache*/ count(*) from xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"
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
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> execute dbms_result_cache.flush~~~~~~~~~~~~~~~~~~~~~~
PL/SQL procedure successfully completed.
SQL> select /*+result_cache*/ count(*) from xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
998 consistent gets
0 physical reads
0 redo size
420 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)
1 rows processed
SQL> select dbms_result_cache.status from dual;
STATUS
--------------------------------------------------------------------------------
ENABLED
SQL> set serveroutput on
SQL> execute 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 = 1248K bytes (1248 blocks)
Maximum Result Size = 62K bytes (62 blocks)
[Memory]
Total Memory = 98632 bytes [0.065% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 93500 bytes [0.062% of the Shared Pool]
....... verhead = 60732 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 where free='NO';
ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0
SQL> col name for a40
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
---------- ---------------------------------------- ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1248
3 Block Count Current 32
4 Result Size Maximum (Blocks) 62
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.
SQL> select id,type,name,block_count,row_count from v$result_cache_objects;
ID TYPE NAME BLOCK_COUNT
---------- ---------- ---------------------------------------- -----------
ROW_COUNT
----------
0 Dependency XH.XHT 1
0
1 Result select /*+result_cache*/ count(*) from x 1
ht
1
下面 看下auto情况
SQL> alter system set result_cache_mode=AUTO ~~动态
2 ;
System altered.
SQL> execute dbms_result_cache.flush
PL/SQL procedure successfully completed.
SQL> alter system flush buffer_cache;
System altered.
SQL> select /*+result_cache*/ count(*) from xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1062 consistent gets
994 physical reads
0 redo size
420 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)
1 rows processed
SQL> select count(*) from xht; 不加 HINT
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 52u209qr9dpb723h5gjvvtz8n5 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; type=AUTO; dependencies=(XH.XHT); attributes=(single-row)
; name="select count(*) from xht"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets~~~~~~~~~~~~~~~~~~~~~(要多查几次 才行，因为auto自动根据 查询频率等判断是否有必要使用生成的result cache)
0 physical reads
0 redo size
420 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)
1 rows processed
SQL> create table xht2 as select * from all_objects;
Table created.
SQL> select count(*) from xht2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | | ~~~~~~自动创建了
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
1068 consistent gets
993 physical reads
0 redo size
420 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)
1 rows processed
SQL> select count(*) from xht2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
998 consistent gets~~~~~~~~~~~~~~~~~~~~~~~~~~~
0 physical reads
0 redo size
420 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)
1 rows processed
SQL> select count(*) from xht2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
998 consistent gets~~~~~~~~~~~~~~~~~~~~但没有使用
0 physical reads
0 redo size
420 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)
1 rows processed
SQL>
QL> select /*+result_cache*/count(*) from xht2; ~~~必须创建这个result cache时 用HINT 才可以
Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.XHT2); attributes=(single-row); name="se
lect /*+result_cache*/count(*) from xht2"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
1066 consistent gets
0 physical reads
0 redo size
420 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)
1 rows processed
SQL> select count(*) from xht2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 706guz9xq1vz9b58gwg391w2y0 | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"
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
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
关于sys object
SQL> set autotrace trace exp
SQL> select /*+result_cache*/ count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
SQL> select /*+result_cache*/ count(*) from xh.xht;
Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 278 (1)|
00:00:04 |
| 1 | RESULT CACHE | 5nqh8hwjybg8r8gs3sk5r1qfbs | | |
|
| 2 | SORT AGGREGATE | | 1 | |
|
| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |
--------------------------------------------------------------------------------
----------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xh.xht"
Note
-----
- dynamic sampling used for this statement
可以看到并不支持sys object ，这也很明显 所有基表，数据字典 都属于sys object 如果对他们可以result cache 那么将会用非常大的内存
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-622483/ ，如需转载，请注明出处，否则将追究法律责任。
