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.
Filed under: Performance Management | Tagged: 11g