This blog has moved here.

Wednesday, September 22, 2010

Statistics on Client Result Cache

I've just noticed that the result cache client statistics are not very accurate on my 11.2.0.1 Oracle server. I have the following java code:

package test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;

public class ClientResultCache {

public static void main(String[] args) throws SQLException, IOException, InterruptedException {
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("oci");
ods.setTNSEntryName("owldb");
ods.setUser("talek");
ods.setPassword("muci");
Connection conn = ods.getConnection();
String query = "select /*+ result_cache */ * from xxx";
((oracle.jdbc.OracleConnection)conn).setImplicitCachingEnabled(true);
((oracle.jdbc.OracleConnection)conn).setStatementCacheSize(10);
PreparedStatement pstmt;
ResultSet rs;
for (int j = 0 ; j < 1000 ; j++ ) {
System.out.println(j);
pstmt = conn.prepareStatement (query);
rs = pstmt.executeQuery();
while (rs.next( ) ) {
}
rs.close();
pstmt.close();
Thread.sleep(100);
}
System.in.read();
}

}

While the above code is running I'm monitoring the CLIENT_RESULT_CACHE_STATS$. And this is what I've got:
   
STAT_ID NAME VALUE
---------- ------------------------------ ----------
1 Block Size 256
2 Block Count Max 128
3 Block Count Current 128
4 Hash Bucket Count 1024
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 812
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0


The "Find Count" should be 999, right? My test program is still running (see the System.in.read at the end) therefore I expect my client result cache to be still there. My first guess was a delay in computing the statistics but even after 15 minutes of waiting I didn't get the right figures. Hmm... am I miss something?