Skip to main content

Instance Dashboard

The database instance dashboard screen gives a complete performance overview of the database instance, the activity within it and the host it is running on. These are the main tabs at the top of the screen:

  • Database Activity - DB activity by Statements and many more dimensions.
  • Activity (Grouped Statements) - DB activity by Grouped Statements and many more dimensions.
  • Host Metrics - Host metrics such as CPU, I/O and memory for the DB host.
  • Database Statistics - Database statistics such as connections, session and more.
  • SQL Statistics - More Statement metrics such as Logical reads, writes and more.
  • Events - Events for the selected instance such as changes, custom events or alerts.
  • Blocking Sessions - Blocking sessions showing locking trees of who was holding locks and who was locked.
  • Deadlocks - List of Deadlocks for the selected time period.

/img/database-instance-screen.jpg

Database Activity Tab​

KPI Widgets​

The key performance indicators at the top of the screen are there to show top level metrics and alerts.

/img/KPI-widgets.jpg

  1. DB Time - This is the total amount of time spent executing SQL statements within the selected period.
  2. Executions - This is the total number of SQL executions within the selected period.
  3. Average Time - This is the average SQL response time within the selected period.
  4. Change History - This shows 2 values: 1) the total number of changes within your environment such as schema changes or database parameter changes within the time range selected and 2) any custom events you have created for things like code releases or infrastructure changes.

Performance over time​

Time Spent and Executions over time for the period selected. If any changes have been detected, or manually added, they will be displayed as annotated markers on the timeline.

/img/perf-over-time.jpg

Statements over time​

From the menu in the top right of the chart you can switch to the statements over time view. The statements in this bar chart are clickable to drill down to the statement details.

/img/perf-over-time.jpg

Wait states over time​

From the menu in the top right of the chart you can switch to the wait states over time view.

/img/perf-over-time.jpg

SQL & Wait state breakdown​

The first ring chart shows the top 10 SQL statements by total DB time. The colours match those in the table below. The second ring chart shows a breakdown of the top 10 wait states.

/img/wait-state-breakdown.jpg

Lower tabs​

The lower tabs show the top SQL, Databases, Sessions, Programs, Client, Batches, Users as well as Changes. Note that some columns are not available depending on which database type you are looking at; for example, programs is not available for MySql and batches is only there for SQL Server. Each of these tables may be sorted by clicking in the column headings which have the arrow pairs. After sorting, the highlighted arrow shows the direction of sort.

/img/top-sql.jpg

Top SQL​

Top SQL statements sorted by total DB time. An inline bar chart is displayed for any statements whose weighted % is more than 5% and coloured to match the ring chart above. Clicking on the statement id or the statement itself will then take you on to the SQL view for that statement.

Hovering over the statement will display a scrollable tooltip where you can see up to 2000 characters of the SQL statement.

Top Waits​

Top wait events sorted by the total DB time. For each wait event, there is a button, which lets you click through to the Knowledge-base to find out more information about the wait event and recommended actions in order to to reduce it.

/img/kb-button.jpg

Top Databases​

Top Databases sorted by total DB time. If you have enabled the Instana integration then a button will be shown here if the matching database is found in Instana.

Top Sessions​

Top Sessions sorted by total DB time.

Top Clients​

Top Clients sorted by total DB time.

Top Users​

Top Users sorted by total DB time.

Activity (Group Statements) Tab​

This contains the same information as the Database Activity tab except that SQL Statements are normalised by stripping out any literals, so that similar statements which only differ by one or more literal values, are grouped together as a single entity.

For example these 3 statements below:

SELECT * FROM DESTINATIONS WHERE COUNTRY = 'London';
SELECT * FROM DESTINATIONS WHERE COUNTRY = 'Paris';
SELECT * FROM DESTINATIONS WHERE COUNTRY = 'New York';

would get grouped together as:

SELECT * FROM DESTINATIONS WHERE COUNTRY = ?

Having the grouped statements view means that you can see the impact of statements which might individually only be small resource consumers, but when grouped together, may have a substantial impact on database performance.

You can click to the Statements tab in the table in the lower half of the screen to see the individual statements within the group.

For SQL Server we also have Grouped Batches which follow the same rules as Grouped Statements.

Host Metrics Tab​

KPI Widgets​

The key performance indicators at the top of the screen are there to show top level metrics and alerts.

/img/KPI-widgets-host-metrics.jpg

CPU and Memory over time​

These charts show the CPU and memory usage over time for any hosts that are linked to this particular database instance.

/img/host-performance-charts.jpg

Monitored hosts table​

This table show the host metrics for any hosts that are linked to this particular database instance.

/img/host-performance-table.jpg

Database Statistics​

This screen provides time-series metrics at the database instance or cluster level. These metrics are the key performance metrics for each database platform and contain metrics related to things such as connections, I/O, executions by type, commits, rollbacks, parsing.

Database statistics tab

These are some of the metrics that are provided for each platform:

MySQL​

  • Open_files
  • Open_tables
  • Com_delete
  • Com_delete_multi
  • Com_insert
  • Com_insert_select
  • Com_replace
  • Com_replace_select
  • Com_select
  • Com_update
  • Connections
  • Opened_files
  • Opened_tables
  • Qcache_hits
  • Queries
  • Questions

PostgresSQL​

  • database_size
  • numbackends
  • blks_hit
  • blks_read
  • confl_bufferpin
  • confl_deadlock
  • confl_lock
  • confl_snapshot
  • confl_tablespace
  • tup_deleted
  • tup_fetched
  • tup_inserted
  • tup_returned
  • tup_updated
  • xact_commit
  • xact_rollback

Oracle​

  • DB time
  • db block changes
  • execute count
  • logons cumulative
  • parse count (hard)
  • parse count (total)
  • parse time cpu
  • parse time elapsed
  • physical reads
  • physical writes
  • redo size
  • session logical reads
  • transaction rollbacks
  • user calls

SQL Server​

  • User Connections
  • Batch Requests/sec
  • Errors/sec
  • Lock Requests/sec
  • Lock Timeouts (timeout > 0)/sec
  • Lock Timeouts/sec
  • Lock Wait Time (ms)
  • Lock Waits/sec
  • Number of Deadlocks/sec
  • Page lookups/sec
  • Page reads/sec
  • Page writes/sec
  • Transactions/sec

CockroachDB​

  • capacity.available
  • capacity.used
  • liveness.livenodes
  • node-id
  • replicas
  • sql.conns
  • sql.service.latency-p99
  • sql.delete.count
  • sql.insert.count
  • sql.new_conns
  • sql.query.count
  • sql.select.count
  • sql.update.count
  • txn.refresh.auto_retries

IBM Db2​

  • APPLS_CUR_CONS
  • APPLS_IN_DB2
  • CONNECTIONS_TOP
  • COORD_AGENTS_TOP
  • APP_RQSTS_COMPLETED_TOTAL
  • DEADLOCKS
  • DYNAMIC_SQL_STMTS
  • FAILED_SQL_STMTS
  • LOCK_WAITS
  • LOCK_WAIT_TIME
  • SELECT_SQL_STMTS
  • STATIC_SQL_STMTS
  • TOTAL_CONS

Informix​

  • activesessions
  • btradata
  • btraidx
  • buffwts
  • bufreads
  • bufwrites
  • chunkwrites
  • ckptwts
  • compress
  • deadlks
  • disksorts
  • dpra
  • dskreads
  • dskwrites
  • executions
  • fgwrites
  • flushes
  • idlesessions
  • isamtot
  • iscommits
  • isdeletes
  • isopens
  • isreads
  • isrewrites
  • isrollbacks
  • isstarts
  • iswrites
  • latchwts
  • lktouts
  • ll_bfwait_ms
  • ll_iowait_ms
  • ll_nbfwaits
  • ll_niowaits
  • llgpagewrites
  • llgrecs
  • llgwrites
  • lockreqs
  • lockwts
  • lruwrites
  • maxconnections
  • maxsortspace
  • memsorts
  • num_cpu_ready
  • num_ready
  • numckpts
  • ovlock
  • ovtrans
  • ovuser
  • pagreads
  • pagwrites
  • plgpagewrites
  • plgwrites
  • rapgs_used
  • seqscans
  • totalsorts

SAP HANA​

  • activesessions
  • commit_count
  • compilation_count
  • current_commit_rate
  • current_compilation_rate
  • current_execution_rate
  • current_memory_usage_rate
  • current_rollback_rate
  • current_transaction_rate
  • current_update_transaction_rate
  • execution_count
  • executions
  • idlesessions
  • maxconnections
  • peak_commit_rate
  • peak_compilation_rate
  • peak_execution_rate
  • peak_memory_usage_rate
  • peak_rollback_rate
  • peak_transaction_rate
  • peak_update_transaction_rate
  • rollback_count

SQL Statistics​

This screen provides more statistics at the SQL Statement level such as CPU time, Physical Reads, Logical Reads and Logical Writes. It is available for Oracle, SQL Server, IBM Db2, PostgreSQL 13 and above, MySQL and CockroachDB 21.2 and above.

SQL statistics tab

Events​

The screen shows any events (Changes, Custom events or Alerts) for the selected instance.

Instance events tab

Blocking Sessions​

The blocking sessions tab shows the sessions which had held locks during the selected period. The top level shows the blockers with a green dot next to the session ID which indicates that they themselves were a blocker but where not blocked. Clicking the '+' icon to expand the tree will show further sessions with a red dot indicating that they were blocked by the session higher up the tree. Those sessions may in turn, be blocking other sessions.

The columns show the Session ID, Client IP, Username, Program and which SQL Statement they were running when they were blocked. It also shows the lock wait event that they were waiting on and the amount of time they were blocked for. Clicking on the Session ID will take you to a historical view of that session. Clicking on the SQL Statement will take you to a historical view of that statements performance. And clicking on the wait event name will take you to the knowledge-base where you can read more about the wait event and what can be done to reduce it.

Blocking Sessions tab

Deadlocks​

Deadlock capture is only available for SQL Server and Oracle.

A database deadlock occurs when two or more transactions or processes within a database system are waiting for each other to release resources, resulting in a state of mutual deadlock where none of the transactions can proceed. This situation can lead to a system freeze or significant performance degradation. DBmarlin captures the deadlock events as well as the sessions involved to help remediate the problem. Clicking into the session id will take you to the Activity screen filtered on that session id. The download button will download an XML file with the full details of the deadlock.

Deadlocks tab