Result cache wrongly reuses a different set of bind variable on the result cached view. (Doc ID 2645058.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• When creating a view of result cache, it is not parameterized with bind variable
• The following testcase shows an example:
  SQL> REM The following testcase will reproduce the symptom.
  SQL> drop user RC cascade;
  SQL> create user RC identified by RC;
  SQL> grant dba to RC;
  SQL> create table RC.TEST (
  col1 VARCHAR2(40),
  col2 VARCHAR2(40)
  );

  SQL> insert into RC.TEST values ('A1','A2');
  SQL> insert into RC.TEST values ('B1','B2');
  SQL> commit;

  SQL> exec dbms_result_cache.flush;

  SQL> var BIND1 varchar2(40);
  SQL> exec :BIND1 :='A1';

  SQL> REM Creating a view of result cache
  SQL> create or replace view RC.TEST_VIEW (ccount1,ccount2)
  AS
  WITH
  AA AS (select /*+ result_cache */ col1 ccount1, count(*) ccount2 from RC.TEST group by col1)
  SELECT ccount1,ccount2 FROM AA;

  SQL> REM 1st bind variable
  SQL> var BIND1 varchar2(40);
  SQL> exec :BIND1 :='A1';
  SQL> select * from RC.TEST_VIEW where CCOUNT1 = :BIND1;

  CCOUNT1 CCOUNT2
  ---------- ---------
  A1 1

  SQL> REM created new result cache
  SQL> select cache_id,cache_key,bucket_no,hash,checksum,name,scan_count from v$result_cache_objects where type='Result';

  CACHE_ID CACHE_KEY BUCKET_NO HASH CHECKSUM NAME SCAN_COUNT
  ------------------------------ ------------------------------ --------- ---------- ---------- ------------------------------------------------------------ ----------
  aj3bfj5qzpppq0ds62m4t19u22 9c46fbmss86651na1cgvfwm38p 1383 2561811815 3985577315 select /*+ result_cache */ col1 ccount1, count(*) ccount2 fr 0

  om RC.TEST group by col1
  SQL> REM 2nd bind variable
  SQL> var BIND2 varchar2(40);
  SQL> exec :BIND2 :='B1';
  SQL> select * from RC.TEST_VIEW where CCOUNT1 = :BIND2;

  CCOUNT1 CCOUNT2
  ---------- ---------
  B1 1

  SQL> REM created another result cache with different cache key, not reused
  SQL> select cache_id,cache_key,bucket_no,hash,checksum,name,scan_count from v$result_cache_objects where type='Result';

  CACHE_ID CACHE_KEY BUCKET_NO HASH CHECKSUM NAME SCAN_COUNT
  ------------------------------ ------------------------------ --------- ---------- ---------- ------------------------------------------------------------ ----------
  aj3bfj5qzpppq0ds62m4t19u22 9c46fbmss86651na1cgvfwm38p 1383 2561811815 3985577315 select /*+ result_cache */ col1 ccount1, count(*) ccount2 fr 0
  om RC.TEST group by col1

  aj3bfj5qzpppq0ds62m4t19u22 7cjfu6zk0z55u05kc9cpu1u45d 523 3996959243 3985577315 select /*+ result_cache */ col1 ccount1, count(*) ccount2 fr 0
  om RC.TEST group by col1

   

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.