Result cache in oracle 11g

DB version: 11.1.0.6

*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.

Leave a Reply