Database sensor

Relevant sensor types

For database tracing inspectIT provides four sensor types:

Example sensor type definition (not necessary to change!)

method-sensor-type jdbc-connection info.novatec.inspectit.agent.sensor.method.jdbc.ConnectionSensor MIN
method-sensor-type jdbc-prepared-statement info.novatec.inspectit.agent.sensor.method.jdbc.PreparedStatementSensor MIN
method-sensor-type jdbc-prepared-statement-parameter info.novatec.inspectit.agent.sensor.method.jdbc.PreparedStatementParameterSensor MIN
method-sensor-type jdbc-statement info.novatec.inspectit.agent.sensor.method.jdbc.StatementSensor MIN
method-sensor-type jdbc-connection-meta-data info.novatec.inspectit.agent.sensor.method.jdbc.ConnectionMetaDataSensor MIN

Prepared Statement Parameter Replacement

Prepared statements are statements compiled against the database that include placeholders which are later set by the application. The benefit is (among other things) that the execution path of the statement is already available at the database and thus the statement is usually executed faster than unprepared statements. Prepared Statements thus have the format "SELECT * FROM table WHERE id = ?".

For a detailed analysis of slow prepared statements a replacement of the placeholder with the concrete value of the statement the application provided prior to execution is often helpful. Prepared statement parameter replacement provide exactly this feature. Returning to the example statement from above, activating prepared statement replacement would replace the placeholder with the value the application provided and lists the statement (for example) as "SELECT * FROM table WHERE id = 1".

Activating/Deactivating

Prepared statement parameter replacement is provided by the Prepared Statement Parameter Sensor. The default configuration file that inspectIT ships already provides the necessary settings that allow capturing of parameters of prepared statements.

To enable parameter replacement the sensor assignment of the prepared statement parameter sensor type to the predefined methods must be enabled (simply remove the comments "#" from the lines in the configuration file). To disable parameter replacement, the sensor assignment needs to be removed (or commented). Enabling and disabling is only active after a restart of the application!

# SQL Prepared Statement Parameter Replacement
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setBigDecimal(int,java.math.BigDecimal) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setBoolean(int,boolean) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setByte(int,byte) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setDate(int,java.sql.Date) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setDouble(int,double) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setFloat(int,float) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setInt(int,int) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setLong(int,long) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setShort(int,short) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setString(int,java.lang.String) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setTime(int,java.sql.Time) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setTimestamp(int,java.sql.Timestamp) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setObject(int,java.lang.Object) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement setArray(int,java.sql.Array) interface=true
#sensor jdbc-prepared-statement-parameter java.sql.PreparedStatement clearParameters() interface=true

Performance impact

The performance impact of activating the prepared statement parameter replacement is medium-high and should not be used for productive monitoring without testing it on an integration platform before. The reason is, that the number of instrumented methods and called methods for each prepared statement invocation is increased by one for each parameter. If the application uses prepared statements having long IN clauses it can easily happen that instead of just one quick call (the query itself) 100 or more calls are necessary to trace the statement.

Other options

String length

^{from version 1.3}

This sensor enables the limitations of the string values collected. The database sensor concretely can limit the maximum size of the SQL strings collected. The following definition will, for example, limit all the strings collected by the sensor to 500 characters:

method-sensor-type jdbc-prepared-statement info.novatec.inspectit.agent.sensor.method.jdbc.PreparedStatementSensor MIN stringLength=500

You can read more about string length limitations on Sensor type definition page.