Oracles function result cache (FRC) works, in simplified terms, by caching the result of a PL/SQL function. This can greatly improve performance, but it can also lead to performance problems.

I had a small discussion with a colleague about FRC, so I jotted down a few pros and cons for using it. Taking into account that the cache is invalidated when the tables it relies on is changed and that cache invalidation and eviction introduces overhead, this is the short list for evaluating FRC.

Obviously, the most gain of FRC comes when you have functions that are:

• “Deterministic”. That is, for the same input and data, they give the same result.
• Heavy, meaning they have some response time, due to data access or calculations
• Executed often
• Operating on data that is not too often updated, causing invalidation
• Having a limited set of inputs: Many different input combinations give a bloated cache and/or cache eviction

As a consequence of the invalidation mechanism, you should reconsider when:

• The sum of updates on the tables it depends on are high, resulting in a high frequency of invalidation – this may lead to latch contention
• The gain in savings is small
• The number of executions is low
• There’s a lot of different input combinations
• The result depends on “moving” variables like time: Comparisons with sysdate/systimestamp does not work well.
• You can do it just as easily in SQL
• You have middle-tier frameworks that saves data “all over the place” as a part of a generic persistence method. (Master-detail classes that update all rows in both tables) This is basically the same as the first bullet point.

In between, there’s a grey area. You may have result cached functions relying on relatively frequently updated tables, where you have fewer, but more heavy queries that may benefit significantly from the cache. In these cases, the entries are usually invalid/not cached, but becomes valid once in a while when your report runs. In these cases the function is so heavy, that just a few cache hits may justify it. But it will have to be tested out in each separate case. It may also be the case that you use the cached function primarily at times when transactions are fewer, like at night time.

Things, off the top of my head that FRC doesn’t work with is:

• Functions with out-parameters. It supports just the scalar return value of the function
• Functions referencing data over database links.