Result Cache in PL/SQL Packages

To use result cache with any function or procedure in a PL/SQL package result_cache must be defined on the specification.

Create Or Replace Package Result_Cache_Test As
Function Get_Country_City (In_City In Varchar2, In_Country In Varchar2) 
Return Varchar2 
result_cache;
end;
/

On the package body list the set of tables underlying sql relies on

Create Or Replace Package Body Result_Cache_Test As
Function Get_Country_City(In_City In Varchar2, In_Country In Varchar2) 
Return Varchar2
Result_Cache  
Relies_On (city,country)
Is
    Ret_City Varchar2(200);
    ret_country varchar2(200);
    Begin
      Select Ci.Name,Co.Name Into Ret_City,Ret_Country
      From City Ci, Country Co
      Where Ci.Country = Co.Code
      And Ci.Code = In_City
      And Co.Code = In_Country;
      
      return ret_city || ':'||ret_country;
    end;
end;
/

Test with PL/SQL Code

set serveroutput on
Declare
val varchar2(400);
Begin
Val := Result_Cache_Test.Get_Country_City('SIN','SG');
dbms_output.put_line(val);
end;
/

Or to know the effects of Result Cache Latch on concurrent access simulate with java code. Main class

public class Main {

    public static void main(String[] args) {

         try {

            OracleDataSource dataSource = new OracleDataSource();
            dataSource.setURL("jdbc:oracle:thin:@url");
            dataSource.setUser("username");
            dataSource.setPassword("password");

            String[][] cities = {{"CMB","LK"},
                                {"LAX","US"},
                                {"SIN","SG"},
                                {"LON","GB"}
                                 };

            int loops = 1000;
            WorkerThread[] threads = new WorkerThread[400];
            String sql1 = "begin ? := result_cache_test.Get_Country_City(?,?); end;";

            for (int i = 0 ; i < threads.length ; i++){

             threads[i] =  new WorkerThread(dataSource.getConnection(), sql1,cities,loops);

            }

            for(int i = 0 ; i < threads.length; i++){

                threads[i].start();
            }
   
        } catch (Exception ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Worker Class

public class WorkerThread extends Thread {

    private Connection con;
    String sql;
    String[][] data;
    int loops;

    public WorkerThread(Connection con, String sql, String[][] data, int loops) {

        this.con = con;
        this.sql = sql;
        this.data = data;
        this.loops = loops;
    }

    @Override
    public void run() {
        try {
            CallableStatement clm;
            
            for (int i = 0; i < loops; i++) {
                
                clm = con.prepareCall(sql);
                clm.registerOutParameter(1, Types.VARCHAR);
                clm.setString(2, data[i%4][0]);
                clm.setString(3, data[i%4][1]);
                clm.execute();
                String out = clm.getString(1);
                Logger.getLogger(WorkerThread.class.getName()).log(Level.INFO,getName() + " "+out);
                clm.close();
            }

            con.close();

        } catch (SQLException ex) {
            Logger.getLogger(WorkerThread.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

View latch misses, sleeps with

select name,gets, misses, sleeps, wait_time 
from V$latch where Name like '%Result%';

NAME                         GETS     MISSES     SLEEPS  WAIT_TIME
---------------------- ---------- ---------- ---------- ----------
Result Cache: Latch       1576450         13          3        681
Result Cache: SO Latch       2439          5          2        787

This test was done on a 4 cpu server on 11gR1 with PSU 11.1.0.7.4 applied.

As of 11gR2 'relies_on' is deprecated. From PL/SQL Language Ref Guide "Release 11.2, the database detects all data sources that are queried while a result-cached function is running, and relies_on_clause does nothing. This clause is deprecated."