image_print query_result_cache.java

 import java.sql.*;
 
 public class result_cache {
 
 	public static void main(String args[])
 	{
 		try {
 
 			Class.forName("oracle.jdbc.driver.OracleDriver");
 			Connection con = DriverManager.getConnection("jdbc:oracle:thin:
                                         @localhost:1521:ORA111", "SCOTT", "TIGER");
 			con.setAutoCommit(true);
 			ResultSet rs = null; 
 			String sql = null;
 			if (args[0].equals("Cache")) {
 				sql = "SELECT emp.deptno , sal , dname , loc "  
 					+"FROM   dept , (SELECT /*+ result_cache */ "
 					+"                             deptno ,AVG( sal ) sal "
 					+"                    FROM   emp "
 					+"                    GROUP  BY deptno ) emp "
 					+"WHERE  dept.deptno = emp.deptno";
 			} else {
 				sql = "SELECT emp.deptno , sal , dname , loc "  
 					+"FROM   dept , (SELECT  deptno ,AVG( sal ) sal "
 					+"                    FROM   emp "
 					+"                    GROUP  BY deptno ) emp "
 					+"WHERE  dept.deptno = emp.deptno";                    	
 			} 	
 
 			PreparedStatement ps = con.prepareStatement(sql);
 			long chktime = System.currentTimeMillis();	
 			for (int i =1; i<= 10000; i++) {
 				rs = ps.executeQuery();
 				while (rs.next()) {}
 			}
 			chktime = System.currentTimeMillis()-chktime;	
 			ps.close();
 			ps = con.prepareStatement("select v.name, m.value from v$mystat m, 
                                         v$statname v where m.statistic# = v.statistic# ");
 			rs = ps.executeQuery();
 			while (rs.next()) {
 				System.out.println(rs.getString(1)+ " ; " +rs.getDouble(2));
 			}   
 			System.out.println("\n\n\n");
 			ps = con.prepareStatement("select event, TOTAL_WAITS, TIME_WAITED from 
                                                  v$session_event where time_waited > 0 and 
                                                  wait_class <> 'Idle'  and sid = userenv('sid') 
                                                  order by 3 desc ");
 			rs = ps.executeQuery();
 			while (rs.next()) {
 				System.out.println(rs.getString(1)+ " ; " 
                                + rs.getLong(2)+"\t"+rs.getLong(3));
 			}    	
 			System.out.println("Elapsed_Time : "+chktime);
 		} catch(Exception e) {
 			e.printStackTrace();
 		}
 
 	}
 }