在Oracle数据库中,SQL解析有几种:

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

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

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,并运行:

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

在数据库中进行查询:

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次。这里关键的代码在于:

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

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

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

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

第40行替换为:

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

第46行替换为:

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

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

Trackback

4 comments untill now

  1. XKGLOB刀 @ 2013-09-16 15:04

    statement caching经常有bug. 要实现一次解析,多次执行,可以用类似下面的代码:

    String SQL =
    “select f1 from hh1 where f2 = ? ” ;
    PreparedStatement stmt = conn.prepareStatement (SQL);
    for ( int i = 1; i <=10000; i++)
    {
    stmt.setInt(1, i);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    String f2 = rs.getString (1);
    System.out.println (“Got: ” + f2);
    }

    或者看一下《Oracle高效设计》的第226页

    [回复]

    老熊 回复:

    @XKGLOB刀, 谢谢你的留言。不过我在文章中也提出了,“除了循环处理数据”。你提到的代码就是属于循环处理数据的一种。这种相对简单。

    不知你提到的BUG主要是哪些?

    [回复]

  2. 刚刚和同事讨论过这个问题,程序中循环调用了封装好s的ql语句查询的方法,他觉得应该像XKGLOB刀所说,把conn.prepareStatement放到循环外部,为此还修改了不好代码。后来才发现开发使用的iBatis已经能够对prepareStatement做缓存处理了,所以大家还是尽量不要从对底层写jdbc代码,一不留神就掉坑里面了。

    [回复]

  3. digdeep126 @ 2013-11-17 20:04

    getStatementWithKey (“cache_test1″);
    这里的参数”cache_test1″是在sql语句中使用 hint 来定义的?

    另外根据这里的方法,对于一些经常执行的大SQL语句,通过这种编程方法,不要DBA帮忙似乎也可以将他们保存在内存中。
    当然DBA在Oracle后端来实现更加方便一些,毕竟编程实现要修改Java代码。

    [回复]

Add your comment now