Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Cross-Session PL/SQL Function Result Cache
Since 11gR1 we have simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA:The cross-session PL/SQL function result cache.
The results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in a SQL query, or within a loop in PL/SQL.
Ok, but how to do this? It’s as simple as adding the RESULT_CACHE clause:
CREATE OR REPLACE FUNCTION procedure_example (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE
The RELIES_ON may be set in 11gR1 but is unnecessary in 11.2 as it automatically tracks dependencies and invalidates the cached results when necessary.
Nice, right?
Cheers!
