New to Oracle 11g: The Server Result Cache

November 5, 2012 at 12:30 pm | Posted in Oracle, Technical Tips | Leave a comment
Tags: ,

If you’ve been using the Oracle database for as many years as I have, I’m sure you’ve noticed how Oracle tends to tweak the contents of the SGA from one release to the next. Oracle release 11g is no exception to that trend, and starting with this release we now have two more caches in the Shared Pool: one called the SQL Query Result Cache and another called the PL/SQL Function Result Cache. These two caches differ from virtually all of the remaining memory areas of the SGA in that the data stored in these two buffers are NOT blocks of data. Rather, the data stored is stored in terms of returned rows from either a SQL or PL/SQL command.

Why might Oracle choose to store the results of a user’s SQL command or the return results of a user’s PL/SQL function? Simply put, if a request for a query comes along that is identical to a previous query request that Oracle had stored in one of these buffers, then it’s possible for Oracle to re-use that prior result set. This technique is even better than checking to see if the commands are identical, so you can potentially save reparsing. If the prior result set matches the new request, and the prior result set is stored in the buffer, then we can skip the effort of parsing as well as any logical or physical reads to bring blocks into the database buffer cache. Obviously, Oracle has to be sure that the result set is still accurate before deciding to use it based on a subsequent SQL statement.

Oracle has the overall responsibility for managing these two new memory areas, but you can influence the optimizer using hints such as RESULT_CACHE, the DBMS_RESULT_CACHE package, and the RESULT_CACHE_MODE init.ora parameter. The following link in the Oracle 11gR2 documentation set,
http://docs.oracle.com/cd/E11882_01/server.112/e25789/memory.htm#CNCPT1920,
will take you to an overview explanation of these two buffer caches. It will also provide links to the details of the SQL Result Cache and another to the PL/SQL Function Result Cache.

Until next time,
— Bob the OrclTestGuy

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: