連線池優化之啟用PoolPreparedStatements

壹頁書發表於2013-12-23
優化
    DBCP連線池可以快取PreparedStatement，本質上就是快取遊標。
    一個SQL語句，無論是Insert,Update,Delete還是Select都是遊標操作，只不過Select遊標指向查詢結果，而其餘的指向修改的目標。
    除了連線可以快取，遊標也是可以快取的，主要是避免遊標的反覆建立。雖然Oracle對完全相同的SQL可以共享執行計劃，但是也需要去共享池查詢這個SQL的資訊(該SQL的Hash值是否在共享池內)。快取遊標，則進一步優化，避免了反覆查詢共享池的操作（個人臆測）.
    首先，做一個實驗，證明遊標可以反覆利用。
    
  1. --建立實驗表
  2. create table t as select rownum r from dual connect by level<10;

  4. set serveroutput on

  6. declare
  7.     cursor cur is select * from t;
  8.     v_record t%rowtype;    
  9. begin
  10.     open cur;
  11.     fetch cur into v_record;
  12.     dbms_output.put_line(v_record.r);
  13.     fetch cur into v_record;
  14.     dbms_output.put_line(v_record.r);
  15.     close cur;
  16.     
  17.     open cur;
  18.     fetch cur into v_record;
  19.     dbms_output.put_line(v_record.r);
  20.     fetch cur into v_record;
  21.     dbms_output.put_line(v_record.r);
  22.     close cur;
  23.     
  24. end;
  25. /
   
    實驗結果:
            1
            2
            1
            2
可以看到遊標在關閉之後，可以重新開啟。並且重新開啟的遊標，與前次開啟的遊標，在資料上沒有任何關係。第一次讀到2，重新開啟之後，會從1開始，而不是從3開始。

這個程式碼如果在JAVA程式中，就是這個樣子的。

  1.         Class.forName("oracle.jdbc.OracleDriver");
  2.         Connection conn = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "edmond", "edmond");
  3.         PreparedStatement cmd = conn.prepareStatement("select * from t");
  4.         //第一次呼叫
  5.         ResultSet rs = cmd.executeQuery();
  6.         rs.next();
  7.         System.out.println(rs.getString(1));
  8.         rs.next();
  9.         System.out.println(rs.getString(1));

  11.         //第二次呼叫
  12.         rs = cmd.executeQuery();
  13.         rs.next();
  14.         System.out.println(rs.getString(1));
  15.         rs.next();
  16.         System.out.println(rs.getString(1));
  17.         cmd.close();
  18.         conn.close();
    值得注意的是，PreparedStatement就表示Oracle的遊標，但是一旦PreparedStatement關閉，就無法重新開啟。所以複用PreparedStatement只需要在關閉之前重新呼叫executeQuery方法即可。
   
    如果連線池啟動PoolPreparedStatements，則可能在每一個Connection的代理物件中，包括下面的結構
    Map> poolPreparedStatements
    其中Key是SQL語句或者SQL語句的Hash值，代理的Connection會根據SQL返回一個可用的prepareStatement;如果沒有，則會建立新的prepareStatement物件。而這個返回的prepareStatement物件，也同樣是代理物件。
    因為在呼叫連線池返回的prepareStatement的close方法時，不會真正的close這個物件，因為這樣就無法實現複用的效果。可能只是修改了這個物件的標誌位，標明其可用。
    下面是DBCP連線池開啟遊標快取的
程式碼。
    可以想見 ds.getConnection()返回的Connection和PreparedStatement應該都是代理物件。
  1.     private static void testDataSource() throws SQLException {
  2.         BasicDataSource ds = new BasicDataSource();
  3.         ds.setUrl("jdbc:oracle:thin:127.0.0.1:1521:orcl");
  4.         ds.setUsername("edmond");
  5.         ds.setPassword("edmond");
  6.         ds.setPoolPreparedStatements(true);
  7.         ds.setMaxOpenPreparedStatements(300);

  9.         Connection conn = ds.getConnection();
  10.         PreparedStatement cmd = conn.prepareStatement("select * from t");
  11.         ResultSet rs = cmd.executeQuery();
  12.         rs.next();
  13.         System.out.println(rs.getString(1));
  14.         rs.next();
  15.         System.out.println(rs.getString(1));
  16.         cmd.close();
  17.         conn.close();
  18.     }
    另外，Oracle遊標對應的是PreparedStatement，而不是ResultSet。
    並且MaxOpenPreparedStatements的設定應該小於Oracle的Open_Cursor的數值。
    
  1. public static void main(String[] args) throws ClassNotFoundException, SQLException {
  2.         List<PreparedStatement> list = new ArrayList<PreparedStatement>();

  4.         Class.forName("oracle.jdbc.OracleDriver");
  5.         Connection conn = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "edmond", "edmond");
  6.         for (int i = 0; i < 305; i++) {
  7.             PreparedStatement cmd = conn.prepareStatement("select * from t");
  8.             
  9.             ResultSet rs = cmd.executeQuery();
  10.             rs.next();
  11.             rs.close();
  12.             rs = null;
  13.             list.add(cmd);
  14.         }
  15.         conn.close();
  16.     }
    結果出現異常：
Exception in thread "main" java.sql.SQLException: ORA-00604: 遞迴 SQL 級別 1 出現錯誤
ORA-01000: 超出開啟遊標的最大數
ORA-00604: 遞迴 SQL 級別 1 出現錯誤
ORA-01000: 超出開啟遊標的最大數
ORA-01000: 超出開啟遊標的最大數
    可以看到，如果PreparedStatement沒有關閉，則Oracle那端的遊標就沒有釋放。
    最終這個連線的遊標超過Oracle的open_cursor數值(預設300)，就會報錯。
    所以啟用了PoolPreparedStatements，一定注意設定MaxOpenPreparedStatements小於Oracle Open_Cursor的數值。    
   


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1064007/，如需轉載，請註明出處，否則將追究法律責任。

相關文章