Great new SQL and PL/SQL features in 11g

Oracle Database 11g

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.