Skip to Main Content
  • Questions

  • why Results cached on one instance cannot be used by another instance

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pankaj.

Asked: June 26, 2018 - 5:53 am UTC

Last updated: June 28, 2018 - 6:00 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Want to know why ?
Each node in a RAC configuration has a private result cache. Results cached on one
instance cannot be used by another instance. However, invalidations work across instances.
To handle all synchronization operations between RAC instances related to the SQL query
result cache, the special RCBG process is used on each instance.

and Connor said...

What makes you think we can't access the result cache from another instance ?

connor@DB> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB1

connor@DB> create table t as select d.* from dba_objects d,
  2    ( select 1 from dual connect by level <= 20 );

Table created.

connor@DB> set autotrace on stat
connor@DB> select /*+ result_cache */ max(owner) from t;

MAX(OWNER)
---------------------------------------------------------------
XDB

Statistics
----------------------------------------------------------
         67  recursive calls
          0  db block gets
      37887  consistent gets
      37782  physical reads
          0  redo size
        349  bytes sent via SQL*Net to client
        480  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
connor@DB> select /*+ result_cache */ max(owner) from t;

MAX(OWNER)
---------------------------------------------------------------
XDB

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

Instance 2

connor@DB> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
DB2

connor@DB> set autotrace on stat
connor@DB> select /*+ result_cache */ max(owner) from t;

MAX(OWNER)
------------------------------------------------------------
XDB

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

connor@DB> set autotrace on stat
connor@DB> select /*+ result_cache */ max(owner) from t;

MAX(OWNER)
------------------------------------------------------------
XDB

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


Rating

  (2 ratings)

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

Comments

Thanks for update

Pankaj Kshirsagar, June 27, 2018 - 4:55 am UTC

I was not sure why we can not, But its mentioned in 11g Performance Tuning guide
Connor McDonald
June 28, 2018 - 6:00 am UTC

You'll need to give me a link to the section - I can't find anything that says it cannot

To connor

A reader, June 27, 2018 - 7:32 am UTC

Connor what are you proving with your select max? Question was related to the result cache ACROSS INSTANCES. So we logically expect that the 1st select on instance2 gives the same stats of the 2nd select of instance1.
Am I missing something?
Connor McDonald
June 28, 2018 - 6:00 am UTC

First query instance 1: runs the query (35,000 io's)
Next query instance 1: 0 io's (result cache)


First query instance 2: *parses* the query (small io's) then uses result cache (otherwise we would see 35,000 io's again)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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