A great feature introduced in the Oracle 11g database is the SQL query ‘Result Cache’. A result cache is an area of memory that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale. The caching mechanism is efficient and easy to use, and it relieves you of designing and developing your own legacy cache procedures and cache-management policies.
The benefits of using the server result cache depend on the application. OLAP applications can benefit significantly from its use. Good candidates for caching are queries that access a high number of rows but return a small number of them as result.
Result cache may not be suitable for volatile databases with high DML operations.
And how does Result Cache work?
When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query.
If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.
You can enhance database performance by using this feature implemented as “SQL Result Cache” or as “PL/SQL Function Cache” options.
The RESULT_CACHE_MODE initialization parameter controls whether the SQL query result cache is used for all queries automatically or if its use will be on demand (manual).
SQL> show parameter RESULT_CACHE_MODE
NAME TYPE VALUE
------------------------------------ ----------- -------
result_cache_mode string MANUAL
You can verify that “Result Cache” is enabled in your database by executing the following query:
SQL> SELECT dbms_result_cache.status() FROM dual;
DBMS_RESULT_CACHE.STATUS()
-------------------------------------------------
ENABLEDSQL Result Cache
To use the “SQL Result Cache” feature you can use a hint that stores the results of the query to be cached in the memory. To enable result-caching for a SQL statement use the /*+ RESULT_CACHE */ hint specified in your query.
Create a test table and populate it with some data:
SQL> CREATE TABLE test_table
2 (n_number int,
3 n_size varchar2(5));
Table created.
SQL> INSERT INTO test_table VALUES (85,'Small');
1 row created.
SQL> INSERT INTO test_table VALUES (92,'Small');
1 row created.
SQL> COMMIT;
Commit complete.Run the following query to get the execution statistics and the response times for a general query without involving the result cache:
SQL> set autotrace on explain statistics
SQL> SELECT n_size, avg(n_number)
2 FROM test_table
3 GROUP BY n_size;
N_SIZ AVG(N_NUMBER)
----- -------------
Small 88.5
Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467
-----------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 4 (25)| 00:00:01|
| 1 | HASH GROUP BY | | 2 | 34 | 4 (25)| 00:00:01|
| 2 | TABLE ACCESS FULL| TEST_TABLE | 2 | 34 | 3 (0)| 00:00:01|
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
606 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedNow use the /*+ RESULT_CACHE */ hint that stores the results of the query to be cached in the memory:
SQL> SELECT /*+ RESULT_CACHE */ n_size, avg(n_number)
2 FROM test_table
3 GROUP BY n_size;
N_SIZ AVG(N_NUMBER)
----- -------------
Small 88.5
Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467
-----------------------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost| Time |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 2 | 34 | 4|00:00:01|
| 1| RESULT CACHE |6jr5h2cqv6zbccs75gy5h8kq3y| | | | |
| 2| HASH GROUP BY | | 2 | 34 | 4|00:00:01|
| 3| TABLE ACCESS FULL|TEST_TABLE | 2 | 34 | 3|00:00:01|
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(TEST.TEST_TABLE); parameters=(nls);
name="SELECT /*+ RESULT_CACHE */ n_size, avg(n_number)
FROM test_table
GROUP BY n_size"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
606 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedNote several differences from the first case:
(1) The explain plan shows RESULT CACHE as an operation.
(2) The note after the explain plan shows what type of caching was performed and on which result.
(3) Take note of “recursive calls” and “consistent gets” and “physical reads” values. They are similar in both of the executions.
Now, let’s execute the second query again:
SQL> SELECT /*+ RESULT_CACHE */ n_size, avg(n_number)
2 FROM test_table
3 GROUP BY n_size;
N_SIZ AVG(N_NUMBER)
----- -------------
Small 88.5
Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467
-----------------------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost| Time |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 2 | 34 | 4|00:00:01|
| 1| RESULT CACHE |6jr5h2cqv6zbccs75gy5h8kq3y| | | | |
| 2| HASH GROUP BY | | 2 | 34 | 4|00:00:01|
| 3| TABLE ACCESS FULL|TEST_TABLE | 2 | 34 | 3|00:00:01|
-----------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(TEST.TEST_TABLE); parameters=(nls);
name="SELECT /*+ RESULT_CACHE */ n_size, avg(n_number)
FROM test_table
GROUP BY n_size"
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
606 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedIn this last execution both execution plans are identical but values of “recursive calls”, “consistent get” and “physical reads” are all “0”. Therefore, no I/O operations were performed for this query.
SQL Result Cache is another cache in the SGA, just like buffer cache or program global area. When you execute a query with the hint result_cache, Oracle performs the operation just like any other operation but the results are stored in the SQL Result Cache.
A future post will address how to enhance database performance by using “PL/SQL Function Cache” feature.
Link to script that contains the examples in this post.