Database dashlet

The Database dashlet (opened from the Cockpits sidebar) or the Database drill-down from the Transaction Flow of the Monitoring Overview provide an overview and in-depth information for database connection pool usage and SQL statement executions.

Logical views

The dashlet is divided into three logical views accessible either by clicking on a chart header label at the top of the dashlet, or a table tab at the bottom:

  • Response Time Hotspots tab ‐ Transaction Response Time Construction chart.
  • Execution Hotspots tab ‐ Percentage of Transactions Calling chart.
  • Pool Usage tab ‐ Pool Usage chart.

Each tab includes a table that provides statistics for the executions. You can select which statistics to display by right-clicking any column head to display the context menu and selecting or clearing the columns.

Response time HotSpots tab

The Transaction Response Time Contribution chart visualizes the contribution of database transactions to the average transaction response time. For example, for the transactions charted in the figure, connection acquisition and query execution result in 1.64ms response time per transaction, and database time contributes on average 2% to transaction time.

Response Time Hotspots tab
Response Time Hotspots tab

The table below the chart displays statistics for each database and connection pool transaction. The following columns are displayed by default.

Column Description
Database and Connection Pool Database connection, identified by the connection properties and connection pool name.
Bind Values The bind values for the SQL statement. This column appears if the analysis of bind values is enabled as described below.
RT/Trans Average response time per transaction, including times for connection acquisition, query preparation, and execution.
Acqu Time/Trans Connection acquisition time per transaction.
Executions/Trans Number of query executions per transaction.
Exec Avg Average execution time of the transactions involved in the execution.
Exec Min Minimum execution time of the transactions involved in the execution.
Exec Max Maximum execution time of the transactions involved in the execution.
Exec Total Total execution time for all transactions.
Executions Total number of executions.
Failed % Percentage of failing database calls.
Round Trips Number of round trips made to the database (call to and response from the database) during execution.

Execution HotSpots tab

The Percentage of Transactions Calling chart visualizes the percentage of transactions that are calling each statement or connection.
In the following figure, 3% of transactions are associated with the first connection pool.

Execution HotSpots tab
Execution HotSpots tab

The table below the chart displays statistics for the SQL execution. The following columns are displayed by default.

Column Description
SQL The SQL statement.
Bind Values The bind values for the SQL statement. This column appears if the analysis of bind values is enabled as described below.
Execs/calling Transaction Number of query executions per transaction that is calling a statement or connection.
Executions Total number of executions.
Preparations Number of prepareStatement calls.
Exec Avg Average execution time of the transactions involved in the execution.
Exec Min Minimum execution time of the transactions involved in the execution.
Exec Max Maximum execution time of the transactions involved in the execution.
Executions/Trans Number of query executions per transaction.
Exec Total Total execution time for all transactions.
Failed % Percentage of failing database calls.
Round Trips Number of round trips made to the database (call to and response from the database) during execution.

Pool Usage tab

The pool usage chart visualizes the maximum pool usage.

In the figure below, 3 out of 50 database pool connections are in use, which is 6% usage.

Pool Usage tab
Pool Usage tab

The table below the chart displays statistics for the pool usage. The following columns are displayed by default.

Column Description
Database and Connection Pool Database connection, identified by the connection properties and connection pool name.
Agent The Agent name.
Max Pool Usage Maximum pool usage.
Min Pool Usage Minimum pool usage.
Pool Size The configured maximum number of connections the connection pool uses.
Acqu Avg Average connection acquisition time per getConnection call.
Acqu Max Maximum connection acquisition time per getConnection call.
Acqu Total Total connection acquisition time for all connections.
Last Pool Usage Last pool usage.
Failed % Percentage of failing database calls.

History drilldown

Each connection pool in the chart legend provides a Show History link. Click this link to drill down to a dashboard that displays database execution time and execution count information.

Database History dashboard
Database History dashboard

Detailed dashboards are available for the following application servers:

  • WebSphere
  • WebLogic
  • JBoss

WebSphere

The following metrics are charted for WebSphere, as shown in the figure.

  • Pool Usage
  • Connection Acquisition
  • Wait Time
  • Thread Wait Count
  • Connection Usage Time
  • Prepared statement cache discard rate
  • Create vs Close Count

WebLogic

The following metrics are charted for WebLogic:

  • Connection Acquisition
  • Pool Usage
  • Delay Time
  • Thread Wait Count
  • Prepared statement cache discard rate
  • Failures to Reconnect Count
  • Created Connections
  • Leaked Connections

JBoss

The following metrics are charted for JBoss:

  • Pool Usage
  • Connection Usage

Sensor Configuration

Use the Configure Sensor Properties dialog box for the JDBC Sensor or ADO.NET Sensor to enable or disable aggregation, select whether bind values should be captured, and set the maximum text size of captured SQL commands. Commands that exceed the size limit are truncated.

Analyze bind values

Bind values can be shown on / apply to the Response Time Hotspots and Execution Hotspots sub-tabs (tabs below the SQL statement list) of the Database dashlet, but not the Pool Usage sub-tab. Also, the more recent approach is the Database drill-down from the Monitoring Overview.

You can show bind values by:

  • Selecting the Show Bind Values icon.  in the dashlet toolbar.
  • Selecting Show Bind Values from the dashlet's context menu.
  • An alternative would be to right-click on the column-header, select More and check the Bind Values column to be shown.
  • To show and group by bind values right-click in the SQL statement body and select Group by > SQL Statement and Bind Values.

If you see asterisks instead of certain bind values in the Bind values column, Settings menu > Dynatrace Server > Settings > Confidential Strings is turned on for that field.

Also, if transaction aggregation (same SQL statement called multiple times only listed once with the number of calls) is turned on in the (JDBC or ADO) sensor this is mutually exclusive with bind value display.

Dashlet display options

Filtering

Click an entry in the connection pool chart legend or click a bar graph to filter the table by the selected connection pool. To remove the filter, click the selected connection pool again.

Table structure modes

To select the structure mode, click the toolbar icon or right-click the dashlet and select the setting from the Structure Mode cascading menu:

  • Flat: Only SQL statement records are shown in table. This mode is useful to sort and compare SQL statements across various database connection pools.
  • Connection Pool: SQL records are grouped by their respective database connection pool. Click the small triangle to the right of a connection pool to expand it to display the SQL statements associated with it.

Connection pool information

JDBC

Application Server Support Level
WebSphere 6 or later, WebLogic 8 or later, JBoss 3 or later Full
Glass Fish 3.1 Open Source Edition Basic
Interstage 8 or later, Cosminexus 8 or later Not Required
OC4J 10g Not Available

PMI

In some environments (like IBM WebSphere) you have to enable PMI (Performance Monitoring Infrastructure) to get JDBC pool metrics.

.NET data

Database Support Level DB Type Host, Username, Password Number of Connections Used Pool Size
Oracle, SQL Server Full Check Check Check Depends on the connection string.
SQL Server CE Standard Check Check Depends on the connection string. Depends on the connection string.
ODBC, OLE DB Basic Check Depends on the connection string. Depends on the connection string. Depends on the connection string.

Tuning guidelines

Follow these guidelines to tune database performance based on the metrics previously described.

  • Use a Type1 driver (JDBC-ODBC bridge) only if you do not have a driver for your database.
  • For two-tier applications that communicate from a Java client to a database, use a Type2 driver, which gives better performance than a Type1 driver.
  • If your system communicates from the client to the database using a middleware server, use a Type3 driver, which gives better performance than Type1 and Type2 drivers.
  • Use a Type4 driver for two-tier and three-tier applications.
  • Use fetchDirection or fetchSize if your driver and database support them.
  • Use a connection pool to cache database connections,
  • Use batch transactions.
  • Use the suitable isolation level for your requirement, for example TRANSACTION_READ_UNCOMMITED for concurrent transaction-based applications or TRANSACTION_NONE for nonconcurrent transaction-based applications.
  • If a statement is executed more than once, use prepared statements.
  • Implement stored procedures to get a result from complex and multiple statements for a single request.
  • Use the batch update facility available in Statements.
  • Use the batch retrieval facility available in Statements or ResultSet.
  • Set up the proper direction for processing rows.
  • Use proper getter and setter methods.
  • Close ResultSets, Statements, and Connections as soon as the application does not need them anymore.
  • Write precise SQL queries.
  • Cache read-only and read-mostly table data.
  • If you retrieve a large amount of data, fetch small amounts iteratively rather than the whole amount of data at once.