SQL Result Set Cache in Oracle 11gR2

What is SQL Result Set Caching?

Like the name suggesting, SQL Result Set Caching provides a mechanism to cache result sets of a SQL query in the memory; specifically, in a memory structure inside the shared pool. This is not to be confused with caching database blocks in the buffer cache; SQL Result Set Caching caches results of SQL statements. The goal is to reduce round trips to the database and improve performance.

Initialization Parameters and Hints

The initialization parameter that governs SQL Result Set caching behavior is “RESULT_CACHE_MODE”.

From the Oracle documentation:

• When set to MANUAL, which is the default, a SQL query result set will only be considered for potential caching if the query itself specifies the “RESULT_CACHE” hint
• When set to FORCE, a query’s result set will always be cached unless the query specifies the NO_RESULT_CACHE hint
• When set to AUTO, Oracle will use an internal algorithm to determine whether a query’s result set will be cached based on expected benefit. We’ll look at this in detail later

Here’s how it looks in our XRAC database:

As we can see, there are some other parameters of note:

• RESULT_CACHE_MAX_SIZE specifies the amount of memory to reserve for the result set cache. Regardless of how large you set this value, Oracle documentation states that it’s not allowed to go above 75% of the total shared pool size – we’ll test this later
• RESULT_CACHE_MAX_RESULT specifies the percentage of the overall result set cache that can be used for a single query to populate the result set cache

Measuring SQL Result Set Cache using DBMS_RESULT_CACHE

The report below shows how to measure SQL Result Set Cache behavior, globally:

From the above, we can see that the maximum result cache size is 4064k. We can also confirm the RESULT-SET_MAX_SIZE as 203k/4064k =~ 5%.

Let’s purge the result set cache and re-run our report:

As you can see, flushing the result set cache zeroes out the cache and all related statistics.

A Simple Test

Now, let’s run a test using a hint:

We can see that the query ran in 0.40 seconds – let’s check our SQL Result Cache report:

Now let’s the same query with SET AUTOTRACE on.

The above shows a couple of interesting things:

1. The completion time went down to 0.01 seconds from 0.40 seconds.

2. The execution plan shows a RESULT CACHE operation, which indicates that SQL Result Cache was being used.

Let’s flush our buffer cache and see the impact:

As we can see, it still ran remarkably fast and did so with zero logical or physical reads.

Now let’s flush our result set cache and check the behavior. In the test below, I’ll exclude the RESULT_CACHE hint:

As this section should demonstrate, we’ve been able to get amazing performance from SQL Result Set caching.

Identifying SQL Result Set cache behavior in Execution Plans

The previous section demonstrated how to identify SQL Result Set Cache behavior in an execution plan.

A more complex test

In this section, we’ll test the impact of SQL Result Set Caching with a larger result set and attempt to go over the 203k size barrier, as specified by (RESULT_SET_MAX_SIZE) * (RESULT_SET_MAX_RESULT). In this test, we’ll use a table called DART_CREQ_STATS, which is about 3MB in size – less than the value for RESULT_SET_MAX_SIZE but more than RESULT_SET_MAX_RESULT:

Let’s run our test:

As we can see, even on the first query with the hint, we see a RESULT CACHE operation in the SQL statement. The query executed in 1.07 seconds, retrieving 55,967 rows. Now let’s run again without the hint:

From this, the only benefit we get over the first test is the savings in physical IO.

Playing with RESULT_CACHE_MAX_RESULT

Using the previous test as a reference let’s now increase our value of RESULT_SET_MAX_RESULT to 100 – effectively using the entire RESULT_CACHE_MAX_SIZE:

Now for the same test – on the first iteration:

We see similar results. But if we run it again:

Notice that the logical IO and physical IO numbers were zero – this means the entire result set was done from the result set cache! In fact, the 0.75 second response time had everything to do with fetching the rows from the result set cache, and is/was reflected in the SQL*Net messaging traffic. Let’s optimize this:

Playing with RESULT_CACHE_MAX_SIZE

The previous tests showed the impact of adjusting RESULT_CACHE_MAX_RESULT. In this test, we’ll set this parameter back to 5 and increase RESULT_CACHE_MAX_SIZE. Here, we’ll use a table called DART_EFFICIENCY, which is almost 160MB in size:

Using our default values, our query (on the second run) shows this:

Now our shared pool size is about 1.3 GB, as indicated below:

So let’s increase RESULT_SET_MAX_SIZE to 200MB:

Finally, let’s re-run our query – the second iteration is displayed:

As we’d expect, we don’t see a tremendous gain because RESULT_CACHE_MAX_RESULT is set to 5%- let’s increase this to 100 and measure again:

Hmm – we still see a lot of IO and a long query time. Let’s take a look at our report.

I’m purposefully showing no output because this when I ran the memory report, it hung for a very, very long time. A check against V$SESSION_WAIT showed my session waiting on the “Result Cache: RC Latch” latch. And in fact, subsequent queries using a RESULT_CACHE hint similarly “hung” on this latch. And while maybe not necessarily a bug, it does make sense – SQL Result Set Caching is intended to cache small results sets for a short period of time. When we increased RESULT_CACHE_MAX_SIZE and ran a big query with a large result set, attempting to fully-populated the latch, it stands to reason that Oracle would need to flush library cache objects to make room for a larger result set cache and that the Result Cache: RC Latch latch would need to be held longer.

I decided to kill the sessions waiting on the latch and note this as something to keep in mind for the future.

RESULT_CACHE_MODE=FORCE

In the tests in this section and the next section, we’ll generate some load on our system using SwingBench. The first load test, which will be our control test, will be done with RESULT_CACHE_MODE=MANUAL and without any specific +RESULT_CACHE hints.

Control test: RESULT_CACHE_MODE=MANUAL

Snapshot low: 6814

Snapshot high: 6815

First test: RESULT_CACHE_MODE=FORCE

Snapshot low: 6816

Snapshot high: 6817

While it’s running:

Near the end of the test:

AWR Comparison

Here’s a summary:

• The test with RESULT_CACHE_MODE=FORCE did slightly more transactions per second, as indicated from the Load Profile section
• The test with RESULT_CACHE_MODE=FORCE generated more DB time by roughly 30%  The test with RESULT_CACHE_MODE=FORCE waited significantly on “enq: RC – Result Cache Contention” locks, which are directly related to result set caching 
• The test with RESULT_CACHE_MODE did slightly less LIO and PIO, which is what we’d expect.
• The test with FORCE did more hard parsing  Based on this workload, I would probably recommend not setting

RESULT_CACHE_MODE=FORCE.

RESULT_CACHE_MODE=AUTO

Second test: RESULT_CACHE_MODE=AUTO

Snapshot low: 6818

Snapshot high: 6820

AWR Comparison: AUTO vs. MANUAL

Summary:

• More DB Time with RESULT_CACHE_MODE=AUTO
• Slightly more TPS with RESULT_CACHE_MODE=AUTO
• Less LIO and PIO with RESULT_CACHE_MODE=AUTO
• Almost zero Result Cache contention events, which is an improvement over RESULT_CACHE_MODE=FORCE
• It appears as if, for this load, RESULT_CACHE_MODE=AUTO offers some improvements over both MANUAL and FORCE

Impact of DML on SQL Result Cache

How does DML impact SQL Result Cache caching behavior? In this section, let’s test it. First, we’ll flush the result set cache:

Let’s make indexes invisible so we can get some full-scans:

Now for the test:

As you can see above, we benefited pretty heavily from SQL Result Set Caching – no LIO or PIO, very fast run-times. Now I’m going to update the rows we’re requesting:

Next, I’ll flush the buffer cache and do some checkpoints:

Now we’ll re-run our initial query:

As we can see, our query ran in 0.79 seconds and did 1265 LIOs, 1136 physical reads. But now let’s update these rows again and immediately re-run our query to determine whether the IO we witnessed was simply due to the DML, or the subsequent checkpoint and buffer cache flush:

What we saw here is interesting:

• After the update, the next query on the data did perform LIO – in other words, accessed blocks in the buffer cache. These hasn’t been flushed to disk yet, so we avoided a physical IO, but we did NOT get the benefit of the result set cache
• The second query did gain full benefit of result set caching
• This shows that DML invalidates the SQL Result Set Cache  

Measuring SQL Result Cache with V$ Views

There are 4 main dynamic performance views associated with SQL Result Set Cache:

V$RESULT_CACHE_STATISTICS

Let’s run a script with a RESULT_CACHE hint:

As you can see, we see the “Create Count Success” value incremented.

V$RESULT_CACHE_MEMORY

Let’s run a query:

Notice another chunk of memory is now allocated.

V$RESULT_CACHE_OBJECTS

There are some interesting things you can see here, but let’s start by showing the view columns and a sample query:

You’ll notice several “Invalid” statuses – these came from our DML tests, I believe.

V$RESULT_CACHE_DEPENDENCY

Using the output from the previous section:

If we follow this through, let’s look at RESULT_ID 3, which is for OBJECT_NO 116311.

We can see that the result set depends on a published dependency, SOE.CUSTOMERS. I can probably be more creative with scripting, but this should provide some sort of insight for now.