The result cache is a place in memory where Oracle stores results. Subsequent queries can then obtain these results quickly. This cache encompasses both SQL queries and PL/SQL functions. Note that the cache can become invalid when the source data of the queries changes.The SQL query result cache is stored in the SGA. The results can be from just fragments of a query. You use the hint /*+ result_cache */ to advise Oracle that you would like to make use of the cache. Check out the DBMS_RESULT_CACHE built-in package to clear or inspect the contents of the cache.
The PL/SQL function result cache stores data across sessions. It also is stored in the SGA. When defining a function, use the RELIES_ON clause to identify tables whose changes may invalidate the results of the cache.
Next time I will start by explaining how you can extract your PL/SQL source code from the database.
