Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, jessy.

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

Last updated: February 02, 2022 - 4:20 am UTC

Version: 19.3.0.0.0

Viewed 1000+ 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


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hash Value

jessy wijaya, January 30, 2022 - 4:21 pm UTC

i have question about hash values.

oracle use cursor sharing only when the sql is exactly the same right?
not when the hash values are the same.

sorry for late response
Connor McDonald
January 31, 2022 - 2:36 am UTC

Even more than both... For example:

User SCOTT runs:

select * from emp

Use MIKE runs:

select * from emp

They are identical SQL and will have identical hash values. THey *might* be the same SQL, but if Mike has his *own* table called EMP, then they refer to different objects.

(This is why we have what is called a "soft parse", ie, I've found something in the cache, but I still need to validate that it is indeed reusable or is actually a different SQL)

Reply

jessy wijaya, January 31, 2022 - 4:27 am UTC

Oracle doesnt soft parse if the "EMP" object from 1st query and second query is different correct?
Connor McDonald
February 02, 2022 - 4:20 am UTC

Well, we don't *know* if that second EMP is indeed a different EMP, so we must soft parse.

Where we can avoid soft parsing is by holding cursors open, so our client holds a pointer to the object in the shared pool so that when it comes to re-using it, we know by definition that it does not need any more "analysis".

Thats why PLSQL is so good - if you trace statements from PLSQL, you'll often see a parse count of 1, and an execution count of >1. We parsed it once, held it open and never had to parse (hard or soft) again

More to Explore

Administration

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