When attempting to profile a Hibernate based application, I found a statement that was incredibly slow and caused the system to basically halt for a few seconds before resuming execution. I wanted to profile it at the database level, and the best tool for the job is Oracle’s own tkprof.
The input for tkprof is a session trace file, and enabling one is a bit tricky. The reason – a JavaEE application, with multiple threads, have multiple connections, multiple database sessions, and the SQL_TRACE is on a per session level (I didn’t want to configure it for the entire database – the trace file would be totally unusable…)
So, I took the code and ran it in a standalone Java application, and enabled SQL Trace. Here’s how:
package com.tona.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import oracle.jdbc.OracleCallableStatement; public class TestJDBC { public static void main(String[] args) throws Exception { Connection c = DriverManager.getConnection(JDBC_CONNECTION_STRING); // Set SQL_Trace to on Statement stmt = c.createStatement(); stmt.executeUpdate("ALTER SESSION SET SQL_TRACE=TRUE"); stmt.close(); // Set SQL Trace location stmt = c.createStatement(); stmt.executeUpdate("alter system set USER_DUMP_DEST='/tmp'"); stmt.close(); // JDBC logic comes here... c.close(); } }
Note
Changing the USER_DUMP_DEST
parameter did not have any effect, and the SQL_TRACE was written to the default trace log directory on the server (in my case it was ./u01/app/oracle/diag/rdbms/SID/SID/trace
)