Oracle SQL | RESULT_CACHE: run-time dependency tracking

As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.

Test function and tables

[sourcecode language=”sql”]
create or replace function f_without_deps(p_tab varchar2) return varchar2
as
res varchar2(30);
begin
execute immediate ‘select ”’||p_tab||”’ from ‘||p_tab||’ where rownum=1′ into res;
return res;
end;
/
create table a as select ‘a’ a from dual;
create table b as select ‘b’ b from dual;
create view v_ab as select a,b from a,b;
[/sourcecode]

[collapse]

And it works fine with normal tables:

v_ab

[sourcecode language=”sql”]
SQL> exec :p_tab:=’v_ab’;

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
———-
v_ab

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID
—————————————————————– ———- — ———- ——— ———- ———- ——-
XTENDER.F_WITHOUT_DEPS 1579 0 Dependency Published 0 0 0
XTENDER.V_AB 3127 2 Dependency Published 0 0 0
XTENDER.B 778 3 Dependency Published 0 0 0
XTENDER.A 464 4 Dependency Published 0 0 0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual 1749 1 Result Published 0 0 0
[/sourcecode]

[collapse]

But don’t forget that the result_cache also caches such functions with the objects, that usually should not be cached, and such objects will not be listed in the result_cache dependencies list:

v$database

[sourcecode language=”sql”]
SQL> exec :p_tab:=’v$database’;

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
———-
v$database

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID
—————————————————————– ———- — ———- ——— ———- ———- ——-
XTENDER.F_WITHOUT_DEPS 772 0 Dependency Published 0 0 0
PUBLIC.V$DATABASE 1363 2 Dependency Published 0 0 0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual 2283 1 Result Published 0 0 0

3 rows selected.
[/sourcecode]

[collapse]

As you can see, there is only dependency on public synonym V$DATABASE, but not real base fixed X$-tables.

SYS.OBJ$

[sourcecode language=”sql”]
SQL> exec :p_tab:=’sys.obj$’;

PL/SQL procedure successfully completed.

SQL> call DBMS_RESULT_CACHE.flush();

Call completed.

SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual;

RESULT
———-
sys.obj$

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID
—————————————————————– ———- — ———- ——— ———- ———- ——-
XTENDER.F_WITHOUT_DEPS 3922 0 Dependency Published 0 0 0
select/*+ result_cache */ f_without_deps(:p_tab) result from dual 3753 1 Result Published 0 0 0

2 rows selected.
[/sourcecode]

[collapse]

The results were cached and the dependencies do not include system objects.
We easily check that the queries with any table in SYS schema or with sysdate,systimestamp,current_date,current_timestamp,dbms_random will not be cached:

SYS tables

[sourcecode language=”sql”]
SQL> select/*+ result_cache */ current_scn result from v$database;

RESULT
———-
##########

1 row selected.

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;

no rows selected

SQL> explain plan for select/*+ result_cache */ * from sys.obj$;

PLAN_TABLE_OUTPUT
————————————————————————–
Plan hash value: 2311451600

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 87256 | 7328K| 296 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| OBJ$ | 87256 | 7328K| 296 (1)| 00:00:04 |
————————————————————————–
[/sourcecode]

[collapse]

Note that there is no “RESULT CACHE” line.
And even if create own tables in SYS schema(don’t do it 🙂), they will not be cached :

SYS.V_AB

[sourcecode language=”sql”]
SYS> create table a as select ‘a’ a from dual;
SYS> create table b as select ‘b’ b from dual;
SYS> create view v_ab as select a,b from a,b;
SYS> grant select on v_ab to xtender;

XTENDER> explain plan for select/*+ result_cache */ * from sys.v_ab;
PLAN_TABLE_OUTPUT
—————————————————————————–
Plan hash value: 215283502

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 6 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 1 | 3 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | B | 1 | 3 | 2 (0)| 00:00:01 |
—————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)
[/sourcecode]

[collapse]

But sys_context and userenv will be cached successbully:

sys_context

[sourcecode language=”sql”]
SQL> explain plan for select/*+ result_cache */ sys_context(‘userenv’,’os_user’) from dual;

PLAN_TABLE_OUTPUT
—————————————————————————————
Plan hash value: 1388734953

—————————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 267m2hcwj08nq5kwxcb0nb2ka8 | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————————————

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ sys_context(‘userenv’,’os_user’) from dual"

14 rows selected.
[/sourcecode]

[collapse]

userenv

[sourcecode language=”sql”]
SQL> explain plan for select/*+ result_cache */ userenv(‘instance’) from dual;

PLAN_TABLE_OUTPUT
—————————————————————————————
Plan hash value: 1388734953

—————————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | dxzj3fks1sqfy35shbbst4332h | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
—————————————————————————————

Result Cache Information (identified by operation id):
——————————————————

1 – column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ userenv(‘instance’) from dual"
[/sourcecode]

[collapse]