Marking sql statements for tracking and debugging
We have a large enterprise with many applications, both old and new, supported by Oracle 10G. When sessions or deadlocks are blocked and we receive trace reports, it would be very convenient to have the details embedded in sql so that we know both the application and specifically where it was executed. In addition to speeding up resolution times, it can help us find places where business processes have been stepping on each other. We have a few thoughts on how this might be achieved, but I'm wondering if this is already a problem. Any thoughts or details on any successful (or otherwise) attempts would be appreciated.
source to share
You can mark chunks of SQL executed through the Oracle DBMS_APPLICATION_INFO package . It works in any application language, runs in the very core of the database, and doesn't 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 for troubleshooting or tuning purposes.
source to share
We dynamically modify our SQL statements so that the command that executed them is in the comment at the beginning of the query. This works because we do our own transaction management and have a strict structure. But the basic code is simple (in Java ... not sure how will work with other languages):
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 unchanged and appears in the database when DBAs review queries. For this purpose, our command architecture maintains a streaming local set of command names. Also, our factory connection wraps the JDBC connection with our own connection object so that this code is present even if people are programming against the odd connection instance of the connection, instead of using the friendly helper methods we usually use.
source to share
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 by program column for your connections, it's simple ..
source to share
"we know both the application and in particular where it was executed from" You do not mention what is written in your applications. 11g gets more information when SQL is released from PLSQL programs that you might want to keep in mind depending on your environment, the possible timing of database updates, and the effort involved in code changes.If the same SQL is issued from multiple applications , it will (mostly) only have one entry in v $ sql, so you won't be able to track it down in one application.
source to share
I wanted to embed comments in SQL statements so that they are tagged completely via tkprof (10.2.0.4). It turned out that at some point, slash or double hyphen comments are removed. Then I used the fake sql prompt syntax that was saved via tkprof for example.
/*+ testrun=4A */
The database ignores it and it serves my needs.
source to share