Monday, April 29, 2013

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.

3 comments:

lee woo said...

Life is like a landscape. You live in the midst of it but can describe it only from the vantage point of
distance. See the link below for more info.

#distance
www.inspgift.com

andrea chiu said...


Time is gold. We should treasure every second of it so that in time we will not regret anything because we only live ones.I have read your article and i was glad and inspired. Thanks for it, Cheers!
Visit my site if you have time, Enjoy.

n8fan.net

www.n8fan.net

Cindy Dy said...

You are an awesome blogger. This is one of the best blog I had visited so far. Hope to read more post from you in the future. Keep it up. God bless.

Bubble
www.gofastek.com