Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Benchmarking DBMS_OUTPUT.GET_LINE[S] from JDBC
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;
import java.util.stream.LongStream;
import java.util.stream.Stream;
public class Oracle {
static String lastMessage;
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.99.100:1521:ORCLCDB";
String user = "TEST";
String password = "TEST";
Properties properties = new Properties();
properties.setProperty("user", user);
properties.setProperty("password", password);
int max = 50;
long[] getLines1 = new long[max];
long[] getLines10 = new long[max];
long[] getLines1000 = new long[max];
long[] getLine = new long[max];
try (Connection c = DriverManager.getConnection(url, properties);
Statement s = c.createStatement()) {
for (int warmup = 0; warmup < 2; warmup++) {
for (int i = 0; i < max; i++) {
s.executeUpdate("begin dbms_output.enable(); end;");
String sql = "begin for i in 1 .. 100 loop dbms_output.put_line('Message ' || i); end loop; end;";
long t1 = System.nanoTime();
logGetLines(c, 100, 1, () -> s.executeUpdate(sql));
long t2 = System.nanoTime();
logGetLines(c, 100, 10, () -> s.executeUpdate(sql));
long t3 = System.nanoTime();
logGetLines(c, 100, 1000, () -> s.executeUpdate(sql));
long t4 = System.nanoTime();
logGetLine(c, 100, () -> s.executeUpdate(sql));
long t5 = System.nanoTime();
s.executeUpdate("begin dbms_output.disable(); end;");
if (warmup > 0) {
getLines1[i] = t2 - t1;
getLines10[i] = t3 - t2;
getLines1000[i] = t4 - t3;
getLine[i] = t5 - t4;
}
}
}
}
// LongSummaryStatistics{count=50, sum=69120455, min=1067521, average=1382409.100000, max=2454614}
// LongSummaryStatistics{count=50, sum=75671496, min=1127253, average=1513429.920000, max=2843735}
// LongSummaryStatistics{count=50, sum=76199718, min=1142187, average=1523994.360000, max=3054935}
// LongSummaryStatistics{count=50, sum=2088201423, min=33737827, average=41764028.460000, max=64498375}
System.out.println(LongStream.of(getLines1).summaryStatistics());
System.out.println(LongStream.of(getLines10).summaryStatistics());
System.out.println(LongStream.of(getLines1000).summaryStatistics());
System.out.println(LongStream.of(getLine).summaryStatistics());
} catch (Exception e) {
e.printStackTrace();
}
}
interface WhyUNoCheckedExceptionRunnable {
void run() throws Exception;
}
static void logGetLines(Connection connection, int size, int fetchSize, WhyUNoCheckedExceptionRunnable runnable) throws Exception {
try (Statement s = connection.createStatement()) {
runnable.run();
try (CallableStatement call = connection.prepareCall(
"declare "
+ " num integer := ?;"
+ "begin "
+ " dbms_output.get_lines(?, num);"
+ "end;"
)) {
call.setFetchSize(fetchSize);
call.setInt(1, size);
call.registerOutParameter(2, Types.ARRAY, "DBMSOUTPUT_LINESARRAY");
call.execute();
Array array = null;
try {
array = call.getArray(2);
Stream.of((Object[]) array.getArray())
.forEach(m -> { lastMessage = (String) m; });
}
finally {
if (array != null)
array.free();
}
}
}
}
static void logGetLine(Connection connection, int size, WhyUNoCheckedExceptionRunnable runnable) throws Exception {
try (Statement s = connection.createStatement()) {
runnable.run();
for (int i = 0; i < size; i++) {
try (CallableStatement call = connection.prepareCall(
"begin "
+ " dbms_output.get_line(?, ?);"
+ "end;"
)) {
call.registerOutParameter(1, Types.VARCHAR);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
lastMessage = call.getString(1);
}
}
}
}
}

vlsi commented Dec 18, 2017

@lukaseder, why do you call prepareCall in a loop inside logGetLine? Why don't you just prepare the call once and execute in a loop?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment