PL/SQL Function Result Cache

This entry is part 6 of 8 in the series New Pl/Sql features 11g

With release 1 of 11g Oracle introduced PL/SQL Function Result Cache. As of release 2 of 11g Oracle further improved PL/SQL Function Result Cache. I’m not saying it’s the best invention since slices bread, but it’s up there. This really is a very nifty function I intent to use a lot. If you’re still on 10g then combined with conditional compiling you can already start preparing for this feature today!

Simply put: any table function returning data from tables, that are queried more often then updated benefits from the use of Function result Cache

So what is it?

  • session shared
  • (semi) automatic
  • function result caching
  • stored in SGA

Why is this so good?

  • Many (good) programs incorporate code wrapping of (semi) static values and sql queries in function calls
  • Often these function calls are plentiful and performed in every session, fetching and returning the same data over and over again
  • Function Result Cache allows for easy caching of the requested data over all sessions

How does it work?

  • You tell Oracle to Result Cache your function
  • Oracle caches the function result the first time a distinct function call is made
  • If the same function call is made again in any session Oracle forgoes execution of the function and simply returns the prior returned value

How is the cache invalidated/time out

  • In 10.1 you have to tell if the validity of the data depends on tables or views with the RELIES ON clause (not needed any more as of 11gR2)
  • As long as no updates are made to the depended tables, Oracle simply returns the data from cache.

Let’s look at an example

Scott creates a function to fetch the job type for a employee and grants the use of the function to his buddy tiger.

Scott executes the function himself

Notice the dbms output. The function is pulled from table and returned. But also stored in cache!
SERVER OUTPUT:
---------------------
fetching job name
function result: SALESMAN

Now tiger executes the same function for the same empno

Now look at the output. The dbms output is missing. That’s function result cache!

SERVER OUTPUT:
---------------------
function result: SALESMAN

The body of the function was never executed. The function results was pulled from cache instead of from table.

Now let’s take this one step further. Scott updates the salary of a completely different employee.

update emp set sal = '900' where empno= 7369;
1 rows updated
commit;
commited

Now if Tiger (or Scott) executes the same function as earlier for for the same empno which wasn’t updated

The output is fetched from table again.

SERVER OUTPUT:
---------------------
fetching job name
function result: SALESMAN

Scott’s update invalidated the function result cache and Oracle will fetch from table again.

BE AWARE: Take caution using Function Result Caching combined with Virtual Private Database features!! This will have some undesired side effects!

Series Navigation<< Use named and mixed notation from SQLPredictable execution order of triggers >>

Posted in Oracle, Pl/Sql Tagged with: , ,

Leave a Reply