This discussion is archived
10 Replies Latest reply: Aug 10, 2012 11:12 AM by rp0428 RSS

Client side result set cache

user451278 Newbie
Currently Being Moderated
Hello,

I try to get the client side result set cache working, but i have no luck :-(
I'm using Oracle Enterprise Edition 11.2.0.1.0 and as client diver 11.2.0.2.0.

Executing the query select /*+ result_cache*/ * from p_item via sql plus or toad will generate an nice execution plan with an RESULT CACHE node and the v$result_cache_objects contains some rows.

After I've check the server side cache works. I want to cache the client side

My simple Java Application looks like

     private static final String ID = UUID.randomUUID().toString();
     
     private static final String JDBC_URL = "jdbc:oracle:oci:@server:1521:ORCL";
     private static final String USER = "user";
     private static final String PASSWORD = "password";

     public static void main(String[] args) throws SQLException {
          
          OracleDataSource ds = new OracleDataSource();
          ds.setImplicitCachingEnabled(true);               
          ds.setURL( JDBC_URL );
          ds.setUser( USER );
          ds.setPassword( PASSWORD );

          String sql = "select /*+ result_cache */ /* " + ID + " */ * from p_item d " +
                         "where d.i_size = :1";

          for( int i=0; i<100; i++ ) {

               OracleConnection connection = (OracleConnection) ds.getConnection();
               connection.setImplicitCachingEnabled(true);
               connection.setStatementCacheSize(10);
               OraclePreparedStatement stmt = (OraclePreparedStatement) connection.prepareStatement( sql );
               stmt.setLong( 1, 176 );
          
               ResultSet rs = stmt.executeQuery();

               int count = 0;
               for(; rs.next(); count++ );
     
               rs.close();
               stmt.close();
               
               System.out.println( "Execution: " + getExecutions(connection) + " Fetched: " + count );
               
               connection.close();
               
          }
     }
     
     private static int getExecutions( Connection connection ) throws SQLException {
          
          String sql = "select executions from v$sqlarea where sql_text like ?";
          
          PreparedStatement stmt = connection.prepareStatement(sql);
          stmt.setString(1, "%" + ID + "%" );
          ResultSet rs = stmt.executeQuery();
          
          if( rs.next() == false )
               return 0;
          
          int result = rs.getInt(1);
          
          if( rs.next() )
               throw new IllegalArgumentException("not unique");
          
          rs.close();
          
          stmt.close();
          
          return result;
     }

100 times the same query is executed and the statement exection count is incemented every time. I expect just 1 statement execution ( client database roundtrip ) and 99 hits in client result set cache. The view CLIENT_RESULT_CACHE_STATS$ is empty :-(

I'm using the oracle documentation at http://download.oracle.com/docs/cd/E14072_01/java.112/e10589/instclnt.htm#BABEDHFF and I don't kown why it does't work :-(

I'm thankful for every tip,

André Kullmann
  • 1. Re: Client side result set cache
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    Print/log your 'sql' variable just before you use it.

    Compare that output to your other cases.
  • 2. Re: Client side result set cache
    user451278 Newbie
    Currently Being Moderated
    Hi,

    my sql var is an 'static' string and looks like

    select /*+ result_cache \*/ /* 700319c0-57fc-4bc4-8312-f28989862a9e */ * from p_item d where d.i_size = :1

    I couldn't see anx misstake :-(

    The random string in the second comment helps to select the statement metadata from v$sqlarea.

    Regards,
    André
  • 3. Re: Client side result set cache
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    Other than that best I can suggest is that I think there is some way to turn on database logging.

    Figure that out, then see what the difference is between the two apps in terms of what they send.

    Additionally try using a hardcoded Statement, not PreparedStatement, to see if that makes any difference.
  • 4. Re: Client side result set cache
    user451278 Newbie
    Currently Being Moderated
    Hello,

    the difference between the pl/sql script and jdbc client is the caching strategy.
    If you use the result set caching on server side the result set will be cached on server ( sga ).
    But I want to use the client side cache, the result set should be cache on client side and there is no server roundtrip needed to fetch an cache result set.

    I used the oracle documention to write my client side cache test, but it doesn't work :-(
    I used the simple server cache test just to ensure that the result set cache feature is enabled.
  • 5. Re: Client side result set cache
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    user451278 wrote:
    the difference between the pl/sql script and jdbc client is the caching strategy.
    To be clear that makes no sense - the two are not comparable.

    So presumably you meant sqlplus for the first.
    If you use the result set caching on server side the result set will be cached on server ( sga ).
    But I want to use the client side cache, the result set should be cache on client side and there is no server roundtrip needed to fetch an cache result set.
    Ahh...no that is not how it works.

    It does not cache it in the client. That includes your sqlplus example.

    The option you are referring to is a way to tell the database server to cache the results of a query in the server. Not the client.

    Some discussion on what 'result_cache' does.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:676698900346506951

    So if you thought this was going to move it to your client then no point in going on.

    Otherwise there might be some obtuse option in the connection string that might have some impact.
  • 6. Re: Client side result set cache
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Hi André,

    I'm by no means a JDBC expert but I have successfully used the Client Result Cache feature exposed by OCI in other environments.

    One thing to confirm is that the server parameter CLIENT_RESULT_CACHE_SIZE has a value of 32K or greater or the OCI client-side cache will not be enabled.

    This parameter defaults to 0 meaning the client-side cache is disabled by default.

    As a DBA user you can determine the value of this parameter with the following query:
    select name, value from v$parameter where name = 'client_result_cache_size';
    To change the value you would do something similar to the following (making changes where appropriate):
    alter system set client_result_cache_size=8M scope=spfile;
    Note that this parameter is a static parameter, so if you change the value you must restart the Oracle instance for the new value to be used.

    In addition to the link you already posted, you can read more about the client-side cache (from an OCI perspective) here:

    http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10646/oci10new.htm#CHDGGFEF

    Perhaps it will be as simple as setting the client_result_cache_size parameter and restarting the instance in your case.

    Regards,

    Mark
  • 7. Re: Client side result set cache
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    I wanted to post a follow-up to (hopefully) clear up a point of potential confusion. That is, with the OCI Client Result Cache, the results are indeed cached on the client in memory managed by OCI.

    As I mentioned in my previous reply, I am not a JDBC (or Java) expert so there is likely a great deal of improvement that can be made to my little test program. However, it is not intended to be exemplary, didactic code - rather, it's hopefully just enough to illustrate that the caching happens on the client (when things are configured correctly, etc).

    My environment for this exercise is Windows 7 64-bit, Java SE 1.6.0_27 32-bit, Oracle Instant Client 11.2.0.2 32-bit, and Oracle Database 11.2.0.2 64-bit.

    Apologies if this is a messy post, but I wanted to make it as close to copy/paste/verify as possible.

    Here's the test code I used:
    import java.sql.ResultSet;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import oracle.jdbc.pool.OracleDataSource;
    import oracle.jdbc.OracleConnection;
    
    class OCIResultCache
    {
      public static void main(String args []) throws SQLException
      {
        OracleDataSource ods = null;
        OracleConnection conn = null;
        PreparedStatement stmt = null;
        ResultSet rset = null;
    
        String sql1 = "select /*+ no_result_cache */ first_name, last_name " +
                      "from hr.employees";
    
        String sql2 = "select /*+ result_cache */ first_name, last_name " +
                      "from hr.employees";
    
        int fetchSize = 128;
    
        long start, end;
    
        try
        {
          ods = new OracleDataSource();
          ods.setURL("jdbc:oracle:oci:@liverpool:1521:V112");
          ods.setUser("orademo");
          ods.setPassword("orademo");
    
          conn = (OracleConnection) ods.getConnection();
          conn.setImplicitCachingEnabled(true);
          conn.setStatementCacheSize(20);
    
          stmt = conn.prepareStatement(sql1);
          stmt.setFetchSize(fetchSize);
    
          start = System.currentTimeMillis();
    
          for (int i=0; i < 10000; i++)
          {
            rset = stmt.executeQuery();
    
            while (rset.next())
              ;
    
            if (rset != null) rset.close();
          }
    
          end = System.currentTimeMillis();
    
          if (stmt != null) stmt.close();
    
          System.out.println();
    
          System.out.println("Execution time [sql1] = " + (end-start) + " ms.");
    
          stmt = conn.prepareStatement(sql2);
          stmt.setFetchSize(fetchSize);
    
          start = System.currentTimeMillis();
    
          for (int i=0; i < 10000; i++)
          {
            rset = stmt.executeQuery();
    
            while (rset.next())
              ;
    
            if (rset != null) rset.close();
          }
    
          end = System.currentTimeMillis();
    
          if (stmt != null) stmt.close();
    
          System.out.println();
    
          System.out.println("Execution time [sql2] = " + (end-start) + " ms.");
    
          System.out.println();
    
          System.out.print("Enter to continue...");
          System.console().readLine();
        }
        finally
        {
          if (rset != null) rset.close();
          if (stmt != null) stmt.close();
          if (conn != null) conn.close();
        }
      }
    }
    In order to show that the results are cached on the client and thus server round-trips are avoided, I generated a 10046 level 12 trace from the database for this session. This was done using the following database logon trigger:
    create or replace trigger logon_trigger
    after logon on database
    begin
      if (user = 'ORADEMO') then
        execute immediate
        'alter session set events ''10046 trace name context forever, level 12''';
      end if;
    end;
    /
    With that in place I then did some environmental setup and executed the test:
    C:\Projects\Test\Java\OCIResultCache>set ORACLE_HOME=C:\Oracle\instantclient_11_2
    
    C:\Projects\Test\Java\OCIResultCache>set CLASSPATH=.;%ORACLE_HOME%\ojdbc6.jar
    
    C:\Projects\Test\Java\OCIResultCache>set PATH=%ORACLE_HOME%\;%PATH%
    
    C:\Projects\Test\Java\OCIResultCache>java OCIResultCache
    
    Execution time [sql1] = 1654 ms.
    
    Execution time [sql2] = 686 ms.
    
    Enter to continue...
    This is all on my laptop, so results are not stellar in terms of performance; however, you can see that the portion of the test that uses the OCI client result cache did execute in approximately half of the time as the non-cached portion.

    But, the more compelling data is in the resulting trace file which I ran through the tkprof utility to make it nicely formatted and summarized:
    SQL ID: cqx6mdvs7mqud Plan Hash: 2228653197
    
    select /*+ no_result_cache */ first_name, last_name 
    from
     hr.employees
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute  10000      0.10       0.10          0          0          0           0
    Fetch    10001      0.49       0.54          0      10001          0     1070000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    20002      0.60       0.65          0      10001          0     1070000
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 94  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
           107        107        107  INDEX FULL SCAN EMP_NAME_IX (cr=2 pr=0 pw=0 time=21 us cost=1 size=1605 card=107)(object id 75241)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                   10001        0.00          0.00
      SQL*Net message from client                 10001        0.00          1.10
    ********************************************************************************
    
    SQL ID: frzmxy93n71ss Plan Hash: 2228653197
    
    select /*+ result_cache */ first_name, last_name 
    from
     hr.employees
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.01          0         11         22           0
    Fetch        2      0.00       0.00          0          0          0         107
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.01          0         11         22         107
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 94  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
           107        107        107  RESULT CACHE  0rdkpjr5p74cf0n0cs95ntguh7 (cr=0 pr=0 pw=0 time=12 us)
             0          0          0   INDEX FULL SCAN EMP_NAME_IX (cr=0 pr=0 pw=0 time=0 us cost=1 size=1605 card=107)(object id 75241)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      log file sync                                   1        0.00          0.00
      SQL*Net message from client                     2        1.13          1.13
    The key differences here are the execute, fetch, and SQL*Net message values. Using the client-side cache, the values drop dramatically due to getting the results from client memory rather than round-trips to the server.

    Of course, corrections, clarifications, etc. welcome and so on...

    Regards,

    Mark
  • 8. Re: Client side result set cache
    user451278 Newbie
    Currently Being Moderated
    Amazing !! It works !!

    After I enabled the connection caching on the DataSource using the deprecated method setConnectionCachingEnabled(true), the statement will be executed only one time. My understanding is that the connection pooling is enabled with setImplicitCachingEnabled(true) was wrong.

    Thanks a lot for your awesome explanation !!

    Regards,
    André
  • 9. Re: Client side result set cache
    807722 Newbie
    Currently Being Moderated
    Hello Mark

    I am doing similar test of Client side result set cache on oracle 11 g rel2

    i am doing test as below

    one oracle 11g rel2 instance is used as server1,from other oracle 11g rel2 server2, i am running queries connecting to server1 through sqlplus,listener(tnsnames )

    i run query few times without any client cache settings then
    i have set init.ora paras for oracle 11g rel2 for client result cache,cache size/cache lag
    i run the same query few times
    in both the cases after each run i am noting statistics
    select * from V$RESULT_CACHE_OBJECTS;
    select * from V$CLIENT_RESULT_CACHE_STATS;
    select * from gv$cpool_conn_info;
    also autotrace,plan etc
    but after using cache this views are getting poplulated ,autiotrace plan shows cache is used
    but i cant see any improvement in performance after using client cache like speed,reduce network trip, etc
    in fact without using cache/with no hint ,query executes faster

    let me know if i am missing something

    thanks
  • 10. Re: Client side result set cache
    rp0428 Guru
    Currently Being Moderated
    That thread is almost a year old and has already been marked ANSWERED.

    If you have a question or problem please create your own thread. Most volunteers are not going to respond to an answered thread.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points