SPEXP

Enjoy spexp tool http://valentinnikotin.com/spexp/

Some words on result cache dependencies tracking in 11.2.

First, every result of a function or query for which result cache is enabled has its own dependencies.
Second, all dependencies are tracked while the function/query is being executed.
Third,  the PL/SQL function result cache and the SQL result cache internals are the same.
Fourth, if you have a function within a query, for which result cache hint is used, the dependencies for the query result will include the dependencies on the objects accessed by the function.

This has several consequences.

1. One function may have different dependencies for different input arguments. Let’s look at the following example:

--drop table t1 purge;
--drop table t2 purge;
create table t1 (a number);
create table t2 (a number);
insert into t1 values (1);
insert into t2 values (1);
commit;
exec dbms_result_cache.flush
create or replace function f (i number)  
  return number
  result_cache 
is
  l_a number;
begin
  dbms_output.put_line('Hello!');
  execute immediate 'select a from t'||i into l_a;
  return l_a;
end;
/
select * from v$result_cache_dependency;
select id, type,   status, name, scan_count, scan_count from v$result_cache_objects;
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,   status, name, scan_count, scan_count from v$result_cache_objects;
select f(2) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select * from T1 for update;
commit;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(2) from dual;
update t2 set a = 2;
commit;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
select f(2) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;

I create 2 tables and populate them with one row:

SQL> create table t1 (a number);
 
Table created.
 
SQL> create table t2 (a number);
 
Table created.
 
SQL> insert into t1 values (1);
 
1 row created.
 
SQL> insert into t2 values (1);
 
1 row created.
 
SQL> commit;
 
Commit complete.

I flush cache to ensure result repeatability:

SQL> exec dbms_result_cache.flush
 
PL/SQL procedure successfully completed.

I create a simple function which uses one of the two tables depending on input value. It also uses dynamic query so that the dependencies of the function can’t be analyzed during the compilation. Every time the function is being executed, ‘Hello!’ is displayed:

SQL> create or replace function f (i number)
  2    return number
  3    result_cache
  4  is
  5    l_a number;
  6  begin
  7    dbms_output.put_line('Hello!');
  8    execute immediate 'select a from t'||i into l_a;
  9    return l_a;
 10  end;
 11  /
 
Function created.

In result cache views we can see that immediately after the creation there aren’t any dependencies or objects:

SQL> select * from v$result_cache_dependency;
 
no rows selected
 
SQL> select id, type,   status, name, scan_count, scan_count from v$result_cache_objects;
 
no rows selected

Now we execute the function so that the function has to access table T1. We see that the function was executed because ‘Hello!’ is displayed:

SQL> select f(1) from dual;
 
      F(1)
----------
         1
 
Hello!

When we look at the views again, we can see one result there that depends on two objects: table T1 and function F:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         1          2     866333
         1          0     865432
 
SQL> select id, type,   status, name, scan_count, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ---------- ----------
         2 Dependency Published LB.T1                                             0          0
         0 Dependency Published LB.F                                              0          0
         1 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0          0

Let’s try calling the function with a different argument, so that it has to access table T2, And again we see that the function was executed:

SQL> select f(2) from dual;
 
      F(2)
----------
         1
 
Hello!

After it’s done we can see that there is one more result that also has two dependencies: table T2 and function F:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         3          4     866334
         1          2     866333
         3          0     865432
         1          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         3 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0

Now we run DML on T1:

SQL> select * from T1 for update;
 
         A
----------
         1
 
SQL> commit;
 
Commit complete.

It leads to the invalidation of the dependent result:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         3          4     866334
         3          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         3 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               0

When we call the function again with the same argument, it will be executed again, instead of using the cached result:

SQL> select f(1) from dual;
 
      F(1)
----------
         1
 
Hello!

And once again we get a new result with its own dependencies:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         3          4     866334
         5          2     866333
         3          0     865432
         5          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         5 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         3 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               0
 
6 rows selected.

However, if we run a function with argument 2 for the other cached result (the one that doesn’t depend on T1), it won’t be executed, but it’ll use the cached result instead:

SQL> select f(2) from dual;
 
      F(2)
----------
         1

Now we run DML on table T2:

SQL> update t2 set a = 2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.

It causes the invalidation of the second result:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         5          2     866333
         5          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         5 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         3 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
 
6 rows selected.

And if we run the function again with argument 1 it won’t really be executed:

SQL> select f(1) from dual;
 
      F(1)
----------
         1

Calling the function with argument 2 after invalidation is what causes real re-execution and displaying the result in the result cache views:

SQL> select f(2) from dual;
 
      F(2)
----------
         2
 
Hello!
SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         6          4     866334
         5          2     866333
         6          0     865432
         5          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         6 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         5 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               1
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         3 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
 
7 rows selected.

2. The same function may have different dependencies for the same input argument at different times.
See example below:

drop table t1 purge;
drop table t2 purge;
create table t1 (a number);
create table t2 (a number);
insert into t1 values (1);
insert into t2 values (1);
commit;
exec dbms_result_cache.flush
create or replace function f (i number) 
  return number
  result_cache 
is
  l_a number;
begin
  dbms_output.put_line('Hello!');
  select t.a into l_a from t1 t;
  if l_a = 2 then
    select t.a into l_a from t2 t;
  end if;
  return l_a;
end;
/
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
update t2 set a = 2;
commit;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
update t1 set a = 2;
commit;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
update t2 set a = 2;
commit;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
update t1 set a = 1;
commit;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;

Once again, I create 2 tables and populate them with one row; and then I flush cache:

SQL> drop table t1 purge;
 
Table dropped.
 
SQL> drop table t2 purge;
 
Table dropped.
 
SQL> create table t1 (a number);
 
Table created.
 
SQL> create table t2 (a number);
 
Table created.
 
SQL> insert into t1 values (1);
 
1 row created.
 
SQL> insert into t2 values (1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_result_cache.flush
 
PL/SQL procedure successfully completed.

I create a function which uses table T1 or both tables T1 and T2. Whether or not table T2 will be accessed depends on the data from T1:

SQL> create or replace function f (i number)
  2    return number
  3    result_cache
  4  is
  5    l_a number;
  6  begin
  7    dbms_output.put_line('Hello!');
  8    select t.a into l_a from t1 t;
  9    if l_a = 2 then
 10      select t.a into l_a from t2 t;
 11    end if;
 12    return l_a;
 13  end;
 14  /
 
Function created.

We execute the function so that the function has to access table T1 only. We can see ‘Hello!’ on our screens, and this is how we know that the result was calculated:

SQL> select f(1) from dual;
 
      F(1)
----------
         1
 
Hello!

We can check the dependencies the same way we did in the previous example:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         1          2     866336
         1          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         1 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0

Next time we call the function it will use the cached result, so we won’t see the ‘Hello!’:

SQL> select f(1) from dual;
 
      F(1)
----------
         1

Then we update table T2 which is explicitly referenced in the function code:

SQL> update t2 set a = 2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.

Nothing happened to the dependencies:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         1          2     866336
         1          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         1 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               1

Executon of the function will still use the cached result, instead of function body execution:

SQL> select f(1) from dual;
 
      F(1)
----------
         1

After that we update table T1 so that the function must access table T2:

SQL> update t1 set a = 2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.

Now we see that the result has become invalid:

SQL> select * from v$result_cache_dependency;
 
no rows selected
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               2

When we execute the function after updating table T1, it causes the function body re-execution and accesses table T2:

SQL> select f(1) from dual;
 
      F(1)
----------
         2
 
Hello!

Thus we have dependencies on tables T1 and T2 for a new published result:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         3          4     866337
         3          2     866336
         3          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         3 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               2

Next execution of the function will use the cached result:

SQL> select f(1) from dual;
 
      F(1)
----------
         2

As the last result has a dependency on table T2, the following update will cause result invalidation:

SQL> update t2 set a = 2;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from v$result_cache_dependency;
 
no rows selected
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               2
         3 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1

Then we check the re-execution, new result publishing and using cached result at second execution:

SQL> select f(1) from dual;
 
      F(1)
----------
         2
 
Hello!
SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         5          4     866337
         5          2     866336
         5          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         5 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               2
         3 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
 
6 rows selected.
 
SQL> select f(1) from dual;
 
      F(1)
----------
         2

When we modify table T1 data back to the initial value, the function doesn’t need to access table T2:

SQL> update t1 set a = 1;
 
1 row updated.
 
SQL> commit;
 
Commit complete.

We see that the last result has been invalidated:

SQL> select * from v$result_cache_dependency;
 
no rows selected
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               2
         3 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
         5 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
 
6 rows selected.

Re-execuition leads to the creation of a new result with no dependency on table T2:

SQL> select f(1) from dual;
 
      F(1)
----------
         1
 
Hello!
SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         6          2     866336
         6          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         4 Dependency Published LB.T2                                             0
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F                                              0
         6 Result     Published "LB"."F"::8."F"#fac892c7867b54c6 #1               0
         1 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               2
         3 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
         5 Result     Invalid   "LB"."F"::8."F"#fac892c7867b54c6 #1               1
 
7 rows selected.

3. If we have a branch in our code in which a dependent object can be bypassed, it can cause a dead end situation, and the result cache can change its value only when it ages out from the cache or if the cache is flushed.
Example:

drop table t1 purge;
create table t1 as select 1 a from dual;
exec dbms_result_cache.flush
create or replace function f (i number)
  return varchar2
  result_cache
is
  res varchar2(33);
  ex_table_does_not_not_exist exception;
  pragma exception_init (ex_table_does_not_not_exist, -942);
begin
  execute immediate 'select * from t'||i into res;
  return res;
exception
  when ex_table_does_not_not_exist then
    return 'no table';
end;
/
select f(1) from dual;
drop table t1 purge;
select f(1) from dual;
create table t1 as select 1 a from dual;
select f(1) from dual;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;

The table and cache flush:

SQL> drop table t1 purge;
 
Table dropped.
 
SQL> create table t1 as select 1 a from dual;
 
Table created.
 
SQL> exec dbms_result_cache.flush
 
PL/SQL procedure successfully completed.

The function, that dynamically queries the table, and, if the table doesn’t exist, returns ‘no table’:

SQL> create or replace function f (i number)
  2    return varchar2
  3    result_cache
  4  is
  5    res varchar2(33);
  6    ex_table_does_not_not_exist exception;
  7    pragma exception_init (ex_table_does_not_not_exist, -942);
  8  begin
  9    execute immediate 'select * from t'||i into res;
 10    return res;
 11  exception
 12    when ex_table_does_not_not_exist then
 13      return 'no table';
 14  end;
 15  /
 
Function created.

Testing the function. It works and returns ’1′:

SQL> select f(1) from dual;
 
F(1)
----
1 

Then I drop table T1:

SQL> drop table t1 purge;
 
Table dropped.

Again, everything is OK, it returns ‘no table’:

SQL> select f(1) from dual;
 
F(1)
----
no table 

Then I create a table T1 again:

SQL> create table t1 as select 1 a from dual;
 
Table created.

And I re-execute the function, but the result doesn’t change (!):

SQL> select f(1) from dual;
 
F(1)
----
no table  

The result has no dependencies on anything except for the function itself:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         3          0     865432
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         2 Dependency Published object-id(866347)                                 0
         0 Dependency Published LB.F                                              0
         3 Result     Published "LB"."F"::8."F"#762ba075453b8b0d #1               1
         1 Result     Invalid   "LB"."F"::8."F"#762ba075453b8b0d #1               0

4. Unusual behaviour of the scalar functions in the select list:

drop table t1 purge;
drop table t2 purge;
create table t1 (a number);
create table t2 (a number);
insert into t1 values (1);
insert into t2 values (1);
insert into t2 values (2);
commit;
exec dbms_result_cache.flush
exec dbms_stats.gather_table_stats(user, 't2')
create or replace function f_scal(i number) return number is
  l_a number;
begin
  dbms_output.put_line('Hello, I''am F_SCAL with argument i = '||i);
  select t.a into l_a from t1 t;
  return l_a;
end;
/
select /*+ result_cache */ f_scal(a) from t2;
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select /*+ result_cache */ f_scal(a) from t2;
select /*+ result_cache */ a from t2 where f_scal(a) = 1;
select /*+ ... */ f_scal(a) from t2;
select /*+ result_cache */ * from (select /*+ no_merge */ f_scal(a) from t2);
explain plan for
select /*+ result_cache */ f_scal(a) from t2;
select * from table(dbms_xplan.display(null, null, 'projection'));
set serveroutput off
select /*+ result_cache gather_plan_statistics */ f_scal(a) from t2;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

The table and cache flush:

SQL> drop table t1 purge;
 
Table dropped.
 
SQL> drop table t2 purge;
 
Table dropped.
 
SQL> create table t1 (a number);
 
Table created.
 
SQL> create table t2 (a number);
 
Table created.
 
SQL> insert into t1 values (1);
 
1 row created.
 
SQL> insert into t2 values (1);
 
1 row created.
 
SQL> insert into t2 values (2);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_result_cache.flush
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_table_stats(user, 't2')
 
PL/SQL procedure successfully completed.

Function creation:

SQL> create or replace function f_scal(i number) return number is
  2    l_a number;
  3  begin
  4    dbms_output.put_line('Hello, I''am F_SCAL with argument i = '||i);
  5    select t.a into l_a from t1 t;
  6    return l_a;
  7  end;
  8  /
 
Function created.

I run a query with result_cache hint and function F in the select list. We see that the function is executed 4 times, 2 times for each row:

SQL> select /*+ result_cache */ f_scal(a) from t2;
 
 F_SCAL(A)
----------
         1
         1
 
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 2
Hello, I'am F_SCAL with argument i = 2

We can also see the result and its dependencies both on table T2 from the query and on table T1 from the function:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         2          3     866429
         2          1     866430
         2          0     866326
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         3 Dependency Published LB.T1                                             0
         1 Dependency Published LB.T2                                             0
         0 Dependency Published LB.F_SCAL                                         0
         2 Result     Published select /*+ result_cache */ f_scal(a)              0
                                from t2

If we execute the query again it won’t be executed but instead the cached value will be used:

SQL> select /*+ result_cache */ f_scal(a) from t2;
 
 F_SCAL(A)
----------
         1
         1

If we move the function to where, the function will be executed only — If we put query with function insideonce for each row:

SQL> select /*+ result_cache */ a from t2 where f_scal(a) = 1;
 
         A
----------
         1
         2
 
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 2

If we remove the hint from the query, the function will be executed once for each row each time the query is executed:

SQL> select /*+ ... */ f_scal(a) from t2;
 
 F_SCAL(A)
----------
         1
         1
 
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 2

If we place the query into the inline view with no_megre hint, it will also be executed once for each row every time the query’s executed:

SQL> select /*+ result_cache */ * from (select /*+ no_merge */ f_scal(a) from t2);
 
 F_SCAL(A)
----------
         1
         1
 
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 1
Hello, I'am F_SCAL with argument i = 2

So it seems that there the code for RESULT CACHE step of the plan in Oracle is non-optimal:

SQL> explain plan for
  2  select /*+ result_cache */ f_scal(a) from t2;
 
Explained.

I formatted the output, and now you can see that the function was executed during step 1. One can see that the dependencies don’t contain the dependencies of the function when it is being explained:

SQL> select * from table(dbms_xplan.display(null, null, 'projection'));
 
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 1513984157
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |     2 |     6 |     3   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | 22b0jttg8zubd00uxu3mpgy2g1 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T2                         |     2 |     6 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Column Projection Information (identified by operation id):
   1 - "F_SCAL"("A")[22]
   2 - "A"[NUMBER,22]
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; 
       dependencies=(LB.F_SCAL, LB.T2); 
       attributes=(dynamic); 
       name="select /*+ result_cache */ f_scal(a) from t2"
 
20 rows selected.

If we try using display_cursor, the Result Cache Information part will be empty:

SQL> set serveroutput off
SQL> select /*+ result_cache gather_plan_statistics */ f_scal(a) from t2;
 
 F_SCAL(A)
----------
         1
         1
 
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID  aya06m73at8g6, child number 0
-------------------------------------
select /*+ result_cache gather_plan_statistics */ f_scal(a) from t2
 
Plan hash value: 1513984157
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |      1 |        |      2 |00:00:00.01 |
|   1 |  RESULT CACHE      | 22b0jttg8zubd00uxu3mpgy2g1 |      1 |        |      2 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL| T2                         |      0 |      2 |      0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 -
 
19 rows selected.

5. You can also take advantage of using result_cache in queries with pipelined functions for which result_cache clause is not allowed:

drop table t1 purge;
create table t1 (a number);
insert into t1 values (1);
commit;
exec dbms_result_cache.flush
create or replace function f_pipe return sys.odcinumberlist pipelined is
begin
  dbms_output.put_line('Hello, I''am F_PIPE!');
  for rec in (select a from t1) loop
    pipe row (rec.a);
  end loop;  
end;
/
select /*+ result_cache */ column_value from table(f_pipe);
select * from v$result_cache_dependency;
select id, type,  status, name, scan_count from v$result_cache_objects;
select /*+ result_cache */ column_value from table(f_pipe);
select id, type,  status, name, scan_count from v$result_cache_objects where type = 'Result';

The table and cache flush:

SQL> drop table t1 purge;
 
Table dropped.
 
SQL> create table t1 (a number);
 
Table created.
 
SQL> insert into t1 values (1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_result_cache.flush
 
PL/SQL procedure successfully completed.

Pipelined function creation:

SQL> create or replace function f_pipe return sys.odcinumberlist pipelined is
  2  begin
  3    dbms_output.put_line('Hello, I''am F_PIPE!');
  4    for rec in (select a from t1) loop
  5      pipe row (rec.a);
  6    end loop;
  7  end;
  8  /
 
Function created.

I execute the query with the function:

SQL> select /*+ result_cache */ column_value from table(f_pipe);
 
COLUMN_VALUE
------------
           1
 
Hello, I'am F_PIPE!

We see that for the previous result all the dependencies required are present:

SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         1          2     866431
         1          0     859231
 
SQL> select id, type,  status, name, scan_count from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         2 Dependency Published LB.T1                                             0
         0 Dependency Published LB.F_PIPE                                         0
         1 Result     Published select /*+ result_cache */ column_value           0
                                from table(f_pipe)

Re-execution uses the result cache:

SQL> select /*+ result_cache */ column_value from table(f_pipe);
 
COLUMN_VALUE
------------
           1

SQL> select id, type,  status, name, scan_count from v$result_cache_objects where type = 'Result';
 
        ID TYPE       STATUS    NAME                                     SCAN_COUNT
---------- ---------- --------- ---------------------------------------- ----------
         1 Result     Published select /*+ result_cache */ column_value           1
                                from table(f_pipe)

Comments in hint

 

It seems that CBO can work with the line comment inside sql hints.

Let’s see. I create a table with 3 indexes:

--drop table ttt purge;
create table ttt
(
  a number not null,
  b number not null,
  c number not null
);

insert into ttt
  select 1,1,1
    from dual connect by level <= 10000;
commit;

create index iiia on ttt(a);
create index iiib on ttt(b);
create index iiic on ttt(c);

And then I’m able to switch access paths by commenting:

explain plan for
select /*+
         --full(ttt)
         --index(ttt(c))
         index(ttt(a))
         --index(ttt(b))
       */
       count(*)
from ttt;
select * from table(dbms_xplan.display);

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| IIIA | 10000 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------

explain plan for
select /*+
         --full(ttt)
         index(ttt(c))
         --index(ttt(a))
         --index(ttt(b))
       */
       count(*)
from ttt;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| IIIC | 10000 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------

It also works fine with the line hint itself:

explain plan for
select --+ index(ttt(b)) -- full(ttt)
count(*)
from ttt;
select * from table(dbms_xplan.display);

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| IIIB | 10000 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------

Here is one little detail, in PL/SQL you can accidentally comment on the end of the hint as well as the part of the hint and get syntax error:

SQL> declare
2    cnt number;
3  begin
4    select /*+ --full(ttt)
5               index(ttt(c))
6               --index(ttt(a)) index(ttt(b))
7               --index(ttt(b)) */
8         count(*)
9    into cnt
10    from ttt;
11  end;
12  /
declare
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
ORA-06512: at line 4

It seems to be a bug, as the hint is a comment itself, so it can contain anything that is allowed for the comments:

SQL> begin
2    /*
3    --*/
4    null;
5  end;
6  /

PL/SQL procedure successfully completed.

Summary. It is still undocumented behaviour, but you may use it while testing hinting strategy.

Follow

Get every new post delivered to your Inbox.

Join 55 other followers