Tagging sql statements for tracing and debugging

Question

We have a large enterprise consisting of many apps both old and new backed by Oracle 10G. When blocking sessions or deadlocks occur and we get trace reports it would be super handy to be able to have embedded some details in the sql so that we know both what app, and specifically where it was executed from. In addition to helping speed up the resolution time, it can help us find places where business processes were stepping on each other. We have a few thoughts on how this could be achieved, but I am curious if this is already a solved problem. Any thoughts or details on any successful (or otherwise) attempts would be appreciated.

Answer 1

We dynamically modify our SQL statements so that the command which executed them is in a comment at the start of the query. This works because we do our own transaction management and have a strict framework. But the base code is simple (in Java... not sure how other languages will deal with this):

String sql = "SELECT * FROM USERS WHERE ID = ?";
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(getComment() + sql);
// etc

String getComment() {
  return " /* " + getCommandName() + " */ ";
}

The JDBC driver passes the comment through intact and it shows up in the database when the DBAs are examining queries. Our command architecture maintains a thread-local stack of command names for this purpose. Also our connection factory wraps the JDBC connection with our own connection object so that this code is present even if people program against the bare-metal Connection instance, instead of using the friendly helper methods we normally use.

Answer 2

You can tag chunks of SQL being executed via the Oracle DBMS_APPLICATION_INFO package. This works in any application language, works within the database engine itself, and does not clutter your SQL statements with comments (smart comments are evil anyway). More importantly, Oracle's various tools recognize the data and help you use it when troubleshooting or tuning.

Answer 3

Properties jdbcProperties = new Properties();

this.jdbcProperties.put("user", userName);
this.jdbcProperties.put("password", password);
this.jdbcProperties.put("v$session.program", "YourApplicationName");
DriverManager.getConnection(url, jdbcProperties);

then check v$session by grouping against program column for your connections, that simple..

Answer 4

"we know both what app, and specifically where it was executed from" You don't mention what your applications are written in. 11g picks up additional information when the SQL is issued from PLSQL programs, which you might want to bear in mind depending on your environment, potential DB upgrade timeframes and the effort involved in code changes. If the same SQL is issued from multiple applications, it will (mostly) only have a single entry in v$sql so you might not be able to trace it back to one app.