PL/SQL Function Cache

SQL Query Result Cache here

This caching mechanism provides you with a language-supported and system-managed means for storing the results of PL/SQL functions in a System Global Area (SGA), which is available to every session that runs your application.

Oracle Database 11g provides the ability to mark a PL/SQL function to indicate that its result should be cached to allow lookup, rather than recalculation, on the next access when the same parameter values are called. This function result cache saves significant space and time.

This is done transparently using the input parameters as the lookup key. The cache is instance wide so that all distinct sessions invoking the function benefit. If the result for a given set of parameters changes, you can use constructs to invalidate the cache entry so that it will be properly recalculated on the next access.

This feature is especially useful when the function returns a value that is calculated from data selected from schema-level tables. For such uses, the invalidation constructs are simple and declarative.

You can include syntax in the source text of a PL/SQL function to request that its results be cached and, to ensure correctness, that the cache be purged when any of a list of tables experiences DML.

When a particular invocation of the result-cached function is a cache hit, then the function body is not executed; instead, the cached value is returned immediately.

 

Usage Notes

·       If function execution results in an unhandled exception, the exception result is not stored in the cache.

·       The body of a result-cached function executes:

·       The first time a session on this database instance calls the function with these parameter values

·       When the cached result for these parameter values is invalid. A cashed result becomes invalid when any database object specified in the RELIES_ONclause of the function definition changes.

·       When the cached result for these parameter values has aged out. If the system needs memory, it might discard the oldest cached values.

·       When the function bypasses the cache

·       The function should not have any side effects.

·       The function should not depend on session-specific settings.

The function should not depend on session-specific application contexts.

 

In the example shown in the slide, the productName function has result caching enabled through the RESULT_CACHE option in the function declaration. In this example, the RELIES_ON clause is used to identify the PRODUCT_DESCRIPTIONS table on which the function results depend.