Method R Discussion Group

Method R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle.

Discuss Method R products, techniques, and events.

Ask new questions or post your own responses.

How can I capture dynamic sql generated by java?

When I run trace with, for example... dbms_system.set_ev(18, 177, 10046, 12, ''); and later examine the trace, I can see some things, but not the critical things which are holding up this procedure.

The procedure is a pl/sql proc which makes a call to a java stored procedure which then does a lot of "things" and it is those "things" that I'm trying to capture.

Looking at the java code wasn't much use as it's mostly undocumented and even our in-house java folks say it's virtually impossible to follow. This makes things even more difficult as you can imagine, since I have had classroom java experience, but have not coded java extensively for a living.
Patrick McShea Send private email
Thursday, October 18, 2007

Is it possible that the Java Stored Procedure is making a connection to the database that is separate from the one that called it. That would explain why the SQL statements from the Java block weren't showing up in the trace file.

Just an idea.
Doug Gault Send private email
Friday, October 19, 2007
So should I just turn tracing on for the whole instance?

(the box I'm on is test, so it's just me)
Patrick McShea Send private email
Friday, October 19, 2007
As Doug mentioned the transaction may be creating/using more than one connection to the database.  You want to trace all possible connections.  If you connect via a connection pool then your transaction may use one or more of the pools established connections.  Therefore you will need to turn on trace for all the possible connections in the pool to guarantee you get your data.  However there is also a chance that your transaction will cause a new connection to be established from the pool.  So you may need to handle that situation.  You could do so with a logon trigger which turns on trace for the new connections. 

So you may need to alter tracing on for the existing connections in the pool and use a logon trigger to handle the new ones.  It really depends on the configuration. 

You could just use a logon trigger, if you shutdown the pool and then restart it prior to your transaction.  You will then need to turn trace off, or shutdown the pool when the transaction is done.
Rick Minutella Send private email
Friday, October 19, 2007
So Rick, is that a "yes" to "If I trace the entire instance will I capture all that?"

(I like binary answers to binary questions)

Patrick McShea Send private email
Saturday, October 20, 2007

Unfortunately, it is difficult to give a "Yes" or "No" answer without knowing the details. "The devil is in the details". So I guess my answer is a "maybe", but I would recommend a better way.  Your goal should be to examine only your transaction and only during the time of interest (when it is running poorly).  Turning on trace instance wide makes the goal often difficult to achieve.

I tried to provide information on why your trial likely did not work and what you can do to handle it. The info may also help you better accommodate future runs against other environments like Prod.

Tracing everything is generally not recommended and would definitely not be appropriate for most clients' situations, especially against Production.  You could try it I guess, but I can't tell you for sure if it will work because I don't know the details.  Details like: What version of Oracle are you using? How you are enabling trace for the whole instance? How are you running your test? What is the architecture of the environment, etc?  If your method of tracing the whole instance handles the situation I described previously, then you will likely get a trace containing your targeted area, but you will also likely get traces for other things that you are not interested in reviewing; thus making the analysis more difficult.

I can tell you that the method I mentioned will allow you to get a more precise trace targeting your transaction. This is because you will limit tracing to a targeted subset of the sessions which your transaction can use.

An even better way would be to instrument your code directly.  So another option may be that. You may want to take a look at the Hotsos ILO, if you are not familiar with it.
Rick Minutella Send private email
Monday, October 22, 2007
If you simply want to capture the SQL itself, you can use DriverManager's LogWriter (DriverManager.setLogWriter) or another tool such as P6Spy ( or JDBC Logger (
Jonah H. Harris Send private email
Friday, November 9, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Discussion Groups Main Page

Powered by FogBugz