Query Returns Same Result Cache ID for Two Different Statements (Doc ID 2548442.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• Same query using result cache issued from different schemas referencing different objects returns wrong results:
  
  

  conn u1

  SQL> select * from v_test;

  C S
  ---------- ----------
  1 1

  conn u2

  SQL> select * from v_test;

  C S
  ---------- ----------
  1 2

  Connecting as u2 (for example):

  conn u2

  select sum(s) from tc2.v_test;

  2

  ---------------------------------------------------------------------------------------------------
  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  ---------------------------------------------------------------------------------------------------
  | 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
  | 1 | SORT AGGREGATE | | 1 | 13 | | |
  | 2 | VIEW | V_TEST | 1 | 13 | 4 (0)| 00:00:01 |
  | 3 | RESULT CACHE | 065j7ngtmsa0w5mqp840td48b0 | | | | |
  | 4 | TABLE ACCESS FULL| T_TAB | 1 | 13 | 4 (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------------

  select sum(s) from tc1.v_test;

  2 >>>>>> this should return 1

  Execution Plan
  ----------------------------------------------------------

  -----------------------------------------------------------------------------------------------------
  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  -----------------------------------------------------------------------------------------------------
  | 0 | SELECT STATEMENT | | 1 | 26 | 6 (17)| 00:00:01 |
  ....
  | 7 | VIEW | | 1 | | 2 (0)| 00:00:01 |
  | 8 | RESULT CACHE | f3jk1n1wgxbzg25v7sghjc1gn7 | | | | | <<<<<<<<<<<<<<<<< Notice the same result cache ID
  | 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------------

   

Changes

 No Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.