Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jessy.

Asked: January 10, 2022 - 10:44 am UTC

Last updated: January 13, 2022 - 8:11 am UTC

Version: 19.3.0.0.0

Viewed 100+ times

You Asked

hi tom i have a simple question about result cache.


1.how exactly does the oracle access the result cache? and how does it knew that it is the same sql statement executed last time?

2.when we cache result of the SQL statement into the result cache and re run the query does oracle instantly access the result cache? or does oracle go into the shared sql area first then go to the result cache to get the result

thanks

and Connor said...

1.how exactly does the oracle access the result cache?

A SQL doesn't have to be exactly the same - we take a normalised version of the SQL and then apply a hash function to it give us a logical result cache "ID". If the ID's match (and all the other normal things about result cache, ie, its published, not stale etc), then we can use it.

eg Two "different" SQLs

SQL> set autotrace on
SQL> SELECT /*+ RESULT_CACHE */ EMPNO, ENAME FROM EMP;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |    14 |   140 |     3   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 51p4zjvmv9mbtgsf577w9vp95y |    14 |   140 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP                        |    14 |   140 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(MCDONAC.EMP); name="SELECT /*+ RESULT_CACHE */ EMPNO, ENAME FROM EMP"


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> select /*+ result_cache */ empno, ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |    14 |   140 |     3   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 51p4zjvmv9mbtgsf577w9vp95y |    14 |   140 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP                        |    14 |   140 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(MCDONAC.EMP); name="select /*+ result_cache */ empno, ename from emp"


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed



2.when we cache result of the SQL statement into the result cache

Standard parsing rules apply. If it needs to be hard parsed (or soft parsed) we'll do so, and determine that a result cache can be used at that point.


If you want to *really* deep dive into things, you can check out Julian Dyke's outdated by still excellent presentation at

http://www.juliandyke.com/Presentations/Presentations.php#ResultCacheInternals


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database