On Oracle Database 11g

Our technologist takes a look at the server results and PL/SQL function caches.

September/October 2007

Over the next few months, I'll be looking at new features coming out with the latest release of Oracle Database, Oracle Database 11g. There are so many to choose from that it was hard to pick a few to start with. So I've been talking with Bryn Llewellyn, the PL/SQL product manager, and he filled me in on a couple of great new SQL and PL/SQL features.

Oracle Database 11g raises the bar on data caching. In the past, Oracle Database cached database blocks. It could cache these blocks in various places, such as the default pool, a keep buffer pool, or a recycle buffer pool. But it always cached blocks of data—the building blocks used to build result sets.

Starting with Oracle Database 11g, the database can also now cache result sets! If you have a query that is executed over and over again against slowly or never-changing data, you will find the new server results cache to be of great interest. This is a feature from which virtually every application can and will benefit.

In a nutshell, the concept is this: when you run a query, Oracle Database 11g will first see if the results of that query have already been computed and cached by some session or user, and if so, it will retrieve the answer from the server result cache instead of gathering all of the database blocks and computing the answer all over again from scratch. The first execution of the query will take as long as usual (because it is computing the result), but subsequent executions will appear to be instantaneous, because the answer is being returned immediately—instead of being computed and then returned.

I view this feature as being analogous to a just-in-time materialized view. In Oracle8i Database Release 1, Oracle introduced the concept of materialized views, whereby DBAs could create a summary table, much as they might create an index, and the database optimizer would recognize that this summary table existed and, when possible, use it to respond to queries rather than query and process the detailed information stored in the base tables. This method of operation works well but is rather static, much like an indexing scheme. The Oracle Database 11g server results cache, however, is in effect creating and maintaining materialized views on the fly, using a cache. The server result cache (a just-in-time materialized view) is populated as needed without DBA intervention.

By way of example, I'll copy the ALL_OBJECTS table into a table:

SQL> create table t
  2  as
  3  select *
  4    from all_objects;
Table created.

Now I'll create a small function that executes a report—a query against this table. To clearly demonstrate the difference in performance of this query, I'll do nothing inside the loop (where I would normally be printing the rows and performing some computation):

SQL> create or replace procedure
  2  my_function
  3  as
  4  begin
  5    for x in
  6    (select owner,
  7              object_type,
  8              count(*) cnt
  9       from t
 10          group by owner, object_type
 11          order by owner, object_type )
 12    loop
 13          -- do_something
 14          null;
 15    end loop;
 16  end;
 17  /
Procedure created.

Now I execute this process three times, noting the time it takes to execute each invocation:

SQL> set timing on
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.54
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
SQL> set timing off

Note that the first execution took a significant amount of time, about 1.5 seconds, due to the physical I/O required to read the data from disk into the conventional buffer cache. The second and third executions took much less time, about 0.1 second, because the blocks needed to answer the query were found in the cache, not on disk. Compare what happens if I add a "cache the results" hint to the query:

SQL> create or replace procedure
  2  my_function
  3  as
  4  begin
  5    for x in
  6    (select /*+ result_cache */
  7               owner,
  8               object_type,
  9               count(*) cnt
 10       from t
 11          group by owner, object_type
 12          order by owner, object_type )
 13    loop
 14          -- do_something
 15          null;
 16    end loop;
 17  end;
 18  /
Procedure created.
SQL> set timing on
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> set timing off

Note how the first execution took about 0.1 second, because the answer was assembled for the first execution, but that the subsequent executions were blindingly fast—sometimes so fast that they appear instantaneous.

The nice thing about this is that the cache is invalidated and refreshed by the database—and the process is completely transparent to the application. The application need not worry about "stale" or invalid results. For example, if I update a single row, thus changing the results-—

SQL> update t
  2  set owner = lower(owner)
  3  where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
—I observe the following behavior:
SQL> set timing on
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> exec my_function
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> set timing off

Note that the first execution after the UPDATE went back up to about 0.1 second of execution time because it had to build the new answer. The subsequent executions benefit from this work and appear instantaneous.

I'm sure that if you sit back and ponder your own applications, you will be able to think of more than one place where the server results cache feature will come in handy. It provides many of the benefits of some materialized views, but without the setup and administrative overhead associated with them.

As they say on late night television, "If you thought that was really good, wait till you see this!" Oracle Database 11g has a new PL/SQL function results cache as well. Whereas the aforementioned server results cache is about caching SQL result sets, this extension of the server results cache feature caches the results of PL/SQL function and procedure calls.

In the past, if you called a PL/SQL function 1,000 times and each function call consumed 1 second, the 1,000 calls would take 1,000 seconds. With this new function results cache feature, depending on the inputs to the function and whether the data underlying the function changes, 1,000 function calls could take about 1 second, total. A small example will be useful: I'll create two functions, identical except in name and compiler parameter options. They will both access the previously created table T:

SQL> create or replace
  2  function not_cached
  3  ( p_owner in varchar2 )
  4  return number
  5  as
  6          l_cnt number;
  7  begin
  8          select count(*)
  9            into l_cnt
 10            from t
 11           where owner = p_owner;
 12          dbms_lock.sleep(1);
 13          return l_cnt;
 14  end;
 15  /
Function created.
SQL> create or replace
  2  function cached
  3  ( p_owner in varchar2 )
  4  return number
  5  result_cache
  6  relies_on(T)
  7  as
  8          l_cnt number;
  9  begin
 10          select count(*)
 11            into l_cnt
 12            from t
 13           where owner = p_owner;
 14          dbms_lock.sleep(1);
 15          return l_cnt;
 16  end;
 17  /
Function created.

The only difference in the functions besides the names are the compiler parameters RESULT_CACHE and RELIES_ON. The RESULT_CACHE directive tells Oracle Database that you would like the answers from this function to be saved, so that if someone invokes this function again with the same inputs, the code should not actually be executed but, rather, that person should just receive the already known answer. The RELIES_ON clause tells the database when to invalidate this function result cache value—in this case, if the table T is modified (which would change the answer from my cached function, hence I need it to be re-executed). Note that for effect, I've put a 1-second sleep in both functions and that that sleep call will make it painfully obvious when the function is actually invoked and when the results are reused.

I start by calling the conventional (not_cached) function three times with timing on:

SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.93
SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.29
SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.07

As you can see, each invocation takes at least one second—the work done by the function plus the SQL it executes take a little more than a second. Now I try out the cached version of this function:

SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.09
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

As you can see, the first execution takes a little more than a second, just like the noncached versions, but the subsequent ones run at light speed, simply because the cached function is, in fact, not being executed. If I modify the table this function relies on or change the inputs I use, I can see that the database does the right thing:

SQL> update t set owner = initcap(owner) where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.25

This first execution of the cached function takes more than a second, because it must refresh the result cache, but subsequent executions benefit from this cached result:

SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

And just to show that the PL/SQL function results cache understands that different inputs result in different outputs, I can call the cached function with a different username:

SQL> exec dbms_output.put_line( cached( 'SYS' ) );
29339
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.21
SQL> exec dbms_output.put_line( cached( 'SYS' ) );
29339
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

Note that the first execution takes a relatively long time (to build the result cache), a subsequent execution is fast, and calling the function with a different username does not invalidate or throw away other cached results:

SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00

Function calls that use SCOTT as the input will remain fast until the cache must be invalidated or until it runs out of room and must make way for other elements.

Again, this is a feature that can be implemented without massive rearchitecting of your applications—in fact, without rearchitecting at all. The compiler parameter RESULT_CACHE can activate this feature, and any client that invokes the function will benefit. For example, informal tests with Oracle Application Express show an approximately 15 percent decrease in runtime in a standard benchmark harness—your mileage may vary, of course!

In the July/August 2007 issue of Oracle Magazine, I wrote about a certain PL/SQL programming construct, the WHEN OTHERS exception handler. I wrote

There's a programming construct, WHEN OTHERS, in PL/SQL that I wish weren't there.

Well, I knew Oracle could never really remove this language feature—but Oracle certainly has made it safer. Just to reiterate why I wish this programming construct did not exist, I'll quote myself again:

But the problem I see is that far too many people use the WHEN OTHERS clause without following it by a RAISE or RAISE_APPLICATION_ERROR call. That effectively hides the error. In reality, the error happened, but it was not handled in any meaningful way; it was just ignored—silently. The invoker of your code has no idea that something went horribly wrong and that your code failed, and he or she typically thinks that everything was, in fact, successful.

Many times—many more times than I can recall or count —"quirky" behavior in a PL/SQL program unit is found to be caused by the improper use of a WHEN OTHERS clause—one that is not followed by RAISE. The error was hidden, error handling was effectively disabled, and the outcome is typically logically corrupt data (the application did not do its job) or the wrong answer.

Now, in Oracle Database 11g, I can easily find the offending bits of code. When I suspect that an exception is being raised and hidden in this fashion, I'll be able to verify my hunch rapidly. Consider this innocuous procedure:

SQL> create table t( x varchar2(4000) );
Table created.
SQL> create or replace
  2  procedure maintain_t
  3  ( p_str in varchar2 )
  4  as
  5  begin
  6    insert into t
  7    ( x ) values
  8    ( p_str );
  9  exception
 10    when others
 11    then
 12      -- call some log_error() routine
 13      null;
 14  end;
 15  /
Procedure created.

It's as simple as can be. Nothing should go wrong, but in case it does, I'll log the error by using a utility routine I've developed. That'll write a nice error message somewhere (hopefully!), but whoever invokes this routine will have no idea that an exception that was not expected—and that could not be fixed—happened. (I see code like this time after time after time.) Now when someone calls this routine,

SQL> exec maintain_t( rpad( 'x', 4001,  'x' ) ); PL/SQL procedure successfully completed. 

it sure looks successful, but it wasn't:

SQL> select * from t;
no rows selected

SQL> alter procedure maintain_t compile
  2  PLSQL_Warnings = 'enable:all'
  3  reuse settings
  4  /
SP2-0805: Procedure altered with
compilation warnings
SQL> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:
LINE/COL     ERROR
----------   -----------------------------
9/8          PLW-06009: procedure
             "MAINTAIN_T" OTHERS handler
             does not end in RAISE or
             RAISE_APPLICATION_ERROR 

And instantly you'll have a list of applications—and the source code lines—to immediately go to and add a simple RAISE to see where this hidden error is coming from. Job done.

There's a saying that goes, "It's the little things in life that really make a difference." So I'll close with a little thing that will make your coding life a bit easier and the PL/SQL language a bit more complete:

SQL> create sequence this_is_nice;
Sequence created.
SQL> declare
  2    n number;
  3  begin
  4    n := this_is_nice.nextval;
  5    dbms_output.put_line( n );
  6  end;
  7  /
1
PL/SQL procedure successfully completed.

Compare that to what would have happened in Oracle Database 10g and prior releases. In Oracle Database 11g, there's no more selecting a sequence FROM DUAL. PL/SQL is that much more complete.