Dynamic SQL and RESULT_CACHE

Over the weekend one of my friends asked me for a clarification on RESULT_CACHE - a really interesting functionality that allows Oracle to cache in SGA results of PL/SQL function calls.

The point of confusion was the fact that between 11gR1 and 11gR2 Oracle development team significantly re-thought this feature - if in R1 developers needed to explicitly specify objects that should be monitored for cache invalidation (via RELIES_ON clause), starting R2 Oracle took care of it directly (RELIES_ON clause is completely ignored).

Interestingly enough, Oracle will monitor not only direct dependencies! If inside of the function there are calls via Dynamic SQL to other objects - those will be monitored too. Last year I had the whole section on this topic at ODTUG KScope'12 ("Dynamic SQL in 11g World")! Here is a snippet from my white-paper:

Integration of Dynamic SQL and RESULT_CACHE

Another good thing about the current implementation of Dynamic SQL is that Oracle’s PL/SQL team actively integrates it with other advanced features. For example, the “result cache” introduced in 11gR1 (and significantly rewritten in 11gR2) is efficient enough to not only auto-detect hard-coded dependencies, but also recognize and record on-the-fly calls made via Dynamic SQL. The following example includes a function that can get current row counts for a given table:

create or replace function f_getCount_nr (i_tab_tx varchar2)
return number
result_cache
is
    v_sql_tx varchar2(256);
    v_out_nr number;
begin
    execute immediate 
      'select count(*) from '||i_tab_tx into v_out_nr;
    return v_out_nr;
end;

Step #1 would be to confirm that (a) result cache actually works and (b) it recognized on-the-fly dependency.

SQL> select f_getCount_nr('EMP') from dual;
F_GETCOUNT_NR('EMP')
--------------------
                  14

SQL> select ro.id,  ro.name,  do.object_name
  2  from   v$result_cache_objects    ro,
  3         v$result_cache_dependency rd,
  4         dba_objects               do
  5  where  ro.id = rd.result_id
  6  and    rd.object_no = do.object_id;

ID NAME                                                   OBJECT_NAME
-- -----------------------------------------------------  ---- 
1  "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440... #1  EMP
1  "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440... #1  F_GETCOUNT_NR

SQL>select f_getCount_nr('EMP') from dual;
F_GETCOUNT_NR('EMP')
--------------------
                  14

SQL> select *
  2  from v$result_cache_statistics
  3  where  name in ('Create Count Success','Find Count');
ID  NAME                 VALUE
--- -------------------- ------
5   Create Count Success 1
7   Find Count           1
SQL>

Oracle successfully recognized the EMP table as cache dependency and was able to return a value from the cache when the function was called a second time. Now to test cache invalidation, I will insert a new row to EMP table and re-fire the function F_GETCOUNT_NR

SQL> insert into emp(empno) values (100);
1 row created.
SQL> commit;
Commit complete.

SQL> select f_getCount_nr('EMP') from dual;
F_GETCOUNT_NR('EMP')
--------------------
                  15

SQL> select id, name, value
  2  from v$result_cache_statistics
  3  where  name in ('Create Count Success',
  4                   'Find Count','Invalidation Count');

ID  NAME                 VALUE
--- -------------------- ------
5   Create Count Success 2
7   Find Count           1
8   Invalidation Count   1
SQL>

This time, Oracle successfully detected data changes and invalidated the previously cached information. Now let’s introduce a new dynamic dependency  (to DEPT table) and see whether the resulting cache would successfully recognize the difference.

SQL> select f_getCount_nr('DEPT') from dual;
F_GETCOUNT_NR('DEPT')
---------------------
                    4
SQL> select id,  name,  object_name
  2  from   v$result_cache_objects    ro,
  3         v$result_cache_dependency rd,
  4         dba_objects               do
  5  where  ro.id = rd.result_id
  6  and    rd.object_no = do.object_id;

ID  NAME                                                            OBJECT_NAME
--- --------------------------------------------------------------- -------------
3   "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1  EMP
3   "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1  F_GETCOUNT_NR
4   "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1  DEPT
4   "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1  F_GETCOUNT_NR

SQL> select id, name, value
  2  from v$result_cache_statistics
  3  where  name in ('Create Count Success',
  4                   'Find Count','Invalidation Count');

ID  NAME                 VALUE
--- -------------------- ------
5   Create Count Success 3
7   Find Count           1
8   Invalidation Count   1
SQL>

As you can see, a variation of the resulting cache with the dependency on DEPT (rather than EMP) was immediately recognized. This means that Dynamic SQL is indeed fully integrated into the overall on-the-fly caching mechanism.