Result cache in oracle 11g

DB version:

*Result cache:
The result cache stores the results of SQL queries and PL/SQL functions in an area called Result Cache Memory in the shared pool.

Server result cache is detetermined by below 3 parameters:
result_cache_max_result              integer     5
result_cache_max_size                big integer 384K
result_cache_mode string      MANUAL
if result_cache_mode is AUTO, no hint is requred to cache the sql result. It is automatically done by oracle.

Client result cache is detemined by below 2 parameters:
client_result_cache_lag              big integer 3000
client_result_cache_size big integer 0 (32K is minimum,less than it will disable the feature)
When client result caching is enabled, the query result set can be cached on the client or on the server or both

Sql Example:
SYS@ORA11G>select/*+ result_cache */ count(*) from t.t2 where object_id>10000;

Both client side and server side require same hint result_cache when result_cache_mode is MANUAL.

Some SQL statements to check Result Cache in 11gR2:

–result cache object status
–Enable Server SQL Result Cache
alter system set result_cache_max_size=100M;
alter system set result_cache_mode = FORCE;

–Disable Server SQL Result Cache
alter system set result_cache_max_size=0;
alter system set result_cache_mode = MANUAL;
exec dbms_result_cache.flush;

–Check Result Cache statistics
col name for a40
col value for a30
select * from v$result_cache_statistics;
select * from v$sgastat where pool=’shared pool’ and NAME like ‘Result%’;
set serveroutput on
exec dbms_result_cache.memory_report;

–Check Cached object status
select status,count(*) from v$result_cache_objects group by status;
select name,status,cache_id,scan_count from v$result_cache_objects where namespace=’SQL’ and rownum

I have another blog entry about it: 11gr2 SQL result cache test notes


About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

5 Responses to Result cache in oracle 11g

  1. neworacledba says:

    this is an informative post…good job

  2. Sun says:

    How could we remove only one result_cache?

    • Alex Zeng says:

      As I know there is no such commands to do so. However, any DML on the table will invalidate it.

  3. Alex Zeng says:

    There are some very good blogs about result cache. If you want to use it in production, you may want to read them.
    The 10 seconds wait time is controlled by parameter hidden “_result_cache_timeout”.

  4. Pingback: 11gr2 SQL result cache test notes « Oracle Explorer: Standing on the shoulders of giants

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: