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:
Here’s how it looks in our XRAC database:
As we can see, there are some other parameters of note:
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:
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:
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:
Measuring SQL Result Cache with V$ Views
There are 4 main dynamic performance views associated with SQL Result Set Cache:
View | Description |
V$RESULT_CACHE_STATISTICS | Lists various cache settings and memory usage statistics – similar to DBMS_RESULT_CACHE.MEMORY_REPORT output |
V$RESULT_CACHE_MEMORY | Lists all memory blocks and corresponding statistics |
V$RESULT_CACHE_OBJECTS | Lists all objects, with cached results and dependencies, along with their attributes |
V$RESULT_CACHE_DEPENDENCY | Lists dependency details between cache results and dependencies |
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.
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy