One of the best feature for Oracle 11g is setting result cache. Result cache reside in Shared Pool area in SGA. Now database can serves result for queries from the cache instead of re-execute query again and again.
But when depending database object is modified cache result stays invalid. Using result cache you can improve performance of your query.
It is reduce I/O, sorts, computations.
There are some restrictions for result cache which could not store data:
- Data dictionary objects;
- Temporary tables;
- Queries with bind variables (if different) ;
- Flashback query`s result;
- Some SQL functions : current_date, current/local_timestamp, sysdate etc;
- Function which has any OUT or IN OUT parameter;
- Function has IN parameter that are CLOB, BLOB, REF CURSOR etc;
- etc
How to configure result cache
New initialize parameters RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MODE exists on Oracle 11g. You can enable/disable result cache using RESULT_CACHE_MAX_SIZE parameter.
If value of
RESULT_CACHE_MAX_SIZE = 0 it means this feature is disabled.
Memory allocate for the result cache is taken from the shared pool.
There are three different modes you can use and they are managed with RESULT_CACHE_MODE init parameter. This parameter is dynamic and can be changed with ALTER SYSTEM and ALTER SESSION. Default is MANUAL.
Using result_cache hint you can use to manually cache a result of query to override the default setting of there parameter in a session level. Also there is has no_result_cache hint.
There are has several views:
V$RESULT_CACHE_STATISTICS
|
Lists various server result cache settings and memory usage statistics.
|
V$RESULT_CACHE_MEMORY
|
Lists all the memory blocks in the server result
cache and their corresponding statistics.
|
V$RESULT_CACHE_OBJECTS
|
Lists all the objects whose results are in
the
server result cache along with their
attributes.
|
V$RESULT_CACHE_DEPENDENCY
|
Lists the dependency details between the results in the server cache and dependencies among these results.
|
CLIENT_RESULT_CACHE_STATS$
|
Stores cache settings and memory usage
statistics for the client result caches obtained from the OCI client
processes.
|
Check resulting our database`s parameters for result cache
SQL> col name format a30
SQL> col value format a20
SQL> select name,value from v$parameter where name like 'result_cache%';
NAME VALUE
------------------------------ --------------------
result_cache_mode MANUAL
result_cache_max_size 1605632
result_cache_max_result 5
result_cache_remote_expiration 0
Now check memory utilization in SHARED POOL:
SQL> select * from v$sgastat where pool='shared pool' and 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 4416
SQL> select * from V$RESULT_CACHE_DEPENDENCY;
no rows selected
SQL> desc dbms_result_cache;
PROCEDURE BYPASS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BYPASS_MODE BOOLEAN IN
SESSION BOOLEAN IN DEFAULT
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE DELETE_DEPENDENCY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
PROCEDURE DELETE_DEPENDENCY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
FUNCTION FLUSH RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
GLOBAL BOOLEAN IN DEFAULT
PROCEDURE FLUSH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM BOOLEAN IN DEFAULT
RETAINSTA BOOLEAN IN DEFAULT
GLOBAL BOOLEAN IN DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER VARCHAR2 IN
NAME VARCHAR2 IN
FUNCTION INVALIDATE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
PROCEDURE INVALIDATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID BINARY_INTEGER IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID VARCHAR2 IN
PROCEDURE INVALIDATE_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID VARCHAR2 IN
PROCEDURE MEMORY_REPORT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DETAILED BOOLEAN IN DEFAULT
FUNCTION STATUS RETURNS VARCHAR2
SQL>
Use memory utilization report for result cache:
SQL> begin dbms_result_cache.memory_report; end;
2 /
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 = 1568K bytes (1568 blocks)
Maximum Result Size = 78K bytes (78 blocks)
[Memory]
Total Memory = 9440 bytes [0.005% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
SQL> select * from v$result_cache_objects;
no rows selected
SQL>
For now result cache is clear, let`s cache query result.
I will use result_cache hint override the default setting of parameter in the session.
No I will create 2 same tables with same structure and data. Then using hint I will execute query and will take explain plan (also without hint I will execute query on base another table also will take explain plan)
and at the end I will compare explain plans and execution times.
SQL> create table ulfet.big_table as select * from dba_objects;
Table created.
SQL> insert into ulfet.big_table select * from ulfet.big_table;
72526 rows created.
SQL> /
145052 rows created.
SQL> /
290104 rows created.
SQL> /
580208 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select count(1) from big_table;
COUNT(1)
----------
1160416
SQL>
Now create second table on base big_table
SQL> create table big_table2 as select * from big_table;
Table created.
SQL>
Now using below query I will cache query result
SQL> SELECT /*+ RESULT_CACHE */
count(*) , object_type
FROM ulfet.big_table GROUP BY object_type
order by 1; 2 3 4
COUNT(*) OBJECT_TYPE
---------- -------------------
16 LOB PARTITION
16 RULE
16 EDITION
32 JAVA SOURCE
32 DESTINATION
48 MATERIALIZED VIEW
48 SCHEDULE
64 SCHEDULER GROUP
80 DIMENSION
112 CONTEXT
144 INDEXTYPE
COUNT(*) OBJECT_TYPE
---------- -------------------
144 WINDOW
144 UNDEFINED
160 CLUSTER
160 RESOURCE PLAN
160 DIRECTORY
208 JOB CLASS
224 JOB
240 EVALUATION CONTEXT
304 PROGRAM
368 RULE SET
400 CONSUMER GROUP
COUNT(*) OBJECT_TYPE
---------- -------------------
640 QUEUE
832 XML SCHEMA
880 OPERATOR
2304 TABLE PARTITION
2512 PROCEDURE
2928 LIBRARY
3664 SEQUENCE
3840 TYPE BODY
4800 INDEX PARTITION
4832 FUNCTION
4944 JAVA DATA
COUNT(*) OBJECT_TYPE
---------- -------------------
9872 TRIGGER
13344 JAVA RESOURCE
14720 LOB
20000 PACKAGE BODY
20960 PACKAGE
45120 TYPE
45968 TABLE
62592 INDEX
80944 VIEW
366720 JAVA CLASS
444880 SYNONYM
44 rows selected.
SQL>
#Now looking at the explain plan
SQL> set autotrace trace explain statistics
SELECT /*+ RESULT_CACHE */
count(*) , object_type
FROM ulfet.big_table GROUP BY object_type
order by 1;
SQL> 2 3 4
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 446130829
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1393K| 14M| 473
8 (3)| 00:00:57 |
| 1 | RESULT CACHE | 2dp92r6vy7zz94wfhzvrp1jz3w | | |
| |
| 2 | SORT ORDER BY | | 1393K| 14M| 473
8 (3)| 00:00:57 |
| 3 | HASH GROUP BY | | 1393K| 14M| 473
8 (3)| 00:00:57 |
| 4 | TABLE ACCESS FULL| BIG_TABLE | 1393K| 14M| 465
7 (1)| 00:00:56 |
--------------------------------------------------------------------------------
-------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(ULFET.BIG_TABLE); parameters=(nls); name="S
ELECT /*+ RESULT_CACHE */
count(*) , object_type
FROM ulfet.big_table GROUP BY object_type
order by 1"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1569 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
SQL>
Now check result cache object view:
SQL> SELECT id, type, name, scn, row_count FROM v$result_cache_objects;
Execution Plan
----------------------------------------------------------
Plan hash value: 2938610334
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 131 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$QESRCOBJ | 1 | 131 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("INST_ID"=USERENV('INSTANCE'))
Statistics
----------------------------------------------------------
310 recursive calls
0 db block gets
67 consistent gets
10 physical reads
0 redo size
841 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> SELECT * FROM v$result_cache_statistics;
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3700223219
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 118 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$QESRCSTA | 1 | 118 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("INST_ID"=USERENV('INSTANCE'))
Statistics
----------------------------------------------------------
285 recursive calls
0 db block gets
69 consistent gets
1 physical reads
0 redo size
887 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> SELECT * FROM v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ --------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1568
3 Block Count Current 32
4 Result Size Maximum (Blocks) 78
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 4
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
11 Hash Chain Length 1
11 rows selected.
Elapsed: 00:00:00.03
SQL>
Now enable timing to check elapsed time of query
SQL> set autotrace off
SQL> set timing on
SQL> SELECT /*+ RESULT_CACHE */
count(*) , object_type
FROM ulfet.big_table GROUP BY object_type
order by 1;SQL> 2 3 4
COUNT(*) OBJECT_TYPE
---------- -------------------
16 LOB PARTITION
16 RULE
16 EDITION
32 JAVA SOURCE
32 DESTINATION
48 MATERIALIZED VIEW
48 SCHEDULE
64 SCHEDULER GROUP
80 DIMENSION
112 CONTEXT
144 INDEXTYPE
COUNT(*) OBJECT_TYPE
---------- -------------------
144 WINDOW
144 UNDEFINED
160 CLUSTER
160 RESOURCE PLAN
160 DIRECTORY
208 JOB CLASS
224 JOB
240 EVALUATION CONTEXT
304 PROGRAM
368 RULE SET
400 CONSUMER GROUP
COUNT(*) OBJECT_TYPE
---------- -------------------
640 QUEUE
832 XML SCHEMA
880 OPERATOR
2304 TABLE PARTITION
2512 PROCEDURE
2928 LIBRARY
3664 SEQUENCE
3840 TYPE BODY
4800 INDEX PARTITION
4832 FUNCTION
4944 JAVA DATA
COUNT(*) OBJECT_TYPE
---------- -------------------
9872 TRIGGER
13344 JAVA RESOURCE
14720 LOB
20000 PACKAGE BODY
20960 PACKAGE
45120 TYPE
45968 TABLE
62592 INDEX
80944 VIEW
366720 JAVA CLASS
444880 SYNONYM
44 rows selected.
Elapsed: 00:00:00.02
SQL>
repeat again
SQL> /
COUNT(*) OBJECT_TYPE
---------- -------------------
16 LOB PARTITION
16 RULE
16 EDITION
32 JAVA SOURCE
32 DESTINATION
48 MATERIALIZED VIEW
48 SCHEDULE
64 SCHEDULER GROUP
80 DIMENSION
112 CONTEXT
144 INDEXTYPE
COUNT(*) OBJECT_TYPE
---------- -------------------
144 WINDOW
144 UNDEFINED
160 CLUSTER
160 RESOURCE PLAN
160 DIRECTORY
208 JOB CLASS
224 JOB
240 EVALUATION CONTEXT
304 PROGRAM
368 RULE SET
400 CONSUMER GROUP
COUNT(*) OBJECT_TYPE
---------- -------------------
640 QUEUE
832 XML SCHEMA
880 OPERATOR
2304 TABLE PARTITION
2512 PROCEDURE
2928 LIBRARY
3664 SEQUENCE
3840 TYPE BODY
4800 INDEX PARTITION
4832 FUNCTION
4944 JAVA DATA
COUNT(*) OBJECT_TYPE
---------- -------------------
9872 TRIGGER
13344 JAVA RESOURCE
14720 LOB
20000 PACKAGE BODY
20960 PACKAGE
45120 TYPE
45968 TABLE
62592 INDEX
80944 VIEW
366720 JAVA CLASS
444880 SYNONYM
44 rows selected.
Elapsed: 00:00:00.00
SQL>
Waw, 00:00:00, EXCELLENT, is it ? :)
Now check another table`s statistic.
SQL> set autotrace trace explain statistics
SQL> SELECT count(*), object_type
FROM ulfet.big_table2 GROUP BY object_type
order by 1; 2 3
44 rows selected.
Elapsed: 00:00:06.44
Execution Plan
----------------------------------------------------------
Plan hash value: 244325093
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 960K| 10M| 4586 (2)| 00:00:56
|
| 1 | SORT ORDER BY | | 960K| 10M| 4586 (2)| 00:00:56
|
| 2 | HASH GROUP BY | | 960K| 10M| 4586 (2)| 00:00:56
|
| 3 | TABLE ACCESS FULL| BIG_TABLE2 | 960K| 10M| 4531 (1)| 00:00:55
|
--------------------------------------------------------------------------------
--
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
16585 consistent gets
16711 physical reads
0 redo size
1569 bytes sent via SQL*Net to client
441 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
44 rows processed
SQL> set autotrace off
SQL> SELECT count(*), object_type
FROM ulfet.big_table2 GROUP BY object_type
order by 1; 2 3
COUNT(*) OBJECT_TYPE
---------- -------------------
16 LOB PARTITION
16 EDITION
16 RULE
32 JAVA SOURCE
32 DESTINATION
48 MATERIALIZED VIEW
48 SCHEDULE
64 SCHEDULER GROUP
80 DIMENSION
112 CONTEXT
144 UNDEFINED
COUNT(*) OBJECT_TYPE
---------- -------------------
144 WINDOW
144 INDEXTYPE
160 DIRECTORY
160 CLUSTER
160 RESOURCE PLAN
208 JOB CLASS
224 JOB
240 EVALUATION CONTEXT
304 PROGRAM
368 RULE SET
400 CONSUMER GROUP
COUNT(*) OBJECT_TYPE
---------- -------------------
640 QUEUE
832 XML SCHEMA
880 OPERATOR
2304 TABLE PARTITION
2512 PROCEDURE
2928 LIBRARY
3664 SEQUENCE
3840 TYPE BODY
4800 INDEX PARTITION
4832 FUNCTION
4944 JAVA DATA
COUNT(*) OBJECT_TYPE
---------- -------------------
9872 TRIGGER
13344 JAVA RESOURCE
14720 LOB
20000 PACKAGE BODY
20960 PACKAGE
45120 TYPE
45968 TABLE
62592 INDEX
80944 VIEW
366720 JAVA CLASS
444880 SYNONYM
44 rows selected.
Elapsed: 00:00:00.80
Re-execute query again
SQL> /
COUNT(*) OBJECT_TYPE
---------- -------------------
16 LOB PARTITION
16 EDITION
16 RULE
32 JAVA SOURCE
32 DESTINATION
48 MATERIALIZED VIEW
48 SCHEDULE
64 SCHEDULER GROUP
80 DIMENSION
112 CONTEXT
144 UNDEFINED
COUNT(*) OBJECT_TYPE
---------- -------------------
144 WINDOW
144 INDEXTYPE
160 DIRECTORY
160 CLUSTER
160 RESOURCE PLAN
208 JOB CLASS
224 JOB
240 EVALUATION CONTEXT
304 PROGRAM
368 RULE SET
400 CONSUMER GROUP
COUNT(*) OBJECT_TYPE
---------- -------------------
640 QUEUE
832 XML SCHEMA
880 OPERATOR
2304 TABLE PARTITION
2512 PROCEDURE
2928 LIBRARY
3664 SEQUENCE
3840 TYPE BODY
4800 INDEX PARTITION
4832 FUNCTION
4944 JAVA DATA
COUNT(*) OBJECT_TYPE
---------- -------------------
9872 TRIGGER
13344 JAVA RESOURCE
14720 LOB
20000 PACKAGE BODY
20960 PACKAGE
45120 TYPE
45968 TABLE
62592 INDEX
80944 VIEW
366720 JAVA CLASS
444880 SYNONYM
44 rows selected.
Elapsed: 00:00:00.37
As you see Oracle use Shared Pool (before used SQL query) but it is not affected as result cache feature.
To monitor result cache use cache_id value provided explain plan on V$RESULT_CACHE_OBJECTS:
SQL> select
NAME, STATUS, ROW_COUNT, BLOCK_COUNT, NAMESPACE, CREATION_TIMESTAMP
from V$RESULT_CACHE_OBJECTS
where CACHE_ID='2dp92r6vy7zz94wfhzvrp1jz3w'; 2 3
NAME STATUS ROW_COUNT BLOCK_COUNT NAMES CREATION_
------------------------------ --------- ---------- ----------- ----- ---------
SELECT /*+ RESULT_CACHE */ Published 44 1 SQL 14-DEC-12
count(*) , object_type
FROM ulfet.big_table GROUP BY
object_type
order by 1
Elapsed: 00:00:00.01
SQL>
You can also use below statement for monitoring:
SQL> select DBMS_RESULT_CACHE.STATUS from dual;
Result cache can be flushed using :
SQL> exec DBMS_RESULT_CACHE.FLUSH;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.24
Check statistic again
SQL> SELECT * FROM v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ --------------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1568
3 Block Count Current 0
4 Result Size Maximum (Blocks) 78
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 rows selected.
SQL>
Conclusion :
Result cache provide us data (result of query) from memory and not read it from file. But it`s expensive, you can compare above query`s explain plan and can easy see the different.
Source:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF987
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF978
http://www.dba-oracle.com/oracle11g/oracle_11g_result_cache_sql_hint.htm
