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

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.

Cache It
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

SQLset 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

Full article...