drop table t1 purge;
drop table t2 purge;
drop function f1;
set echo on;
create table t1 as select level a, level b from dual connect by level<=100;
create table t2 as select level a, level b from dual connect by level<=100;
create function f1 return int as
   res int;
begin
   select count(*) into res from t2;
   return res;
end;
/
alter system flush shared_pool;

alter table t1 result_cache (mode force);
set feed on sql_id;
select count(*) cnt from t1 where f1>0;
def rc_sqlid = &_sql_id
col result_cache for a15;
select sql_id,child_number,plan_hash_value,invalidations,optimizer_env_hash_value,result_cache from v$sql s where sql_id='&rc_sqlid';
col PLAN_TABLE_OUTPUT for a120;
select t.* from v$sql s,table(dbms_xplan.display_cursor('&rc_sqlid',child_number)) t
where s.sql_id='&rc_sqlid';
col object_name new_val cache_id;
select object_name from v$sql_plan p where p.sql_id='&rc_sqlid' and operation='RESULT CACHE';
col name for a40;
col cache_id for a10;
col cache_key for a10;
select o.id,o.name, 
       d.*,
       do.cache_id, do.cache_key,do.object_no
from  v$result_cache_objects o
     ,v$result_cache_dependency d
     ,v$result_cache_objects do
where o.cache_id='&cache_id'
  and d.result_id=o.id
  and do.id = d.depend_id;
set echo off;

SQL> create table t1 as select level a, level b from dual connect by level<=100;

Table created.

SQL> create table t2 as select level a, level b from dual connect by level<=100;

Table created.

SQL> create function f1 return int as
  2     res int;
  3  begin
  4     select count(*) into res from t2;
  5     return res;
  6  end;
  7  /

Function created.

SQL> alter table t1 result_cache (mode force);

Table altered.

SQL> set feed on sql_id;
SQL> select count(*) cnt from t1 where f1>0;

       CNT
----------
       100

1 row selected.

SQL_ID: a9nyksby4dfqm
SQL> def rc_sqlid = &_sql_id
SQL> col result_cache for a15;
SQL> select sql_id,child_number,plan_hash_value,invalidations,optimizer_env_hash_value,result_cache from v$sql s where sql_id='&rc_sqlid';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE INVALIDATIONS OPTIMIZER_ENV_HASH_VALUE RESULT_CACHE
------------- ------------ --------------- ------------- ------------------------ ---------------
a9nyksby4dfqm            0      4294799605             0               1605398594 Y

1 row selected.

SQL_ID: bf2z9mfkdwnb1
SQL> col PLAN_TABLE_OUTPUT for a120;
SQL> select t.* from v$sql s,table(dbms_xplan.display_cursor('&rc_sqlid',child_number)) t
  2  where s.sql_id='&rc_sqlid';

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  a9nyksby4dfqm, child number 0
-------------------------------------
select count(*) cnt from t1 where f1>0

Plan hash value: 4294799605

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name                       | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |       |     3 (100)|          |
|   1 |  RESULT CACHE        | 0d95aym0kvw37casnbgjdnqzfh |     1 |            |          |
|   2 |   SORT AGGREGATE     |                            |     1 |            |          |
|*  3 |    FILTER            |                            |       |            |          |
|   4 |     TABLE ACCESS FULL| T1                         |   100 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("F1"()>0)

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

   1 -

SQL> col object_name new_val cache_id;
SQL> select object_name from v$sql_plan p where p.sql_id='&rc_sqlid' and operation='RESULT CACHE';

OBJECT_NAME
------------------------------
0d95aym0kvw37casnbgjdnqzfh

1 row selected.

SQL> col name for a40;
SQL> col cache_id for a10;
SQL> col cache_key for a10;
SQL> select o.id,o.name,
  2         d.*,
  3         do.cache_id, do.cache_key,do.object_no
  4  from  v$result_cache_objects o
  5       ,v$result_cache_dependency d
  6       ,v$result_cache_objects do
  7  where o.cache_id='&cache_id'
  8    and d.result_id=o.id
  9    and do.id = d.depend_id;

        ID NAME                                      RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID CACHE_ID   CACHE_KEY   OBJECT_NO
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        64 select count(*) cnt from t1 where f1>0           64         66      79027          3 XTENDER.T2 XTENDER.T2      79027
        64 select count(*) cnt from t1 where f1>0           64         95      79026          3 XTENDER.T1 XTENDER.T1      79026
        64 select count(*) cnt from t1 where f1>0           64         71      78995          3 XTENDER.F1 XTENDER.F1      78995

3 rows selected.