import java.util.ArrayList;
import java.util.Date;
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import java.util.concurrent.atomic.*;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
public class UCPDemo extends Thread
{
private static PoolDataSource pds = null;
private static int NUM_OF_THREADS = 3;
private static int tx_cnt = 100;
private static int mode = 1;
private static boolean dbg = false;
private static long start_t = 0;
private static long end_t = 0;
private static long idx = 0;
private static AtomicLong counter = new AtomicLong();
private String host_n = "";
private String inst_n = "";
private static String svc = "";
private static String url = "";
private int thr_id = -1;
private static String sql_stmt = "";
private static String sql_stmt_mode1 = "insert into rac_perftest values (t1_seq.nextval, ?, ?, sysdate) ";
private static String sql_stmt_mode2 = "insert into rac_perftest values (?, ?, ?, sysdate) ";
private long max_ct = 0;
private long min_ct = 99999;
public UCPDemo(int tid) throws SQLException
{
thr_id = tid;
// Create pool-enabled data source instance.
//
}
public static void create_pool() throws SQLException
{
System.out.println(" -> Create UCP POOL " );
pds = PoolDataSourceFactory.getPoolDataSource();
// PoolDataSource and UCP configuration
//set the connection properties on the data source and pool properties
pds.setUser("scott");
pds.setPassword("tiger");
pds.setURL(url);
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setInitialPoolSize(10);
pds.setMinPoolSize(3);
pds.setMaxPoolSize(100);
}
public void displayPoolDetails () throws SQLException
{
System.out.println("-----------");
System.out.println("NumberOfAvailableConnections: " + pds.getAvailableConnectionsCount());
System.out.println("BorrowedConnectionsCount: " + pds.getBorrowedConnectionsCount());
System.out.println("-----------");
}
static void set_ev(Connection c) throws Exception
{
Statement stmt = c.createStatement();
stmt.executeQuery ("alter session set events='10046 trace name context forever, level 12' ");
stmt.close();
}
//public void run () throws Exception
public void run ()
{
int rc = 0;
try
{
Connection conn = pds.getConnection();
//System.out.println(" --> Retrieved a connection from pool");
print_c(conn);
if ( dbg )
{
System.out.println(" --> Enabling 10046 tracing");
set_ev(conn);
}
long thr_start = System.currentTimeMillis( );
for (rc=0; rc<tx_cnt; rc++ )
do_insert(conn, (rc+1 ));
long thr_end = System.currentTimeMillis( );
conn.close();
System.out.printf("\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b");
System.out.printf("Thread_id: %3d - Instance: %s - Row Inserted : %5d - MIN Commit time [ms]: %4d - MAX Commit time [ms]: %4d - Thread TPS: %5.2f\n" ,
thr_id,inst_n, tx_cnt, min_ct, max_ct, (float) tx_cnt*1000/(thr_end-thr_start) );
//System.out.println("\nReleased connection to pool - Instance: " + inst_n + " - Records inserted: " + rc);
} catch (Exception e)
{
System.out.println("Thread got Exception: " + e);
e.printStackTrace();
return;
}
//finally display pool details again
//displayPoolDetails();
}
public synchronized long incrementAndGet()
{
idx++;
return idx;
}
public void do_insert(Connection c, int tx_id ) throws Exception
{
long p_idx = 0;
long s_commit = 0;
long e_commit = 0;
long diff_commit = 9999999;
PreparedStatement pstmt = c.prepareStatement (sql_stmt);
if ( mode == 1 )
{
pstmt.setString (1, inst_n);
pstmt.setString (2, host_n);
}
else
{
p_idx = counter.incrementAndGet();
pstmt.setLong (1, p_idx);
pstmt.setString (2, inst_n);
pstmt.setString (3, host_n);
}
pstmt.execute ();
pstmt.close();
s_commit = System.currentTimeMillis( );
c.commit();
e_commit = System.currentTimeMillis( );
diff_commit = e_commit - s_commit;
if (diff_commit > max_ct )
max_ct = diff_commit;
if (diff_commit < min_ct & diff_commit > 0 )
min_ct = diff_commit;
if ( (tx_id % 50) ==0 )
System.out.printf("\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\bTX : %5d - min_ct: %4d - max_ct: %4d" ,
tx_id, min_ct, max_ct );
}
public void print_c(Connection c) throws Exception
{
String query =
"select 'Connected at: ' || to_char(sysdate,'HH24:MI:SS') || ' to instance: ' || instance_name || ' at host: ' || host_name, instance_name, host_name from v$instance " ;
Statement stmt = c.createStatement ();
ResultSet rset = stmt.executeQuery (query);
rset.next ();
System.out.println(rset.getString (1) );
inst_n = rset.getString (2) ;
host_n = rset.getString (3) ;
stmt.close();
}
public static void main(String[] args) throws Exception
{
if (args.length == 0 )
{
System.out.println("Usage: java UCPDemo SERVICE Number_of_threads Rows_to_be_inserte With/Without_sequence");
System.out.println(" : java UCPDemo grac4 10 500 -noseq -nodebug");
System.out.println(" : java UCPDemo grac41 10 500 -seq -debug");
System.exit(1);
}
if (args.length >= 1)
{
svc = args[0];
url = "jdbc:oracle:thin:@//grac4-scan.grid4.example.com:1521/"+svc;
}
if (args.length >= 2)
NUM_OF_THREADS = Integer.parseInt (args[1]);
if (args.length >= 3)
tx_cnt = Integer.parseInt (args[2]);
sql_stmt = sql_stmt_mode1;
if (args.length >= 4)
if ( args[3].equals("-noseq"))
{
mode = 2;
sql_stmt = sql_stmt_mode2;
}
if (args.length >= 5)
if ( args[4].equals("-debug"))
dbg = true;
System.out.println("Started at: " + new Date() + " - URL: " + url + " - Number of Threads: " + NUM_OF_THREADS + " - tx_cnt:" + tx_cnt +
" - mode: " + mode + " Debug mode: " + dbg );
System.out.println(" -> SQL: " + sql_stmt);
Thread[] threadList = new Thread[NUM_OF_THREADS];
create_pool();
start_t = System.currentTimeMillis( );
for (int i = 0; i < NUM_OF_THREADS; i++)
{
threadList[i] = new UCPDemo(i+1);
threadList[i].start();
}
for (int i = 0; i < NUM_OF_THREADS; i++)
{
threadList[i].join();
}
end_t = System.currentTimeMillis( );
long diff = end_t - start_t;
System.out.printf("Runtime : " + (diff/1000) + " [s] - TPS: %5.2f\n",(float) tx_cnt*1000*NUM_OF_THREADS/diff );
}
}