Feuertip #26: Help save our planet with the result cache

Software is powered by hardware. Hardware is still, for the most part, powered by fossil fuels. And even when it is mostly powered by renewable sources, we need to carefully consume this energy to make sure that it is used in the best way to combat the climate crisis.

That’s what it means to be facing “code red for the planet.”

Each and every one of us should be looking at each and every way to slow down planetary warming and stop human-caused extinctions. Right?

So, yes, avoid single-use plastic. Pick up and throw away plastic when you find it on the ground. Volunteer at parks and preserves to help restore native ecosystems.

And use the Oracle Database result cache feature.

Huh?

Well, that’s just one example of a feature that reduces CPU consumption. And that’s a way we as software developers can help (and offset the insanity of Bitcoin energy consumption).

It’s also a very cool feature. The basic idea is this: if the result of calling a function (or executing a query) will be the same as the last time you called it, why execute the function again? Why not return that same result? Or as I express it in an image:

This is merely to say: your users will thank you, too, because your application will get faster.

Consider the following function:

function in_production return boolean
is 
   l_state app_config.app_state%type;
begin
   select app_state
     into l_state
     from app_config;
   return l_state = 'PRODUCTION';
end;

I’ve used functions like this to, among other things, make sure that when I am testing email functionality, I never spam real users – like I have done when I didn’t do this.

Here’s an example of using the function:

begin
   send_email (p_to => 
                  case when in_production then 'sfeuerstein@insum.ca'
                       else l_user.email_address
                       end,
               ...);

The problem with this approach is that I now execute that query every time I want to send an email (and who knows what else). If I do lots of emailing, it could be a problem.

OK, then let’s fix that:

function in_production return boolean
   result_cache
is 
l_state app_config.app_state%type;
begin
   select app_state
     into l_state
     from app_config;
   return l_state = 'PRODUCTION';
end;

I add just that one keyword to the header of the function and now here’s how my application behaves:

1. The first time in_production is called in that database instance, the PL/SQL runtime engine (henceforth referred to as PL/SQL) calls the function and gets the result.
2. It stores the result in the result cache pool along with all of the parameter values (of which there are none).
3. From that point on, whenever any user in that instance calls in_production, the value in the cache is returned without executing the query.
4. If a change is made to the table and committed, then go back to step #1.

Since the app_config table rarely, if ever, changes in production, there’s a good chance that function will only be executed once until the database is restarted.

Pretty cool, right?

Now, this is one of those features that is simultaneously easy to use, powerful, and dangerous.

Easy to use:

All you have to do is add the result_cache keyword to the header of the function (if in a package, it must be added in both specification and body). That’s it. A wonderfully declarative feature of this fantastic procedural language.

Compare this, for example, to the deep, challenging transformation you must make to your code to make full use of forall.

Powerful:

Improve performance and reduce CPU consumption for a wide variety of scenarios:

• Queries: you can add a /*+ result_cache */ hint to ask the SQL engine to treat the query just like PL/SQL treats a result-cached function.
• Useful for both static datasets (like materialized views) and any table that is queried much more frequently (and the same rows) than it is updated.
• Not limited to functions that execute SQL. Generally applicable to any deterministic or “near-deterministic” (just made that up) function.

Dangerous:

• Applications can be blocked by contention over latches on the result cache pool.
• If the result cache pool is too small, the least recently used algorithm can cancel out performance benefits.
• Careless application of the result_cache keyword can result in bad data being delivered to your users.

Allow me to repeat that:

Careless application of the result_cache keyword can result in bad data being delivered to your users.

In this Feuertip, I will introduce the feature and discuss its applicability.

In a follow-up, I’ll explore some of the dangers.

Further Study

ORACLE-BASE on the result cache

LiveSQL scripts

Differences between result cache and deterministic keyword (my blog)

Watch the full Feuertips episode here