Result cache is not used although result_cache_max_size parameter is set (Doc ID 1928474.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2016***

Symptoms

When trying to use result cache by setting the parameter result_cache_max_size and explicitly using the result_cache hint in a query, the result cache is not being used as per the explain plan output.

Example:

SELECT /*+ RESULT_CACHE */ deptno, AVG(sal) FROM scott.emp GROUP BY deptno;

   DEPTNO   AVG(SAL)
---------- ----------
       30 1566.66667
       20       2175
       10 2916.66667

SQL> explain plan for SELECT /*+ RESULT_CACHE */ deptno, AVG(sal) FROM scott.emp GROUP BY deptno;
Explained.

SQL> @?/rdbms/admin/utlxplp

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   364 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    14 |   364 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
  - dynamic sampling used for this statement (level=2)

13 rows selected.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms