how to enable SQL trace for a closed source app running on jboss and using ojdbc14.jar

Question

Hello Hackers! I have a java app (deployed on Jboss/ WebLogic) that I have to use, it uses ojdbc14.jar to talk to oracle, it's is a neat app but the documentation sucks, a lot of magic happens behind the scenes. I need to trace all SQL calls.

I have complete control on DB, App servers, DBA rights, admin rights everything.

How do I do it? Should I learn AOP? Should I research on Jdbc wrappers. Please provide a hack.

Answer 1

You could use something like P6Spy, a JDBC Proxy Driver (that acts like a Decorator):

P6Spy is an open source framework for applications to intercept and optionally modify database statements. The P6Spy distribution includes P6Log, which intercepts and logs the database statements of any application that uses JDBC. This application is particularly useful for developers to monitor the SQL statements produced by EJB servers, enabling the developer to write code that achieves maximum efficiency on the server. P6Spy is designed to be installed in minutes and requires no code changes.

Another option would be to activate SQL logging at Oracle level (see Using Application Tracing Tools and DBMS_MONITOR).

Answer 2

Here's the usual options:

• JDBC proxy, such as P6Spy mentioned by @Pascal
• App level logging. This may/may not be possible. If it's a Hibernate app, you can turn in on.
• TCP proxy (my favourite). Take a tool like TcpTrace or Balance, and pipe all your traffic through it. This is my favorite, as this captures everything.
• Ethernet. WireShark can show you the traffic - requires setting up a few filters, but you don't have to modify your applications config.
• Oracle logging. This can require a lot of server resources, and takes a bit of setup.

Answer 3

On Weblogic you should be able to enable jdbc debug via the console if you are using a connection pool for your connections. See

http://stackoverflow.com/questions/792741/logging-jpa-sql-with-weblogic-10-3

For step by step, only skip the JPA and look for a different option.

Answer 4

As an Oracle guy, I'd just turn on tracing on the database.

Of course in 10g there is quite a lot of tracing already happening -- the Automatic Workload Repository (AWR) is probably logging the significant SQL statements already.

If you use Enterprise manager then you can see live events in the database: http://download.oracle.com/docs/cd/B19306%5F01/server.102/b28051/tdppt%5Frealtime.htm#TDPPT033

As Pascal says, DBMS_MONITOR is also a good tool for individual session monitoring: http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14211/sqltrace.htm#CHDHEJDG

Answer 5

To add another option with a wider perspective, you could use a JavaEE profiler probe in your application server and configure it to log SQL statements.

I've personally used HP Diagnostics (free evaluation limited for 5 threads) for this and it worked great.