Oracle JDBC中的语句缓存



在Oracle数据库中，SQL解析有几种：

• 硬解析，过多的硬解析在系统中产生shared pool latch和library cache liatch争用，消耗过多的shared pool，使得系统不具有可伸缩性。
• 软解析，过多的软解析仍然可能会导致系统问题，特别是如果有少量的SQL高并发地进行软解析，会产生library cache latch或者是share方式的mutex争用。
• 软软解析，其实这也也属于软解析，与普通的软解析不同的是，软软解析的SQL会在会话的cached cursor中命中。
• 一次解析，多次执行，这是解析次数最少的方式，也是系统最具有可扩展性的方式。

那么在JAVA开发的应用中，怎么样才能实现上述第4种方式？如果是循环处理某种数据，这个比较容易实现。其实对于不是这种情况，Oracle也提供了很好的方式来实现这一点。下面是一个例子（例子代码文件为TestStmtCache.java）。

view plain copy to clipboard print ?

1. import java.sql.*;  
2. import oracle.jdbc.driver.OracleConnection;  
3.   
4. public class TestStmtCache {  
5.     public static Connection getConnection() throws Exception {  
6.         String driver = "oracle.jdbc.driver.OracleDriver";  
7.         String url = "jdbc:oracle:thin:@localhost:1521:xj11g";  
8.         Class.forName(driver);  
9.         return DriverManager.getConnection(url, "test", "test");  
10.     }  
11.   
12.     public static void main(String args[]) {  
13.         Connection conn = null;  
14.         try {  
15.             conn = getConnection();  
16.             conn.setAutoCommit(false);  
17.             ((OracleConnection)conn).setStatementCacheSize(0);  
18.             for (int i=0; i <200; i++) {  
19.                 testNoCache(conn);  
20.             }  
21.             ((OracleConnection)conn).setStatementCacheSize(20);  
22.             ((OracleConnection)conn).setImplicitCachingEnabled(true);  
23.   
24.             for (int i=0; i <200; i++) {  
25.                 testCache(conn);  
26.             }  
27.         } catch (Exception e) {  
28.             e.printStackTrace();  
29.         } finally {  
30.             try {  
31.                 conn.close();  
32.             } catch (SQLException e) {  
33.                 e.printStackTrace();  
34.             }  
35.         }  
36.     }  
37.     public static void testCache(Connection conn) {  
38.         PreparedStatement pstmt = null;  
39.         try {  
40.             pstmt = conn.prepareStatement("select /*cache_test1 */ * from t1 where rownum<=1");  
41.             pstmt.execute();  
42.         } catch (Exception e) {  
43.             e.printStackTrace();  
44.         } finally {  
45.             try {  
46.                 pstmt.close();  
47.             } catch (SQLException e) {  
48.                 e.printStackTrace();  
49.             }  
50.         }  
51.     }  
52.   
53.     public static void testNoCache(Connection conn) {  
54.         PreparedStatement pstmt = null;  
55.         try {  
56.             pstmt = conn.prepareStatement("select /*nocache_test1 */ * from t1 where rownum<=1");  
57.             pstmt.execute();  
58.         } catch (Exception e) {  
59.             e.printStackTrace();  
60.         } finally {  
61.             try {  
62.                 pstmt.close();  
63.             } catch (SQLException e) {  
64.                 e.printStackTrace();  
65.             }  
66.         }  
67.     }  
68. }  

import java.sql.*;
import oracle.jdbc.driver.OracleConnection;

public class TestStmtCache {
    public static Connection getConnection() throws Exception {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xj11g";
        Class.forName(driver);
        return DriverManager.getConnection(url, "test", "test");
    }

    public static void main(String args[]) {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ((OracleConnection)conn).setStatementCacheSize(0);
            for (int i=0; i <200; i++) {
                testNoCache(conn);
            }
            ((OracleConnection)conn).setStatementCacheSize(20);
            ((OracleConnection)conn).setImplicitCachingEnabled(true);

            for (int i=0; i <200; i++) {
                testCache(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void testCache(Connection conn) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("select /*cache_test1 */ * from t1 where rownum<=1");
            pstmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void testNoCache(Connection conn) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("select /*nocache_test1 */ * from t1 where rownum<=1");
            pstmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

编译上述代码文件TestStmtCache.java，并运行：

view plain copy to clipboard print ?

1. E:\JavaCode>set CLASSPATH=.;ojdbc14.jar  
2.   
3. E:\JavaCode>d:\works\Java\jdk1.5.0_21\bin\javac TestStmtCache.java  
4.   
5. E:\JavaCode>d:\works\Java\jdk1.5.0_21\jre\bin\java TestStmtCache  

E:\JavaCode>set CLASSPATH=.;ojdbc14.jar

E:\JavaCode>d:\works\Java\jdk1.5.0_21\bin\javac TestStmtCache.java

E:\JavaCode>d:\works\Java\jdk1.5.0_21\jre\bin\java TestStmtCache

在数据库中进行查询：

view plain copy to clipboard print ?

1. SYS@xj11g> select sql_id,parse_calls,executions,sql_text from v$sqlarea where sql_text like '%cache_test%' and sql_text not like '%v$%';  
2.   
3. SQL_ID        PARSE_CALLS  EXECUTIONS SQL_TEXT  
4. ------------- ----------- ----------- ------------------------------------------------------------  
5. 3nbu9qp40ptjk         200         200 select /*nocache_test1 */ * from t1 where rownum< =1  
6. 47hja0fwmmb6c           1         200 select /*cache_test1 */ * from t1 where rownum<=1  

SYS@xj11g> select sql_id,parse_calls,executions,sql_text from v$sqlarea where sql_text like '%cache_test%' and sql_text not like '%v$%';

SQL_ID        PARSE_CALLS  EXECUTIONS SQL_TEXT
------------- ----------- ----------- ------------------------------------------------------------
3nbu9qp40ptjk         200         200 select /*nocache_test1 */ * from t1 where rownum< =1
47hja0fwmmb6c           1         200 select /*cache_test1 */ * from t1 where rownum<=1

可以看到，这两条SQL语句，都执行了200次，但是标记为”nocache_test1″的SQL没有进行语句缓存，其parse calls为200次，即解析了200次，其中一次是硬解析。而标记为”cache_test1″的SQL语句，使用了语句缓存，但是parse calls只有1次，即只有一次硬解析，执行了200次。这里关键的代码在于：

view plain copy to clipboard print ?

1. ((OracleConnection)conn).setStatementCacheSize(20);  
2. ((OracleConnection)conn).setImplicitCachingEnabled(true);  

            ((OracleConnection)conn).setStatementCacheSize(20);
            ((OracleConnection)conn).setImplicitCachingEnabled(true);

上述第一行代码设置语句缓存大小，当然20比较偏小，对于比较大型的系统来说，设到200-300比较合适，不过这会耗用一定数量的JAVA内存。这个数值表示一个连接能够缓存多少语句。第二行代码是设置隐式打开语句缓存，也即自动会对PreparedStatement的SQL语句进行缓存。

那么，上述的方式无疑是比较简单的，但是这种方式有一个问题就是，缓存的利用效率可能不高，因为JAVA会将不常用的SQL语句也进行了缓存。Oracle的JDBC驱动也提供了一种手工控制的方式：
将测试代码中的第22行替换为：

view plain copy to clipboard print ?

1. ((OracleConnection)conn).setExplicitCachingEnabled(true);  

((OracleConnection)conn).setExplicitCachingEnabled(true);

第40行替换为：

view plain copy to clipboard print ?

1. pstmt = ((OracleConnection)conn).getStatementWithKey ("cache_test1");  
2. if (pstmt==null)  

pstmt = ((OracleConnection)conn).getStatementWithKey ("cache_test1");
if (pstmt==null)

第46行替换为：

view plain copy to clipboard print ?

1. ((OraclePreparedStatement)pstmt).closeWithKey ("cache_test1");  

((OraclePreparedStatement)pstmt).closeWithKey ("cache_test1");

这样通过手工编码的方式控制哪些语句需要缓存，哪些不需要。
关于语句缓存（Statement Caching）可以参考Oracle在线文档：Statement and Result Set Caching