Result
cache
Result cache has been introduce in earlier
version also but from Oracle 11g it has been enhanced to great level. So let’s try to learn more about this feature.
Result cache feature
does exactly what its name implies, it
caches the results of queries and puts it into a slice of the shared
pool. If you have a query that is executed often and reads data that
rarely changes, this feature can increase performance significantly. When
the query executes, Oracle will first look in the result cache to see if a
previous result for this query is cached. If so, it will retrieve those
results instead of reading all the blocks and creating the results again.
The initial execution will run in the normal time but subsequent executions
will seem to be nearly instantaneous.
Important points to understand are:
Oracle can cache the results of both SQL
& PL/SQL.
Memory allocated for the
result cache is taken from the shared pool.
Let’s figure out important parameter useful
to administer the result cache:
SQL>
select name,value from v$parameter where name like 'result_cache%';
NAME VALUE
------------------------------
-------------------------
result_cache_mode MANUAL
result_cache_max_size 47677440
result_cache_max_result 5
result_cache_remote_expiration 0
Trying to understand these parameter:
result_cache_mode: Default value to this
parameter is Manual. That means you have pass hint in the query to use the
result cache feature. Other option to this parameter is force, that mean all
the query will be cached but only if they fit in the cache.
result_cache_max_size: This the parameter which
defines the size of the result cache. We need to understand, result cache is
the part of shared pool and maximum size can be 75% of the shared pool.
result_cache_max_result: Specifies
percentage of RESULT_CACHE_MAX_SIZE that any single query result set can
occupy. (Just to prevent the big result set from throwing out all other small
resultset).
result_cache_remote_expiration: It defines
the number of minutes that cached the remote object will be remain valid. Value
should be set to positive value if remote database is accessed for reading
purpose.
I am trying to check the feature with the
table whose value are very static and will not often.
10:21:02 SQL> set autotrace on
10:21:08 SQL> select count(1) from JAY.RESULTCACHE1;
COUNT(1)
----------
16207591
Execution Plan
----------------------------------------------------------
Plan hash value: 821388131
-----------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 | 16648
(2)| 00:03:20 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| RESULTCACHE1 | 16M| 16648
(2)| 00:03:20 |
-----------------------------------------------------------------------------
Note
-----
- dynamic
sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
84 recursive calls
0 db block gets
60637 consistent gets
60494 physical reads
0 redo size
529 bytes sent via SQL*Net to
client
523 bytes received via SQL*Net
from client
2 SQL*Net roundtrips to/from
client
5 sorts (memory)
0 sorts (disk)
1 rows processed
10:22:14 SQL>
Consistent gets and the physical read are
the two important points I want to see. Now to check the feature, I have
re-started the instance.
SQL> select /*+ result_cache */ count(1) from JAY.RESULTCACHE1;
COUNT(1)
----------
16207591
Execution Plan
----------------------------------------------------------
Plan hash value: 821388131
------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 | 16648
(2)| 00:03:20 |
| 1 | RESULT CACHE | cbchvkkjudm1v8shgd97yfvwt4 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| RESULTCACHE1 | 16M| 16648
(2)| 00:03:20 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation
id):
------------------------------------------------------
1 -
column-count=1; dependencies=(JAY.RESULTCACHE1); attributes=(single-row);
name="select /*+ result_cache */ count(1) from JAY.RESULTCACHE1"
Note
-----
- dynamic
sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
60581 consistent gets
60394 physical reads
0 redo size
529 bytes sent via SQL*Net to
client
523 bytes received via SQL*Net
from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
For first time, we have consistent and
physical reads too. Now check for the second time.
SQL> select /*+ result_cache */ count(1) from JAY.RESULTCACHE1;
COUNT(1)
----------
16207591
Execution Plan
----------------------------------------------------------
Plan hash value: 821388131
------------------------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 | 16648
(2)| 00:03:20 |
| 1 | RESULT CACHE | cbchvkkjudm1v8shgd97yfvwt4 | |
| |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| RESULTCACHE1 | 16M| 16648
(2)| 00:03:20 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation
id):
------------------------------------------------------
1 -
column-count=1; dependencies=(JAY.RESULTCACHE1); attributes=(single-row);
name="select /*+ result_cache */ count(1) from JAY.RESULTCACHE1"
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
529 bytes sent via SQL*Net to
client
523 bytes received via SQL*Net
from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Below is
the overall result when I check for couple more time:
Elapsed: 00:00:14.85
consistent gets: 60581
physical reads: 60394
consistent gets: 60581
physical reads: 60394
Elapsed: 00:00:00.06
consistent gets: 0
physical reads: 0
consistent gets: 0
physical reads: 0
Elapsed: 00:00:00.04
consistent gets: 0
physical reads: 0
consistent gets: 0
physical reads: 0
Many will be confuse Result cache with
buffer pool and keep pool. But both are different things and that is for sure.
Result cache exist in shared pool and had
nothing to do with buffer pool. Also buffer pool cache the data block and
result cache, cache the result.
I check with the below to confirm that result cache exist inside the shared pool:
SQL> select * from v$sgastat where pool='shared
pool' and name like 'Result%';
POOL
NAME BYTES
------------ ------------------------------
----------
shared pool
Result Cache: State Objs
2896
shared pool
Result Cache
163976
shared pool
Result Cache: Memory Mgr
200
shared pool
Result Cache: Cache Mgr
208
shared pool
Result Cache: Bloom Fltr
2048
SQL>
Oracle provided the DBMS_RESULT_CACHE package
to administrator the result cache. The package can be used for:
STATUS: Display the current status of the
result package.
FLUSH: to flush the cache.
Report: to check the memory report.
SQL> select dbms_result_cache.status from dual;
STATUS
--------------------------------------------------------------------------------
ENABLED
Above
confirms result cache parameter is set to non zero value.
SQL> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
result_cache_max_size big integer 46560K
SQL>
SQL> set serveroutput on size 99999
SQL> exec
DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")
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
= 46560K bytes (46560 blocks)
Maximum Result Size = 2328K bytes (2328 blocks)
[Memory]
Total Memory = 169328 bytes [0.004% of the Shared
Pool]
... Fixed Memory = 5352 bytes [0.000% of the Shared
Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr
= 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 163976 bytes [0.003% of the
Shared Pool]
....... Overhead = 131208 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 24K bytes (3K slots)
........... Chunk Maps = 12K bytes
........... Miscellaneous = 28808 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>
SQL> exec DBMS_RESULT_CACHE.flush;
PL/SQL procedure successfully completed.
SQL>
When to use and when not to use:
Any change in the dependent object will
make entire result cached invalidated and it need to be flush out manually. But
with version 11.2, Oracle manage this job automatically.
In RAC, all instance managed its own result
cache. But invalidation of any of the instance cached result will be maintained
across all the other instances.
Too much reading of the cache will cause
latch and may also hit performance issue, so it may also need to be monitored.
SQL> select * from v$latchname where name like
'Result Cache%'
2 ;
LATCH#
NAME
HASH
----------
---------------------------------------------------------------- ----------
405
Result Cache: RC Latch 1054203712
406
Result Cache: SO Latch
986859868
407
Result Cache: MB Latch
995186388
SQL>
Also the background process that is responsible for this feature:
SQL> select description from V$BGPROCESS where
name like 'RC%';
DESCRIPTION
----------------------------------------------------------------
Result Cache: Background
SQL>
We need
to learn more for this feature. As always suggestions and corrections are
welcome.
Nice to Read ....
ReplyDeleteI did not understand - "Any change in the dependent object will make entire result cached invalidated and it need to be flush out manually. But with version 11.2, Oracle manage this job automatically. " .....
Hi Nipanjan,
Deleteif there is any change made in the table that is already in the cache, for 11gR1 result cache makes it invalid but not flush it out. But from 11gR2 onward, whenever their will be any change in the dependent object which is in the cache, result will be flushed automatically.