追求 JDBC on Oracle 最佳性能? 如何才好? Maclean Liu
古希腊的 Delphi( 世界中心 ), 屹立着 Parnassus Mount( 诗檀山 ), 山上有一座阿波罗神庙, 庙中住着女祭司 (Oracle)
兴一利 不如 除一害
Jdbc 性能案例 1: 问题 for (i = 0; i < 1000000; i++) { conn = ds.getconnection(userid, password); pstmt = conn.preparestatement("select name FROM employees WHERE id = " + i); rset = pstmt.executequery(); } conn.close(); 循环内获得 connection 并解析执行,connection 非缓存的解析在 Oracle 中都很昂贵
Jdbc 性能案例 1: 结果 响应时间 : 150ms 吞吐量 : 300tps CPU 方面 Sys 和 User 都很高
Jdbc1 性能案例 1: 对策 conn = ds.getconnection(userid, password); for (i = 0; i < 1000000; i++) { pstmt = conn.preparestatement("select name FROM employees WHERE id = " + i); rset = pstmt.executequery(); } conn.close(); 只建立必要的 connection 到数据库
Jdbc1 性能案例 1: 改良后 响应时间 : 20ms 吞吐量 : 3,000tps latch: shared pool 此时的瓶颈凸显在解析 (parse) 并发争用上
Jdbc 性能案例 1: 原因 conn = ds.getconnection(userid, password); for (i = 0; i < 1000000; i++) { { pstmt = conn.preparestatement("select name FROM employees WHERE id = " + i); rset = pstmt.executequery(); } conn.close(); 每次执行都使用拼凑的 SQL 语句, 未启用绑定变量, 默认情况下每次均硬解析 hard parse
Jdbc 性能案例 1: 进一步对策 conn = ds.getconnection(userid, password); for (i = 0; i < 1000000; i++) { { pstmt = conn.preparestatement("select name FROM employees WHERE id =?"); pstmt.setint(1, i); rset = pstmt.executequery(); } conn.close(); 使用绑定变量, 避免每次执行都硬解析
Jdbc1 性能案例 1: 再改良后 响应时间 : 1ms 吞吐量 : 25,000tps 并发争用减少 CPU 被充分使用
Jdbc1 性能案例 1: 正确使用 PrepareStatement PrepareStatement SQL 语句解析 Bind 变量绑定 Execute SQL 语句执行 For { PrepareStatement Bind Execute } PrepareStatement For { Bind Execute }
Jdbc1 性能案例 1: 进一步对策 conn = ds.getconnection(userid, password); pstmt = conn.preparestatement("select name FROM employees WHERE id =?"); for (i = 0; i < 1000000; i++) { { pstmt.setint(1, i); rset = pstmt.executequery(); } conn.close(); 预解析, 只解析一次 For preparestatement, http://www.oracle.com/technetwork/testcontent/jdbcch5-131209.pdf
Jdbc1 性能案例 1: 再再改良后 响应时间 : 1ms 吞吐量 : 34,000tps 吞吐量进一步提高
Jdbc1 性能案例 1: 再改良后
Jdbc1 性能案例 2: 永远不够的 open_cursor ORA-1000 报错 maximum open cursors exceeded 达到 session 最大游标数 故障发生业务停滞 大量 SQL*Net break/reset to client 等待事件 OPEN_CURSOR 参数已经很大了, 都到 10000 了, 开发人员要求 DBA 进一步加大该参数到 30000
Jdbc1 性能案例 2: 原因 try { rset = pstmt.executequery(); rset.close(); pstmt.close(); } catch (SQLException e) { e.printstacktrace(); } 代码里是写了关闭游标, 但存在还没执行就跑到异常处理里去的可能
Jdbc1 性能案例 2: 对策 try { rset = pstmt.executequery(); rset.close(); } catch (SQLException e) { e.printstacktrace(); } finally { if (pstmt!= null) try {pstmt.close();} catch (SQLException e) {...} } Finally 中调用关闭游标, TRY 块结束后总会执行
Jdbc1 性能案例 3: 永远不够的 processes 吞吐量接近于零 负载也接近于零 CPU 使用率也接近于零 告警日志中出现 ORA-00020 错误? 吞吐量暴跌, 应用会话无法连接数据库? 开发人员要求 DBA 进一步加到 processes 参数?
Jdbc1 性能案例 3: 原因 try { rset = pstmt.executequery(); rset.close(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printstacktrace(); } 代码里是写了关闭 connection, 但存在还没执行就跑到异常处理里去的可能
Jdbc1 性能案例 3: 对策 try { rset = pstmt.executequery(); rset.close(); } catch (SQLException e) { e.printstacktrace(); } finally { } Finally 中调用关闭 connection, TRY 块结束后总会执行 if (pstmt!= null) try {pstmt.close();} catch (SQLException e) {...} if (conn!= null) try {conn.close();} catch (SQLException e) {...}
Jdbc1 性能案例 4 莫名出现大量锁等待 没有任何告警, 但所有应用全部超时? 大量 session 处于锁等待挂起状态 开发人员要求 DBA 去 kill 锁数据的 session?
Jdbc1 性能案例 4: 原因 try { rset = pstmt.executeupdate(); conn.commit(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printstacktrace(); } 代码中写了 commit, 但没考虑异常处理 可能的 session leak
Jdbc1 性能案例 4: 对策 try { 当出现异常就 rollback 释放锁 rset = pstmt.executupdate(); conn.commit(); } catch (SQLException e) { if (conn!= null ) try {conn.rollback();} catch (SQLException e) {...} e.printstacktrace(); } finally { if (pstmt!= null) try {pstmt.close();} catch (SQLException e) {...} if (conn!= null) try {conn.close();} catch (SQLException e) {...} }
Other tips:set AutoCommit Off 关闭自动 commit, 在应用真正需要的时候 commit, 即维护了必要的事务又减少了 log file sync 等待 Connection.setAutoCommit(false);
Other tips: 批量 Insert DriverManager.registerDriver(new oracle.jdbc.driver.oracledriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@","scott","tiger"); PreparedStatement ps = conn.preparestatement ("insert into dept values (?,?,?)"); //Change batch size for this statement to 3 ((OraclePreparedStatement)ps).setExecuteBatch (3); ps.setint (1, 23); ps.setstring (2, "Sales"); ps.setstring (3, "USA"); ps.executeupdate (); //JDBC queues this for later execution ps.setint (1, 24); ps.setstring (2, "Blue Sky"); ps.setstring (3, "Montana"); ps.executeupdate (); //JDBC queues this for later execution ps.setint (1, 25); ps.setstring (2, "Applications"); ps.setstring (3, "India"); ps.executeupdate (); //The queue size equals the batch value of 3 //JDBC sends the requests to the // database ps.setint (1, 26); ps.setstring (2, "HR"); ps.setstring (3, "Mongolia"); ps.executeupdate (); //JDBC queues this for later execution ((OraclePreparedStatement)ps).sendBatch (); //JDBC sends the queued request..
Other tips:prefetch Rows DriverManager.registerDriver(new oracle.jdbc.driver.oracledriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@","scott","tiger"); //Set the default row prefetch setting for this connection ((OracleConnection)conn).setDefaultRowPrefetch (7); /* The following statement gets the default row prefetch value for the connection, that is, 7. */ Statement stmt = conn.createstatement (); /* Subsequent statements look the same, regardless of the row prefetch value. Only execution time changes. */ ResultSet rset = stmt.executequery ("select ename from emp"); System.out.println ( rset.next () ); while( rset.next () ) System.out.println ( rset.getstring (1) ); //Override the default row prefetch setting for this statement ( (OracleStatement)stmt ).setrowprefetch (2); rset = stmt.executequery ("select ename from emp"); System.out.println ( rset.next () ); while( rset.next () ) System.out.println ( rset.getstring (1) );
Oracle Database 11g R2 JDBC BasicFiles LOB Pre-Fetch Faster Lob Data Fetching by performing all operations on server in a single single roundtrip setlobprefetchsize() select name, bio from LOB_tab 1. Parse 2. Execute 3. Fetch metadata 4. Fetch LOB data
Oracle Database 11g R2 Zero-Copy SecureFiles LOB Faster SecureFiles operations by bypassing server-side buffer copy setupsecurefile() Blob.getBytes() 1. Fetch LOB data (bypass internal buffer)
JDBC Lob Pre-Fetch Performance Performance benchmark fetches CLOBs of sizes 1K to 50K with PREFETCH enabled and PREFETCH disabled, against BASICFILE LOBs and SECUREFILE LOBs.
www.parnassusdata.com 400-690-3643 Thank You