Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 156
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 158
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 159
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 160
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data.
The Result Cache feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many users will look for the same data via reporting tools.
This is particularly useful when you query a large amount of data to return only a few rows, for example, the last month sales aggregated by week.
Oracle will have to look for a large amount of data to retrieve all sales and make the aggregate, but the result will be only 4 rows.
For the first query execution, the runtime will be standard but for the subsequent queries, it will be very fast.
This cache is part of the shared pool.
Managing the Result Cache
The Result Cache is managed by 3 initialization parameters:
| PARAMETER | DESCRIPTION |
|---|---|
| RESULT_CACHE_MAX_SIZE | Used to managed the size of the result cache, if set to 0, the result cache is disabled |
| RESULT_CACHE_MAX_RESULT | Used to define the max percentage of the result cache a single query can use. |
| RESULT_CACHE_REMOTE_EXPIRATION | This parameter defines the time in minutes that the result cache remains valid. By default the cache is valid until the dependant objects are modified (parameter set to 0) |
By default, the size of the result cache depends of you initialization parameters.
If MEMORY_TARGET parameter is set, then the result cache is 0.25% of this value.
If SGA_TARGET is set, then the result cache value is 0.5% of this value.
If you manage the shared pool manualy by setting the SHARED_POOL_SIZE parameter, then the result cache size is 1% of this value assigned to the shared pool.
How to use the result cache ?
The result cache can be used in two ways.
Using the result cache in queries
To use the result cache in queries, you need to add the RESULT_CACHE hint in the query.
SELECT /*+ RESULT_CACHE */ SUM(emp.emp_id), dep.dep_name
FROM employees emp,
departments dep
where emp.dep_id = dep.dep_id
GROUP BY dep.dep_name
ORDER BY dep.dep_name;
Using the result cache in functions
To use it in functions, you must mention it in the create statement.
CREATE OR REPLACE FUNCTION format_customer_name (
p_cust_id IN customers.cust_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
RELIES_ON (customers) IS
v_name VARCHAR2(4000);
BEGIN
counter.increment();
SELECT cust_first_name || ' ' || cust_last_name
INTO v_name
FROM customers
WHERE cust_id = p_cust_id;
RETURN v_name;
END format_customer_name;
/
Performance improvement
here is a small test to show the benefits of using the result cache.
SQL> create user demo identified by demo;
User created.
SQL> alter user demo quota unlimited on users;
User altered.
SQL> create table demo.test as select * from dba_objects union all select * from dba_objects union all select * from dba_objects;
Table created.
SQL> set lines 300
SQL> set autotrace traceonly exp stat
SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 396 | 1273 (1)| 00:00:01 |
| 1 | RESULT CACHE | d45yr4zbr96uv494hpz99gkkn1 | | | | |
| 2 | HASH GROUP BY | | 44 | 396 | 1273 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 272K| 2397K| 1268 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
4587 consistent gets
4582 physical reads
0 redo size
1756 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
The first execution is a normal execution and Oracle caches the rows into the shared pool, as you can see just under the plan, oracle gets statistics about the data to cache; the number of columns and the dependent objects.
Remember that if you alter the dependent object, the data cached is invalidated.
To see what is actually cached into the result cache, we can query this view
SQL> SELECT id, type, creation_timestamp, block_count,
column_count, pin_count, row_count
FROM V$RESULT_CACHE_OBJECTS
WHERE cache_id = 'd45yr4zbr96uv494hpz99gkkn1';
ID TYPE CREATION_ BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT
---------- ---------- --------- ----------- ------------ ---------- ----------
4 Result 22-APR-14 1 2 0 44
The cache_id used in the query is from the column name of the plan. As you can see the rows from my query are cached.
Now let’s see what happens if I execute the same query a second time.
SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 396 | 1273 (1)| 00:00:01 |
| 1 | RESULT CACHE | d45yr4zbr96uv494hpz99gkkn1 | | | | |
| 2 | HASH GROUP BY | | 44 | 396 | 1273 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 272K| 2397K| 1268 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1756 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
SQL>
When we look at the plan statistics, there is a huge improvement, nothing has been done, the data is just retrieved from the cache without any overhead.
Now we will alter the dependant object to see how Oracle handles it.
SQL> delete from demo.test where object_type='TABLE';
7164 rows deleted.
SQL> set autotrace traceonly exp stat
SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;
43 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 396 | 1062 (1)| 00:00:01 |
| 1 | RESULT CACHE | d45yr4zbr96uv494hpz99gkkn1 | | | | |
| 2 | HASH GROUP BY | | 44 | 396 | 1062 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 272K| 2397K| 1056 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3825 consistent gets
0 physical reads
0 redo size
1745 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43 rows processed
I issued a delete statement on the table, immediately oracle invalidate the data in the result cache and will no longer use it until the transaction is committed or rolled back, even if I issue the same statement again.
Now I issue a commit and run the statement again.
SQL> commit;
Commit complete.
SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;
43 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 396 | 1062 (1)| 00:00:01 |
| 1 | RESULT CACHE | d45yr4zbr96uv494hpz99gkkn1 | | | | |
| 2 | HASH GROUP BY | | 44 | 396 | 1062 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 272K| 2397K| 1056 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3825 consistent gets
0 physical reads
0 redo size
1745 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43 rows processed
For the first execution, oracle cache the altered data. Now we issue the statement again.
SQL> select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type;
43 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 396 | 1062 (1)| 00:00:01 |
| 1 | RESULT CACHE | d45yr4zbr96uv494hpz99gkkn1 | | | | |
| 2 | HASH GROUP BY | | 44 | 396 | 1062 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 272K| 2397K| 1056 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(DEMO.TEST); parameters=(nls); name="select /*+ RESULT_CACHE */ object_type, count(*) from demo.test group by object_type"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1745 bytes sent via SQL*Net to client
565 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43 rows processed
Oracle cached the new data in the result cache.
SQL> SELECT id, type, creation_timestamp, block_count,
column_count, pin_count, row_count, invalidations
FROM V$RESULT_CACHE_OBJECTS
WHERE cache_id = 'd45yr4zbr96uv494hpz99gkkn1';
ID TYPE CREATION_ BLOCK_COUNT COLUMN_COUNT PIN_COUNT ROW_COUNT INVALIDATIONS
---------- ---------- --------- ----------- ------------ ---------- ---------- -------------
2 Result 22-APR-14 1 2 0 43 0
1 Result 22-APR-14 1 2 0 44 0
I have now two sets in the result cache, one before and one after the delete.
As you can see, you can expect to huge performance benefits by using this feature, but to be efficient, you have to select the queries you choose to cache with caution because it can rapidly be memory consuming and can result in latch contention.