Oracle Result Caching

Hi everyone,

In this article, we will examine the Result Caching mechanism that was available with Oracle 11g. (Result Caching feature can be used with Enterprise Edition.)

Caching, as known by all, is a frequently used technique to improve performance in computer systems. This technique at its simplest can be described as a mechanism which foresee the transferring of the data, CPU uses frequently, to the nearest memory unit (Cache). In Oracle level, Result Caching is a performance improving tool that has been developed from this infrastructure. Oracle, in this context, stores the info about data dictionary in cache and stores cursors in library cache thus it supports performance enhancement.

Oracle supports 3 types of result cache structures. To examine them in order:

1- Server Result Cache

2- Client Result Cache

3- PL/SQL Funcition Result Cache

 

SERVER RESULT CACHE

As can be understood from its name, it’s a cache area that exists in Oracle server region. In this area, results that are circulated by our queries are stored.

The main motivation of Result Cache is preventing the same queries running multiple times, from to be executed over and over again. Therefore, performance enhancement is intended by providing the query results to be circulated hastily on memory before the query is run.

As Result Cache Server exists in shared pool, it is possible to use this cache mechanism for the same queries run in different sessions to use this cache mechanism. This feature affects the query performance positively in database systems where the number of the users simultaneously accessing to the db is high.

In queries where variable is used, as it is possible for the variable parameter to change every time, result sets which are produced as the parameter changes are transferred to result cache separately because of their query results being different. If we want to be able to observe result cache performance while using bind variable, it would be enough to run the query with the same variable.

As the Temporary tables are processed as session-based, they are not cached (https://emrahmeteen.wordpress.com/2012/02/19/oracle-global-temproray-tables/). Moreover, queries that give reference to non-deterministic functions and sequences are not cached. Queries that use data dictionary view or endangers Read Consistency principle in DB are also not cached.
Now with an example, let us see runtime differences and how we use our cache structure.

SET TIMING ON
SELECT /*+result_cache*/
p.prod_category,
c.country_id,
SUM (s.quantity_sold) AS quantity_sold,
SUM (s.amount_sold) AS amount_sold
FROM sales s, customers c, products p
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;
Elapsed: 00:00:07.98

If we want our query’s result to be cached by the server, we give the hint /*+result_cache*/ to our query as can be understood from above. Our query result takes a long time as when our query runs for the first time there’s an execution. After the query is run and the result is taken, the query result is stored in cache. Now let us run the same query for the second time and examine the time difference.

SET TIMING ON
SELECT /*+result_cache*/
p.prod_category,
c.country_id,
SUM (s.quantity_sold) AS quantity_sold,
SUM (s.amount_sold) AS amount_sold
FROM sales s, customers c, products p
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;
Elapsed: 00:00:00.23

As can be seen, after the query result is stored in cache the query result, read from the cache directly, is circulated without being run again. Now let us look at the execution plan of our query.

EXPLAIN PLAN
FOR
SELECT /*+result_cache*/
p.prod_category,
c.country_id,
SUM (s.quantity_sold) AS quantity_sold,
SUM (s.amount_sold) AS amount_sold
FROM sales s, customers c, products p
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;
 
set linesize 500
select * from table(dbms_xplan.display);

11

When we examine the execution plan of the query, it’s seen that there’s a step for the result’s to be read from result cache in the step no: 1. The unique value at this step under the Name column is cache_id. We can learn how many times this query were run and its other data with this cache id.

SELECT status,
creation_timestamp,
build_time,
row_count,
scan_count
FROM v$result_cache_objects
WHERE cache_id = ‘4fq786v0sq49wgux1y518akv7q’;

22

CLIENT RESULT CACHE
As can be understood from its name, it’s the cache mechanism where the query results are stored in the cache in client region.

The most significant advantage of Client Result Cache is there’s no time loss that can be experienced from a query between server and client or the result transfer caused by it. As the query is also in client, the process can be executed much faster. However, there’s also a significant disadvantage is this system. If the data in Cache is expired, meaning that the query result has changed because of tables’ in query source taking dml, it will constantly poll the server in order to understand this invalidation. This is not an effective way. In the meantime, the fact that the result is stored in client cannot ensure up-to-dateness of the query result in means of data unity.

It is activated by client_result_cache_size parameter. Its default is 0, in other words, disable. In order to use client cache, this parameter should be set with a valid value.

PL/SQL Function Result Cache

PL/SQL Function Result Cache is the cache area that exists in Oracle Server. Circulation values of PL/SQL functions are stored in this area. It should not be considered that this server uses a different memory structure from result cache. Both of these cache structures use the same memory structure.

With this cache method, the results circulated by the functions that are run with the same parameters are stored in the cache. When the function is invoked with the same parameters again, the function result stored in the cache is rotated to user without being run again. Therefore, it provides speed and performance enhancement while avoiding making executions.

Now let’s clarify this structure with an example.

CREATE OR REPLACE FUNCTION get_sales_total_for_customer (p_cust_id NUMBER)
RETURN NUMBER
RESULT_CACHE
IS
v_total NUMBER;
BEGIN
SELECT SUM (amount_sold)
INTO v_total
FROM sales
WHERE cust_id = p_cust_id;
RETURN v_total;
END get_sales_total_for_customer;
/
SET TIMING ON
DECLARE
v_total NUMBER;
BEGIN
v_total := get_sales_total_for_customer (2865);
DBMS_OUTPUT.put_line (v_total);
END;

PL/SQL Block the First Vocation  — Elapsed: 00:00:01.09
PL/SQL Block the Second Vocation — Elapsed: 00:00:00.04

We expressed that the result to be circulated by PL/SQL FUNCTION will be cached while we were creating it. Afterwards, when we coded a PL/SQL block that uses our function and invoked it with the same parameter for the second time, the process was executed much faster as the result has come back from the cache.

These should not be neglected for PL/SQL Functions Result Cache:

– Functions that can take on parameters of OUT or IN OUT types,
– Pipelined functions,
– Functions that invoke Anonym pl/sql blocks,
– Functions that circulate values of LOB’s, REF CURSOR, Object or Record types

Cannot be used in Result Cache structure. Apart from these exceptional cases, created functions can benefit from this mechanism and be cached.

REFERENCES:

TroubleShooting Oracle Performance, Christian Antognini, 2008

http://www.oracle-developer.net/display.php?id=504

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s