Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 301

How long does this remain invalid? Like other cache areas in an Oracle Database instance, the memory
management of the result cache section is also the same; the invalid results will be moved to the dirty list and will
be flushed out of the cache section as new data gets loaded to the cache section. When the query executes again, a
new set of data is loaded into the result cache. The required result should always be the most current committed data
to meet Read Consistency. Frequent invalidations and reloading can cause contention for the result cache and will
trigger an ' enq: RC - Result Cache: Contention ' wait event.
If the result cache for a query is invalidated on instance 1, the identical result caches, if they exist, on ALL
instances will also be invalidated. Using the results cache feature only makes sense if the underlying tables/data
do not change for long periods of time. As a best practice, the result cache feature is more suitable for a data
warehouse environment.
Function Result Cache
Very similar to the behavior of the query result cache feature discussed earlier, the function result cache caches all
the results returned by the user-defined function in the same result cache section of the shared pool. The result cache
area is shared by both the results from the user-defined function and the query that uses /*+ RESULT CACHE */ hint.
Unlike the /*+ RESULT CACHE */ hint used in the query option earlier, the result cache is enabled at the function
level by an attribute RESULT_CACHE with the function definition.
FUNCTION <name> <INPUT PARAMETERS>
RESULT_CACHE
Apart from the result cache parameter, there is another optional parameter called RELIES_ON< object name > that
is used to inform the optimizer regarding the base table that the function depends on. RELIES_ON will help invalidate
any data in the result cache buffer should the base table referenced in the function change. Examples always help to
explain concepts better, so let's do a workshop.
Workshop
In this workshop, the discussions will focus on defining and validating the function result cache feature.
Step 1
Create a function with the RESULT_CACHE and RELIES_ON attributes. The function below returns the balance on
account for a customer:
CREATE OR REPLACE FUNCTION totalbalance(p_id IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON(CUSTOMER)
AS
l_cust_total NUMBER := 0;
BEGIN -- get the total balance for the customer id passed in
SELECT SUM(c_balance)
INTO l_cust_total
FROM customer
WHERE c_id = p_id;
-- return the result
RETURN l_cust_total;
END;
/