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);
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’;
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.