PowerPoint 演示文稿

Transcription

1 追求 JDBC on Oracle 最佳性能? 如何才好? Maclean Liu

2 古希腊的 Delphi( 世界中心 ), 屹立着 Parnassus Mount( 诗檀山 ), 山上有一座阿波罗神庙, 庙中住着女祭司 (Oracle)

3 兴一利 不如 除一害

4 Jdbc 性能案例 1: 问题 for (i = 0; i < ; i++) { conn = ds.getconnection(userid, password); pstmt = conn.preparestatement("select name FROM employees WHERE id = " + i); rset = pstmt.executequery(); } conn.close(); 循环内获得 connection 并解析执行,connection 非缓存的解析在 Oracle 中都很昂贵

5 Jdbc 性能案例 1: 结果 响应时间 : 150ms 吞吐量 : 300tps CPU 方面 Sys 和 User 都很高

6 Jdbc1 性能案例 1: 对策 conn = ds.getconnection(userid, password); for (i = 0; i < ; i++) { pstmt = conn.preparestatement("select name FROM employees WHERE id = " + i); rset = pstmt.executequery(); } conn.close(); 只建立必要的 connection 到数据库

7 Jdbc1 性能案例 1: 改良后 响应时间 : 20ms 吞吐量 : 3,000tps latch: shared pool 此时的瓶颈凸显在解析 (parse) 并发争用上

8 Jdbc 性能案例 1: 原因 conn = ds.getconnection(userid, password); for (i = 0; i < ; i++) { { pstmt = conn.preparestatement("select name FROM employees WHERE id = " + i); rset = pstmt.executequery(); } conn.close(); 每次执行都使用拼凑的 SQL 语句, 未启用绑定变量, 默认情况下每次均硬解析 hard parse

9 Jdbc 性能案例 1: 进一步对策 conn = ds.getconnection(userid, password); for (i = 0; i < ; i++) { { pstmt = conn.preparestatement("select name FROM employees WHERE id =?"); pstmt.setint(1, i); rset = pstmt.executequery(); } conn.close(); 使用绑定变量, 避免每次执行都硬解析

10 Jdbc1 性能案例 1: 再改良后 响应时间 : 1ms 吞吐量 : 25,000tps 并发争用减少 CPU 被充分使用

11 Jdbc1 性能案例 1: 正确使用 PrepareStatement PrepareStatement SQL 语句解析 Bind 变量绑定 Execute SQL 语句执行 For { PrepareStatement Bind Execute } PrepareStatement For { Bind Execute }

12 Jdbc1 性能案例 1: 进一步对策 conn = ds.getconnection(userid, password); pstmt = conn.preparestatement("select name FROM employees WHERE id =?"); for (i = 0; i < ; i++) { { pstmt.setint(1, i); rset = pstmt.executequery(); } conn.close(); 预解析, 只解析一次 For preparestatement,

13 Jdbc1 性能案例 1: 再再改良后 响应时间 : 1ms 吞吐量 : 34,000tps 吞吐量进一步提高

14 Jdbc1 性能案例 1: 再改良后

15 Jdbc1 性能案例 2: 永远不够的 open_cursor ORA-1000 报错 maximum open cursors exceeded 达到 session 最大游标数 故障发生业务停滞 大量 SQL*Net break/reset to client 等待事件 OPEN_CURSOR 参数已经很大了, 都到 了, 开发人员要求 DBA 进一步加大该参数到 30000

16 Jdbc1 性能案例 2: 原因 try { rset = pstmt.executequery(); rset.close(); pstmt.close(); } catch (SQLException e) { e.printstacktrace(); } 代码里是写了关闭游标, 但存在还没执行就跑到异常处理里去的可能

17 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 块结束后总会执行

18 Jdbc1 性能案例 3: 永远不够的 processes 吞吐量接近于零 负载也接近于零 CPU 使用率也接近于零 告警日志中出现 ORA 错误? 吞吐量暴跌, 应用会话无法连接数据库? 开发人员要求 DBA 进一步加到 processes 参数?

19 Jdbc1 性能案例 3: 原因 try { rset = pstmt.executequery(); rset.close(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printstacktrace(); } 代码里是写了关闭 connection, 但存在还没执行就跑到异常处理里去的可能

20 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) {...}

21 Jdbc1 性能案例 4 莫名出现大量锁等待 没有任何告警, 但所有应用全部超时? 大量 session 处于锁等待挂起状态 开发人员要求 DBA 去 kill 锁数据的 session?

22 Jdbc1 性能案例 4: 原因 try { rset = pstmt.executeupdate(); conn.commit(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printstacktrace(); } 代码中写了 commit, 但没考虑异常处理 可能的 session leak

23 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) {...} }

24 Other tips:set AutoCommit Off 关闭自动 commit, 在应用真正需要的时候 commit, 即维护了必要的事务又减少了 log file sync 等待 Connection.setAutoCommit(false);

25 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..

26 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) );

27 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

28 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)

29 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.

30 Thank You