1000% Performance Improvement Using Oracle 11g Result Cache

About Oracle 11g Server Result Cache

Result Cache is a new feature in Oracle 11g and it does exactly what its name implies, it caches the results of queries and puts it into a slice of the shared pool. If you have a query that is executed often and reads data that rarely changes, this feature can increase performance significantly. When the query executes, Oracle will first look in the result cache to see if a previous result for this query is cached. If so, it will retrieve those results instead of reading all the blocks and creating the results again. The initial execution will run in the normal time but subsequent executions will seem to be nearly instataneous.

An example query that makes good use of the result cache would be something similar to below. In this case, the ORDER_HISTORY table contains very static data that will not change often if at all.

When this query executes on my database, here are the statistics:

Elapsed: 00:00:12.04

Consistent gets: 31907

Physical reads: 31897

 

Here’s the execution plan for this query:

------------------------------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |               |    47 |   705 |  9047   (3)| 00:01:49 |

|   1 |  HASH GROUP BY     |               |    47 |   705 |  9047   (3)| 00:01:49 |

|*  2 |   TABLE ACCESS FULL| ORDER_HISTORY |  2990K|    42M|  8915   (2)| 00:01:47 |

------------------------------------------------------------------------------------

Each time this query executes it is required to read a lot of data (2.9 million rows) to calculate the total sales by state for the 1st quarter of last year even if that data does not change. You could create a materialized view to help performance in this situation but why worry about the setup and administrative overhead associated with them. Using the result cache feature, the database could run the query once and remember what the answer was so it can be quickly retrieved. To utilize result cache at the query level, we can add a hint:

SELECT /*+ result_cache */ state, sum(order_total)
FROM   order_history
WHERE  order_date BETWEEN to_date('20090101','YYYYMMDD') AND to_date('20090331','YYYYMMDD')
GROUP BY state

Below are three successive executions of this query: Elapsed: 00:00:14.85 consistent gets: 31907 physical reads: 31897 Elapsed: 00:00:00.06 consistent gets: 0 physical reads: 0 Elapsed: 00:00:00.04 consistent gets: 0 physical reads: 0 The first execution of the query populated the result cache, but notice how the 2nd and 3rd execution of the query took no time at all and no logical or physical reads. This was because Oracle read the data from the result cache instead of pouring through millions of rows of data. Here is the new execution plan for the query. Note the new result cache information provided in the plan output:

------------------------------------------------------------------------------------------

| Id|Operation           | Name                       |Rows |Bytes |Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|  0|SELECT STATEMENT    |                            |  47 |  705 | 9046   (3)|00:01:49 |

|  1| RESULT CACHE       | 2004xqwuc1r8bb69jff4d4ww01 |     |      |           |         |

|  2|  HASH GROUP BY     |                            |  47 |  705 | 9046   (3)|00:01:49 |

|* 3|   TABLE ACCESS FULL| ORDER_HISTORY              |2957K|   42M| 8915   (2)|00:01:47 |

------------------------------------------------------------------------------------------

Function Result Cache

You can also use the Result Cache with functions by using the “result_cache” option:

CREATE OR REPLACE FUNCTION state_sales_totals (p_state IN VARCHAR2) RETURN
NUMBER RESULT_CACHE
AS
   l_order_total NUMBER := 0;
BEGIN   -- grab the data for the state passed in
   SELECT sum(order_total)
   INTO   l_order_total
   FROM   order_history
   WHERE  order_date between to_date('20090101','YYYYMMDD') and
          to_date('20090331','YYYYMMDD')
   AND    state = p_state;
   -- resturn the resultsRETURN l_order_total;
end;
/

When we utilize this function, notice how the results of the function are also cached and the response is instantaneous: SELECT state_sales_totals(‘IL’) FROM dual; Elapsed: 00:00:11.56 consitent gets: 31939 physical reads: 27405 Elapsed: 00:00:00.01 consistent gets: 0 physical reads: 0