In 11g, oracle has introduced a new memory sub component which is called result cache. Result cache was introduced to cache the results of most frequently accessed select statements.
In order to configure result cache, we need to set the following init.ora parameters :
1.result_cache_mode=manual/auto/force(default is manual)
2.result_cache_max_size
3.result_cache_max_result(default value 5%)
Manual means, oracle is not going to cache the results of SQL statements. If we want to cache the results, we need to pass a hint i.e
result_cache with the SQL statements.
>select /*+ result cache */ e.ename,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno;
Auto means, oracle optimizer automatically determines for which statements it has to cache based on
the frequency of the execution, cost of the execution and how frequently the objects are getting modified.
Force means oracle caches the results of all SQL statements.
A single query result by default can occupy only 5% of the result cache.
If AMM(Automatic Memory Management)is enabled, by default result cache occupies 0.25% of memory target value.
If ASMM is enabled, by default 0.5% of sga_target will be allocated to result cache.
If shared_pool_size parameter is set manually, by default result cache occupies 1% of shared pool.
We can also bypass result cache with the help of the hint "no_result_cache".
oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
sys>startup
sys>show parameter result_cache
sys>set serveroutput on
sys>exec dbms_result_cache.memory_report;
sys>exec dbms_result_cache.flush; -- to clear the result cache.
scott>set autotrace traceonly
scott>select /*+ result_cache */ * from salgrade;
scott>create table sales(saleid number) result_Cache(mode default);
scott>alter table sales result_Cache(mode force);
sys>exec dbms_result_cache.flush;
