Implementing result cache in PL/SQL

Result caching in PL/SQL is second component of server-side caching in Oracle 11g. As we discussed briefly in the first section, results of frequently used PL/SQL functions can be retained at the server cache. The PL/SQL result cache feature uses the same infrastructure as the server result cache. When a function marked for result cache is executed, its result is cached at the server cache along with the parameters. The server picks up the result from the cache memory, if the same function is executed with the same parameters. In this way, the server saves a handful of time by bypassing the execution of the function body every time it is invoked, resulting into enhanced performance. The function can be a standalone, packaged, or locally declared (in a subprogram, not in anonymous PL/SQL block) one.

However, the cached result gets invalidated when the function or its referencing tables undergo a structural change followed by recompilation. The cached result...