Introduction to result cache

The term cache is not new in the Oracle family. Prior to Oracle 11g, we have often heard of caching queries in SGA. Oracle 11g makes major enhancements in this area and evolves two new caching features namely, server result cache and client result cache.

Result cache allows the storage of result sets from a SQL query or PL/SQL function in a specific memory location, known as cache memory, along with the query specifications. Now, whenever the same SQL or the function (with the same specifications) is re-executed, the server picks up the result directly from the cache memory. The approach bypasses the SQL or function execution process—thus, saving a substantial amount of time. The server makes almost no effort in executing the SQL or PL/SQL function by employing cache memory for frequently executing queries and functions.

The specific memory location or cache memory is a new SGA memory component, dedicated for cached results. The query specification includes the...