Unknown SQL in Stored Procedure

Products

• CA Application Performance Management (APM / Wily / Introscope)

Issue/Introduction

Using Oracle 19c JDBC driver and 10.7 HF85 which supports this to address issues with Unknown SQL

Previously Unknown sql was related to the following class: oracle.jdbc.driver.T4CPreparedStatement .

Now, there are unknown sql with stored procedures related to the following class: oracle.jdbc.driver.T4CCallableStatement.

This is for an application running in Weblogic for an application deployed in Oracle Webcenter sites.

 

Environment

Release : 10.7.0

Component : Introscope

Cause

It is apparent that statement->sql-string mapping tracer did not see callable statement and thus it could not return normalized sql statement for metric naming purpose.

This could happen if an oracle specific, non-standard method of db connection was called to create CallableStatement instance.

 

Resolution

These are suggestions that can be applied to any agent that already supports Oracle 19c, this includes 10.7 HF85 and 21.3 and higher

 

1. Append following directives to wily/releases/<release>/core/config/sqlagent.pbd

TraceOneMethodWithParametersIfFlagged: SQLAgentConnections prepareCall(Ljava/lang/String;Ljava/util/Properties;)Ljava/sql/CallableStatement; StatementToSQLMappingTracer SQLAgent
#TraceOneMethodWithParametersIfFlagged: SQLAgentConnections prepareCallWithKey(Ljava/lang/String;)Ljava/sql/CallableStatement; StatementToSQLMappingTracer SQLAgent
#TraceOneMethodWithParametersIfFlagged: SQLAgentConnections getCallWithKey(Ljava/lang/String;)Ljava/sql/CallableStatement; StatementToSQLMappingTracer SQLAgent

2. Test with the initial configuration, if there are still Unknown SQL metrics returned, please uncomment the two commented lines as well

TraceOneMethodWithParametersIfFlagged: SQLAgentConnections prepareCall(Ljava/lang/String;Ljava/util/Properties;)Ljava/sql/CallableStatement; StatementToSQLMappingTracer SQLAgent
TraceOneMethodWithParametersIfFlagged: SQLAgentConnections prepareCallWithKey(Ljava/lang/String;)Ljava/sql/CallableStatement; StatementToSQLMappingTracer SQLAgent
TraceOneMethodWithParametersIfFlagged: SQLAgentConnections getCallWithKey(Ljava/lang/String;)Ljava/sql/CallableStatement; StatementToSQLMappingTracer SQLAgent