28 January 2014

7089 views

3 0

Result Cache (Part 1)

First, solve the problem. Then, write the code. – John Johnson We are always looking for ways to do things faster. Sure you can use more CPU power or more memory in the database server, but there’s a limit to that approach. Be it the amount of money being spent, the limits of the [current] CPU power or other limits

First, solve the problem. Then, write the code.
– John Johnson

We are always looking for ways to do things faster. Sure you can use more CPU power or more memory in the database server, but there’s a limit to that approach. Be it the amount of money being spent, the limits of the [current] CPU power or other limits. The fasted way of doing something is by not doing it at all. That sounds kind of strange, but actually that is what result caching does. Well, not completely, but at least it doesn’t want to run the same code twice.

Single Point Of Definition

As a developer you are (or should be) lazy. Not lazy as in not willing to do work, but lazy as in not willing to write the same code twice. That’s why we write procedures and functions. As soon as you write a particular piece of code and you think: ‘Hey, this looks familiar’ you probably start searching for the already existing code and either copy-paste it (bad idea) or figure out a way to re-use the existing code by putting it into a procedure or function of its own.
Result caching is based on pretty much the same idea, but in this case it is not the code, but the result of the code.

Deterministic

The idea behind the function result cache is that if you call a function with the same parameters, then the result of the function is the same.
Consider this example function:

CREATE OR REPLACE FUNCTION

multiply( operand_a IN NUMBER

, operand_b IN NUMBER) RETURN NUMBER

IS

BEGIN

RETURN operand_a * operand_b;

END multiply;

If you call this procedure, depending on the input values, you will always get the same result. This function is deterministic. We can add this to the function by adding the DETERMINISIC keyword. Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments. You should not specify this option if your code relies on the value of a package variable or a field in a table. If you call the function, Oracle may choose not to execute it but just return the already cached result. This may lead to unpredictable results.
Another issue with a deterministic function is that the cache is only session wide. If another session runs the same code with the same parameters the code is still executed. The cache is not shared between sessions.

Caching

Another way to cache the results is by using package variables. This is especially useful when you want to avoid the context switching the Oracle server executes whenever you execute a SQL statement. You can read more on caching in these articles:

The same issue as with a deterministic function arises here. The cache is just for the current session and if the data is changed during the session or at least since the cache has been built than the cache will return old or stale data. You would have to write tons of code the keep the cache fresh or up to date. Intersession communication to make sure the cache gets refreshed when the data is altered, refresh code for the cache, or at least part of the cache. A whole lot of work and probably not something you will get to work flawlessly. Even if you’re the best PL/SQL Developer in the world.

Function Result Cache

This is where Oracle comes to the rescue. Since version 11G release 1 you have the possibility to add the keyword RESULT_CACHE to your function. This tells Oracle to cache the result across sessions. If your function relies on data from tables then you should also include the RELIES_ON(<table_name>). This way the cache gets invalidated automatically when the data in the table changes. This option has become obsolete since version 11G release 2. The database figures out where the function relies on. You can still include the relies_on clause, but it will be for documentation purposes only.
This way Oracle will cache the results of your function, not only for you session, but across sessions. Another great thing about this is when your function is dependent on a database table and the underlying data changes (either in your session or in another session) then the cache will be invalidated and it will be repopulated.
Functions don’t have to be dependent on tables to use this feature.
Let’s create two functions:

CREATE OR REPLACE FUNCTION frc_multiply(left_in IN NUMBER

, right_in IN NUMBER) RETURN NUMBER

IS

BEGIN

dbms_lock.sleep(1);

RETURN left_in * right_in;

END frc_multiply;

And:

CREATE OR REPLACE FUNCTION frc_multiply_c(left_in IN NUMBER

, right_in IN NUMBER) RETURN NUMBER RESULT_CACHE

IS

BEGIN

dbms_lock.sleep(1);

RETURN left_in * right_in;

END frc_multiply_c;

If we call these functions over and over again

DECLARE

c_iterations CONSTANT pls_integer := 5;

l_result NUMBER;

BEGIN

timer.snap;

FOR iterations IN 1 .. c_iterations LOOP

l_result := frc_multiply(7,6);

END LOOP;

timer.show;

timer.snap;

FOR iterations IN 1 .. c_iterations LOOP

l_result := frc_multiply_c(7,6);

END LOOP;

timer.show;

END;

You will notice the Result Cached function gets called just once.

[elapsed] 5.08 seconds

[elapsed] 1.05 seconds

PL/SQL procedure successfully completed

This function is only executed the first time it is called, the consecutive calls just return the results.

In Part 2 I will investigate queries and how to check and set the status of the cache.

Result Cache (Part 1)

Single Point Of Definition

Deterministic

Caching

Function Result Cache

Subscribe for more articles

Rate this article

Patrick Barel

Related articles

The Importance of Caching

Result Cache (Part 2)

A Guide to INSERT, UPDATE, and DELETE Statements in Oracle

Tags