RSS Feed

Method R Discussion Group

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,

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.

So should I just turn tracing on for the whole instance?

(the box I'm on is test, so it's just me)

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.

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,

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.

If you simply want to capture the SQL itself, you can use DriverManager's LogWriter (DriverManager.setLogWriter) or another tool such as P6Spy (http://www.p6spy.com/) or JDBC Logger (http://jdbclogger.sourceforge.net/index.html).