fetchSIze 对性能的影响
class DemoPrefetch{public static void main(String[] args){ int connLevelDefaultPrefetch = 5; int stmtLevelFetchSize = 20; int rsetLevelFetchSize = 50; Connection conn = null; try { conn = JDBCUtil.getConnection("fuyou", "fuyou", "orcl"); System.out.println( "\nDefault connection fetch size: " + ((OracleConnection) conn).getDefaultRowPrefetch() ); System.out.println( "setting the default fetch size at connection level to " + connLevelDefaultPrefetch ); ((OracleConnection) conn).setDefaultRowPrefetch( connLevelDefaultPrefetch); System.out.println( "Now the connection fetch size: " + ((OracleConnection) conn).getDefaultRowPrefetch() ); JDBCUtil.getTraceName(conn);//取得跟踪文件 JDBCUtil.startTrace( conn );//设置跟踪 _demoPstmtFetchSize( conn, connLevelDefaultPrefetch, stmtLevelFetchSize); _demoPstmtFetchSizeWithRsetOverride( conn, connLevelDefaultPrefetch, stmtLevelFetchSize, rsetLevelFetchSize ); _demoCstmtFetchSize( conn, connLevelDefaultPrefetch, stmtLevelFetchSize ); _demoCstmtFetchSizeWithRsetOverride( conn, connLevelDefaultPrefetch, stmtLevelFetchSize, rsetLevelFetchSize ); JDBCUtil.cancleTrace(conn); } catch (SQLException e) { // handle the exception properly - in this case, we just // print the stack trace. JDBCUtil.printException ( e ); } finally { // release the JDBC resources in the finally clause. JDBCUtil.close( conn ); }} // end of main()private static void _demoPstmtFetchSize( Connection conn, int connLevelDefaultPrefetch, int stmtLevelFetchSize ) throws SQLException{ System.out.println( "Inside _demoPstmtFetchSize" ); String sqlTag = "/*+" + "(CONN="+ connLevelDefaultPrefetch + ")" + "(PSTMT=" + stmtLevelFetchSize + ")" + "*/"; String stmtString = "select x "+ sqlTag + " from t1 where rownum <= ?"; PreparedStatement pstmt = null; ResultSet rset = null; try { pstmt = conn.prepareStatement( stmtString ); System.out.println( "\tDefault statement fetch size: " + pstmt.getFetchSize()); pstmt.setFetchSize( stmtLevelFetchSize ); System.out.println( "\tnew statement fetch size: " + pstmt.getFetchSize()); pstmt.setInt( 1, 100 ); rset = pstmt.executeQuery(); System.out.println( "\tResult set fetch size: " + rset.getFetchSize()); int i=0; while (rset.next()) { i++; } System.out.println( "\tnumber of times in the loop: " + i ); } finally { // release JDBC related resources in the finally clause. JDBCUtil.close( rset ); JDBCUtil.close( pstmt ); }}private static void _demoPstmtFetchSizeWithRsetOverride( Connection conn, int connLevelDefaultPrefetch, int stmtLevelFetchSize, int rsetLevelFetchSize ) throws SQLException{ System.out.println( "Inside _demoPstmtFetchSizeWithRsetOverride" ); String sqlTag = "/*+" + "(CONN="+ connLevelDefaultPrefetch + ")" + "(PSTMT=" + stmtLevelFetchSize + ")" + "(RSET="+ rsetLevelFetchSize + ")" + "*/"; String stmtString = "select x "+ sqlTag + " from t1 where rownum <= ?"; PreparedStatement pstmt = null; ResultSet rset = null; try { pstmt = conn.prepareStatement( stmtString ); System.out.println( "\tDefault statement fetch size: " + pstmt.getFetchSize()); pstmt.setFetchSize( stmtLevelFetchSize ); System.out.println( "\tnew statement fetch size: " + pstmt.getFetchSize()); pstmt.setInt( 1, 100 ); rset = pstmt.executeQuery(); rset.setFetchSize( rsetLevelFetchSize ); System.out.println( "\tnew result set fetch size: " + rset.getFetchSize()); int i=0; while (rset.next()) { i++; } System.out.println( "\tnumber of times in the loop: " + i ); } finally { // release JDBC related resources in the finally clause. JDBCUtil.close( rset ); JDBCUtil.close( pstmt ); }}// demo fetch size using callable statementprivate static void _demoCstmtFetchSize( Connection conn, int connLevelDefaultPrefetch, int stmtLevelFetchSize ) throws SQLException{ System.out.println( "Inside _demoCstmtFetchSize" ); String sqlTag = "/*+" + "(CONN="+ connLevelDefaultPrefetch + ")" + "(CSTMT=" + stmtLevelFetchSize + ")" + "*/"; String stmtString = "{ call prefetch_pkg.get_details ( ?, ?, ? ) }"; CallableStatement cstmt = null; ResultSet rset = null; try { cstmt = conn.prepareCall( stmtString ); System.out.println( "\tDefault statement fetch size: " + cstmt.getFetchSize()); cstmt.setFetchSize( stmtLevelFetchSize ); System.out.println( "\tnew statement fetch size: " + cstmt.getFetchSize()); cstmt.setInt( 1, 100); // number of rows to be fetched cstmt.setString( 2, sqlTag ); cstmt.registerOutParameter( 3, OracleTypes.CURSOR ); // execute the query cstmt.execute(); rset = (ResultSet) cstmt.getObject( 3 ); System.out.println( "\tresult set fetch size: " + rset.getFetchSize()); System.out.println( "\tHowever, in case of callable statement, the real fetch size for all result sets obtained from the statement is the same as the one set at the connection level." ); int i=0; while (rset.next()) { i++; } System.out.println( "\tnumber of times in the loop: " + i ); } finally { // release JDBC related resources in the finally clause. JDBCUtil.close( rset ); JDBCUtil.close( cstmt ); }}// demo fetch size using callable statementprivate static void _demoCstmtFetchSizeWithRsetOverride( Connection conn, int connLevelDefaultPrefetch, int stmtLevelFetchSize, int rsetLevelFetchSize ) throws SQLException{ System.out.println( "Inside _demoCstmtFetchSizeWithRsetOverride" ); String sqlTag = "/*+" + "(CONN="+ connLevelDefaultPrefetch + ")" + "(CSTMT=" + stmtLevelFetchSize + ")" + "(RSET="+ rsetLevelFetchSize + ")" + "*/"; String stmtString = "{ call prefetch_pkg.get_details ( ?, ?, ? ) }"; CallableStatement cstmt = null; ResultSet rset = null; try { cstmt = conn.prepareCall( stmtString ); System.out.println( "\tDefault statement fetch size: " + cstmt.getFetchSize()); cstmt.setFetchSize( stmtLevelFetchSize ); System.out.println( "\tnew statement fetch size: " + cstmt.getFetchSize()); cstmt.setInt( 1, 100); // number of rows to be fetched cstmt.setString( 2, sqlTag ); cstmt.registerOutParameter( 3, OracleTypes.CURSOR ); // execute the query cstmt.execute(); rset = (ResultSet) cstmt.getObject( 3 ); rset.setFetchSize( rsetLevelFetchSize ); System.out.println( "\tnew result set fetch size: " + rset.getFetchSize()); System.out.println( "\tHowever, in case of callable statement, the real fetch size for all result sets obtained from the statement is the same as the one set at the connection level." ); int i=0; while (rset.next()) { i++; } System.out.println( "\tnumber of times in the loop: " + i ); } finally { // release JDBC related resources in the finally clause. JDBCUtil.close( rset ); JDBCUtil.close( cstmt ); }}} // end of program上面是测试类
下面是tkporf 出来的文件，只摘取了相关数据
select x /*+(CONN=5)(PSTMT=20)(RSET=50)*/ from t1 where rownum <= :1call count cpu elapsed disk query current rows------- -------------- ---------- ---------- ---------- --------------------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.01 0.00 0 2 0 0Fetch 3 0.00 0.00 0 6 0 100------- -------------- ---------- ---------- ---------- --------------------total 5 0.01 0.00 0 8 0 100Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 62Rows Row Source Operation---------------------------------------------------------- 100COUNT STOPKEY (cr=6 pr=0 pw=0 time=1055 us) 100 TABLE ACCESS FULL T1 (cr=6 pr=0 pw=0 time=449 us)Elapsed times include waiting on following events:Event waited on Times Max. WaitTotal Waited---------------------------------------- Waited----------------------SQL*Net message to client 4 0.00 0.00SQL*Net message from client 4 0.01 0.01********************************************************************************BEGIN prefetch_pkg.get_details ( :1, :2, :3 ) ; END;call count cpu elapsed disk query current rows------- -------------- ---------- ---------- ---------- --------------------Parse 2 0.00 0.00 0 0 0 0Execute 2 0.04 0.05 0 276 0 2Fetch 0 0.00 0.00 0 0 0 0------- -------------- ---------- ---------- ---------- --------------------total 4 0.04 0.05 0 276 0 2Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 62Elapsed times include waiting on following events:Event waited on Times Max. WaitTotal Waited---------------------------------------- Waited----------------------SQL*Net message to client 2 0.00 0.00SQL*Net message from client 2 0.00 0.00********************************************************************************select /*+(CONN=5)(CSTMT=20)*/x from t1 where rownum <= :p_fetch_size call count cpu elapsed disk query current rows------- -------------- ---------- ---------- ---------- --------------------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 2 0 0Fetch 21 0.00 0.00 0 23 0 100------- -------------- ---------- ---------- ---------- --------------------total 23 0.00 0.00 0 25 0 100Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 62Rows Row Source Operation---------------------------------------------------------- 100COUNT STOPKEY (cr=23 pr=0 pw=0 time=1063 us) 100 TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=454 us)Elapsed times include waiting on following events:Event waited on Times Max. WaitTotal Waited---------------------------------------- Waited----------------------SQL*Net message to client 21 0.00 0.00SQL*Net message from client 21 0.00 0.00********************************************************************************select /*+(CONN=5)(CSTMT=20)(RSET=50)*/x from t1 where rownum <= :p_fetch_size call count cpu elapsed disk query current rows------- -------------- ---------- ---------- ---------- --------------------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 2 0 0Fetch 3 0.00 0.00 0 5 0 100------- -------------- ---------- ---------- ---------- --------------------total 5 0.00 0.00 0 7 0 100Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 62Rows Row Source Operation---------------------------------------------------------- 100COUNT STOPKEY (cr=5 pr=0 pw=0 time=949 us) 100 TABLE ACCESS FULL T1 (cr=5 pr=0 pw=0 time=342 us)
代码已经很明显了，我就不多说了
页:
[1]